Tips to Efficiently create database unit tests

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/tips_to_efficiently_create_database_unit_tests.htm]

A while ago I published an article and blogged on how to Unit Test the Data Layer. Recall that the basic approach is:

1. Create a test-only instance of the database
2. Initialize the table data to a specific state
3. Run your object
4. Check either the return value of that object (for selects) or the new state of the database (for insert/update/delete).

A common problem I keep facing is that these tests can take long to create, and longer to modify. Below are a few tips that I've been learning.

Creating the initialization script is usually tedious and time-consuming. One approach is to create or find a tool that takes a select statement and generates inserts from its result set (I built such a tool, but I can't release it because it's proprietary to my company, Ameripay). This lets you easily copy existing data for testing purposes.

I modified this tool to put tabs after each column, and place the insert and values sections on separate lines.

insert into myTable ( myId,    col1,     col2) values ( '1',     'abc',     'def')

This lets me easily copy the text into Excel, which then automatically formats it in a tabular way that lines up column headers with contents, like so:

insert into myTable(	myId,	col1,	col2	)
values(	'1',	'abc',	'def'	)

It's much easier to edit this way!

Therefore to maintain the data, I only need the raw SQL script itself - not some fancy generator/mapping tool or spreadsheet. I can now take the raw text and swap it between:

• Query Analyzer -  to easily test it
• Visual Studio - in a source file as the contents of an ExecuteNonQuery(@"...") method that runs it to initialize the test
• Excel - to put it in tabular form and maintain it.

Two applications of this:

1. I created a MasterInserts Excel sheet that contains standard inserts for each table. Therefore if I have 5 objects all requiring test data from the same table, I go to this master sheet as the definitive source, copy the lines I need, and modify them appropriately. This prevents me from wasting time re-creating SQL inserts for tables.
2. Say I have multiple tests for a single object. 90% of the base data for each test is the same - usually it's just tweaking a value like making sure a SP handles a null column. Therefore I refactor the common base script to a single method that runs it in ExecuteNonQuery. Then each test can call this method and merely do the simple update or additional insert that it needs.

The sky is always the limit, but it's getting there.