The Chris O Show

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 with1 table with 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!