Creating Database Unit Tests in 10 minutes.

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

Unit testing the database is hard - and there are mixed reactions to it. My take is that there could be legitimate logic in the database (transactions, SQL aggregations, reusable functions, complex SP like paging, etc...). The problem with unit-testing the database is creating a test database and filling it with data. At Paylocity, we created a tool called the MassDataHandler to help us unit-test our data layer. We recently open-sourced this tool, and you can download the beta here.

The MassDataHandler is a framework to assist with Database Unit Testing. The framework makes it very easy to insert test data into a database, which in turn makes it very easy to write database unit tests. The user merely needs to specify their relevant data in a simple XML fragment, and then the framework uses knowledge of the database schema to do all the grunt work and convert that XML data into SQL, from which it populates the database.

The XML fragments that contain the test data are robust and refactorable. You can include expressions like variables substitution and identity lookup, default row templates, and include statements to import sub-templates. Because the framework already knows the database schema, you only need to specify the relevant data, and the framework can auto-populate the rest of the row’s required columns with dummy data.

For example, the following Xml script would insert data into three tables - providing you variables, default row templates, and automatically handling the identity lookups and non-null fields:

  <Root>

    <Variables>
      <Variable name="lastName" value="Simpson" />
    Variables>

    <Table name="Customer">
      <Row CustomerName="Homer $(lastName)" />
      <Row CustomerName="Marge $(lastName)" />
    Table>

    <Table name="Product">
      <Row ProductName="KrustBurger" Description="best burger ever" />
    Table>

    <Table name="Order">
      <Default LastUpdate="12/23/1997" />
      <Row CustomerId="Customer.@1" ProductId="Product.@1" />
      <Row CustomerId="Customer.@2" ProductId="Product.@1" />
    Table>
   
  Root>

Download the MDH Framework Beta here.

By making it so easy to insert and maintain test data, the MDH framework helps you write database unit tests.