SqlDatabaseInstaller

Installers provide a consistent way of implementing automated setup logic for an application, and as I previously wrote, they also work admirably well for setting up test fixtures. The BCL, however, only contains a limited amount of pre-built Installers, so if you need to install something else than performance counters, event logs, MSMQ queues, or Windows services, you need to define your own.

One very common installation requirement is the need to install a database as part of application installation. For SQL Server, the database itself will most likely be defined as a set of T-SQL DDL scripts, but you need some code to execute those scripts if you need to do this as part of an automated installation, and a few general-purpose Installers serve this purpose nicely.

In this post, I'll describe the first of such Installers, and subsequent posts will describe the rest.

The purpose of the SqlDatabaseInstaller is simply to create the database itself. Uninstallation conversely deletes the database.

 public class SqlDatabaseInstaller : Installer

 { 

     public override void Install(IDictionary stateSaver)

     {

         this.ExecuteNonQuery("CREATE DATABASE {0}");

         base.Install(stateSaver);

     }

     public override void Uninstall(IDictionary savedState)

     {

         base.Uninstall(savedState);

         string dropStatement =

             "IF EXISTS (SELECT [name] FROM sys.databases WHERE [name] = '{0}') BEGIN DROP DATABASE {0} END";

         this.ExecuteNonQuery(dropStatement);

     }

     private void ExecuteNonQuery(string sqlStatement)

     {

         string connectionString = this.Context.Parameters["ConnectionString"];

         SqlConnectionStringBuilder scsb =

             new SqlConnectionStringBuilder(connectionString);

         string databaseName = scsb.InitialCatalog;

         scsb.InitialCatalog = "master";

         using (SqlConnection conn = new SqlConnection(scsb.ConnectionString))

         {

             using (SqlCommand cmd =

                 new SqlCommand(string.Format(sqlStatement, databaseName), conn))

             {

                 conn.Open();

                 cmd.ExecuteNonQuery();

             }

         }

     }

 } 

The tricky part here is the ExecuteNonQuery method - not that executing a non-query is in itself difficult, but obtaining the connection string turns out to be non-trivial. Since it wouldn't be a general-purpose Installer if it contained a hard-coded connection string, one must be supplied via the Installer's Context, so there's a bit of convention built into SqlDatabaseInstaller:

• The user of the SqlDatabaseInstaller is expected to supply a value for the ConnectionString parameter. When the Installer is being executed from managed code, this parameter value must be added to its Context's Parameters collection; when the Installer is being executed via InstallUtil, the connection string must be supplied as a command-line parameter.
• The supplied connection string is expected to point to the database being created. While this may seem counter-intuitive, it makes a lot of sense if you see several database-related Installers bundled together, since all subsequent Installers will use the unmodified connection string with the correct database to populate its schema, etc.

You can override Installer.HelpText to supply this information to the user, like this:

 public override string HelpText

 {

     get

     {

         StringBuilder sb = new StringBuilder();

         sb.AppendLine("/ConnectionString (required)");

         sb.AppendLine(" Example:");

         sb.AppendLine(" InstallUtil /ConnectionString=\"Server=localhost; Database=MyDB; Integrated Security=true;\"");

         return sb.ToString();

     }

 }

This would display the help text mixed in with the general help on options if you write InstallUtil [assembly] /? on a command line.

You may also want to add some error handling to create a more helpful error message in case the user forgets to supply the connection string, or it isn't correctly formatted.

While this post described a general-purpose Installer that creates a SQL Server database upon installation, and deletes it again upon uninstallation, future posts will describe other Installers for populating the database with schema and data.

Comments

• Anonymous
  November 30, 2007
  In my previous post I described a general-purpose Installer that can be used to create a SQL Server database

• Anonymous
  December 11, 2007
  The last of a series of SQL Server-related database Installers is a little helper that simply makes it

• Anonymous
  January 31, 2008
  The comment has been removed

• Anonymous
  September 17, 2008
  The comment has been removed