Relocated blog to www.thechrisoshow.com

This blog has now been relocated to www.thechrisoshow.com

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

Automatically load the Asp.net Web Server

When I’m debugging my Asp.net applications, I find myself just wanting a quick and easy way of starting the asp.net web server. You can do this from the command line like this:

"C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\WebDev.WebServer.exe" /path:[PATH OF YOUR WEB APP] /port:[WEB PORT] /vpath:[/mywebapp]

You can also use the funky explorer plug-in designed by Robert McClaws here. This lets you run the webserver just by right clicking on a folder path.

Zlib:BufError

So, I started installing Capistrano yesterday using:

gems install capistrano

Annoyingly, the net-ssh dependency failed with the hideous error message:
ERROR: While executing gem … (Zlib::BufError) buffer error

This seems to only happen on windows – and there doesn’t seem to be any documentation on how to fix this.

Thankfully, you can fix this problem by upgrading your RubyGems install using

gem update --system

I then installed Capistrano with no probs!