Unit testing with SQLExpress

28 October 2011

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.