Geeks With Blogs
// ThomasWeller C#/.NET software development, software integrity, life as a freelancer, and all the rest

Almost every software project comes with a database. Sometimes it will be developed from scratch and in parallel with the actual domain model for a new (aka. 'greenfield') project, sometimes it will be a pre-existent datastore provided by the customer or coming from an already running software that has to be extended (then we can call it a 'brownfield' project). In either case, you will likely end up with a bunch of database scripts that need to be executed as part of your installation process.

I often came across this scenario during my professional life, and more often than not that DB script stuff is treated somewhat carelessly. After all, it isn't a 'real' software project and managing it is not immediately familiar to most software developers. For some of us, the database is just something that has to be there, all other questions are left to the DB admins. But on the other hand, "getting the database right" and keeping it in parallel with the rest of the software is crucial for a software project - and something that often causes a significant amount of problems. We definitely should give it some more love.

That's why I took me some time and came up with a solution to automate script execution for a MS SQL Server database. The sample project can be downloaded from here. It uses the AdventureWorksLT sample database from Microsoft (which you could get here, but note that downloading it is not necessary - the sample solution will build it from scratch). For code execution, I will use the Gallio/MbUnit testing framework - this (or any other xUnit framework) is a well-suited tool not only for unit testing but for any kind of code automation...

Ok now, the first things that we need are SQL-scripts that can be executed by the SqlCmd.exe command line tool to create our database from scratch. In most cases the scripts can be easily extracted from the DB instance with SQL Server Management Studio ("script table as... | CREATE to... | file..." or something similar). However, this is not the case for the CREATE DATABASE script, because we want to use a variable for the name of the new database and MS SQL Server does not allow the usage of variables for db objects. So to be able to dynamically provide the database name to the CREATE DATABASE script at runtime, we have to do a little string replacement magic. This is how the final script looks like:

USE [master]

GO

 

-- **************************************************************************************

-- This somewhat strange template replacement mechanism is required because MS SQL Server

-- doesn't allow using variables in a create table statement for the new db.

-- **************************************************************************************

 

DECLARE @template nvarchar(max);

SET @template  = '

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = ''__dbname__'')

begin

    DROP DATABASE __dbname__

    PRINT(''Database [__dbname__] dropped for recreation...'')

end

CREATE DATABASE [__dbname__]

IF (1 = FULLTEXTSERVICEPROPERTY(''IsFullTextInstalled''))

begin

    EXEC [__dbname__].[dbo].[sp_fulltext_database] @action = ''enable''

end

ALTER DATABASE [__dbname__] SET ANSI_NULL_DEFAULT OFF

ALTER DATABASE [__dbname__] SET ANSI_NULLS OFF

ALTER DATABASE [__dbname__] SET ANSI_PADDING OFF

ALTER DATABASE [__dbname__] SET ANSI_WARNINGS OFF

ALTER DATABASE [__dbname__] SET ARITHABORT OFF

ALTER DATABASE [__dbname__] SET AUTO_CLOSE OFF

ALTER DATABASE [__dbname__] SET AUTO_CREATE_STATISTICS ON

ALTER DATABASE [__dbname__] SET AUTO_SHRINK OFF

ALTER DATABASE [__dbname__] SET AUTO_UPDATE_STATISTICS ON

ALTER DATABASE [__dbname__] SET CURSOR_CLOSE_ON_COMMIT OFF

ALTER DATABASE [__dbname__] SET CURSOR_DEFAULT GLOBAL

ALTER DATABASE [__dbname__] SET CONCAT_NULL_YIELDS_NULL OFF

ALTER DATABASE [__dbname__] SET NUMERIC_ROUNDABORT OFF

ALTER DATABASE [__dbname__] SET QUOTED_IDENTIFIER OFF

ALTER DATABASE [__dbname__] SET RECURSIVE_TRIGGERS OFF

ALTER DATABASE [__dbname__] SET DISABLE_BROKER

