Salient Solutions

Database Testing: A disposable MS SQL database fixture

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:

1. remove drop/create statements.
2. 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();
        }
    }
}

Print | posted on Monday, February 15, 2010 6:00 AM |