How to insert data in sql server by using code stage in Blue Prism, Can anyone help me in this please?

Jay Rawat

New Member
I have output collection and i want to inset it into database by using code stage. Please help me in this.. Thanks..!
 
Last edited:

LeoLugo

Member
I’ve never attempted this but I have a question. Is there a reason you are trying to do this in a code stage as opposed to using the Data - SQL Server VBO?
 

sivagelli

Well-Known Member
I have output collection and i want to inset it into database by using code stage. Please help me in this.. Thanks..!
sqlBulkCopy is of help in case of writing to SQL server from a Collection. Using dqlBulkCopy there is no need to write a stored procedure.
Refer to the link and get started.

@LeoLugo Using either Data- OLEDB or Data- SQL Server VBO, the default actions will not write back to the tables from collection. You can execute queries/stored procedures to make changes to DB tables.
I’ve never attempted this but I have a question. Is there a reason you are trying to do this in a code stage as opposed to using the Data - SQL Server VBO?
 

Jay Rawat

New Member
I’ve never attempted this but I have a question. Is there a reason you are trying to do this in a code stage as opposed to using the Data - SQL Server VBO?


Hi Leo, Thanks for your reply..!

This is a client requirement not to use SQL SERVER VBO, I have output excel collection and i want save that collection into sql database by using code stage.
 

Jay Rawat

New Member
sqlBulkCopy is of help in case of writing to SQL server from a Collection. Using dqlBulkCopy there is no need to write a stored procedure.
Refer to the link and get started.

@LeoLugo Using either Data- OLEDB or Data- SQL Server VBO, the default actions will not write back to the tables from collection. You can execute queries/stored procedures to make changes to DB tables.

Hello Shiva, Thank You so much for your reply..!

I will try it out this as you suggested in the link but i am stuck one part i have a excel data in output collection and i want to save that data into sql database.
I wrote this code in code stage for this but occurring lots of namespaces error, I included only System.Data and System.Data.SqlClient, Please see below error screenshot and Suggest..! Please help me out if you have any idea about this.. Thanks..!

var SQL = "";
var SQLROWTemp = "";
var SQLColumnTemp = "";
var RecCount = 0;

try
{

foreach (DataRow row in InputData)
{
RecCount += 1;
SQLROWTemp += "(";
SQLColumnTemp = "";
SQL = "";

SQL += "INSERT INTO " + TableName + " (";
foreach (DataColumn column in InputData)
{
SQL += column.ColumnName + ",";
}

SQL = SQL.Substring(0, SQL.Length - 1) + ") Values";

foreach (DataColumn column in InputData)
{
SQLColumnTemp += "'" + row[column.ColumnName].ToString().TrimEnd().Replace("'", "''") + "',";

}

SQLROWTemp += SQLColumnTemp.Substring(0, SQLColumnTemp.Length - 1) + ")";
FinalSQL = FinalSQL + SQL + SQLROWTemp;
SQLROWTemp = "";

}

SqlConnection conn = new SqlConnection();
conn.ConnectionString =
"Data Source=DESKTOP-6KQ82F6;" +
"Initial Catalog=Demo;" +
"Integrated Security=SSPI;";
conn.Open();

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
{
bulkCopy.DestinationTableName = TableName;

try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(InputData);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}

catch (Exception)
{

throw;
}

}


View attachment Test1.jpg
 

Attachments

  • Test2.jpg
    153.4 KB · Views: 77
  • Test3.jpg
    126.8 KB · Views: 48
Last edited:

Jay Rawat

New Member

Attachments

  • Input-Parameter.JPG
    52.6 KB · Views: 90
  • FlowOf-Object.JPG
    50 KB · Views: 71
  • Initialize-Namespace.JPG
    47.1 KB · Views: 65
  • Output-Parameter.JPG
    50.1 KB · Views: 60

sivagelli

Well-Known Member
Looking at the code snippet, you also have to pass Server and Database names as inputs. Have you?
Also, change the input parameter name, InputData to DataCollection.

@naveedraza can you share more details of your code usage?
 

Jay Rawat

New Member
@sivagelli , Yes i created the data items for server & database details but facing an same issue. some time it is saying the code class is not instance of an Object.
 
Top