Update: I like the looks of Jess Chadwick’s solution better… I’ll post another update after I try it.
Problem: I want to have a test project that I can easily give to someone else and they can run all the tests – even tests that run against SQL Express.
Sounds simple, but it doesn’t seem to be. Here is one post that outlines the issues of using the SQL Express (SqlE) database in a DataDirectory: http://social.msdn.microsoft.com/Forums/en-US/vststest/thread/f6325a92-0231-45f3-92a5-176cc45691ad
So I was looking for a solution that would not require any work by someone if I just gave them the projects. The post outlines what I found. I did get it to work, but it does seem to take quite a bit of code. There are two different solutions listed here. The first attaches and detaches in the test file. The second puts an executable together than can be called via a batch file in the Setup and Cleanup scripts for your Test Run Configuration.
Overview
Regardless of which scenario is right for you, the general idea is to have your SqlE Data and Log files deployed with your test run configuration, attach them when needed, then detach them when you are done. I was doing some work with the Entity Framework, so my database files for the data and log respectively are: EF.mdf and EF_log.ldf. With that in mind here is how I have my Deployment setup for my test run configuration:
So now your database files are deployed for testing, but they need to be attached… and the deployment directory looks something like this: F:\Visual Studio 2008\Your Solution\TestResults\UserName_MachineName 2009-08-02 08_07_42\Out, and of course is different for each test run.
Attaching/Detaching in Code
This works nicely if you only have one test file or are happy with attaching/detaching with each test or test file. Attaching is fairly simple. Here is the code for that:
using System;
using System.Collections.Specialized;
using System.Data.EntityClient;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Security.AccessControl;
using System.Security.Principal;
using System.Threading;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.VisualStudio.TestTools.UnitTesting;
(… skipping the class declaration and only showing the code snippets…)
private string DataSource { get { return @”.\SQLEXPRESS”; } }
private string InitialCatalog { get { return @”EF”; } }
private Server SqlServerWindowsAuth
{
get
{
if (ReferenceEquals(_sqlServer, null))
{
_sqlServer = new Server(DataSource);
ServerConnection sConn = _sqlServer.ConnectionContext;
sConn.LoginSecure = true;
}
return _sqlServer;
}
}
internal bool IsDBAttached
{
get
{
return SqlServer.Databases.Contains(InitialCatalog);
}
}
private string DataFilePath
{
get { return TestContext.TestDeploymentDir + @”\EF.mdf”; }
}
private string LogFilePath
{
get { return TestContext.TestDeploymentDir + @”\EF_log.ldf”; }
}
internal StringCollection DBFiles
{
get
{
StringCollection files = new StringCollection();
files.Add(DataFilePath);
files.Add(LogFilePath);
return files;
}
}
internal void AttachDatabase()
{
if (!IsDBAttached)
{
SqlServer.AttachDatabase(InitialCatalog, DBFiles);
}
}
internal void DetachDatabase()
{
if (IsDBAttached)
{
using (SqlConnection conn = new SqlConnection(SimpleConnectionString))
{
// Thit technique will let you gracefully close all the connections.
// I could not find a way to do that using Smo. I would always get
// the error that there were active connections so I could not
// Detach the database.
string cmdText = “USE master\n”
+ “ALTER DATABASE “ + InitialCatalog + “\n”
+ “SET OFFLINE WITH ROLLBACK IMMEDIATE\n”;
conn.Open();
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.ExecuteNonQuery();
conn.Close();
SqlServer.DetachDatabase(InitialCatalog, false, false);
}
}
}
(I now cannot find the blog post for the person that solved this… I do remember that her site was pink… I think it was Julian Kuiters, but her site seems to have issues at the moment.)
Now that seems pretty simple. And as long as you only need to Attach and Detach one time. But it gets more interesting if you need to Attach and Detach multiple times. The primary issue is that it seems on my box that regardless of how I have permissions set, the Detach process removes the necessary permissions for reattaching the database. So now I need to add some code to fix that problem. For that code I need to credit TaylorMichaelL in this post: http://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/c513ca26-9bf8-4e39-a993-4ebf90aaece6/ .
private void SetFilePermissions()
{
SetFilePermissions(DataFilePath);
SetFilePermissions(LogFilePath);
}
private void SetFilePermissions(string path)
{
// Get the security identifier for “Everyone”… We’re not worried about permissions
// in this test scenario.
SecurityIdentifier everyoneSid = new SecurityIdentifier(WellKnownSidType.WorldSid, null);
NTAccount everyoneAcct = (NTAccount)everyoneSid.Translate(typeof(NTAccount));
FileSecurity dataSec = File.GetAccessControl(path);
dataSec.AddAccessRule(new FileSystemAccessRule(everyoneAcct, FileSystemRights.FullControl, AccessControlType.Allow));
File.SetAccessControl(path, dataSec);
}
Then I just modify the AttachDatabase() method you saw earlier by adding SetFilePermissions() before the Attach method.
internal void AttachDatabase()
{
if (!IsDBAttached)
{
SetFilePermissions();
SqlServer.AttachDatabase(InitialCatalog, DBFiles);
}
}
So with this code I seem to be able to attach and detach as much as I like and the performance doesn’t seem that bad. A test that does two attaches, two queries, and two detaches takes 2 seconds… I clearly wouldn’t want to do that for every test, but comes in handy…
Attaching/Detaching via .bat in Setup/Cleanup
Now let’s say that I’m not testing what happens if the connection is dropped, and that all I want to do is have the database attached for that test run. It’s been a while since I’ve done this, but it seems that if I attach if not attached, but then never detach, I run into trouble. Therefore, I’d like to detach. Therefore I created a console application that can detach a database, then created a detach.bat file that has this line (DetachTestDatabase.exe was the name of the console app I created):
“{f073afa9b3cad59b43edffc8236236232bb532d50165f68f2787a3c583ed137f}TestDeploymentDir{f073afa9b3cad59b43edffc8236236232bb532d50165f68f2787a3c583ed137f}\DetachTestDatabase.exe”
Also, here’s a note I have at the top of that console application:
// NOTE: This must always be built as 32-bit at the moment.
// See this link for more details – basically, the Microsoft.SqlServer.BatchCmd file is not loaded
// on 64-bit machines.
// http://social.msdn.microsoft.com/Forums/en-US/sqlsmoanddmo/thread/861b57bc-aa21-4b03-bd01-5c164e2bac4e
// Wait for a bit so the connections are closed…
Thread.Sleep(3000); // 3 seconds.
And with that, I have a solution that I can easily transfer to someone else’s machine – provided they have SQL Express installed – and they can run with minimal setup. A bit of effort on my part, but when working with others it saves a lot of time.