I have been looking at ways to regression test the data access layer of a .Net application that has a heavy reliance on stored procedures. There are tools that can help do this, and I did consider both
dbfit and
ndbunit, but neither of them could satisfy my criteria.
Criteria
- Ease of use – No time to train developers or testers on how to use a new test framework
- Ability to use straight SQL statements
- The tool or mechanism must integrate easily into this and other projects.
I also need to provide unit testing for a data warehouse project, so something that I could use on both would be perfect
- The generated tests must be easy to put under version control. Being able to tightly couple tests with a specific version or schema is very important, but more about that another time.
The .net application I'm trying to test already has a data access layer that could be used to create these tests, but the implementation of this particular layer is complicated and would require the test engineers working on the project to have a high level of .net code understanding.
Solution
The solution I came up with creates a very simple data access layer using System.Data.SqlClient and Nunit (download NUnit
here). The only complexity that the tester needs to think about is the way they construct the sql and how they write assertions.
Using standard nunit test fixtures, in the test set up I connect to a database, and then in the tests I execute queries and stored procedures, using simple asserts to validate the results.
Here is how its done.
Created a standard class library project that references:
Nunit
nunit.framework
Microsoft
system.data
I'm using a factory pattern with interfaces that allow easy creation of a database session management class which can be used throughout multiple test fixtures. The session manager has methods that create connections to a database defined in a factory, query the database, and execute stored procedures.
The test fixture:
using System;
using NUnit.Framework;
using Codedetective.Database;
namespace Codedetective.Tests
{
[TestFixture]
public class DbChecks
{
readonly IDatabaseSessionFactory _dbFactory;
public DbChecks()
{
_dbFactory = DatabaseSessionFactory.Create
(@"Database=CodeDectiveExamples;Data Source=local\test;
User=*******;Password=******);
}
[Test, Description(“Identify whether TestSet 501 is created and active”)]
public void DbTest01()
{
using (var session = _dbFactory.Create())
{
var query = session.CreateQuery(
@"select count(*) from testset
where testSetId = 501 and Active = '1'");
var result = query.GetSingleResult<int>();
Console.WriteLine("test 1 " + ((result == 1) ? "passed" : "failed"));
Assert.AreEqual(result, 1);
}
}
The database session manager:
namespace Codedetective.Database
{
public class DatabaseSession : IDatabaseSession
{
public string ConnectionString { get; set; }
private SqlConnection _connection;
private SqlTransaction _transaction;
public DatabaseSession(string connectionString)
{
ConnectionString = connectionString;
}
public SqlConnection GetConnection()
{
if (_connection == null)
{
InitializeConnection();
}
return _connection;
}
public SqlTransaction GetTransaction()
{
if (_transaction == null)
{
InitializeConnection();
}
return _transaction;
}
private void InitializeConnection()
{
_connection = new SqlConnection(ConnectionString);
_connection.Open();
_transaction = _connection.BeginTransaction();
}
public void Dispose()
{
if (_transaction != null)
_transaction.Dispose();
if (_connection != null)
_connection.Dispose();
}
public IDatabaseQuery CreateQuery(string query)
{
var command = GetConnection().CreateCommand();
command.CommandText = query;
command.Transaction = _transaction;
return new DatabaseQuery(command);
}
public IDatabaseNoQuery CreateNoQuery(string insertstring)
{
var command = GetConnection().CreateCommand();
command.CommandText = insertstring;
command.Transaction = _transaction;
return new DatabaseNoQuery(command);
}
public void Commit()
{
_transaction.Commit();
}
public void Rollback()
{
_transaction.Rollback();
}
}
}
The interface of the DatabaseSession class:
using System;
using System.Data.SqlClient;
namespace Codedetective.Database
{
public interface IDatabaseSession : IDisposable
{
IDatabaseQuery CreateQuery(string query);
IDatabaseNoQuery CreateNoQuery(string insertstring);
SqlConnection GetConnection();
SqlTransaction GetTransaction();
void Commit();
void Rollback();
}
}
The factory that we use to create the database session manager
namespace Codedetective.Database
{
public class DatabaseSessionFactory : IDatabaseSessionFactory
{
public string ConnectionString { get; set; }
public IDatabaseSession Create()
{
return new DatabaseSession(ConnectionString);
}
public static IDatabaseSessionFactory Create(string connectionString)
{
var sessionFactory = new DatabaseSessionFactory
{
ConnectionString = connectionString
};
return sessionFactory;
}
}
}
The interface for DatabaseSessionFactory:
namespace Codedetective.Database
{
public interface IDatabaseSessionFactory
{
IDatabaseSession Create();
}
}
Finally we create the methods that will be used to execute queries and stored procedures
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
namespace Codedetective.Database
{
public class DatabaseQuery : IDatabaseQuery
{
SqlCommand Command { get; set; }
public DatabaseQuery(SqlCommand command)
{
Command = command;
}
public void AddParameter(string name, object value, System.Data.DbType dbType)
{
var parameter = Command.Parameters.AddWithValue(name, value);
parameter.DbType = dbType;
}
public TResult GetSingleResult<TResult>()
{
return (TResult)Convert.ChangeType(Command.ExecuteScalar(), typeof(TResult));
}
public IEnumerable<TResult> GetResults<TResult>()
{
Type resultType = typeof(TResult);
IList<TResult> result = new List<TResult>();
if (resultType.FullName.StartsWith("System."))
{
using (var reader = Command.ExecuteReader())
{
while (reader.Read())
{
var value = reader.GetValue(0);
result.Add((TResult)(value != DBNull.Value ? value : null));
}
}
}
else
{
var properties = typeof(TResult).GetProperties();
using (var reader = Command.ExecuteReader())
{
while (reader.Read())
{
var entity = Activator.CreateInstance<TResult>();
foreach (var property in properties)
{
var value = reader[property.Name];
property.SetValue(entity, value != DBNull.Value ? value : null, null);
}
result.Add(entity);
}
}
}
return result;
}
}
}
The interface for DatabaseQuery is IDatabaseQuery:
using System.Collections.Generic;
using System.Data;
namespace Codedetective.Database
{
public interface IDatabaseQuery
{
void AddParameter(string name, object value, DbType dbType);
TResult GetSingleResult<TResult>();
IEnumerable<TResult> GetResults<TResult>();
}
}
Now for stored procedures execution, we create a class called DatabaseNoQuery
using System.Data.SqlClient;
namespace Codedetective.Database
{
public class DatabaseNoQuery : IDatabaseNoQuery
{
SqlCommand Command { get; set; }
public DatabaseNoQuery(SqlCommand command)
{
Command = command;
}
public void AddParameter(string name, object value, System.Data.DbType dbType)
{
var parameter = Command.Parameters.AddWithValue(name, value);
parameter.DbType = dbType;
}
public int ExecuteInsert()
{
int rows = Command.ExecuteNonQuery();
return rows;
}
}
}
The interface for DatabaseNoQuery is IDatabaseNoQuery
namespace Codedetective.Database
{
public interface IDatabaseNoQuery
{
int ExecuteInsert();
void AddParameter(string name, object value, System.Data.DbType dbType);
}
}
This is a long way from being a tool such as DbFit which opens up automation to even non programmers, but it serves a purpose, and does it well. The entire team can now write these tests which can be run alongside the rest of the project tests.
