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