Get Test Infected with NUnit: Unit Test Your .NET Data Access Layer


Steven A. Smith

October 2003

Applies to:
    Microsoft® ASP.NET

Summary: Learn how to use NUnit and some related tools to successfully support testing a Data Access Layer for ASP.NET applications. Test-driven development (TDD) has grown in popularity recently, especially with the growth of the Extreme Programming (XP) methodology. NUnit is an open-source unit-testing tool built for .NET, which follows in a long line of similar xUnit testing tools built for other platforms. It provides an easy-to-use framework for writing and running unit tests for your .NET applications. (15 printed pages)

Download NUnitSample.msi.


Test Driven Development
NUnit: A Test Framework for .NET
Related Tools
A Simple Application
Lessons Learned


All developers know their code should be tested to improve its quality. However, most developers hate to test. They especially hate to test their own code, since doing so uses time that instead could be spent writing cool new features and requires them to face their own potentially imperfect code. One way around these psychological barriers to testing is to write tests before writing the code, and to make it clear that the code isn't done until it passes the tests. The tests, in turn, reflect the external requirements of the code's behavior—in essence the tests document the design of the code. Writing tests first as a methodology is known as test driven development (or in some circles test driven design), or TDD.

Test Driven Development

Test driven development, or TDD, is of course too large a topic for this article. You can learn more about it at or from some of the additional resources listed at the end of this article. A few relevant points are explained below.

Improved Productivity

A key principle of TDD is that developers are most productive when they are in the process of fixing a bug in an application (note: not looking for the bug). This is one of the few times during development that measurable progress is being made on the project. While developers spend time on other things, like finding bugs, they aren't able to spend as much time writing code to correct defects (for example, fixing bugs). A key benefit of test driven development (TDD) is to maximize the time developers spend in bug-fix mode, thus maximizing their productivity.

Improved Quality

