Typically, unit testing your data-access class follows a pretty vanilla pattern. If you're using the repository pattern, for example, it's going to look something like:

public interface ICustomerRepository {
    Customer Get(int id);
}
public class CustomerRepository: ICustomerRepository {
    public Customer Get(int id) {}
}

[TestMethod()]
public void Test_Customer_Repository_Returns_Customer_When_Passed_Valid_Id()
{
    //setup
    var mockICustomerRepo = new Mock<ICustomerRepository>();
    var customerRepo = new CustomerRepository(mockICustomerRepo.Object);
    mockICustomerRepo.Setup(c => c.Get(1))
        .Returns(new Customer{ Id = 1, FirstName = "Sanjay", LastName = "Uttam"}.Verifiable();
    var result = customerRepo.Get(1);
    //assert some stuff
}

That's all fine and well, and it may sufficient in certain applications. But what happens when you want to test whether your repository methods populate objects correctly? Perhaps you've got one or two queries that are still using stored-procedures for performance or legacy reasons. In that case, the above test is really evaluating the correctness of your Get method in the Customer class on the assumption that at run-time, the stored-procedure or query returns the correct data.

A simple contrived example:

//Customer Class
public class Customer{
    public int Id {get; set;}
    public string FirstName {get; set;}
    public string LastName {get; set;}
    public DateTime DateOfBirth {get; set;}
}

//Your DB query or stored-procedure contents
SELECT Id, FirstName, LastName FROM Customer WHERE Id = @Id

In this example, our query is missing the DateOfBirth column. That's an easy omission to make, especially if the column is new in the Customer table. There is at least one major issue with this besides Customer.DateOfBirth not being populated. Your unit test doesn't actually verify that the correct data is returned, because the unit test returns an object that hasn't been created from the database engine. It's something easy to fix in this example, but I've worked on systems in the past where the most critical logic in the application was a stored procedure that pulled logs of what a user has consumed (including quantity, calories, serving size, etc.). The stored-procedure was highly efficient when compared to working with an ORM, but at the cost of complexity. It did all sorts of fun stuff, including PIVOTs, CTEs, and running some basic calculations. It's easy to miss making changes in that type of scenario, but you can work around that.

One approach to this is using an in-memory database at test-time to actually run the queries you need to run and ensure that it does in-fact provide the desired result. Here is the basic sequence of steps we need to take:

1 - In our unit-test class' set-up event, create a SqlLite instance, create all objects, and insert all relevant data

2 - Run the repository class methods you need to test. You'll need to create a mechanism for injecting a database engine instance into your actual repository class, so you can provide an instance of SqlLite rather than SqlServer (or whatever you're using at runtime).

3 - In your unit test class' tear down event, destroy the database you created.

There is a full link to the source below, but here's a snippet of the important bits.

    [TestClass]
    public class CustomerRepositoryTests
    {
        public Container Container { get; set; }
        public IDbConnectionFactory DbFactory { get; set; }

        [TestInitialize]
        public void TestInitialize()
        {
            Container = new Container();
            Container.Register<IDbConnectionFactory>(
                new OrmLiteConnectionFactory(":memory:", false, SqliteDialect.Provider));
            DbFactory = Container.Resolve<IDbConnectionFactory>();

            using (var db = DbFactory.Open())
            {
                db.CreateTable<Customer>(overwrite: true);
                db.Insert(new Customer
                {
                    FirstName = "Sanjay",
                    LastName = "Uttam"
                });
            }
        }

        [TestMethod]
        public void Test_Get_Customer_By_Id()
        {
            var result = new CustomerRepository(DbFactory).Get(1);
            Assert.AreEqual(1, result.Id);
        }
    }

Not surprisingly, this might be overkill for you, but hey, as with anything else, it depends. There are a couple of pros and cons that come to mind immediately.

You can find a demo on GitHub. Note, my demo uses ServiceStack 3.9.71, but this approach doesn't require you to.

Pros

• Opportunity to test actual data-access logic. This is likely only equitable in terms of time/value when data-access code is complex and/or external (e.g., stored procedure) or when there data-specific conditions that you must verify are treated correctly.

• Ability to create certain data-related conditions and ensure data-access acts in the desired manner

Cons

• Objects and data must be accurately created at set-up and tear-down (There might be a way to create the in-memory database on the fly with some hacking, though)

• SqlLite query syntax will most likely not be a 1:1 with your actual database engine.

comments powered by Disqus