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!

Need to find a lawyer for your business? Get in touch with my company Lexoo and we'll find you a great one for free.