Description

This code sample will provide a method to unit test backend database operations, adding records, updating records and removal of records using ms-access database table.

I’ve been on various developer forums where developers have issues with their data operations and in most cases are not only dealing with the data operations but also how information is passed to the data operations via controls such as TextBox, ListBox, DataGridView, ComboBox etc along with not writing SQL operations properly e.g. not using parameters.

By following the examples here you will learn how to separate form controls from the operations in a data class.

The first step is to know what operations will be performed in your application e.g. read, add, edit, delete are not common but you may also want to test other things to yet I will focus on read, add, edit and delete.

For those who are writing their data operations in a form, stop now and write your data operations in a class that resides in a class project that your front end desktop application will call to perform the data operations.

By placing data operations in a class project this permits these operations to be used in more than one form in a project or perhaps in the future you need to create another application using the same functions be it the same database tables or not, now you have a framework to reuse.

Once you have written the methods in a class within a class project create a unit test project, add a unit test (or rename the default unit test) and name it say DataOperationsUnitTest. That way if there will be unit test for non data operations they are easy to see. 

In Test Explorer we can see our test.

In my C# version of this sample topic I used traits which are not available in VB.NET but that is not a problem.


 

Set up for working with data is done by finding the desktop project, locating the database and copying the database to the unit test project. This is done in a base class that we inherit in the unit test. Note UpperFolder method (language extension included) allows us to traverse the unit test project, up to the solution folder, down to our desktop project named FrontEnd, drill down to bin\Debug and get our database

The magic to copy the database is by inheriting the base class TestBaseData which in turn allows us to call CopyDatabase in test init which means each time a unit test runs the database is copied fresh, similar to copy to output folder for a desktop project. There are other ways to do this with a unit test file configure file but that is harder to follow along with as the file is not visible in the solution and my focus is on the actual test.


 

Rather than go through each unit test what I want to stress is that the production data class (not in the unit test project) was written with no mind to the unit test meaning when some developers write unit test they are tempted to alter the production code to allow them to do easier unit testing, that is not right. A unit test will test operations that your front end project does.

Study the test, note how in the add new record there are two test, one passes with adding a record and one passes without adding a record. The one that does not add a record is expected to fail because I intentionally did not pass a required field value which violates a table constraint. I also do a good and bad test on a find method also. If and when a test fails the second parameter to the Assert method will display and you'll know immedately what failed especially when there is more than one assertion in a test method.

VERY IMPORTANT Unit test allow for repeating an operation that may take many steps for a user to do through a user interface and may not be done the same way each time. If there is a failure in the app at runtime and does not fail in the unit test we can then look at "did we use proper assertion?", did you use the same database schema, was there an issue with permissions or drivers missing etc.

Solution view