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
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!