Batch Insert of Records in .Net

So, I've got a DataTable behind the scenes practically packed with data (1000s of rows) - and I want to insert this into a table on sql server using a stored procedure.

The wrong way:

//The dsSourceDataSet is a populated dataset with 
//a table that has tonnes of rows
foreach (DataRow row in dsSourceDataSet[0].Rows)
  SqlCommand command = new SqlCommand("sp_my_proc", connection)
  command.Parameters.AddWithValue("@param1", row["field1"].Value);
  command.Parameters.AddWithValue("@param2", row["field2"].Value);
  //Yada yada

I mean, let's face it - this works so I thought "Hey, that's good enough for me!"
But man was it sloooow!

Then my boss showed me the CORRECT way of doing things:

The right way:

SqlCommand command = new SqlCommand("sp_my_proc", oConnSource);
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
SqlParameter param1 =
 dataAdapter.InsertCommand.Parameters.Add("@param1", SqlDbType.VarChar);
param1 .SourceColumn = "field1";
param1 .SourceVersion = DataRowVersion.Original;

So what's happening? Instead of looping through the table like a crazy gooloot - everything gets crammed into the dataset and fired off to the database!

And it is SOOO much faster! This process took a little over an hour before, now it takes a few minutes!


Subscribe via RSS

Back to all blog posts