Batch Insert of Records in .Net

May 27th 2007

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
  command.ExecuteNonQuery();
}

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;
dataAdapter.Update(dsSourceDataSet);

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!

Hooray!