Database Testing With DBUnit

DBUnit is an Open Source Database Testing Framework which was created by Manuel Laflamme. DBUnit is a Database Unit Testing Framework to Test Database Driven Classes, Procedures or Functions. NUnit, MVC .NET/Visual Studio testing platforms can be used to build a standard unit test suite. Developer should create a dataset, defined within an XML file, and then extends one of a number of parent classes to inherit pre-defined functionality in order to use the framework. An IDbConnection implementation to connect to the database, then DbUnit.NET can be used to test the data access code.

Following Operations can be performed using DBUnit:

• UPDATE
• TRUNCATE
• INSERT
• REFRESH
• DELETE
• CLEAN_INSERT
• DELETE_ALL
• NONE

Other Operations:

• Composite Operation
• Transaction Operation
• Identity Insert Operation

DBUnit Support Databases:

OracleDatabase MsSQL MySQL IbmDB2 IbmInformix H2 HypersonicSQL

PostgreSQL SybaseSQL InterBase OpenBase8 SapDB/MaxDB Derby FrontBase

HOW TO WRITE A DBUnit TEST CASE?

To create a DBUnit .NET test case we may have to follow the following approaches:

1. Create a XML Dataset or Export Data from Databases:

If the DBUnit Test Class Name is DbUnitTestCase then the Data Set name has to be DbUnitTestCaseDataset.xml and has to be in the same folder. And, here is the example of flat XML DataSet:

Here is the example of structured XML exported from MS SQL Server Database:

Here is the example of structured XML exported from MS SQL Server Database:



34
This is a test!
22

10



This can be converted to flat XML like this:

< messageid ="34" msgcontent="”" messagethreadid="22" attachmentlink="””" ownerid="10/">

2. Create a Test Class and Extend DbUnitTestCase:

using System;

using System.Data;

using System.Data.SQLite;

using DbUnit.Framework;

using NUnit.Framework;

namespace MyDbUnitTestFrameWork

