Tuesday, December 15, 2009

Simple C# Database Application Unit Test

I promised a way to unit test Database driven applications.  I've read that it is so difficult to do this and it is so hard to maintain.  The reason is that those people try to couple unit tests and the database.  Separate them!  Don't keep a unit testing database around because it will be out of date sooner than you think, tests will begin to fail and you will not continue to create / update those unit tests.

How do you separate the database from the application?  Capture the data.  Code is worth more than words, so here's my sample database app:


public void DoStuff()
{
    DataSet myData = GetMyData();

    foreach (DataRow dr in myData.Tables[0].Rows)
    {
        Console.WriteLine("Data: " + dr[0] + "," + dr[1]);
    }
}
It's too coupled with the database, so it's way too complicated to unit test reliably.  Step 1, capture the data and get out. Step 2, use the data you provide in the unit test or get it from the database.  Here's what your application should look like:

public bool captureData = true;
public DataSet myData = null;
public void DoStuff2()
{
    if( myData == null )
        myData = GetMyData();

    if (captureData)
    {
        myData.WriteXmlSchema("c:/myData.DataSet.schema.xml");
        myData.WriteXml("c:/myData.DataSet.xml");
        return;
    }

    foreach (DataRow dr in myData.Tables[0].Rows)
    {
        Console.WriteLine("Data: " + dr[0] + "," + dr[1]);
    }
}
And here's what our unit test will look like:


[TestMethod()]
public void DoStuff2Test()
{
    DataSet unitTestMyData = new DataSet();
    unitTestMyData.ReadXmlSchema("c:/myData.DataSet.schema.xml");
    unitTestMyData.ReadXml("c:/myData.DataSet.xml");

    MyDatabaseApp.Program p = new MyDatabaseApp.Program();
    p.myData = unitTestMyData;
    p.DoStuff2();

    Assert.AreEqual(2, p.myData.Tables["thetable"].Rows.Count, "Need 2 rows of this data for this reason");
}


Make sure you read the schema before the xml data otherwise all your columns will be strings.  I told you this was a lot easier than people make it out to be.  Make sure you begin writing unit tests for your all your Database driven appliactions, C#, VB.Net, doesn't matter... do it!