Jeff and .NET

I test this against an access database - there is a master in source control, and the test works against a copy.

Works fine for most things. When I need to operate against SQL Server, I test against a database and modify my code to rollback instead of commit :-)

With data access there are so many unknowns such as identity field values, you cant simply compare objects. My collegue Andrew Stopford (http://weblogs.asp.net/astopford) has mentioned something about moc objects but I havent had much chance to dig into this.

Most of my code uses data readers and i tend to reference them by column number rather than name (for performance).

For this reason there are several things i check within the unit test.

1. There is data coming back.
2. The data in the column is of the correct format
3. Referencing by column number and referencing by columnn name are the same.


NB: Samples are all in C# and are a guideline only.
1. //ensure we there is data available
if(!dr.read())
{
Assert.Fail("No Data Returned");
}

2. //try and set the data to the type you want the try-catch block will indicate any cast/conversion errors
try
{
string ColNumVal = dr.GetString(0);
string ColRefVal = dr["myCol"].ToString();
}Catch()
{
Assert.Fail("Col 0 Data cast/conversion error");
}

3. //we check referencing the value by column and by name return the same value.
if( ColNumVal != ColRefVal)
{
Assert.Fail("Col 0 data not consistence with column name reference");

}


I hope this helps, if you need anymore insight please ask.

Atul, I don't think that accessing columns by number is wise.
I prefer to pay a (very small) performance penalty price than risking breaking code because somebody changed the data structure or the SQL/Stored Procedure code.

Daniel, I would say that when your working with a substantial amount of data this 'very small' performance penalty soon mounts up.

One of the major points of Unit Testing is Regression testing to ensure that as changes are made the underlying system functions still remain constant and work as required by the business logic.

The Unit Test column check is in place to ensure that if the data structure is altered and no longer works with the function the test will fail and will indicate that the current changes have caused the working build to fail.

If you really wanted to you could use:

dr.GetString(dr.GetOrdinal(“mycol”)) to get the column ordinal and then use this.
You could also map the ordinals once and re-use them which would be pretty good.

Imagine a int32 the way you suggest:
int i = Convert.ToInt32(dr["myCol"])

This is well known to be less effecient than int i = dr.GetInt32(0).


It all boils down to how much you care about the performance of your system, me personally I try to use best practice and prefer the code to run with performance in mind than readability.

See my MSDN article dealing with exactly these issues: how do you test your data access layer?
http://msdn.microsoft.com/msdnmag/issues/05/06/UnitTesting/default.aspx

Roy, had a read of the article not sure where it directly address's the problems of say for example: Reading data from the database and testing the returned data against the expected result.

If your SELECT is calling out each column, using the ordinals is fine, and it's what I do on everything now.

There is a great tool out there called Sherpa that actually builds unit tests for each of your entities in an OOD. Go check this thing out... I've never seen anything like it. http://www.propersonal.com
Hope this helps...