I have an .mdf DB file and I want to use this DB for integration testing.
In every test I should:
- Create a copy of an .mdf file.
- Attach DB to an .mdf copy file
- Do testing
- Detach DB copy
- Delete DB copy
Just to make all clear, integration testing should look something like this:
[TestFixture]
public class WebJobTests : IntegrationTest
{
[SetUp()]
public void Init()
{
CreateTestDbCopy();
AttachTestDbCopy();
// Other init
}
[TearDown]
public void TestCleanup()
{
DetachTestDbCopy();
DeleteTestDbCopy();
}
[Test]
public void SomeIntegrationTest()
{
//Do testing
}
}
The main thing that I want you to take a look is my IntegrationTest class. This is an abstract class that should be inherited in every integration test class, like in example above.
/// <summary>
/// Help functions for Integration tests.
/// </summary>
public abstract class IntegrationTest
{
private const string conectionStringName = "IntegrationTestDB";
private string connectionString;
private string dbPath;
private string dbPathCopy;
private string logFileCopy;
private const string dbCopyName = "TestDbCopy";
public IntegrationTest()
{
connectionString = ConfigurationManager.ConnectionStrings[conectionStringName].ConnectionString;
//Location of mdf file:
dbPath = System.Configuration.ConfigurationManager.AppSettings["DbPath"];
var directory = Path.GetDirectoryName(dbPath);
dbPathCopy = directory + "\\" + dbCopyName + ".mdf";
logFileCopy = directory + "\\" + dbCopyName + "_log.ldf";
}
/// <summary>
/// Detach DB after finish using.
/// </summary>
protected void DetachTestDbCopy()
{
KillProcesses();
try
{
const string Sql = @"sys.sp_detach_db @DbName";
using (var connection = new SqlConnection(this.connectionString))
using (var command = new SqlCommand(Sql, connection))
{
command.Parameters.Add("@DbName", SqlDbType.VarChar).Value = dbCopyName;
connection.Open();
command.ExecuteNonQuery();
}
}
catch
{
throw;
}
}
/// <summary>
/// Kill process should be use before detach becosue detach can't be done if process is live.
/// </summary>
private void KillProcesses()
{
const string Sql = @"SELECT SPId
FROM master..SysProcesses
WHERE DBId = DB_ID(@DbName)";
const string kill = "exec('kill {0};')";
try
{
List<int> AllSpIds = new List<int>();
using (var connection = new SqlConnection(this.connectionString))
using (var command = new SqlCommand(Sql, connection))
using (var killCommand = new SqlCommand(Sql, connection))
{
command.Parameters.Add("@DbName", SqlDbType.VarChar).Value = dbCopyName;
connection.Open();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
AllSpIds.Add(reader.GetInt16(0));
}
}
}
if (AllSpIds.Count > 0)
{
using (var connection = new SqlConnection(this.connectionString))
using (var command = new SqlCommand(kill, connection))
{
connection.Open();
foreach (var SpIds in AllSpIds)
{
command.CommandText = string.Format(command.CommandText, SpIds);
command.ExecuteNonQuery();
}
}
}
}
catch
{
throw;
}
}
/// <summary>
/// Atach DB befor start using.
/// </summary>
protected void AttachTestDbCopy()
{
try
{
if (IsDbAttached()) {
DetachTestDbCopy();
}
using (var connection = new SqlConnection(this.connectionString))
using (var command = new SqlCommand("exec sys.sp_attach_single_file_db @DbName, @DbPath", connection))
{
command.Parameters.Add("@DbName", SqlDbType.VarChar).Value = dbCopyName;
command.Parameters.Add("@DbPath", SqlDbType.VarChar).Value = this.dbPathCopy;
connection.Open();
command.ExecuteNonQuery();
}
}
catch
{
throw;
}
}
private bool IsDbAttached()
{
const string isAttachedSqL = @"SELECT count(*)
FROM sys.master_files
WHERE DB_NAME(database_id) = @DbName";
bool isAttached = false;
try
{
using (var connection = new SqlConnection(this.connectionString))
using (var command = new SqlCommand(isAttachedSqL, connection))
{
command.Parameters.Add("@DbName", SqlDbType.VarChar).Value = dbCopyName;
connection.Open();
var count = command.ExecuteScalar();
isAttached = (int)count > 0;
}
}
catch
{
throw;
}
return isAttached;
}
/// <summary>
/// Create temporary copy of orginal mdf file with new name.
/// </summary>
protected void CreateTestDbCopy()
{
DeleteTestDbCopy();
System.IO.File.Copy(dbPath, dbPathCopy, true);
}
/// <summary>
/// Delete temporary copy of mdf file.
/// </summary>
protected void DeleteTestDbCopy()
{
if (File.Exists(dbPathCopy))
{
File.Delete(dbPathCopy);
}
if (File.Exists(logFileCopy))
{
File.Delete(logFileCopy);
}
}
}
