These classes build a non-denominational fixture that will Drop-Create-Drop a database in MS SqlServer. Following are sample implementations using NUnit and Fluent NHibernate.
To use the fixture, just supply a connection string that points to the database you want to create, override TestFixtureSetup and add some DDL script to the Scripts StringCollection.
NOTE: the fixture takes care of dropping and creating and declaring USING so to prepare a standard DB create script:
- remove drop/create statements.
- remove all USING statements.
The latest source and tests are at http://spikes.codeplex.com/SourceControl/changeset/view/41478
In use with NUnit
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using DeadSimpleDTO;
using Loader.Northwind;
using NUnit.Framework;
namespace Tests
{
[TestFixture]
public class LoaderFixture : NUnitDbFixture
{
private readonly DeadSimpleLoader _loader;
public LoaderFixture()
{
_loader = new DeadSimpleLoader(ConnectionString);
Scripts.Add(File.ReadAllText(Path.Combine("scripts", "NorthwindExemplar.sql")));
}
[Test]
public void Get()
{
var cat = _loader.Get(1);
Assert.IsNotNull(cat);
Assert.AreEqual(1, cat.CategoryID);
}
}
}
The classes
// <copyright project="Salient.SqlServer" file="DatabaseFixture.cs" company="Sky Sanders">
// This source is a Public Domain Dedication.
// Please see http://spikes.codeplex.com/ for details.
// Attribution is appreciated
// </copyright>
// <version>1.0</version>
using System;
using System.Collections.Specialized;
using Salient.SqlServer.Management;
namespace Salient.SqlServer.Testing
{
public abstract class DatabaseFixture : Database
{
private readonly StringCollection _scripts = new StringCollection();
private int _uuid;
protected DatabaseFixture(string dataSource, string initialCatalog)
: base(dataSource, initialCatalog)
{
}
protected DatabaseFixture(string dataSource, string initialCatalog, string userId, string password)
: base(dataSource, initialCatalog, userId, password)
{
}
protected DatabaseFixture(string connectionString)
: base(connectionString)
{
}
protected StringCollection Scripts
{
get { return _scripts; }
}
protected int NextId()
{
return ++_uuid;
}
protected static int TI()
{
return Environment.TickCount;
}
protected static decimal TD()
{
return Environment.TickCount;
}
protected static string TS()
{
return Environment.TickCount.ToString();
}
public virtual void TestFixtureSetUp()
{
DropAndCreate(_scripts);
}
public virtual void TestFixtureTearDown()
{
Drop();
}
}
}
// <copyright project="Salient.SqlServer" file="Database.cs" company="Sky Sanders">
// This source is a Public Domain Dedication.
// Please see http://spikes.codeplex.com/ for details.
// Attribution is appreciated
// </copyright>
// <version>1.0</version>
using System.Collections.Specialized;
using System.Data.SqlClient;
namespace Salient.SqlServer.Management
{
public class Database : SqlCmdRunner
{
private const string CreateDb =
@"
USE [master]
GO
CREATE DATABASE [{0}]
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [{0}].[dbo].[sp_fulltext_database] @action = 'enable'
end
ALTER DATABASE [{0}] SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE [{0}] SET ANSI_NULLS OFF
ALTER DATABASE [{0}] SET ANSI_PADDING OFF
ALTER DATABASE [{0}] SET ANSI_WARNINGS OFF
ALTER DATABASE [{0}] SET ARITHABORT OFF
ALTER DATABASE [{0}] SET AUTO_CLOSE OFF
ALTER DATABASE [{0}] SET AUTO_CREATE_STATISTICS ON
ALTER DATABASE [{0}] SET AUTO_SHRINK OFF
ALTER DATABASE [{0}] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [{0}] SET CURSOR_CLOSE_ON_COMMIT OFF
ALTER DATABASE [{0}] SET CURSOR_DEFAULT GLOBAL
ALTER DATABASE [{0}] SET CONCAT_NULL_YIELDS_NULL OFF
ALTER DATABASE [{0}] SET NUMERIC_ROUNDABORT OFF
ALTER DATABASE [{0}] SET QUOTED_IDENTIFIER OFF
ALTER DATABASE [{0}] SET RECURSIVE_TRIGGERS OFF
ALTER DATABASE [{0}] SET DISABLE_BROKER
ALTER DATABASE [{0}] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
ALTER DATABASE [{0}] SET DATE_CORRELATION_OPTIMIZATION OFF
ALTER DATABASE [{0}] SET TRUSTWORTHY OFF
ALTER DATABASE [{0}] SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE [{0}] SET PARAMETERIZATION SIMPLE
ALTER DATABASE [{0}] SET READ_COMMITTED_SNAPSHOT OFF
ALTER DATABASE [{0}] SET READ_WRITE
ALTER DATABASE [{0}] SET RECOVERY FULL
ALTER DATABASE [{0}] SET MULTI_USER
ALTER DATABASE [{0}] SET PAGE_VERIFY CHECKSUM
ALTER DATABASE [{0}] SET DB_CHAINING OFF
PRINT('Database [{0}] created...')
GO
";
private const string DropDb =
@"
USE [master]
GO
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = '{0}')
begin
ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE {0}
PRINT('Database [{0}] dropped for recreation...')
end
GO
";
private const string UseDb = "USE {0}\r\nGO";
public Database(string dataSource, string initialCatalog)
{
DataSource = dataSource;
InitialCatalog = initialCatalog;
IntegratedSecurity = true;
}
public Database(string dataSource, string initialCatalog, string userId, string password)
{
DataSource = dataSource;
InitialCatalog = initialCatalog;
UserID = userId;
Password = password;
}
public Database(string connectionString)
{
ConnectionString = connectionString;
}
public void DropAndCreate()
{
DropAndCreate(new StringCollection());
}
public void DropAndCreate(StringCollection scripts)
{
Drop();
Create(scripts);
}
public void Drop()
{
var query = new StringCollection
{
string.Format(DropDb,
InitialCatalog)
};
var runner = new SqlCmdRunner
{
ConnectionString =
new SqlConnectionStringBuilder(ConnectionString) {InitialCatalog = "Master"}.
ConnectionString
};
runner.ExecuteScript(query);
}
/// <summary>
///
/// </summary>
/// <param name="scripts">Batches to run after database creation. Do NOT include CREATE DATABASE batch. It is included by this method.</param>
public void Create(StringCollection scripts)
{
scripts.Insert(0,
string.Format(UseDb,
InitialCatalog));
scripts.Insert(0,
string.Format(CreateDb,
InitialCatalog));
var runner = new SqlCmdRunner
{
ConnectionString =
new SqlConnectionStringBuilder(ConnectionString) {InitialCatalog = "Master"}.
ConnectionString
};
runner.ExecuteScript(scripts);
}
public void Create()
{
Create(new StringCollection());
}
}
}
// <copyright project="Salient.SqlServer" file="SqlCmdRunner.cs" company="Sky Sanders">
// This source is a Public Domain Dedication.
// Please see http://spikes.codeplex.com/ for details.
// Attribution is appreciated
// </copyright>
// <version>1.0</version>
using System.Collections.Specialized;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
namespace Salient.SqlServer.Management
{
/// <summary>
/// Starting on a new DB fixture
/// </summary>
public class SqlCmdRunner
{
private SqlConnectionStringBuilder _csBuilder = new SqlConnectionStringBuilder();
#region Connection String Properties
public string InitialCatalog
{
get { return _csBuilder.InitialCatalog; }
set { _csBuilder.InitialCatalog = value; }
}
public string DataSource
{
get { return _csBuilder.DataSource; }
set { _csBuilder.DataSource = value; }
}
public string ConnectionString
{
get { return _csBuilder.ConnectionString; }
set { _csBuilder = new SqlConnectionStringBuilder(value); }
}
public bool IntegratedSecurity
{
get { return _csBuilder.IntegratedSecurity; }
set { _csBuilder.IntegratedSecurity = value; }
}
public string UserID
{
get { return _csBuilder.UserID; }
set { _csBuilder.UserID = value; }
}
public string Password
{
get { return _csBuilder.Password; }
set { _csBuilder.Password = value; }
}
#endregion
public int[] ExecuteScript(StringCollection script)
{
return ExecuteScript(script, ExecutionTypes.Default);
}
public int[] ExecuteScript(StringCollection script, ExecutionTypes executionTypes)
{
using (var conn = new SqlConnection(ConnectionString))
{
var serverConn = new ServerConnection(conn);
var server = new Server(serverConn);
// ExecutionFailureException
int[] result;
try
{
result = server.ConnectionContext.ExecuteNonQuery(script, executionTypes);
}
catch (ExecutionFailureException ex)
{
throw ex.InnerException;
}
return result;
}
}
}
}
using System.Configuration;
using System.Text;
using NUnit.Framework;
using Salient.SqlServer.Testing;
namespace Tests
{
public class NUnitDbFixture : DatabaseFixture
{
public NUnitDbFixture()
: base(ConfigurationManager.ConnectionStrings["testDb"].ConnectionString)
{
}
/// <summary>
/// Empty override as a place to hang the attribute
/// </summary>
[TestFixtureSetUp]
public override void TestFixtureSetUp()
{
base.TestFixtureSetUp();
}
/// <summary>
/// Empty override as a place to hang the attribute
/// </summary>
[TestFixtureTearDown]
public override void TestFixtureTearDown()
{
base.TestFixtureTearDown();
}
}
}
Technorati tags:
SQL Server,
Testing