Ways of unit testing data access layer

Question

I have be trying to look for an effective way in unit testing my data access layer in C#. I'm primary a Java developer and have only used C# for about 6 months, in the past i've used a library called DBUnit to test against a known state database. I haven't been able to find a similar active library that can be used, closest seems to be nDBUnit but it hasn't been active for awhile now.

There seems to be a lot of conflicting methods on how and why in C#. Ideally I want to test the data access layer using mocking without the need to connect to a database and then unit test the store procedure in a separate set of tests.

In the system I'm working on, the data access layer is to use ADO.net (without the use of the Entity Framework) to call store procedures on a SQL Server.

Below is a sample code of what I have to work with; to go down the mocking path, I would have to be able to mock the SqlCommand (using IDbCommand) and/or mock the SqlConnection.

So my question is what seems to be the best way (if there is such a thing) to do this? So far the only way would be to make Proxy object that is passed into the constructor so it can return the mocked Sql* objects for testing.

I haven't had a chance to look at all the available C# mock libraries available yet.

public class CustomerRepository : ICustomerRepository
{
   private string connectionString;

   public CustomerRepository (string connectionString)
   {
     this.connectionString = connectionString;
   }

   public int Create(Customer customer)
   {

     SqlParameter paramOutId = new SqlParameter("@out_id", SqlDbType.Int);
     paramOutId.Direction = ParameterDirection.Output;
     List<SqlParameter> sqlParams = new List<SqlParameter>()
     {
       paramOutId,
       new SqlParameter("@name", customer.Name)
     }

     SqlConnection connection = GetConnection();
     try
     {
       SqlCommand command = new SqlCommand("store_proc_name", connection);

       command.CommandType = CommandType.StoredProcedure;

       command.Parameters.AddRange(sqlParams.ToArray());

       int results = command.ExecuteNonQuery();

       return (int) paramOutId.Value;
     }
     finally
     {
       CloseConnection(connection);
     }

   }

}

Efren

Answer 1

It's unfortunate that you can't find a tool that puts your database into a known state and lets you run your CustomerRepository against the database to test the CustomerRepository. However, the answer is not to start using mocks to mock out all of the ADO calls. By doing that, you end up creating a unit test that doesn't really test any logic: it's just testing that the code is written the way you think it should be written.

Let's say that I end up writing a SQL INSERT as my command to create the customer in the SQL database. Now let's say that we're making a change so that the customer table has different fields (that breaks our INSERT command) and that now we should be using a stored procedure to create the customer. The test with mocks would still pass, even though the implementation it's testing is now broken. Additionally, if you fixed the implementation to use stored procedures, your unit tests would now fail. What is the point of a unit test if it continues to pass when it should fail but then would fail when you fixed the system?

See this question for some possible alternatives. It looks like the marked answer is to actually just end up using DBUnit in C# using IKVM.

So, there might be alternative avenues to continue to explore, but mocking the ADO calls is just going to lead to brittle tests that don't really test anything important.

Calves

Thanks, I gave this a better look over the weekend, I agree that the test should access a real database, with our Java projects it has worked just like that especially as tables and column names have changed as the projects grew. I took a look at the IKM method, I'd rather not introduce anything too complex for the other developers to manage and understand. – Efren

Exactly, this is why you should create a service that uses a repository/DAL. You mock the repository, inject it into the service and test that service. Then you're testing the logic in the service in isolation. If the repository breaks (returns incorrect data), the logic will fail and the test will fail. It's always funny to see people mocking a repository and then asserting returns from that repository. You're not testing anything with that. – Cloudland

Answer 2

This layer's job is to connect the code to the database. It has to encapsulate the knowledge about db connection and syntax. In usually maps the domain language to database language. I look at this part of the unit tests as integration test, and as such I test that the database schema is equivalent against real or test database. More on the subject here.

Siftings

Thanks, I think I looked too munch into this, I originally wanted to do that but with the lack of such libraries in c# I started to look for an alternative. – Efren