In TDD, all non-trivial features of an application are tested. These tests are written before the actual code is written, and they are run all the time. Whenever a test fails, the developer immediately corrects the failure. So, when the time comes to add new functionality, the developer assumes the functionality exists and writes a test. He then runs the tests (which often won't even compile if they rely on as-yet-undefined classes and methods), and when they fail, he enters bug-fix mode and begins adding code to the application until he can get all tests to pass once more. Often, the process of writing the test for the functionality will provide the developer with a better understanding of how the production code should be designed, resulting in improved final code quality.

TDD also makes code easier to maintain, upgrade, and redesign, and has other benefits which the reader is encouraged to research.

NUnit: A Test Framework for .NET

NUnit is an open-source testing framework for .NET, modeled after other similar testing frameworks in the xUnit family, such as JUnit for Java (in fact the initial version was a direct port of JUnit). You can download the latest version of NUnit from NUnit.

NUnit provides a simple way for developers to write unit tests of the .NET classes, and comes with a small, sample application that demonstrates its use. The current version is written in C# and relies on attributes rather than inheritance and/or naming conventions to define tests and test suites. The main attributes involved are TestFixture, which applies to a class containing tests, SetUp and TearDown, which are run before and after each test, and Test.

NUnit also provides a simple graphical user interface that lets you select which assembly you want to test and which set of tests within that assembly you want to run. It then runs all of the tests in the assembly (or namespace or class) selected, displaying a green bar if everything passes and a red bar if any tests failed. Details of each failed test are also displayed, making it very easy to locate the cause of the failure.


Figure 1. NUnit's GUI tool provides instant feedback that everything is running as it should be, according to the tests defined so far.


Figure 2. NUnit shows the details of a failure caused by an invalid connection string.

There are other testing tools coming to market for .NET, but today NUnit has the largest support base, and is both free and open source, making it something you may want to try first.

NUnit Visual Studio .NET Addin

This addin, which requires NUnit, allows you to run your tests without leaving the Visual Studio .NET IDE. Simply right-click on a project or solution in the Solution Explorer to run all tests within that project or solution. The results are displayed in the Output window. Unfortunately, there are no pretty green or red bars, but if having one less program open is important to you, this can be a useful tool.


Figure 3. Run tests in Visual Studio .NET with a simple right-click.


Figure 4. Output from NUnit tests run within Visual Studio .NET


NUnitASP allows TDD to be extended to the user interface in ASP.NET, by providing a means of testing Web controls. Its usage is beyond the scope of this article, but you may learn more about it from the NUnitASP Web site.

A Simple Application

To demonstrate TDD, I've used it to help me redevelop a multi-tier sample application for ASP.NET. The application demonstrates how to register a user for an application, how to grant them access to secure web pages, and how to allow them to sign in and sign out. The data access layer for this application is concerned with adding users to the database, retrieving user information, and verifying that user logins are correct. I’ve used this sample for a couple of years now and built it with TDD several times, learning new things with each iteration.

Lessons Learned

Use a Test Database

This should really go without saying, but just to be sure, you should always run your tests against a test database that is completely separate from your production database. The schema should match the production database's schema as closely as possible. The database should contain no data, except perhaps static lookup table data like lists of US states or city-ZIP code tables. All of the data you’ll be testing will be added during the setup of each test.

Database Setup

Set up the test database using a stored procedure. Originally, I was running the tests using setup routines in the code. At first this resulted in the database changing slightly with every test (most notably, IDENTITY columns were growing ever larger). Eventually, I coded enough into the SetUp and TearDown routines in my test code to get the database back to a standard repeatable state, but then I found that I was duplicating a lot of this code within each TestFixture class. Since it was all SQL script, I moved it into a pair of stored procedures, and I found that this made for a much cleaner set of test fixtures. The following code sample demonstrates how to set up simple test_SetUp and test_TearDown stored procedures, with code for resetting IDENTITY columns. The DBCC CHECKIDENT function is key to putting IDENTITY columns back to their original states.

Code sample. SetUp and TearDown stored procedures ensure tests run in a consistent, realistic environment.

exec test_TearDown

exec usp_InsertUser 'John', 'Doe', '', 'password', 0
exec usp_InsertUser 'Jane', 'Smith', '', 'password', 0
exec usp_InsertUser 'Admin', 'User', '', 'password', 0

insert Roles (RoleName) values ('Admins')
insert Roles (RoleName) values ('Power Users')
insert Roles (RoleName) values ('Editors')

-- John Doe
insert UsersInRoles (UserID, RoleID) values (1,1)
insert UsersInRoles (UserID, RoleID) values (1,2)
-- Jane Smith
insert UsersInRoles (UserID, RoleID) values (2,3)
-- Admin User
insert UsersInRoles (UserID, RoleID) values (3,1)

By using the MS Data Access Application Block, the amount of code required to call these procedures is minimized (one line per call). Figure 5 shows an example TestFixture for my Users class, which has methods for adding users to and retrieving them from the database. A test user is defined with private variables and used in each of the tests that require an existing user (this user is added by the test_SetUp stored procedure).


Figure 5. A sample TestFixture

Notice that SetUp and TearDown are each only one line of code. In fact, since these are the same for all tests against this database (not just the ones in this class), I could probably just move these to my test's base class, which defines its ConnectionString property. Also note that I'm calling test_SetUp in both SetUp and TearDown—this is so my front-end application, which uses the same test database, has some data it can use. It would be more efficient to have a totally separate test database, which was completely emptied in each TearDown.

The two tests shown, GetUser, and AddUserFailure, use different techniques to ensure that the underlying Users.GetUser() method is working correctly. In the GetUser() test, assertions are used to verify that all properties of a user extracted from the database match up with expected values. Each AssertEquals() call succeeds if the second and third parameters are equal. If not, then the test fails and the message provided as the first parameter is displayed. The AddUserFailure test uses the ExpectedException attribute defined in the NUnit framework. In this case, the AddUser method should throw an exception if an attempt is made to add a user whose email already exists in the users database. This test will only succeed if an exception of the proper type is thrown.

Most tests consist of some setup (anything not done in the global SetUp routine) followed by one or more assertions. In testing a data access layer, none of my tests have needed to be more than a few lines long (at most one assertion per column/property, as with the GetUser() test above). Once the tests are in place, the data access layer can be refactored with confidence. For example, in my production DAL code, I had the code from some time ago. I added the tests, made sure they covered everything I cared about, and then converted my methods to use the Microsoft® Data Access Application Block, greatly reducing the lines of code necessary. I reran the tests periodically to ensure that everything still worked. Once that was done, I implemented an intelligent caching layer within the DAL, which was largely transparent from the calling code and thus could be tested with the same set of tests (and a few new ones to test the caching features). Having the test suite allowed me to make these fairly drastic changes quickly and with confidence.


In order to maximize the performance of your test suite, you should make sure your setup and teardown scripts are doing as little as possible. Generally, the only test cases that matter with regard to quantity are 0, 1, and more than 1. Thus, there is generally little benefit derived from testing that a table works with fifty rows of data versus testing that it works with two or three rows.

If you have a subset of tests that require a substantial amount of data to be present, place these tests in their own separate test fixture and create separate stored procedures for their setup and teardown. Typically you’ll want these stored procedures to call test_SetUp and test_TearDown internally.

Upgrading to .NET 1.1

If you have NUnit version 2.0 and .NET 1.1, NUnit will not work by default. You can fix this by adding a<startup>section to NUnit's configuration file. Within the startup element, you'll specify supportedRuntime and requiredRuntime versions, to force it to run under 1.1. You can do this for some projects and not others if you want to continue running NUnit 2.0 under .NET 1.0 for some projects. The final section would look like this:

      <supportedRuntime version="v1.1.4322"/>
      <requiredRuntime version="v1.1.4322"/>

Note that the latest version of NUnit is 2.1 at the time of this writing, and provides support for .NET 1.1 intrinsically.

Configuration Files

Setting up configuration files for NUnit can be tricky if you haven't done it before. This is a very common requirement, especially in the data access scenario, since most data access layers depend on a configuration file for their database connection string information. In order to use a configuration file with NUnit, follow these steps:

  1. Copy your ASP.NET web.config to your NUnit test project's output folder (for example, /bin).
  2. Rename the copy to the same name as your NUnit test project assembly, with ".config" added to the end. (if the assembly name is "AspAlliance.Data.UnitTests.dll" then the config file should be "AspAlliance.Data.UnitTests.dll.config").
  3. Edit the new .config file and change your database settings so you are pointing to your test database, not production!

Sample Configuration File: MyCompany.Security.Data.UnitTests.dll.config

<?xml version="1.0" encoding="utf-8" ?> 
   <!-- Required for NUnit 2.0, but not for 2.1 -->
      <supportedRuntime version="v1.1.4322"/>
      <requiredRuntime version="v1.1.4322"/>
      <add key="MyApplication.ConnectionString" 
         database=NUnit;Integrated Security=true" />


Scalability is a valid concern with this approach. If we wanted to simulate production exactly, our setup procedure would backup the production database to the test database before each unit test. Assuming the database is more than a few megabytes, this would take far too long (unit tests should run in seconds, or at worst a few minutes, but no longer, for all but the largest of applications). So, as long as only a sampling of production data is sufficient and covers the relevant variations in the production data, the setup procedure should only involve a few inserts per table, and thus should scale fairly well (see Performance, above).

At some point, for some applications, this technique will simply be too slow to use as frequently as TDD demands. At that point, one option is to scale back the frequency with which these tests are run. Darren Hobbs suggests this in his brief article, Putting the Unit in Unit Tests. Darren contends that most tests that rely on a lot of database setup are too slow and too brittle (prone to breaking during design changes) to be run all the time, as unit tests should be. I agree that if and when such tests do become a burden, one reasonable solution would be to only run them prior to checking code into production, rather than on every build or feature adjustment.

Another option worth exploring is to use mock objects instead of a test database. However, since we're concerned with testing the data access layer here, there is very little for the code to do without a database to talk to, which is why I haven't gone this route. Creating mock objects also takes more development time than writing a couple of setup and teardown stored procedures. A brief comparison of test databases vs. mock objects can be found from the Unit Testing Database Code on the DevDaily Web Site.


Using NUnit, you can quickly detect problems in your data access layer (before your users do). By following a test-first development style, you will write better code and have greater confidence in your ability to modify the code without breaking other parts of the application. Hopefully this article has made you curious to learn more about test driven development, NUnit, and how they can help you build high quality .NET applications.


About the Author:

Steven A. Smith, Microsoft ASP.NET MVP, is president and owner of He is also the owner and head instructor for ASPSmith Ltd, a .NET-focused training company. He has authored two books, the ASP.NET Developer's Cookbook and ASP.NET By Example, as well as articles in MSDN® and AspNetPRO magazines. Steve speaks at several conferences each year and is a member of the INETA speaker's bureau. Steve has a Master's degree in Business Administration and a Bachelor of Science degree in Computer Science Engineering. Steve can be reached at

© Microsoft Corporation. All rights reserved.