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!

Visual Studio First Unit Test

Well, I decided to go with the built in unit test that Visual Studio 2008 provides simply because of the integration and everyone has it available without any installs.  The whole point is to just working with unit tests.  The lessons learned can be applied to any unit test software like NUnit.  So I started out with a simple method that does the same thing Math.Pow() does, raises a decimal to a power, returning a decimal.  (MyMath.cs)

public static class MyMath
{
  public static decimal MyPower(decimal num, int power)
  {
    int i = 0;
    decimal retval = 1;

    while (i < power)
    {
      retval *= num;
      i++;
    }

    return retval;
  }
}

After creating the method, i select the Test menu in Visual Studio and New Test....  In the Add New Test dialog box, I select the Unit Test template and name it TestPower.cs, and select "Create a new Visual C# test project...".  I click OK and name the new project TestProjectSimpleUnitTest and click Create.

I am presented with a new project TestProjectSimpleUnitTest that contains TestPower.cs.  Scrolling to the end of that file, I see:

[TestMethod]
public void TestMethod1()
{
      //
      // TODO: Add test logic    here
      //
}

I rename that method to TestMyPower() and delete the comments.  I right click the TestProjectSimpleUnitTest project and set it as the Startup Project.  I then press F5 to start the unit test and see that it passed.

Now, lets actually test MyPower.  First thing we have to do is add a reference to the SimpleUnitTest project in the TestProjectSimpleUnitTest project.  Add a using clause at the top of the TestPower.cs file:

using SimpleUnitTest;

and fill in my test method like this:

[TestMethod]
public void TestMethodMyPower()
{
  Assert.AreEqual(9m, MyMath.MyPower(3, 2), "3 to power of 2");
  Assert.AreEqual(1m, MyMath.MyPower(3, 0), "3 to power of 0");
  Assert.AreEqual(27m, MyMath.MyPower(3, 3), "3 to power of 3");
}

All my tests pass in this case.  But, supposing they didn't it's easy to set a breakpoint in the MyPower code or in the unit test code and select debug in the Test Results pane and debug your code in a controlled fashion that does not impact your project using MyPower!

Next time I'm going to get into the real good stuff... creating unit test for a database driven application!

Discovering the importance of Unit Tests

I used to never create unit tests for the code I'd write.  Why?  Deadlines.  Get it done, test it, send it to QA, have defects reported, rinse and repeat.  I became tired of that so I pressed my boss to give me a little more time in development so that I can create formal unit tests for my code and send stronger code to QA.  It took a littler longer to do than I'd hoped, so that I wasn't too thrilled about.  However, the results were astounding!  All programmers out there, you have to start creating unit tests for your code!  NOW!

The application I'm working on takes data in from multiple databases, processes it and writes data back out to the databases.  I had to overcome database dependency for my unit tests.  Nothing worse than getting a new database instance for development and discover 60 unit tests break.

Second obstacle probably the biggest benefit for development and that was what happens when QA finds a defect?  You need to recreate the defect.  How long does that take?  Minimum a half hour?  Sometimes days?  To recreate the defect, you need the data they were using.  Sure, you can setup your database the way QA says it was, but was it really setup exactly the way they say?  Come on, we all have that doubt in the back of our heads because it's bitten us before.  What can you possibly do to alleviate this headache?  Aren't your unit tests database independent?  So, capture the data and make a unit test out of it!

I've had a few occasions when I had a defect that had a simple one liner solution.  Out of 230+ unit tests, the fix broke about 40 or more unit test!  Would QA catch it?  During regression testing, sure!  If there were moving on like usual, no, they would have not caught those scenarios.

I'll post my experiences with unit testing and integration into Visual Studio 2008 using Resharper soon.

Here is how to create a simple Database Application Unit Test [CodingCramp.blogspot.com].