Skip to content

Instantly share code, notes, and snippets.

@lloydkevin lloydkevin/DatabaseInit.cs Secret

Last active Jun 29, 2017
Embed
What would you like to do?
C# Database Integration Test Setup (NUnit)

C# Database Integration Test Setup (NUnit)

Details borrowed from Pluralsight

Details

  • Database is initialized once during every run on a test suite.
  • The build server will have LocalDB installed.
  • Integration tests are run
  • Database is removed and deleted
  • Used the following packages to parse files with GO statements
    • Microsoft.SqlServer.Smo.dll
    • Microsoft.SqlServer.SqlEnum.dll
    • Microsoft.Web.Infrastructure
[SetUpFixture]
public class DatabaseInit
{
private static string _runningPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
[OneTimeSetUp]
public void SetUpDatabase()
{
Debug.WriteLine("SetUpDatabase");
DestroyDatabase();
CreateDatabase();
}
[OneTimeTearDown]
public void TearDownDatabase()
{
Debug.WriteLine("TearDownDatabase");
DestroyDatabase();
}
private static void CreateDatabase()
{
ExecuteSqlCommand(Master, $@"
CREATE DATABASE [MY_DATABASE_NAME]
ON (NAME = 'MY_DATABASE_NAME',
FILENAME = '{Filename}')");
// seed from file (possibly from migration)
var sqlFile = Path.Combine(_runningPath, "Seed.sql");
var sql = File.ReadAllText(sqlFile);
ExecuteSqlCommand(GetSqlConnectionStringBuilder("MY_DATABASE_NAME"), sql);
}
private static void DestroyDatabase()
{
var fileNames = ExecuteSqlQuery(Master, @"
SELECT [physical_name] FROM [sys].[master_files]
WHERE [database_id] = DB_ID('MY_DATABASE_NAME')",
row => (string)row["physical_name"]);
if (fileNames.Any())
{
ExecuteSqlCommand(Master, @"
ALTER DATABASE [MY_DATABASE_NAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
EXEC sp_detach_db 'MY_DATABASE_NAME'");
fileNames.ForEach(File.Delete);
}
}
private static void ExecuteSqlCommand(
SqlConnectionStringBuilder connectionStringBuilder,
string commandText)
{
using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
{
//connection.Open();
//using (var command = connection.CreateCommand())
//{
// command.CommandText = commandText;
// command.ExecuteNonQuery();
//}
// using SMO Dlls to parse files with "GO"
Server server = new Server(new ServerConnection(connection));
server.ConnectionContext.ExecuteNonQuery(commandText);
}
}
private static List<T> ExecuteSqlQuery<T>(
SqlConnectionStringBuilder connectionStringBuilder,
string queryText,
Func<SqlDataReader, T> read)
{
var result = new List<T>();
using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = queryText;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
result.Add(read(reader));
}
}
}
}
return result;
}
private static SqlConnectionStringBuilder Master => GetSqlConnectionStringBuilder("master");
private static SqlConnectionStringBuilder GetSqlConnectionStringBuilder(string database)
{
return new SqlConnectionStringBuilder
{
DataSource = @"(LocalDB)\MSSQLLocalDB",
InitialCatalog = database,
IntegratedSecurity = true
};
}
private static string Filename => Path.Combine(_runningPath,"MY_DATABASE_NAME.mdf");
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.