When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips

Book Reviews
Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
Web Hosts
Author an Article

Print this Page!
Published: Wednesday, April 6, 2005

Unit Testing the Data Access Layer, Part 2

By Tim Stall

  • Read Part 1

  • 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:

    1. Each developer has their own copy of test database, perhaps even running locally on their machine.
    2. 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.
    3. Compare the last-modified date of the schema file to the date of the last schema change in the test database.
    4. Based on this comparison, run the schema create file.
    These steps are outlined in the figure below.

    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 System.IO.FileInfo class:

    private static DateTime GetLastFileUpdate(string strFullFilePath) 
       System.IO.FileInfo fi = new System.IO.FileInfo(strFullFilePath);
       return fi.LastWriteTime;

    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:

    select max(crDate) from sysobjects

    The method 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 (using our 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.
    These two methods are the core methods used to testing the DAL components.

    Testing DAL Components

    With our TestHelper class, 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 Book table of the database.

    [Test] public void CreateEmployee() 
          //set initial values
          string strSqlCleanUp = @"delete from Books where 
             title='MyBook' and author='My Author'";
          //run DAL component
          BookDAL.InsertBook("MyBook", "My Author");
          //check final DB was updated
          DataTable dt = null;
          dt = DataHelper.ExecuteSql(@"select count(*) from Books 
             where title='MyBook' and author='My Author'");
          //Do potential resource clean-up here:
    } //end of method

    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 string 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 SetUp attribute.

    Within the try-block we set the initial state, which in this case is simply clearing the Books 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 finally-block. 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:

    CREATE FUNCTION dbo.fn_getOrderNumber (@counter as int)
    RETURNS varchar(50) AS
       Declare @ordercode char(10)
       select @ordercode = Ordercode from Config
       return  RTRIM(@ordercode) + cast(@counter as varchar(5))

    The NUnit test for it is:

    [Test] public void CreateOrderNumber() 
       DataTable dt = null;
       dt = DataHelper.ExecuteSQL(@"select dbo.fn_getOrderNumber(13)");
       string str = Convert.ToString(dt.Rows[0][0]);
    } //end of method

    While we can't directly call a function like we can a stored procedure, we can call it within a SQL select statement:

    select dbo.fn_getOrderNumber(13)

    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.

    Testing Triggers

    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 BookDAL.Search():

    [Test] public void Paging() 
       string strScript = @"Group1\InsertValues1.sql";
       DataTable dt = BookDAL.Search("B");
       Assert.AreEqual(3,dt.Rows.Count,"Row Count doesn't match");
       Assert.AreEqual("Author6", Convert.ToString(dt.Rows[2]["author"]));

    Like all the other tests, we first recreate the database schema. Next, the large blast of data is inserted via the 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:

    insert into books (col1,col2,col3...) values ('val1','val2','val3'...);
    insert into books (col1,col2,col3...) values ('val1','val2','val3'...);
    insert into books (col1,col2,col3...) values ('val1','val2','val3'...);
    insert into books (col1,col2,col3...) values ('val1','val2','val3'...);

    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.

    Advanced Options

    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:
    1. An empty test. This ensures that NUnit is installed correctly.
    2. A test that accesses the database using the normal connection method.
    3. A test that accesses the database by executing the RunScript() method against a test table, and then ensuring that it correctly inserted that data.
    Second, because each developer needs their own personal instance of the test database, we want to be able to make the name configurable. We can modify our RunScript() method to do this:
    1. Use a token like ~~DatabaseName~~ instead of a hard-coded name.
    2. TestHelper.RunScript makes 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.
    3. Run that temp script instead.
    4. Delete the temp file at the end of the test.
    Third, we may want to store the SQL scripts as embedded resources. While using embedded resources requires initial extra overhead and is outside the scope of this article, it would save us from storing the Project's root directory as a key in the 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 CriteriaProblem with Database Unit Testing
    AutomaticCall RecreateDBSchema() to ensure that the schema is first set up, and then set the initial database state via dynamic SQL statements, or running a SQL script with the RunScript() method.
    ThoroughSet 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.
    RepeatableReset 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).
    IndependentHave 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.
    ProfessionalOptimize 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.

    Happy Programming!

    By Tim Stall


  • Download the complete code (in ZIP format)

    About the Author:

    Tim Stall is a Chicago-based technical consultant for Computer Sciences Corporation, a global IT services company.

  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article