{

[TestFixture]

public class MyDBUnitTestCase1 : DbUnitTestCase

{

private IDbConnection connection = null;

3. Implement Setup and TearDown:

If we use the Flat XML, then the Setup method of the data set would be:

[SetUp]

public void SetUp()

{

dataset = new EmbeddedFlatXmlDataSet(this.GetType());

}

But, if we use Structed XML, then the Setup method of the data set would be:

[SetUp]

public void SetUp()

{

dataset = new EmbeddedStructuredXmlDataSet(this.GetType());

}

[TestFixtureSetUp]

public void SetUpFixture()

{

SQLiteConnection.CreateFile("MyLocalDB.db");

connection = new SQLiteConnection("Data Source= MyLocalDB.db");

connection.Open();

CreateTables();

PrimaryKeyFilter pkfilter = new PrimaryKeyFilter();

pkfilter.Add("Message", new PrimaryKey("id"));

pkfilter.Add("Thread", new PrimaryKey("id"));

base.PKFilter = pkfilter;

base.DatabaseHandler = new GenericDatabaseHandler(this.connection);

}

[SetUp]

public void SetUpTestCase()

{

base.SetUp();

}

[TestFixtureTearDown]

public void TearDownFixture()

{

connection.Close();

connection.Dispose();

connection = null;

}

[TearDown]

public void TearDownTestCase()

{

base.TearDown();

Assert.AreEqual(0, Count("select count(*) from Message"));

Assert.AreEqual(0, Count("select count(*) from Thread"));

connection.Close();

}

4. Implement Test method:

[Test]

public void TestQueryMessage()

{

Assert.AreEqual(1, Count("select count(*) from Message"));

Assert.AreEqual(1, Count("select count(*) from Thread where id = 1"));

}

So, a sample DUnit .NET Test Class should look like:

using System;

using System.Data;

using System.Data.SQLite;

using DbUnit.Framework;

using NUnit.Framework;

namespace MyDbUnitTestFrameWork

{

[TestFixture]

public class MyDBUnitTestCase1 : DbUnitTestCase

{

private IDbConnection connection = null;

[TestFixtureSetUp]

public void SetUpFixture()

{

SQLiteConnection.CreateFile("test.db");

connection = new SQLiteConnection("Data Source=test.db");

connection.Open();

CreateTables();

PrimaryKeyFilter pkfilter = new PrimaryKeyFilter();

pkfilter.Add("people", new PrimaryKey("id"));

pkfilter.Add("Thread", new PrimaryKey("id"));

base.PKFilter = pkfilter;

base.DatabaseHandler = new GenericDatabaseHandler(this.connection);

}

[TestFixtureTearDown]

public void TearDownFixture()

{

connection.Close();

connection.Dispose();

connection = null;

}

[SetUp]

public void SetUpTestCase()

{

base.SetUp();

}

[TearDown]

public void TearDownTestCase()

{

base.TearDown();

Assert.AreEqual(0, Count("select count(*) from Message"));

Assert.AreEqual(0, Count("select count(*) from Thread"));

}

[Test]

public void TestSomething()

{

Assert.AreEqual(2, Count("select count(*) from Message"));

}

[Test]

public void TestSomethingElse()

{

Assert.AreEqual(1, Count("select count(*) from Message where id = 1"));

}

[Test]

public void TestMessage2()

{

Assert.AreEqual(1, Count("select count(*) from Message where id = 2"));

}

[Test]

public void TestInvalidMessage()

{

Assert.AreEqual(0, Count("select count(*) from Message where id = 7"));

}

[Test]

public void TestThread()

{

Assert.AreEqual(1, Count("select count(*) from Thread"));

Assert.AreEqual(1, Count("select count(*) from Thread where id = 1"));

}

private void CreateTables()

{

ExecuteDML("create table Message (id integer primary key, givennames varchar2(50), surname varchar2(50))");

ExecuteDML("create table Thread (id integer primary key, head integer, spouse integer)");

}

private void ExecuteDML(string dml)

{

using (IDbCommand cmd = connection.CreateCommand())

{

cmd.CommandText = dml;

cmd.ExecuteNonQuery();

}

}

private int Count(string sql)

{

int result = -1;

using (IDbCommand cmd = connection.CreateCommand())

{

cmd.CommandText = sql;

using (IDataReader reader = cmd.ExecuteReader())

{

if (reader.Read())

{

result = reader.GetInt32(0);

}

}

}

return result;

}

}

}

Figure 7: DBUnit Test Result using NUnit

Then, if we open up the .NET project from NUnit Application and right click on the project and select Run All, it will run all the tests. The passed tests should have a green check sign and failed test should have a red cross sign. Then if we right click on specific test, we may look at the deatil of that test.

DBUnit Benefits:

• DBUnit provides a stable framework to create and adopt Database Test cases with minimal effor
• It can manage the state of database through the test’s life cycle
• At the end of testing the database can be placed back into its pre-test state
• A database can be populated with a desired data set before a test
• DBUnit test can easily automated by controlling major dependencies
• DBUnit test can be performed utilizing most types of database objects (Tables, Views, Stored Procedures)
• DBUnit Tests do not require manual intervention between tests
• DBUnit Test results doesn’t need manual interpretation
• Code stability increases in a Team environment
• Easy to Learn and Implement
• Wide range of support for standard databases
• DBUnit documentation is pretty well defined
• DBUnit tests are easily manageable
• It is easier to generate the seed data using the dump feature

DBUnit Limitations:

• Primary test data is XML but it is also possible to create test cases for standard databases
• Test Data generation plan is absent and so have to create and refresh the test dataset when Actual schema changes and seed and output data could be different
• Primary support for XML Dataset is Flat XML and need to have all columns defined in the first row
• BBUnit does not support multiple Resultset

---

DBUnit Location: http://dbunit-net.sourceforge.net/GettingStarted.html