ALTER DATABASE [__dbname__] SET AUTO_UPDATE_STATISTICS_ASYNC OFF

ALTER DATABASE [__dbname__] SET DATE_CORRELATION_OPTIMIZATION OFF

ALTER DATABASE [__dbname__] SET TRUSTWORTHY OFF

ALTER DATABASE [__dbname__] SET ALLOW_SNAPSHOT_ISOLATION OFF

ALTER DATABASE [__dbname__] SET PARAMETERIZATION SIMPLE

ALTER DATABASE [__dbname__] SET READ_COMMITTED_SNAPSHOT OFF

ALTER DATABASE [__dbname__] SET READ_WRITE

ALTER DATABASE [__dbname__] SET RECOVERY FULL

ALTER DATABASE [__dbname__] SET MULTI_USER

ALTER DATABASE [__dbname__] SET PAGE_VERIFY CHECKSUM

ALTER DATABASE [__dbname__] SET DB_CHAINING OFF

 

PRINT(''Database [__dbname__] created...'')';

 

DECLARE @sqlscript nvarchar(max);

SET @sqlscript = REPLACE(@template, '__dbname__', '$(dbname)');

 

exec(@sqlscript);

 

GO

With this script, we can execute the following command line (which is what we need):

sqlcmd -U <usr> -P <pwd> -S <srv> -i "<script>" -v dbname=<db>

All other scripts for the various database objects can be left unchanged like they are generated by Manage-ment Studio. They can be executed via SqlCmd.exe with the below command line:

sqlcmd -U <usr> -P <pwd> -S <srv> -d <db> -i "<script>"

We place all these scripts in a well-known folder and add it to a VS solution (this is btw. the only method to put a database under Source Control). Then we add a class library project to the solution, that will contain one single class: The 'test fixture' that acts as a kind of 'script execution engine' (I named it CreateDatabaseFixture). The final solution will look something like this in VS' Solution Explorer:

DbScriptAutomationDemoSlnExplorer

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Now that we have everything in place, we can finally write some code for our CreateDatabaseFixture class. First we define some constants for paths, filenames and database credentials, along with some convenience properties for data access:

namespace ScriptAutomation

{

    /// <summary>

    /// Gallio/MbUnit fixture to automate db script execution via SqlCmd.

    /// </summary>

    [TestFixture]

    public class CreateDatabaseFixture

    {

        private const string SqlCmdExe = @"""C:\Program Files\Microsoft SQL Server\90\Tools\Binn\SqlCmd.exe""";

        private const string ScriptFolder = @"..\..\..\Database\Scripts";

 

        private const string CreateDatabaseScript = "_dbCreate.sql";

        private const string CreateSchemaScript = "Create-Schema-SalesLT.sql";

        private const string CreateTypesScript = "Create-Types.sql";

 

        private const string User = "test";

        private const string Password = "test";

        private const string Server = "DB";

        private const string Database = "AdventureWorksLT_DEMO";

        private const string Schema = "SalesLT";

 

        /// <summary>

        /// Builds a  MS sql server connection string from the constants.

        /// </summary>

        /// <value>The MS sql server connection string.</value>

        private static string ConnectionString

        {

            get

            {

                return string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}",

                                     Server, Database, User, Password);

            }

        }

 

        /// <summary>

        /// Gets a db connection.

        /// </summary>

        /// <value>The db connection.</value>

        private static IDbConnection DbConnection

        {

            get { return new SqlConnection(ConnectionString); }

        }

 

       ...

 

    } // class CreateDatabaseFixture

 

} // namespace ScriptAutomation

