Insert multiple rows in SQL as sqlparameter

Question

I got 1000 rows in my data-table dt and I want to insert all this data to my sql table in one shot.I know how to do this with a for loop..but I wonder is there any other better way to do this all rows to datatbase in single shot. My current code to insert this data is like this

 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 any one give me a hand on this..

msdn.microsoft.com/en-us/library/…

Sankara
– Sankara

2013-07-18 12:14:29 +00:00
Commented Jul 18, 2013 at 12:14 — Sankara
– Sankara, Commented Jul 18, 2013 at 12:14

Mike Perrenoud · Accepted Answer · 2013-07-18 12:22:56Z

2

You can do this by leveraging the SqlBulkCopy class. In short, get a SqlConnection created and opened and then use this code to copy that in bulk from the DataTable to the server.

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

edited Jul 18, 2013 at 12:22

answered Jul 18, 2013 at 12:17

Mike Perrenoud

68.1k32 gold badges167 silver badges238 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:32:52Z

1

You could also use a table type parameter and pass an entire dataset from C# to the SQL Server.

See this question: INSERT using LIST into Stored Procedure

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Jul 18, 2013 at 12:20

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

Comments

Charan Ghate · Accepted Answer · 2014-12-24 12:34:20Z

With 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();
        }

Collectives™ on Stack Overflow

Insert multiple rows in SQL as sqlparameter

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related