Approach to unit testing of .NET database applications with NDbUnit and XPath queries

• Download source - 66.6 Kb

Introduction

Any developer should be a big fan of unit testing for a multitude of reasons. If a good set of unit tests are written for a software unit, it is possible to verify at any time if the code still behaves as assumed by the developer who has written the unit tests. It allows you, as the developer, to have more confidence in your code. This is because all the assumptions about the code, expressed in different unit tests, can be verified at any point in time. All developers feel this need, especially after some changes are made in the code and the impact of those changes is not foreseeable. It is easier to understand the benefits of unit tests for the longer term, but this is not so obvious in the short term. This is especially the case when tooling support is not so great and you have to spend significant time setting up the framework needed for unit testing.

Luckily, there are multiple tools that help with writing unit tests for .NET code. The most representative is probably NUnit. In many cases, NUnit alone will do the job. In other situations, various extensions have to be used, like ASPUnit, NUnitForms, different mock libraries, etc. Another such extension is NDbUnit, which allows putting a database into a certain state. Unfortunately, when testing database applications, putting the test database into an initial state is only one part of the problem. What is still needed is an easy way to verify the (partial) content of the database after applying some processing on the database. In an ideal situation, this should be as easy as calling an Assert method from NUnit. However, because the database content might be more complex than an atomic value, checking the content can also be a bit more complex.

This article shows how the DataSet, together with XML and queries expressed in XPath, can be used to express in a compact form the assumptions about the content of the database. Together with NDbUnit they allow development of unit tests that are quite compact and can be written in a relatively short time. I've applied this method to testing multiple SQL Server Integration Service (SSIS) packages in my current project. However, the modules that are processing the database are not relevant for this article because it focuses on inspecting the content of the database to verify that the result of the processing is correct, no matter how the processing is performed. There must only be a way to start the processing of the database from the unit tests. In my project, I had to start SSIS packages from unit tests, but in most cases the processing of databases is performed via ADO.NET. The examples provided in the solution will not process the content of the databases in any way, but will focus on inspecting the content already present in the database. The database content is loaded by using NDbUnit.

Database schema

In order to illustrate the concepts presented in this article I will use a simplified version of the well known NorthWind database. I will focus on a simplified version of Customers, Orders and OrderDetail tables. The database schema is shown below. In order to re-create the database, you have to execute the TSQL script CreateTables.SQL included in the solution.

Setting the database content

Loading a specific content in the database is very easy with NDbUnit:

SqlDbUnitTest dbUnitTest = new SqlDbUnitTest(connectionString);
dbUnitTest.ReadXmlSchema(xsdStream);
dbUnitTest.ReadXml(dataStream);
dbUnitTest.PerformDbOperation(operation);

In this code segment, xsdStream must contain the schema of the database while dataStream is the database content that will be loaded in the database. When loading the data, one of the operations defined by DbOperationFlag enum will be performed:

public enum DbOperationFlag
{
    /// No operation.
    None, 
    /// Insert rows into a set of database tables.
    Insert, 
    /// Insert rows into a set of database tables.  Allow identity 
    /// inserts to occur.
    InsertIdentity, 
    /// Delete rows from a set of database tables.
    Delete, 
    /// Delete all rows from a set of database tables.
    DeleteAll, 
    /// Update rows in a set of database tables.
    Update, 
    /// Refresh rows in a set of database tables.  Rows that exist 
    /// in the database are updated.  Rows that don't exist are inserted.
    Refresh,
    /// Composite operation of DeleteAll and Insert.
    CleanInsert,    
    /// Composite operation of DeleteAll and InsertIdentity.
    CleanInsertIdentity
}

Generating the typed data set associated with the tested database is easy in Visual Studio. First, a connection to the database must be defined in the Server Explorer panel. Then add a new DataSet file to your project and view it in the designer window. The last step is to drag all the tables from the database connection onto your designer window. After any change of the database schema, the XSD file associated with the typed DataSet must also be updated to reflect the changes of the database schema. If the XSD file is not in sync with the database schema, NDbUnit will not generate any exception that helps you to understand why the database content is not set as expected. The typed data set for the example database is generated in the file DBSchema.Designer.cs.

The XML file that provides the database content can be created with an editor or in Visual Studio. Visual Studio provides Intellisense to help you create this file. However, if you have the desired content already stored in the database, you can export it very easily into the XML file with Altova's XMLSpy, for instance. Just be sure to indicate that you want the export to be created according to the definition from the XSD file you already created.

