C# Unit test for stored procedure methods

Question

Instead of Entity Framework, I am using enterprise library to perform the data operations.

The following is my sample BL method which is used to update the person culture. I don't have idea about how to write unit test for these kind of methods.

public static Culture UpdatePersonCulture(Guid userId, Culture culture)
{
    try
    {
        if (userId == Guid.Empty)
            throw new exception("User id should not be empty");

        DatabaseProviderFactory factory = new DatabaseProviderFactory();
        Database database = factory.CreateDefault();

        using (DbConnection dbConnection = database.CreateConnection())
        {
            if (dbConnection.State == ConnectionState.Closed)
                dbConnection.Open();

            DbCommand dbCommand = database.GetStoredProcCommand("usp_UpdatePersonCulture");
            database.DiscoverParameters(dbCommand);
            dbCommand.Parameters["@userId"].Value = userId;
            dbCommand.Parameters["@cultureId"].Value = culture.Id;

            DataSet dataSet = database.ExecuteDataSet(dbCommand);

            return BLDataPopulation.PopulateCultures(dataSet.Tables[0]).FirstOrDefault();
        }
    }
    catch (Exception exception)
    {
        throw BLLogger.Log(exception);
    }
}

Answer 1

Change your classes to be unit test-able

A unit test means given an input, my code will behave in a known behavior and produces a known-in-advance output. It should execute fast because it executes often (could even be executed whenever the code changes like with NCrunch). A unit test scope should be a class.

Integration tests are tests for which you test at a higher scope. You test whether several class, part of programs, or even different programs work well together. However, the stability of these tests (since it could rely on external components that could fail from time to time), the definition of "working well" which remains at best blurry, and the maintenance costs of such tests, make the added value of integration tests really low. In fact, I often saw projects without any of them or even integration tests decommissioned because of the maintenance costs.

However, you can limit the untestable code to the minimum:

• Separate interfaces from the implementation: isolate external resource (like file I/O or database) access behind an interface hiding the implementation complexity.

• Avoid static code, because it's impossible to unit test static code. Implementation is rigged in with static classes. If the static code calls external resources then you cannot control input and output and you cannot test the result then.

• Learn to refactor: Legacy code can be easily handled if you build the skills to do so. Isolate a small piece of code, write a unit test for it, and replace it with more robust code.

• Test first or Test driven development, TDD can make the newly written code testable from the beginning. It's a process involving writing the expectations you have for a given class before writing the implementation of the class.

• Behavior driven development or BDD: Sometimes when all you have in your basket, is customer requests, you cannot easily go to the lowest level required for unit tests. You can use tools like Specflow to build a requirements grammar and transform it to tests.

In order to successfully write unit tests and refactor your legacy untestable code, you'll need IOC tools (since constructors signatures have tendency to grow with theses techniques) and Mock framework like Moq in order to simulate the interfaces you'll add while not binding them to any implementation.

For example in your case, I've refactored the class containing the method to test:

public class ClassToTest : IInterfaceToTest
{
    private readonly IBLLogger _logger;
    private readonly IBLDataPopulation _blDataPopulation;
    private readonly IDatabase _database;

    public ClassToTest(IBLLogger logger, IBLDataPopulation blDataPopulation, IDatabase database)
    {
        _logger = logger;
        _blDataPopulation = blDataPopulation;
        _database = database;
    }

    // this cannot be static if you want
    // to unit test the classes depending on this one.
    public Culture UpdatePersonCulture(Guid userId, Culture culture)
    {
        try
        {
            if (userId == Guid.Empty)
                throw new Exception("User id should not be empty");

            var dataSet = _database.ProvideCultureMappings(userId, culture); 

            return _blDataPopulation.PopulateCultures(dataSet.Tables[0]).FirstOrDefault();

        }
        catch (Exception exception)
        {
            throw _logger.Log(exception);
        }
    }
}

the interfaces I added:

public interface IDatabase
{
    DataSet ProvideCultureMappings(Guid userId, Culture culture);
}

public interface IBLLogger
{
    Exception Log(Exception exception);
}

public interface IBLDataPopulation
{
    IEnumerable<Culture> PopulateCultures(DataTable dataTable);
}

the result:

[TestFixture]
public class DatabaseUnitTest
{
    [Test]
    public void Test()
    {
        var logger = new Mock<IBLLogger>(MockBehavior.Strict);
        var blDataPopulation = new Mock<IBLDataPopulation>(MockBehavior.Strict);
        var database = new Mock<IDatabase>(MockBehavior.Strict);
        var toTest = new ClassToTest(logger.Object, blDataPopulation.Object, database.Object);
        var userId = Guid.NewGuid();
        var culture = new Culture{Id = "MyId"};
        var dataSet = new DataSet();
        var table = new DataTable();
        dataSet.Tables.Add(table);
        database.Setup(x => x.ProvideCultureMappings(userId, culture)).Returns(dataSet);
        var cultureList = new List<Culture> {culture, new Culture {Id = "AnotherCulture"}};
        blDataPopulation.Setup(x => x.PopulateCultures(table)).Returns(cultureList);
        var result = toTest.UpdatePersonCulture(userId, culture);
        Assert.AreEqual(result.Id, culture.Id);
    }
}

and as a bonus the non unit testable class:

public class Database : IDatabase
{
    public DataSet ProvideCultureMappings(Guid userId, Culture culture)
    {

        DatabaseProviderFactory factory = new DatabaseProviderFactory();
        Database database = factory.CreateDefault();

        using (DbConnection dbConnection = database.CreateConnection())
        {
            if (dbConnection.State == ConnectionState.Closed)
                dbConnection.Open();

            DbCommand dbCommand = database.GetStoredProcCommand("usp_UpdatePersonCulture");
            database.DiscoverParameters(dbCommand);
            dbCommand.Parameters["@userId"].Value = userId;
            dbCommand.Parameters["@cultureId"].Value = culture.Id;

            DataSet dataSet = database.ExecuteDataSet(dbCommand);
            return dataSet;
        }
    }

    private DataSet ExecuteDataSet(DbCommand dbCommand)
    {
        throw new NotImplementedException();
    }

    private void DiscoverParameters(DbCommand dbCommand)
    {
        throw new NotImplementedException();
    }

    private DbCommand GetStoredProcCommand(string uspUpdatepersonculture)
    {
        throw new NotImplementedException();
    }

    private DbConnection CreateConnection()
    {
        throw new NotImplementedException();
    }
}