Ack! My DataTable magically grew a new data column. Here's why.

Ugh. That was almost fun :)

I was showing some of the guys how to do unit testing against a database, and we got stuck on a test that does an insert using a dataset. Imagine this: you use a simple data adapter against the “categories“ table in northwind, using a simple insert command with one parameter to be sent to an insertCategory stored procedure in the database.

Couldn't be simpler, right? Yet a strange thing was happening: The data table that was being updated (there was only one of those in the DS, for Categories table) starts out before the update with 4 columns (for the 4 cols in the original categories table), but after calling data adapter.update(DS) , the table suddenly contains 5 columns(!). I kid you not.

We started trying to track this down, and after about 30 mins, we found what the problem was: The insert sproc was returning the identity value of the newly insert row like so:

INSERT INTO categories.... bla

Select scope_identity()

This was a problem for the data adapter which could not map back the returned value into the updated data row in question, so it automatically created a new data column inside the data table. Changing the syntax to this:

INSERT INTO categories.... bla

Select scope_identity() as CategoryID

fixed it up real nice. beware!

Another interesting anecdote: this is one of those small annoying bugs that creep up on you. Because we were doing this whole exercise in a test-driven manner, we were able to discover it almost instantly (well, not the source, but the fact that there was a problem). Imagine having to wade through massive amounts of code in a production application, hunting this bug. ugh!

How did we discover that the column counts were different? We were using a small testing utility class that was provided by the generous Mr. Darrell Norton, who's had lots of experience with TDD in the DB world. This small utility class compares two datasets in many ways, including all current values and row counts. Thanks Darrell!

Maybe you can post it?