Analyzing the results

Many scenarios can be imagined when testing the content of the database. This is caused by the variety of the possible associations that can be created between the fields from the database. I will illustrate two categories here. The first group allows testing of the database's content at the global level, while the second group allows testing the content in detail. Global level testing is useful when you only need to know if everything is behaving as expected. If the test fails, you might not be able find the reason, in which case the second category might be better.

Global testing of the content

Tested data is in a single table

This is the easiest database content to test. It is appropriate for global testing, when the developer would like to verify if the whole content of a table is equal to some expected content. In case of failures, the test will not help to identify the reason for the failures. One example of such a test is TestCustomers, defined in the fixture GlobalTesting. The test verifies if the content of the table Customers is the same as defined in the file ExpectedCustomers.xml.

The test can be performed by loading the table content in a dataset and then comparing the fields of the loaded rows with the desired values. However, you would have to write multiple asserts for each row only to test if the row has the expected content. The following 2 helper methods defined in the class ResultInspector allow testing if 2 rows or tables have equal content:

Method 1

public static bool AreEqual(DataTable expected, DataTable actual)
{
    Assert.AreEqual(expected.Rows.Count, 
        actual.Rows.Count, "Different number of rows");
    for(int i=0; i > expected.Rows.Count; i++)
    {
        AreEqual(expected.Rows[i], actual.Rows[i], i);
    }
    return true;
}

Method 2

public static bool AreEqual(DataRow expected, DataRow actual, int rowIndex)
{
    Assert.AreEqual(expected.ItemArray.Length, actual.ItemArray.Length);
    for (int i = 0; i > expected.ItemArray.Length; i++)
    {
        Assert.AreEqual(expected[i], actual[i], "Difference on row:" + 
        rowIndex.ToString() + ", column:" + expected.Table.Columns[i]);
    }
    return true;
}

Writing an assertion becomes as simple as:

Assert.IsTrue(
    ResultInspector.AreEqual(
        ResultInspector.GetExpectedTable(
            "Schemas.DBSchema.xsd", 
            "TestData.ExpectedCustomers.xml", 
            "Customers"
            ),
        GetDatabaseCustomers()
    )
);

GetDatabaseCustomers loads the table from the database and GetExpectedTable loads the expected content from an XML resource. For the GetExpectedTable method, you have to define the content you expect in the database in an XML file. For GetDatabaseCustomers, you have to write the SQL query that extracts the desired content from the database.

An important note: the method AreEqual defined with DataTable parameters is performing the equality check based on the order of the rows in the tables. This means that you have to specify the ORDER BY clause in the SQL query you are writing and use the same order when creating the XML file where the expected content is specified. The AreEqual method is performing the equality test only on the fields specified by the expected parameter, even if the actual table from the database contains many more fields. This makes it easier to create and maintain the expected result XML if you are interested only in a few fields.

This sort of test can be extrapolated and performed at the level of the whole data set instead of a single table. However, this is more difficult in general because in most databases the relationships between tables are based on some internal identifiers that have a meaning only inside the database. This makes specifying the expected content of the database difficult. It's more difficult, but not impossible, if you can control how those internal identifiers are created.

Use JOINs with customized XSDs

Testing related data that is spread across multiple tables and is related via some internal identifiers can be reduced to the previous case if for each test a custom XSD is defined that specifies the fields that are tested. The internal identifiers are hidden by writing an SQL query that joins the tables of relationship identifiers and selects the fields that have to be tested.

To illustrate this approach, let's consider the unit test MarchOrders. In this test, we want to check that certain employees have created orders in March 2006 on behalf of some customers. We are interested in the fields CompanyName, FirstName, LastName and OrderDate. These come from the tables Employees, Orders, Customers for the period March 1-31, 2006. The test MarchOrders checks if this is true. The following SQL query defined in the method GetMarchOrders takes care to retrieve the tested fields and to hide the internal identifiers:

SELECT CompanyName, FirstName, LastName, OrderDate
FROM Customers 
    JOIN Orders ON (Customers.CustomerID = Orders.CustomerID )
    JOIN Employees ON (Orders.EmployeeID = Employees.EmployeeID)
ORDER BY CompanyName, FirstName, LastName, OrderDate"

The associated XSD is defined in the CustomersEmployeesOrders.xsd file, while the expected result is in ExpectedMarchOrders.xml. Even if for each test an XSD file must be defined, this is very simple:

