I am trying to use SQL Express to support unit testing of functionality which requires database access.
I created an express database (.mdf) which contains a database snapshot which is the database environment that each test expects to see when it starts. Through the execution of the test the database will be modified arbitrarily and I need to reset the database to the original state before the next test executes.
Here's what I do today:
Before Test:
The original database is copied to a unique location in the temp directory
A unique database name is generated (GUID-based)
I connect to the new database file with:
Server=.\SQLEXPRESS;AttachDbFilename="Path\To\Temp\File";Database="GuidDBName";Initial Catalog="GuidDBName";Trusted_Connection=Yes;User Instance=true
During the test all database access is routed to the temporary database GuidDBName
After Test:
Close all active database connections and clear the connection pool
Connect to the master database and detach the database:
Server=.\SQLEXPRESS;Database=master;Initial Catalog=master;Trusted_Connection=Yes;User Instance=true - sp_detach_db 'GuidDBName'
Delete the temporary file
When I do this, everything seems to work correctly but when I re-open the original database it has changed unexpectedly and includes the modifications that were made by the test as it ran.
Can anyone explain why this is happening and/or provide a solution?
Short version - I have no idea what's happening here, I can't reproduce this behavior based on the information you've provided.
Long version - Your connection strings have duplicate information in them and I'm guessing that part of the problem is that you're connecting to different databases that you think you are. I also have question about you're original database; you don't mention how your are opening the original database to discover that it has been modified. I'm guessing you're using Managment Studio, but I'll bet you're looking at the parent instance SQLEXPRESS and not the User Instance you're creating with the connection string you've provided. A User Instance is a completely separate instance of SQL Server.
The duplication you have is that Database= and Initial Catalog= are fundamentally the same thing. (Check out this BOL topic for the gorey details on connection string keywords in SNAC.) On top of that, you don't really need to use either of them when you use AttachDbFilename in combination with User Instance=True since SQL Express will automatically named the database with the file path.
I wrote a little test routine to try and cause this, but I couldn't get it to fail. Here is what I did if you want to compare it to your code solution:
static void Main(string[] args){
// Create a new copy of the database
string path1 = "C:\\Users\\mikewa\\Documents\\RANUTest\\Source\\foo.mdf";
string path2 = "C:\\Users\\mikewa\\Documents\\RANUTest\\foo.mdf";
string path2log = "C:\\Users\\mikewa\\Documents\\RANUTest\\foo_log.ldf";
try
{
File.Delete(path2);
File.Delete(path2log);
File.Copy(path1, path2);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
// Attach the temp database to the User Instance
string connectionString = "Server=.\\SQLEXPRESS;AttachDbFilename=" + path2 + ";Integrated Security=True;User Instance=True";
using (SqlConnection cnn = new SqlConnection(connectionString))
{
cnn.Open();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "CREATE TABLE dbo.tblFoo (myField int NULL)";
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = cnn;
cmd.ExecuteNonQuery();
cnn.Close();
}
SqlConnection.ClearAllPools();
// Now kill the temp database in SQL
string masterConnectionString = "Server=.\\SQLEXPRESS;Database=master;Initial Catalog=master;Trusted_Connection=Yes;User Instance=True";
using (SqlConnection cnn = new SqlConnection(masterConnectionString))
{
SqlCommand cmd = new SqlCommand("sp_detach_db '" + path2 + "'");
cmd.CommandType = System.Data.CommandType.Text;
cmd.Connection = cnn;
cnn.Open();
cmd.ExecuteNonQuery();
}
}
As you can see, I've stripped the extra keywords out of my connection string and just used the complete file path, represented by the variable 'path2' as the file name. This automatic naming happens automatically (hence the name) as part of attaching the file to a User Instance. I use the same variable later to detach the file. After I run this, I'm able to examine my source file and see that the modification does not exist (i.e. no table named tblFoo).
Check around to ensure that you don't have multiple copies of your original database laying around. If you're looking at the "original file" in Management Studio while connected to the parent instance, it's possible that you're actually looking at a different file than the one you're copying.
Mike
