Unit Testing the Data Access Layer, Part 2By Tim Stall
In Part 1 we examined the steps needed to perform unit tests on a data access layer, delving into prerequisites for running the SQL scripts. In this second and final part we'll see how to reset the schema to achieve a good "before" state and then examine how to tie everything together to write test cases for the DAL.
Prerequisite: Reset the Schema
In order to reliably run our tests, the test database schema must always be synchronized with the application's development schema. Because it's too slow to recreate the schema with every test, we need a way to only recreate it when the two are out of sync. Re-syncing the schema consists of creating the test database if it doesn't exist, and re-creating the test database if the development schema has been updated. Determining this requires knowing the last-modified date of both the test and development schemas.
One approach for resetting the test schema is:
- Each developer has their own copy of test database, perhaps even running locally on their machine.
- Have a SQL create-script for the Test database schema. Whenever the development database is successfully updated, those updates are applied to this create script. This will update the last-modified date of the schema file.
- Compare the last-modified date of the schema file to the date of the last schema change in the test database.
- Based on this comparison, run the schema create file.
The tedious details are flushed out in the code download, so let's focus on the technical concepts. Enterprise Manager can generate the SQL create script for any object, including the entire Database itself. Therefore we can use this to generate scripts and keep our schema file up to date.
We can get the last-modified-date of the schema file using the
Getting the last modified date of the test database is a little more involved. We need to find the max create date for all
the objects in the database. Fortunately, the
SysObjects table provides this information by listing every object
and its create date. We can therefore get this date with the following select statement:
GetLastSchemaFileUpdate() wraps this SQL call, returning the last modified date of the test database.
If the database doesn't exist, then this method just returns
DateTime.MaxValue. We can compare the
last-modified dates from the Test database and schema file to determine whether the schema file should be run
RunScript() method from the previous section). If the database's last-modified date is
DateTime.MaxValue, then we know it doesn't exist and should create it. Also, if the schema file is more
recent then the Test database, then we should run the schema to update the Test database.
To summarize, the
TestHelper class has two germane methods:
RecreateDBSchema– Ensures that the database schema is accurate.
RunScript– Runs an arbitrary SQL batch script given the script's file name.
Testing DAL Components
TestHelperclass, we can now easily test Data Access Layer components. The code below shows how to test the
BookDAL.InsertBook()method, which takes a title and author and inserts it into the
Booktable of the database.
We apply NUnit's category attribute to put all our databases in a custom
Database category. This provides the
option of including or excluding these tests from NUnit using NUnit's category tab. (Note that we could replace the literal
Database with a constant.)
Next we call the method
TestHelper.RecreateDBSchema() to ensure that the database matches the expected schema.
Note that if every Test in the class required this, we could refactor this call to method with the
try-block we set the initial state, which in this case is simply clearing the
table. We then call
BookDAL.InsertBook(), which ultimately inserts a value into the database. We can verify
this by querying the database again. Any potential resource clean-up, such as closing datareaders, can be done in the
Note that there's no need to return the database to the "before" state in the
finally-block because each test
initializes its data before running. By cleaning up before the test (as opposed to after it), we're not relying on other
developers to clean up properly.
Testing User-Defined Functions
The database can contain custom user-defined functions (UDFs) with non-trivial logic that should be tested. For example, suppose that when you insert an order you need to generate an OrderNumber based off the newly inserted primary key and some other value from a table lookup. Rather than pass that key and lookup value back to the application, have it do the logic, and then return the value with another database hit, it's simple enough to just have a database function generate the OrderNumber. The code below demonstrates how to test the function
fn_getOrderNumber, which takes an integer (the primary key) and returns the OrderNumber as a string. The SQL script for fn_getOrderNumber is:
The NUnit test for it is:
While we can't directly call a function like we can a stored procedure, we can call it within a SQL select statement:
Therefore we use our
DataHelper.ExecuteSQL() method again to populate a DataTable with the single return value.
We reference that scalar, convert it to a string, and compare it to the expected value.
The steps for testing triggers are similar to the steps we've already examined. Start by initializing the before state, invoke the trigger by deleting, updating, or inserting into the table, and then use SQL statements to query the end condition.
Testing Components that Require Setting up Data
Certain tests might require setting up a large amount of data. For example a Business Object's stored procedure may join with several lookup tables, or a paging control needs enough data to force multi-page functionality. For times like these, our
RunScript()utility method carries the day. The following code provides a template for a complex search by testing
Like all the other tests, we first recreate the database schema. Next, the large blast of data is inserted
InsertValues1.sql SQL script. This script file contains numerous
INSERT statements necessary
for creating the initial data that the
BookDAL.Search() method needs for testing.
This SQL script is executing using the
RunScript() method. Once the needed data has been injected
we can complete our test by calling the
BookDAL.Search() method and checking the DataTable that it returns.
As a tangent, maintaining the SQL script as a list of inserts can be tedious and error prone because the columns don't line up with the values in a tabular format. For example, a script may look like so:
While this works for a small number of statements, it gets increasingly harder to ensure that the right value is going to the right column for SQL scripts that might need hundreds of thousands of statements. One solution is to make an Excel sheet that dynamically assembles the SQL inserts based off tabular values. For example, the sheet below, from the Excel workbook in the download, has three sections:
Section 1 is the data in convenient, tabular format. The developer maintains the data here. Section 2 uses Excel's
powerful formula abilities to dynamically assemble the appropriate
INSERT statements based off the values in
Section 1 and the start of the insert statement in Section 3. Excel will then automatically update the inserts in
Section 2 whenever the values are changed in Section 1. This lets us maintain the data in tabular format (easy for humans)
while always having the insert scripts required for the test cases.
There are three additional topics that we need to cover: Ensuring that the connection information is correct; developing a way to configure the name of the database used in the scripts; and possibly using embedded resources to store the SQL scripts. First, the connection information must be correct, else all the database tests will fail and the entire framework will be useless. One solution is to have a suite of diagnostic tests designed solely for ensuring that the test environment is functional. This should contain at least three tests. If any of the database connection information is wrong, these tests will expose it. The three tests would be comprised of:
- An empty test. This ensures that NUnit is installed correctly.
- A test that accesses the database using the normal connection method.
- A test that accesses the database by executing the
RunScript()method against a test table, and then ensuring that it correctly inserted that data.
RunScript()method to do this:
- Use a token like
~~DatabaseName~~instead of a hard-coded name.
TestHelper.RunScriptmakes a temporary copy of the script, replacing the token with the database name from a config file. You can use
System.IO.Path.GetTempFileName()to create a temporary file for the script copy.
- Run that temp script instead.
- Delete the temp file at the end of the test.
App.Config. You can read more about Embedded Resources in my blog entry Embedded Resources.
Integrating with Legacy Code
Because many development projects require modifying an existing code base, we want to be able to apply this framework to legacy code. Fortunately we can because all of these techniques are additive and can be applied incrementally. They merely add to the project without breaking what already exists. These techniques have stayed away from breaking-changes like forcing all tests to inherit from a new base class, requiring all tests to be run with a modified version of NUnit, or requiring database tests to be stored in a separate directory.
The very nature of a database makes is problematic for unit testing. However given the quality assurance that test coverage provides the project, it's worth investing in a strategy. This article shows how .NET and other Microsoft Products (including OSQL and even Excel) can construct a practical framework for unit testing the database. At the beginning of this article we listed problems with making database unit tests for each test criteria. Now we list solutions for those same criteria.
|Unit Testing Criteria||Problem with Database Unit Testing|
|Thorough||Set the initial state of the database, call the DAL method directly or the SQL function by embedding it in a dynamic SQL call, and then query the new state to verify that the object worked correctly.|
|Repeatable||Reset the data at the start of each test. Because this won't affect auto-incrementing primary keys, don't base the test off that. If we do need a primary key, then use a dynamic SQL statement to get that key given some other unique field that isn't auto-incrementing (such as SSN in an employee table).|
|Independent||Have each test set the data needed for that test. For complicated initializations, make a SQL script, store it in the same directory as the test, and then run the script with the RunScript method.|
|Professional||Optimize the test performance by recreating the schema only when necessary, using only the minimum amount of data needed in the SQL scripts.|
We can create a reusable Test Utility class with methods for running any OSQL script and ensuring that the baseline schema is reset. We can then apply these to test the Data Access Layer as well as different database objects like functions and triggers. Lastly we can set up diagnostic unit tests to check that a machines environment is configured correctly. All these techniques use open source or standard Microsoft tools that you probably already have, and can therefore start being applied right away.
By Tim Stall
About the Author:
Tim Stall is a Chicago-based technical consultant for Computer Sciences Corporation, a global IT services company.