The "heart" of our 'test fixture' then is the method that executes a given SQL-script - it uses an instance of the System.Process class to run the SqlCmd.exe tool and to provide it with the above shown command line. Additionally, the method asserts that the execution was actually successful and writes any console output to the test log:

        /// <summary>

        /// Executes the <c>SqlCmd</c> script and verifies the result.

        /// </summary>

        /// <remarks>

        /// Writes the process output to the <see cref="TestLog"/>. Asserts that

        /// <c>SqlCmd</c>'s exit code is 0 (zero) and that there is no error output.

        /// </remarks>

        /// <param name="script">The script.</param>

        private static void ExecuteSqlCmdScript(string script)

        {

            script = Path.Combine(ScriptFolder, script);

 

            using (Process proc = new Process())

            {

                string arguments = string.Format(@"-U ""{0}"" -P ""{1}"" -S ""{2}"" -d ""{3}"" -i ""{4}""",

                                                 User, Password, Server, Database, script);

 

                proc.StartInfo.FileName = SqlCmdExe;

                proc.StartInfo.Arguments = arguments;

 

                proc.StartInfo.UseShellExecute = false;

                proc.StartInfo.RedirectStandardOutput = true;

                proc.StartInfo.RedirectStandardError = true;

                proc.StartInfo.CreateNoWindow = true;

 

                proc.Start();

                proc.WaitForExit();

 

                string output = proc.StandardOutput.ReadToEnd();

                string error = proc.StandardError.ReadToEnd();

 

                if (!string.IsNullOrEmpty(output))

                {

                    TestLog.WriteLine(output);

                }

 

                if (!string.IsNullOrEmpty(error))

                {

                    TestLog.WriteLine();

                    TestLog.WriteLine("Error output:");

                    TestLog.WriteLine("************");

                    TestLog.WriteLine(error);

                }

 

                Assert.AreEqual(0, proc.ExitCode,

                               "Process exitcode (SqlCmd.exe) is not zero.");

                Assert.IsTrue(string.IsNullOrEmpty(error),

                             "Execution of script '{0}' produced some error output (see test log).",

                             script);

            }

        }