Answer 3

To test DataAccess Layer you will need a more complex structure.

DataAccess Layer will call references from Repository objects. Repo object will call references from Entity Framework DbSets via UnitOfWork design pattern.

DataAccess Layer (TOP)
|
UnitOfWork
|
Repository Pattern Classes
|
EF Context
|
Actual Database

After you set structure, then you will mock repository classes. For example, items will be inserted to DB instead will go to mock object. Later you will assert against your mock object to see item inserted or not.

Please take a look at Implementing the Repository and Unit of Work Patterns

Penicillin

Answer 4

For writing unit test cases, you will have to use DB interface methods and mock those interface methods. we cannot mock predefined classes and hence you will have to use interface. If you think you need any command or datareader instance that should be of a predefined class, then write an interface method in which you typecast your interface instance, change it to class type, use the class properties and again return interface instance. For example, If you need a oracleCommand, Create IDBCommand and pass IDBCommand to ur interface and change the type, use it and return it.

Ex code ,

 public interface IDbConnectionFactory
    {
        IDbConnection GetConnection();
        IDbCommand BindByName(IDbCommand dbCommand);
    }


public class DbConnectionFactory : IDbConnectionFactory
{

    public IDbConnection GetConnection()
    {
        try
        {
            var connection = new SQLConnection();

            var connection = connection.CreateConnection();
            if (connection != null)
            {
                connection.ConnectionString = _connectionString;
                return connection;
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
        }
        return null;
    }

    public IDbCommand BindByName(IDbCommand dbCommand)
    {
        var command = (SQLCommand)dbCommand;
        command.BindByName = true;
        return command;
    }
}

//Now write ur DB test case

public void CustomerRepository_Create_Returns_Int()
{

    var customer = new Customer(); //Initialise values of customer here
    var _refCustomerRepository = new CustomerRepository();
    var _mockDBConnection = new Mock<IDbConnection>();

    var _mockDBCommand = new Mock<IDbCommand>();

    var _mockDBConnectionFactory = new Mock<IDbConnectionFactory>();

   

    _mockDBConnectionFactory.Setup(c =>
        c.GetConnection()).Returns(_mockDBConnection.Object);

    _mockDBConnection.Setup(m => 
        m.CreateCommand()).Returns(_mockDBCommand.Object);

    // for out value. Rename "@out_id" to "out_id". 
    _mockDBCommand.Setup(m => m.Parameters.Add(ParameterDirection.Output));
    // Rename ur paramter from "@name" to "name", it'll work
    _mockDBCommand.Setup(m => m.Parameters.Add("name"));

    _mockDBCommand.Setup(m => m.Parameters.AddRange(5));

    _mockDBCommand.Setup(m => m.ExecuteNonQuery()).Returns(1);

    var result = _refCustomerRepository.Create(saveCustomerSettings);

    //  Assert your result here

}

// For testing execute reader

var _mockDataReader = new Mock<IDataReader>();

    _mockDataReader.SetupSequence(_ => _.Read())
                      .Returns(true)
                      .Returns(false);

// Do this for all columns that a query/SP returns 
   _mockDataReader.Setup(x => x["COLUMN_NAME"]).Returns("RETURN_VALUE");
   _mockDataReader.Setup(x => x["COLUMN_NAME"]).Returns("RETURN_VALUE"); 
   _mockDataReader.Setup(x => x["COLUMN_NAME"]).Returns("RETURN_VALUE");

For Ex, if returns customer having properties such as FIRST_NAME,LAST_NAME, EMAIL

   _mockDataReader.Setup(x => 
            x["FIRST_NAME"]).Returns("giveAnyValueYouWantThisColumnToReturn");
   _mockDataReader.Setup(x => 
            x["LAST_NAME"]).Returns("giveAnyValueYouWantThisColumnToReturn"); 
   _mockDataReader.Setup(x => 
            x["EMAIL"]).Returns("giveAnyValueYouWantThisColumnToReturn");

Demetriusdemeyer