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!

6 comments:

  1. Try EffiProz Database (http://www.EffiProz.com).

    EffiProz is a database written entirely in C#. comprehensive SQL support including Stored Procedures, Triggers and Functions. Ideal for embed in .Net applications. Support Silverlight 3 and .Net compact framework as well
    Include Visual Studio ad-in, ADO.Net provider, Entity Framework provider, etc.

    ReplyDelete
  2. You posted the same code for the application and for the unit test :(. Please fix that.

    ReplyDelete
  3. Hey Dan,You posted the same code twice. I'm looking for this exact information. Would you be kind enough to update this post, please. Thanks.

    ReplyDelete
  4. Hi Dan,
    I would like to see the unit testing code also. So can you please still provide it here?
    Thanks in advance.

    ReplyDelete
  5. Sorry about posting the same code twice. I've update the posting to include the unit test code.

    ReplyDelete
  6. Hi Dan,
    Thanks for the fast answer :) The example is very clear now ;)

    ReplyDelete