(Note: This method is not suitable for the CREATE DATABASE script, since we need a somewhat different command line for that. The CreateDatabaseFixture class contains an extra method for this purpose, which is almost identical to the above one. Also there are some similar methods for database objects other than tables. They are not shown here, because they don't provide any additional information.)

With the ExecuteSqlCmdScript() 'workhorse' and another helper method, called VerifyTableCreation(), our final 'test' to create all tables, looks as simple as this:

[Test, DependsOn("CreateBlankDatabase"), DependsOn("CreateSchema"), DependsOn("CreateTypes")]

[Factory("TablesAndScripts")]

public void CreateTables(string tableName, string scriptName)

{

    ExecuteSqlCmdScript(scriptName);

    VerifyTableCreation(tableName);

}

To run this data-driven 'test', we also need a factory method (TablesAndScripts()) that lists all our tables and related scripts. Here it is:

/// <summary>Datasource for the <see cref="CreateTables"/> method.</summary>

/// <remarks>This is executed in strict top-to-bottom order (dependencies!).</remarks>

/// <returns>Tables and their related Create-Scripts</returns>

public IEnumerable TablesAndScripts()

{

    yield return new object[] { "ProductCategory", "Create-Table-ProductCategory.sql" };

    yield return new object[] { "ProductModel", "Create-Table-ProductModel.sql" };

    yield return new object[] { "Product", "Create-Table-Product.sql" };

    yield return new object[] { "ProductDescription", "Create-Table-ProductDescription.sql" };

    yield return new object[] { "ProductModelProductDescription", "Create-Table-ProductModelProductDescription.sql" };

    yield return new object[] { "Address", "Create-Table-Address.sql" };

    yield return new object[] { "Customer", "Create-Table-Customer.sql" };

    yield return new object[] { "CustomerAddress", "Create-Table-CustomerAddress.sql" };

    yield return new object[] { "SalesOrderHeader", "Create-Table-SalesOrderHeader.sql" };

    yield return new object[] { "SalesOrderDetail", "Create-Table-SalesOrderDetail.sql" };

}

The CreateTables() method will execute once for each item that TablesAndScripts() returns. Here's the resulting test report for an execution of the entire CreateDatabaseFixture class:

ScriptAutomation_GallioTestReport

As you can see, the execution order is exactly like we wanted it to be. The code imposed a strict sequence on the various test methods by using MbUnit's DependsOn attribute on method level together with the declaration order inside the TablesAndScripts() factory method. This may sound somewhat trivial, but it's not. Gallio is a unit testing framework, and good unit tests are supposed to not depend on each other. Consequently, the framework normally does not guarantee for any execution order. However, for this particular use case, controlling this order is of course absolutely crucial.

The here outlined methodology is also a good way to keep up with historical schema changes, like they often occur during the lifecycle of a software project and its accompanying database: If you alter something on your database schema, SQL Server Management Studio can generate a change-script for you, which you then can manage and execute just like all the other SQL-scripts. Just add another line to the end of the TablesAndScripts() factory method for each new change-script, it will be executed in the desired order.

I provided a sample solution (VS 2008) with the above described 'test fixture' and the SQL-scripts. To run it, you will need access to an SQL server instance and you must have the Gallio framework installed, which you can download from here (it's free).

 

kickit
shoutit
delicious facebook digg reddit linkedin stumbleupon technorati mrwong yahoo google-48x48 twitter email favorites
Posted on Tuesday, September 8, 2009 5:37 PM Automation | Back to top


Comments on this post: Automating database script execution

# re: Automating database script execution
Requesting Gravatar...
Hi Thomas,
I read through your blog about automating database scripts and I have downloaded the VS 2008 files to examine them closer hoping I'd find a solution for the situation I am faced with but, I'm looking...

I have need of a solution that allows me to automate the creation of over 4000 tables for just one database. And, since MS SQL Server does not allow variable substitution for table names, I'm stuck!

Even with your creative approach to variable name substitution, I noticed you still needed to type the names of each table in your script. So, I was wondering if it was possible to write a C# program that created the script to create the tables? Is this possible?

Thanks for the information posted in your blog and I'll keep searching.

Regards
Gary
Left by Gary Gorman on Nov 25, 2009 2:03 AM

# re: Automating database script execution
Requesting Gravatar...
Gary,

the above approach needs an already existing SQL script in any case, so it seems to be not exactly what you're looking for - although it might be helpful if you'll have to _run_ the 4000 scripts :-)...

If you need to create scripts in some way or the other, you may look at a Code Generator like e.g. MyGeneration (http://www.mygenerationsoftware.com/).

- Thomas
Left by Thomas Weller on Nov 25, 2009 5:25 AM

# re: Automating database script execution
Requesting Gravatar...
Actually, creating databases and tables can be using SQLCMD variables, even though this is not the point of the article. But to help us to know, here is how it could work. Variables can be supplied in the input file, and the name given on the command line using the -i argument or overridden in the command line using the -v argument.

[code]
:setvar DatabaseName Junk
:setvar TableName MyTable

USE master;
GO

IF EXISTS (SELECT * FROM sys.databases d WHERE d.[name] = N'$(DatabaseName)')
DROP DATABASE [$(DatabaseName)];
GO

CREATE DATABASE [$(DatabaseName)];
GO

USE [$(DatabaseName)];
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = N'$(TableName)')
DROP TABLE [$(TableName)];
GO

CREATE TABLE [$(TableName)]
(
ID INT IDENTITY
, SomeChar CHAR(10)
, SomeDate DATETIME
);
GO

INSERT [$(TableName)] (SomeChar, SomeDate) VALUES ('A', GETDATE());
GO

SELECT * FROM [$(TableName)];
GO

SELECT * FROM INFORMATION_SCHEMA.TABLES t WHERE t.TABLE_NAME = N'$(TableName)';
GO

USE master;
GO

DROP DATABASE [$(DatabaseName)];
GO
[/code]

Left by Graeme on May 20, 2014 5:47 PM

Your comment:
 (will show your gravatar)


Copyright © Thomas Weller | Powered by: GeeksWithBlogs.net | Join free