Data Access Layer – Unit Testing without the fear of breaking code
By practicing Test Driven Development (TDD), developers will do their best to improve their code without the fear that is normally associated with code changes. It will boost developer’s confidence that the integrity of the code is in place. Writing tests provides another form of “living document” because it is being updated and used regularly as you continue with your development. It will show how your code should be called.
Unit testing using mock up objects for the database is so complicated but using the Microsoft Enterprise Library – (Microsoft.Practices.EnterpriseLibrary.Data.dll) it will be easier and simple to implement.
You can download the source code here.
I have here a class that can be used for the database testing environment.
using System;
using System.Transactions;
using Microsoft.Practices.EnterpriseLibrary.Data;
namespace TestCommon
{
public class TestDBEnvironment : IDisposable
{
string _dbName; //this is the name of the database connection string in your app.config
Database _db;
TransactionScope _transScope;
public TestDBEnvironment(string dbName)
{
_dbName = dbName;
_db = null;
_transScope = new TransactionScope();
}
public Database GetTestDatabase()
{
if (_transScope == null)
_transScope = new TransactionScope();
if (_db == null)
_db = DatabaseFactory.CreateDatabase(_dbName);
return _db;
}
#region IDisposable Members
public void Dispose()
{
if (_transScope != null)
{
_transScope.Dispose();
_transScope = null;
_db = null;
}
}
#endregion
}
}
This class implementation is using a TransactionScope. It will not commit the changes to the database, instead it will dispose or abort what ever changes you’ve made after the test. The interesting part here is that you can simulate and verify your stored procedures or any data access operations (CRUD - Create, Retrieve, Update, and Delete) you’ve implemented in your code within the scope of your test.
So, in your data access layer implementation you can do the following:
This public method will insert to your database.
public void Add(Database db, Customer customer)
{
using (DbCommand command = ConstructInsertCommand(db, customer))
{
db.ExecuteNonQuery(command);
}
}
This private method will construct your select or how are you going to call your stored procedure.
private DbCommand ConstructInsertCommand(Database db, Customer customer)
{
DbCommand command = db.GetStoredProcCommand("dbo.CustomerCreate");
if (customer.CustomerId != null)
{
db.AddInParameter(command, "customerId", DbType.String, customer.CustomerId);
}
… and so on…
return command;
}
This public method will verify what you’ve inserted to the database.
public List<Customer> FindCustomerByLastName(Database db, string lastName)
{
List<Customer> results = new List<Customer>();
string sql = "SELECT * FROM Customer WHERE LastName LIKE @LastName";
using (DbCommand cmd = db.GetSqlStringCommand(sql))
{
db.AddInParameter(cmd, "lastName", DbType.String, lastName);
using (IDataReader rdr = db.ExecuteReader(cmd))
{
while (rdr.Read())
{
results.Add(ConvertDataReader(rdr));
}
}
}
return results;
}
This private method will convert the result of your select from the DataReader to the Customer object.
private Customer ConvertDataReader(IDataReader reader)
{
Customer customer = new Customer();
int customerIdIndex = reader.GetOrdinal("CustomerId");
if (!reader.IsDBNull(customerIdIndex))
{
customer.CustomerId = reader.GetString(customerIdIndex);
}
… and so on …
return customer;
}
Then, this is how you will implement it in your unit test.
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Text;
using System.Collections.Generic;
using MyAppDataAccessLayer;
using TestCommon;
using Microsoft.Practices.EnterpriseLibrary.Data;
namespace VSTSUnitTest
{
/// <summary>
///This is a test class for MyAppDataAccessLayer.CustomerDataAccess and is intended
///to contain all MyAppDataAccessLayer.CustomerDataAccess Unit Tests
///</summary>
[TestClass()]
public class CustomerDataAccessTest
{
TestDBEnvironment _testDbEnv;
Database _db;
//Use TestInitialize to run code before running each test
[TestInitialize()]
public void MyTestInitialize()
{
_testDbEnv = new TestDBEnvironment("DBUnitTest_Conn");
_db = _testDbEnv.GetTestDatabase();
}
//Use TestCleanup to run code after each test has run
[TestCleanup()]
public void MyTestCleanup()
{
_testDbEnv.Dispose();
}
[TestMethod()]
public void AddCustomerTest()
{
CustomerDataAccess target = new CustomerDataAccess();
Customer customer = new Customer();
customer.CustomerId = "123";
customer.FirstName = "John";
customer.LastName = "Doe";
customer.CustomerLevel = "1";
customer.SSNumber = "5555";
target.Add(_db, customer);
// get the inserted customer from the database
List<Customer> expected = target.FindCustomerByLastName(_db, "Doe");
Assert.AreEqual(expected[0].LastName, customer.LastName, "AddCustomerTest Did not return the expected value");
Assert.IsTrue(expected.Count == 1, "List result did not return the expected value");
}
}
}
Summary:
Tests are working examples of how to invoke a piece of code and provides a working specification. TDD allows us to refactor, or change the implementation of a class, without the fear of breaking it.
Martin Fowler: “The code is the design”.