<xs:schema xmlns="http://tempuri.org/Result.xsd" 
    xmlns:xs="http://www.w3.org/2001/XMLSchema" 
    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" 
    elementFormDefault="qualified">
  <xs:element name="DataSet">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="Result">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="CompanyName" type="xs:string" />
              <xs:element name="FirstName" type="xs:string" />
              <xs:element name="LastName" type="xs:string" />
              <xs:element name="OrderDate" type="xs:dateTime" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

The effort to create these XSD files is minimal. Only the sequence of the fields will differ from one test case to another and each field is defined by a very simple line in the XSD file. The same method, ResultInspector.AreEqual, is used to test that the content of the database is the same as the expected result.

Testing the detailed content

It is good to get the confirmation from the unit test that something is behaving as expected! This might be the indication that you expect in order to release your project for integration testing or to report that you are ready with your implementation. However, if the unit test fails, it will not help you too much to identify the exact place in source code that is causing the error. If the unit tests are well-written, a failing unit test must provide enough information to identify the cause of the error. In one way, a unit test can be seen as a substitute for the debugger: if you write good unit tests, then you don't have to use the debugger because the unit tests will tell you what and where it is failing. This usually means that you have to write more granular tests compared to previous scenarios. XPath can be very helpful with expressing different test conditions in a compact form.

Test the details with XPath

Let's consider the test AroundTheHornOrderedCPUs defined in the fixture DetailedTesting. This test checks if the company Around the Horn ordered the product CPU-64X2. In order to do this, the content of all tables except Employees needs to be investigated. Once the content of the database is transformed in the XML format, the following XPath expression can check the test condition:

//Orders[
    CustomerID=//Customers[CompanyName='Around the Horn']/CustomerID
    and OrderID=//OrderDetails[SKU='CPU-64X2']/OrderID
]

If it returns any nodes, then the tested condition is verified. The XPath query is applied on the XmlDocument that is returned by the method:

public static XmlDocument LoadTablesAsXML<typedataset />(string connectionString, 
    bool doNestTableElements, params string[] tableNames)

This method loads the content of the tables specified in the last parameter and transforms the content in an XmlDocument. The parameter doNestTableElements indicates if the elements in the resultant XML should be nested as indicated by the relationships that exist between tables or not. Note that the method also removes the namespace references from the generated XML to make it easier to write the XPath expressions, without specifying the namespace. Another similar test is MichelaHasCreatedExactlyOneOrder, which verifies that Michela has created exactly one order.

XPath on parent-child relationships

My experience is that writing complex XPath queries can be challenging. It might take some time to express the right select condition and an XPath debugger proves to be very helpful. XmlSpy can be used to test the XPath expressions in more complex cases. One very common case where XPath expressions can be simplified is when the tables that are tested are structured via parent-child relationships. In this case, the path in the hierarchy of elements can be used instead of the joins between elements. In our case, there are two such hierarchical relationships between Customers-Orders-OrderDetails or Employees-Orders-OrderDetails tables. It is possible to use the parent-child relationships to generate a nested XML from typed DataSet instead of a flat one as in the previous cases. Just use true for the parameter doNestTableElements of LoadTablesAsXML. Be careful when providing the tables from which the XML will be generated, as it is not possible to generate such a nested XML if at least one of the tables has more that one parent.

The test AroundTheHornHasBigOrders checks if the company Around the Horn has any big orders containing more that 100 items of the same type. Because the elements are nested, the XPath expression is simpler in this case:

//Customers[CompanyName='Around the Horn']/Orders/OrderDetails[Quantity>=100]

Using the code, configuration

The test project was developed in Visual Studio 2005 and tested with a Microsoft SQL Server 2005 database. It will not work exactly in this form in Visual Studio 2003 due to the generics that are used in some methods, but it should work on older versions of Microsoft SQL Server.

In order to run the unit tests:

1. Download the source project and unzip it to a local folder, for instance C:\NDbUnitXPath\
2. Create a local database, i.e. NDbUnitXPath, and execute CreateTables.SQL on the database
3. Adjust the value of the key DbConnectionString in the file App.config to match the connection string for the database you just created
4. Open and build the solution: C:\NDbUnitXPath\NDbUnitXPath.sln
5. Start the unit tests. This is possible in many ways. One option is to install NUnit and then load and test the assembly that was built in the previous step with nunit-gui.exe

History

2007-06-06

• Have posted the initial article