Unit testing a Data Access Layer

I've 开发者_开发百科been reading up on unit testing the data access layer of a project. Most options boil down to:

• Use a dedicated test database but cleanup in the finalizing phase of all unit tests (or do it manually)
• Use the database but don't commit or simply roll back
• Mock the database

In a former project we used to use the rollback way but I like to know more about the other options and how these are best performed. If you have samples/articles/videos/... please share them.

---

You need to have 2 types of tests with a project. unit tests and integration tests

Unit tests test one aspect of your project without the dependencies on data access and presentation. For unit tests you would mock your database and user dependency injection to uncouple your code from the data provider. This leads to a better architecture and enables you to plug in a different data provider if you so wish. e.g. moving from ADO.net to nHibernate.

Integration tests are where you test your whole system and make sure your code can get the correct data from the database etc. For integration tests each developer should have a copy of the database on their workstation against which they can test. You should try to automate the create and population of your database so you can quickly and easily go back to a good copy of the database. Tools like nant and DBFit will help you to script your databases creation.

I wouldn't use a central database to test on as other developers may be testing on it at the same time and it may not be in a good state and you could get false positives and spend ages trying to debug a problem which is not a problem.

---

I prefer to use a test database instead of the do not commit idea.

My dev database has dummy records or a fully sanitized sampling of production data.

My integration testing database is a copy of the actual production database (this version is the one used to test just before I roll changes out live).

---

The main responsibility of DAL is to persist/fetch data from database, so the system under test is DAL + Database. There is no sence to write tests on DAL using database mock - who really cares what sql query was executed to fetch particular entity? It is neccessary to verify whether right entity was selected and all attributes were correctly mapped.

In order to do this I usually clean up database, fill up database with test data and fetch it with DAL methods.

       [SetUp]
    public void SetUp()
    {
        Database.Clear();
        manager = new ServiceManager();
    }

    [TearDown]
    public void TearDown()
    {
        manager.Dispose();
    }

    [Test]
    public void InsertAndLoadOrderContract()
    {
        MinOrderModel model = new OrderBuilder().InsertMinimalOrder(manager);

        Contract contract = TestObjectGenerator.GenerateEntity<Contract>();
        manager.Contract.InsertOrderContract(model.Order.OrderCompositeId, contract);

        Contract selectedContract = manager.Contract.SelectById(contract.ContractId);

        AssertContract(selectedContract, contract);
    }

    private static void AssertContract(IContract actual, IContract expected)
    {
        Assert.That(actual.AgencyCodeOther, Is.EqualTo(expected.AgencyCodeOther));
        Assert.That(actual.AgencyFK, Is.EqualTo(expected.AgencyFK));
        Assert.That(actual.ContractId, Is.EqualTo(expected.ContractId));
        Assert.That(actual.Ident, Is.EqualTo(expected.Ident));
    }

Some parts of this test can be replaced on more convenient ones:

1. It is possible to use DbUnit to fill up database with data
2. Do not clean up database but roll back transaction in "TearDown" method
3. Use more convenient database engine for tests (SQLLite for example)

---

I would mock the database. Dealing with database in a test is painful as you need to create the database, create schema, then drop it, make sure there are no connections hanging around, etc is painful.

Another thing that makes me uncomfortable is the fact that verifying your code logic is "too far away" from the code. I would go down the road of putting Sql functions (connections, commands etc) behind a mockable class and verify that DAL calls the right methods. Also, the tests run a lot faster this way.

Here are a few quick sql abstraction classes and example usage + unit test.

public class SqlConnectionBase : IDisposable {
    private readonly SqlConnection m_Connection;

    public SqlConnectionBase(string connString) {
        m_Connection = new SqlConnection(connString);
    }

    public virtual SqlConnection Object { get { return m_Connection; } }

    public virtual void Open() {
        m_Connection.Open();
    }
    public virtual void Close() {
        m_Connection.Close();
    }

    #region IDisposable Members

    public virtual void Dispose() {
        m_Connection.Dispose();
    }

    #endregion
}

public class SqlCommandBase  : IDisposable{
    private readonly SqlCommand m_Command;

    public SqlCommandBase() {
        m_Command = new SqlCommand();
    }
    public SqlCommandBase(string cmdText, SqlConnectionBase connection) {
        m_Command = new SqlCommand(cmdText, connection.Object);
    }
    public SqlCommandBase(SqlConnectionBase connection) {
        m_Command = new SqlCommand();
        m_Command.Connection = connection.Object;
    }

    public virtual int ExecuteNonQuery() { return m_Command.ExecuteNonQuery(); }
    public virtual string CommandText { get { return m_Command.CommandText; } set { m_Command.CommandText = value; } }

    public virtual void AddParameter(SqlParameter sqlParameter) {
        m_Command.Parameters.Add(sqlParameter);
    }

    #region IDisposable Members

    virtual public void Dispose() {
        m_Command.Dispose();
    }

    #endregion
}
public class SqlFactory {
    public virtual SqlCommandBase CreateCommand(string query, SqlConnectionBase conn) {
        return new SqlCommandBase(query, conn);
    }
    public virtual SqlCommandBase CreateCommand(SqlConnectionBase conn) {
        return new SqlCommandBase(conn);
    }

    public virtual SqlConnectionBase CreateConnection(string connString) {
        return new SqlConnectionBase(connString);
    }

}

public class DBUser {
   public DBUser(SqlFactory factory) {
     m_factory = factory; //dependency constructor, will be used during unit testing
   }

   public DBUser() {
     m_factory = new SqlFactory(); //used during normal execution
   }

   public void DoSomething() {
     var conn = m_factory.CreateConnection("server=servername,database=...");
     var cmd =  m_factory.CreateCommand(conn);
     cmd.CommandText = "Select * from users";
     cmd.ExecuteNonQuery();
   }

   [TestMethod]
   public void DoSomethingTest() {
     var factoryMock = new Mock<SqlFactory>();
     var cmdMock = new Mock<CommandBase>();
     factoryMock.Setup(f=>f.CreateConnection(It.IsAny<string>())).Returns(cmdMock.Object);
     DBUser dbUser = new DBUser(factoryMock.Object);
     dbUser.DoSomething();

     //Verify that DoSomething is called.
     cmdMock.Verify(c=>c.DoSomething());
   }
}