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.
CREATE TABLE OldPnLReport
( AccountNumber int,
CREATE TABLE NewPnLReport
( AccountNumber int,
You would like to compare the aggregated NetSales from FurnitureSales
and ServiceSales in NewPnLReport against OldPnLReport. Here are the queries.
,([FurnitureSales] + [ServiceSales]) as [NetSales]
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.
private readonly SqlCommand _command;
public TestDataTable(String connstr, String selectQuery, CommandType commandType)
SqlConnection conn = new SqlConnection(connstr);
_command = new SqlCommand(selectQuery, conn);
_command.CommandType = commandType;
public void SetInputParameter(string parameterName, object parameterValue)
new SqlParameter(parameterName, parameterValue);
public DataTable Table
DataTable _dataTable = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(_command);
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.
private static StreamWriter sw = null;
private static void InitWriter(String filePath)
sw = new StreamWriter(filePath, true);
private static void Write(DataTable dt)
int i = 0;
for (i = 0; i < dt.Columns.Count - 1; i++)
sw.Write(dt.Columns[i].ColumnName + " | ");
foreach (DataRow row in dt.Rows)
object array = row.ItemArray;
for (i = 0; i < array.Length - 1; i++)
sw.Write(array[i].ToString() + " | ");
catch (Exception ex)
Console.WriteLine("Invalid Operation when writing data table: \n" + ex.ToString());
public static bool AreIdenticalResultSets(DataTable dt1, DataTable dt2, String logFilePath)
var v1 = dt1.AsEnumerable();
var v2 = dt2.AsEnumerable();
var diff1 = v1.Except(v2, DataRowComparer.Default);
var diff2 = v2.Except(v1, DataRowComparer.Default);
sw.WriteLine("Rows in the first table, but not in the second table:");
DataTable diffTbl1 = diff1.CopyToDataTable();
sw.WriteLine("Rows in the second table, but not in the first table:");
DataTable diffTbl2 = diff2.CopyToDataTable();
return !(diff1.Any() || diff2.Any());
Now we are ready to build our test case. We will use the following components
from the NUnit framework.
This attribute marks a class that
contains tests and, optionally, setup or teardown methods.
This attribute is used inside a TestFixture to provide a common set of
functions that are performed just before each test method is called.
This attribute marks a method
inside a TestFixture class as a test.
An assertion is a function or macro that verifies the behavior (or the
state) of the unit under test. Failure of an assertion throws an exception,
aborting the execution of the current test.
Here is our test fixture.
public class DBTest
private DataTable dt1;
private DataTable dt2;
private String logFilePath = "C:\\Windows\\Temp\\PnLDiff.txt";
public void Init()
String comparedDate = "2009-10-16";
String connstr1 = "Data Source=localhost;Initial Catalog=TestDB;Integrated Security=true";
String selectQry1 = "SELECT [AccountNumber],[NetSales],[Costs],[NetIncome] " +
"FROM [testDB].[dbo].[OldPnLReport] " +
"WHERE vol.[ModifiedDate] >= @comparedDate;";
TestDataTable tdt1 = new TestDataTable(connstr1, selectQry1, CommandType.Text);
dt1 = tdt1.Table;
String connstr2 = "Data Source=localhost;Initial Catalog=TestDB;Integrated Security=true";
String selectQry2 = "SELECT [AccountNumber],([FurnitureSales] + [ServiceSales]) as [NetSales],[Costs],[NetIncome] " +
"FROM [testDB].[dbo].[NewPnLReport] " +
"WHERE [date] >= @comparedDate;";
TestDataTable tdt2 = new TestDataTable(connstr2, selectQry2, CommandType.Text);
dt2 = tdt2.Table;
public void ResultSetEqual_Test()
Assert.IsTrue(ResultSetComparer.AreIdenticalResultSets(dt1, dt2, logFilePath ));
Lets run the nunit-console command to test our case.
cd "C:\Program Files (x86)\NUnit 2.5.2\bin\net-2.0"
nunit-console.exe "D:\DatabaseUnitTesting\DBUnitTestDemo\DBUnitTestDemo\bin\Release\DBUnitTestDemo.dll" /nologo /nodots
If there is no discrepancy between the existing and new PnL reports,
the test would succeed as shown below.
Insert two rows into the two tables to create a failed case.
insert into OldPnLReport
values (1, 5033.00, 2038.00, 2995.00, '2009-10-16');
insert into NewPnLReport
values (1, 2041.00, 2900.00, 2038.00, 2993.00, '2009-10-16');
Run the above command again.
As you can see, this time the test case ResultSetEqual_Test failed. Open
the log file in notepad and you will see the difference between the two tables.
Database unit testing is an important part of any database development
cycle. In this article, we demonstrated how to create a database unit test case
using NUnit framework, C# and LINQ.
See All Articles by Columnist Yan Pan