Insert multiple rows in SQL as sqlparameter

Question

I have 1000 rows in my dt data table, and I want to insert all this data into my sql table in one shot. I know how to do this with a for loop ... but I wonder if there are others the best way to do this is all the rows for datatbase in a single frame. My current code to insert this data is as follows

  DataTable dt = result.Tables[0]; SqlHelper.ExecuteScalar(GlobalSettings.DbDSN, CommandType.Text, "INSERT INTO tbl_Projects (Project,Owner,Consultant ,Contractor,Value ,Level1,Level2 ,Status ,Country ,CreatedDate ,CreatedByID ,CreatedByName) VALUES (@Project,@Owner,@Consultant ,@Contractor,@Value ,@Level1,@Level2 ,@Status ,@Country ,@CreatedDate ,@CreatedByID ,@CreatedByName)", new SqlParameter("@Project",dt.Rows[0].ItemArray[0]), new SqlParameter("@Owner", dt.Rows[0].ItemArray[1]), new SqlParameter("@Consultant", dt.Rows[0].ItemArray[2]), new SqlParameter("@Contractor", dt.Rows[0].ItemArray[3]), new SqlParameter("@Value", dt.Rows[0].ItemArray[4]), new SqlParameter("@Level1", dt.Rows[0].ItemArray[5]), new SqlParameter("@Level2", dt.Rows[0].ItemArray[6]), new SqlParameter("@Status", dt.Rows[0].ItemArray[7]), new SqlParameter("@Country", dt.Rows[0].ItemArray[8]), new SqlParameter("@CreatedDate", System.DateTime.Now), new SqlParameter("@CreatedByID", ""), new SqlParameter("@CreatedByName", "")); 

Can someone give me a hand on this.

Answer 1

You can do this using the SqlBulkCopy class. In short, create and open a SqlConnection , and then use this code to copy this content from the DataTable to the server.

 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn)) { bulkCopy.DestinationTableName = "tbl_Projects"; bulkCopy.WriteToServer(dt); } 

Answer 2

You can also use a table type parameter and transfer the entire dataset from C # to SQL Server.

See this question: INSERT using LIST in a stored procedure

Answer 3

C # using SqlBulkCopy bulkcopy = new SqlBulkCopy(con)

You can insert 10 rows at a time

  DataTable dt = new DataTable(); dt.Columns.Add("a"); dt.Columns.Add("b"); dt.Columns.Add("c"); dt.Columns.Add("d"); for (int i = 0; i < 10; i++) { DataRow dr = dt.NewRow(); dr["a"] = 1; dr["b"] = 2; dr["c"] = "Charan"; dr["d"] = 4; dt.Rows.Add(dr); } SqlConnection con = new SqlConnection("Connection String"); using (SqlBulkCopy bulkcopy = new SqlBulkCopy(con)) { con.Open(); bulkcopy.DestinationTableName = "Sample"; bulkcopy.WriteToServer(dt); con.Close(); }