Unit Testing the Database Access Layer

Its been fun after getting into Test Driven Development

What I do is, test each individual components, layers in my project before its being used by other components and layers. So, currently am testing the Database Access Layer (DAL). Testing DAL is more tricky and many people follow different methods.

1) Many follow a Repository Pattern to test the DAL

2) Many use a in-memory database to test the DAL

3) Many use development database to test the DAL

4) Many use live database to test the DAL

So, which is best? – That really depends on your interest. For the Repository Pattern and in-memory database you may need to write a full length code which does similar job as your DAL, and that requires some amount of your development time. Development database means that each developer will have access to a development database in his local machine or network server. This is also good because you test as what would happen in the real time. Live database is same as development database, except that you are in high risk as you are working against the live database!

What I have chosen? – Testing against a development database

As I learn more about TDD, I will eventually shift to Repository Pattern

I ran into more problems of implementing the Repository Pattern as my DAL is LINQ-to-SQL and though there were few posts about how to unit-test LINQ-to-SQL layer, It was ending up doing more work for testing!

So, what I did was – I maintained my table data as Lists and passed on to DAL and tested against them.

1) I made my test data ready

private static List<Admin> expectedAdminList;

private void PrepareAdminTestData()
{
    expectedAdminList.Add(new Admin{AdminId = "admin",AdminPwd = "genius"});
}

The data that resides in my list is the data that is already present or in other words, the data which I have put in my development database.

2) So, now running a simple test to get the record count,

[Test]
public void Check_Property_AdminCount()
{
    int nExpected = expectedAdminList.Count;

    AdminContractor adminContract = new AdminContractor();

    int nResult = adminContract.AdminCount;

    Assert.That(nResult, Is.EqualTo(nExpected));
}

Its nothing but, checking against the count of my List

3) What about Insert?

[Test]
public void Check_Method_InsertAdmin()
{
    expectedAdminList.Add(new Admin { AdminId = "chaks", AdminPwd = "chaks123" });

    Admin inputAdmin = expectedAdminList[(expectedAdminList.Count-1)];

    AdminContractor adminContract = new AdminContractor();

    adminContract.InsertAdmin(inputAdmin);

    Admin retAdmin = adminContract.GetAdmin(inputAdmin.AdminId);

    Assert.That(retAdmin.AdminId, Is.EqualTo(inputAdmin.AdminId));

    Assert.That(retAdmin.AdminPwd, Is.EqualTo(inputAdmin.AdminPwd));
}

Add to the list and then insert that new element. And now since your List which is holding the table data temporarily is updated, even the above test to count the number of records also passes, as we test the record count against the list count

Update and Delete also hold the similar way – Change the list, send to DAL

You have to keep one thing in mind when Deleting – you have to delete the item from the list too after being successfully removed from the database

Here it is,

[Test]
public void Check_Method_DeleteAdmin()
{
    bool blnExpected = true;
    Admin inputAdmin = expectedAdminList[(expectedAdminList.Count-1)];
    string inputAdminId = inputAdmin.AdminId;

    AdminContractor adminContract = new AdminContractor();

    adminContract.DeleteAdmin(inputAdminId);

    bool blnResult = adminContract.CheckAdminIdAvailability(inputAdminId);

    Assert.That(blnResult, Is.EqualTo(blnExpected));

    expectedAdminList.Remove(inputAdmin);
}

You can see that if the Assert passes,we delete that item from our list.

Remember one thing that if you want to run this test individually (of course we do want it ), use the [Setup] attribute (NUnit)

Here are my test results,

<Educator.Tests> (32 tests), [0:03.88] Success
  Educator.Tests.BusinessLogicTests (32 tests), [0:03.88] Success
    AdminContractTest (21 tests), [0:03.71] Success
      Check_AdminContract_Default_Contructor, [0:00.01] Success
      Check_Property_AdminCount, [0:01.99] Success
      Check_Method_GetAllAdmins, [0:00.14] Success
      Check_Method_GetAdmin_By_Id_Parameter, [0:00.32] Success
      Check_Method_GetAdmin_By_Name_Parameter_Exception, [0:00.03] Success
      Check_Method_InsertAdmin, [0:00.23] Success
      Check_Method_InsertAdmin_Exception, [0:00.15] Success
      Check_Method_UpdateAdmin, [0:00.10] Success
      Check_Method_CheckAdminIdAvailability_True, [0:00.01] Success
      Check_Method_CheckAdminIdAvailability_False, [0:00.00] Success
      Check_Method_DeleteAdmin, [0:00.03] Success
      Check_Property_DepartmentCount, [0:00.01] Success
      Check_Method_GetAllDepartments, [0:00.01] Success
      Check_Method_GetDepartment_By_Id_Parameter, [0:00.04] Success
      Check_Method_GetDepartment_By_GUID, [0:00.10] Success
      Check_Method_InsertDepartment, [0:00.04] Success
      Check_Method_InsertDepartment_Exception, [0:00.01] Success
      Check_Method_UpdateDepartment, [0:00.03] Success
      Check_Method_CheckDepartmentIdAvailability_True, [0:00.01] Success
      Check_Method_CheckDepartmentIdAvailability_False, [0:00.01] Success
      Check_Method_DeleteDepartment, [0:00.01] Success
    LoginContractTest (11 tests), [0:00.17] Success
      Check_LoginContractor_Default_Constructor, [0:00.01] Success
      Check_Method_CheckIdAvailability_Return_False, [0:00.03] Success
      Check_Method_CheckIdAvailability_Return_True, [0:00.00] Success
      Check_Property_UserCount, [0:00.00] Success
      Check_Method_GetAllUsers, [0:00.01] Success
      Check_Method_GetUser, [0:00.00] Success
      Check_Mthod_Login_True, [0:00.01] Success
      Check_Mthod_Login_False, [0:00.01] Success
      Check_Method_Register, [0:00.01] Success
      Check_Method_UpdateUser, [0:00.01] Success
      Check_Method_DeleteUser, [0:00.03] Success

I am sure this is not the best way, but it works for now