Unit testing with SQLExpress

When creating unit tests for your data access code you should also have a database with well know data that should not change. Recently I needed one for a unit test project of a resource access assembly using Entity Framework 4.1. The tests should work for every developer and also on a build server. During my study for the MCTS 70-516 exam (Accessing Data with .NET 4) I found out that you can use SQL Server Express to connect to a MDF file by using “AttachDbFilename” in the connection string. So my college made a copy of the MDF file from his development SQL Server and placed it as content file in the test project in Visual Studio. But it took some trial and error to get it working correctly.

First make sure that “Enable deployment” is checked in the Deployment page of the Local test settings, so the deployment items of the test project will be copied to the “Out” directory of the test run (see: How to: Configure Test Deployment). In the connection string “|DataDirectory|” can be used like: “AttachDbFilename=|DataDirectory|\ EFDatabase.mdf”. It will be automatically replaced to the “Out” directory of the current test run by the unit testing framework of Visual Studio.

Then use the DeploymentItem attribute, with the name of your MDF file, above the test methods that needs the database. This will give every test run a fresh copy of the database.

Do not use “Initial Catalog” or “Database” in the connection string. This causes a SqlException like “Database ‘C:\…\Out\EFDatabase.mdf’ already exists. Choose a different database name.” at the second test run. Omitting “Initial Catalog” or “Database” lets SQL Server attach the file with a generated unique database name. However every test run will attach a new copy of the database to your SQL Server instance and you have to detach them manually.

With some custom code (calling the “sp_detach_db” stored procedure from the master database) in the ClassCleanup method I managed to detach the database automatically. But there is a far more convenient solution by using SQL Server Express Edition User Instances! It can be activated with “User Instance=True” in the connection string:

<configuration>
<connectionStrings>
<add name="EFContext" providerName="System.Data.SqlClient" connectionString=
"Server=.\SQLExpress;
AttachDbFilename=|DataDirectory|\EFDatabase.mdf;
Trusted_Connection=Yes;
User Instance=True" />
</connectionStrings>
</configuration>

A user instance is similar to a normal instance but it is created on demand while normal instances are created during setup. The service account for a user instance is the Windows user who opened the SQL Client connection to the database. When a connection with this connection string opens successfully, the user application is connected to a user instance of SQL Server Express running as the user who opened the connection. Because the account of the user is the service account for the instance, it has full administrator rights to the attached database. So there is no need for entering users into the test database and setting their memberships! The unit test will work for every developer and build server in the project!

The sqlservr.exe process that is started is kept running for a while after the last connection to the instance is closed. After this period it automatically shuts down and all databases are
detached.

Advertisements

One Response to Unit testing with SQLExpress

  1. Alexander says:

    Just to make some infrastructure detail explicit: You need a SQL-Express installation to run these tests. If you use VS 2010, you need not junger as SQL Express 2008R2. (VS 11 is able to deal with SQL Express 2012). Note: I you want to use an existing database, (may be has detached from other SQL Server), make sure, that that SQL Server hat 2008R2 or earlier version to have the mdf file in the proper version.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: