Featured Database Articles

Database Unit Testing

Unit testing provides a structured and automated way of testing
individual components of a system. Database unit testing specifically tests the
data consumed by different components of the application. As data quality has
become increasingly important to any organization, database unit testing also
becomes an even more important part of software quality assurance. By
developing database unit tests, you can create a collection of tests and run
them during development to ensure that your features work as you expect. Such a
collection of tests is very useful for regression testing. When an application is
upgraded or re-factored, with the unit testing cases you can verify that the
data output by the application is consistent between different versions. In
this article, I will show you how to implement database unit testing to compare
two result sets using C# and LINQ. We will build our test cases on the
open-source unit-testing framework, NUnit, as it is written entirely in C#,
fast and easy to use. Before using NUnit, you need to go to http://www.nunit.org/index.php?p=download
to download the framework and install it on your computer. Installation
instructions are at http://www.nunit.org/index.php?p=installation&r=2.5.2. You can follow
the approach in this article to develop your own unit test cases. If you are
not familiar with programming, there are a few database unit testing software
available. AnyDBTest at http://www.anydbtest.com is an excellent
product. It is the first and only automated DB unit testing tool available
using XML as test case. It also supports data validation between heterogeneous
data sources, including Oracle, SQL Server, and MySQL etc.

Assume that you are a DBA working at a furniture store. The sales of
your company come from furniture sales and services sales, for example,
installation. The existing version of the PnL report only presents the total
sales number. The accounting department in your company would like have a
breakdown of sales between furniture and services on the PnL report. The
developers have developed a new version of the PnL report application. Before
releasing the new PnL report, you work with the developers to unit test the
data saved in the database tables by the existing and new PnL report
application to make sure the numbers are consistent. Here is the schema of the
existing and new PnL tables.

Above are the result sets to compare. In
our testing application, we first get our result sets from the database and
convert them into in-memory DataTable objects to
prepare for the comparison. The constructor of the TestDataTable class accepts
a database connection string, and a select query. You can also set the input
parameters with the SetInputParameter function. The Table property of the class
returns a DataTable containing the result set from the select query.

We also need a class to compare two DataTable objects with LINQ, and
write the discrepancies between the two result sets into a file specified in a
variable logFilePath. The ResultSetComparer class does that.