Deploy SQL Databases Easily with the Installer Class

Introduction

If you made an application that uses an SQL database that needs to be located on the client server, VS.NET Setup Project doesn't help too much. You could go for InstallShield or another product that will make things easy, but the costs will get higher. So, while searching for a free solution, I found an article on MSDN about using the Installer class and custom actions to make this happen. The code is in VB .NET, so this article ports it to C# with some new features that I've found useful, such as storing the connection string to the database more safely, restoring the initial state of the server if something fails, and an uninstall feature. All you need to do is to make a class derived from System.Configuration.Install and add two Embedded Resources named install.txt & uninstall.txt to the solution. The install.txt will contain the SQL script for your database and uninstall.txt the drop script. For the database script, I am using the ASPstate script made by the Microsoft team for the ASP.NET InSQL session state:

< sessionState
   mode ="SQLServer"
   stateConnectionString ="tcpip=127.0.0.1:42424"
   sqlConnectionString = "data source=aleph;
                          User ID=ASPsession;Password=ASPsession;"
   cookieless = "false"
   timeout    = "60"
/>

For my Web application to work on a client server, I need to make a MSI with my app and the SQL script. To run the script at install time, I've made a .dll named ScriptInstall; the code for it will follow.

Installer Class Code

First, you declare a string that will have a default value and can be overridden by the Install method:

[RunInstaller(true)]
public class ScriptInstall : Installer
{
   //default value, it will be overwritten by the installer
   string conStr= "packet size=4096;integrated security=SSPI;" +
                  "data source=\"(local)\";
                  persist security info=False;" +
                  "initial catalog=master";

I use two static functions that will return the script content and the connection string to the SQL server:

private static string GetScript(string name)
{
   Assembly asm = Assembly.GetExecutingAssembly();
   Stream str = asm.GetManifestResourceStream(asm.GetName().Name +
                                              "." + name);
   StreamReader reader = new StreamReader(str);
   return reader.ReadToEnd();
}
private static string GetLogin(string databaseServer,string userName,
                               string userPass,string database)
{
   return "server=" + databaseServer + ";database="+database+
                      ";User ID=" + userName + ";Password=" + userPass;
}

Then, process two functions that will run install.txt and uninstall.txt onto the SQL server. The ExecuteSQL has a regex that splits the script after GO so I can execute them one by one with SQLCommand. I am doing this because ADO.NET will throw an exception if the SQL script contains "GO".

private static void ExecuteSql(SqlConnection sqlCon)
{
   string[] SqlLine;
   Regex regex = new Regex("^GO",RegexOptions.IgnoreCase |
                           RegexOptions.Multiline);

   string txtSQL = GetScript("install.txt");
   SqlLine = regex.Split(txtSQL);

   SqlCommand cmd = sqlCon.CreateCommand();
   cmd.Connection = sqlCon;

   foreach(string line in SqlLine)
   {
      if(line.Length>0)
      {
         cmd.CommandText = line;
         cmd.CommandType = CommandType.Text;
         try
         {
            cmd.ExecuteNonQuery();
         }
         catch(SqlException)
         {
            //rollback
            ExecuteDrop(sqlCon);
            break;
         }
      }
   }
}
private static void ExecuteDrop(SqlConnection sqlCon)
{
   if(sqlCon.State!=ConnectionState.Closed)sqlCon.Close();
   sqlCon.Open();
   SqlCommand cmd  = sqlCon.CreateCommand();
   cmd.Connection  = sqlCon;
   cmd.CommandText = GetScript("uninstall.txt");
   cmd.CommandType = CommandType.Text;
   cmd.ExecuteNonQuery();
   sqlCon.Close();
}

Having the functions, now you can override Install(IDictionary stateSaver) and Uninstall(IDictionary savedState). In the Install method, besides running the SQL script on to the server, I save the connection data submitted by the user. It's dangerous to save connection strings, so I use RijndaelManaged to encrypt it. You can find the class in the source as well. I am saving the connection string because I need it at uninstall to drop the database ASP state.

public override void Install(IDictionary stateSaver)
{
   base.Install (stateSaver);

   if(Context.Parameters["databaseServer"].Length>0 &&
      Context.Parameters["userName"].Length>0 &&
      Context.Parameters["userPass"].Length>0)
   {
      conStr = GetLogin(
         Context.Parameters["databaseServer"],
         Context.Parameters["userName"],
         Context.Parameters["userPass"],
         "master");

      RijndaelCryptography rijndael = new RijndaelCryptography();
      rijndael.GenKey();
      rijndael.Encrypt(conStr);
      //save information in the state-saver IDictionary
      //to be used in the Uninstall method
      stateSaver.Add("key",rijndael.Key);
      stateSaver.Add("IV",rijndael.IV);
      stateSaver.Add("conStr",rijndael.Encrypted);
   }

   SqlConnection sqlCon = new SqlConnection(conStr);

   sqlCon.Open();
   ExecuteSql(sqlCon);
   if(sqlCon.State!=ConnectionState.Closed)sqlCon.Close();
}

public override void Uninstall(IDictionary savedState)
{
   base.Uninstall (savedState);

   if(savedState.Contains("conStr"))
   {
      RijndaelCryptography rijndael = new RijndaelCryptography();
      rijndael.Key = (byte[])savedState["key"];
      rijndael.IV  = (byte[])savedState["IV"];
      conStr = rijndael.Decrypt((byte[])savedState["conStr"]);
   }

   SqlConnection sqlCon = new SqlConnection(conStr);

   ExecuteDrop(sqlCon);
}

Deploy SQL Databases Easily with the Installer Class

The Rijndael Class looks like this. Nothing fancy, just MSDN style:

/// <summary>
/// Simple Rijndael implementation
/// </summary>
internal class RijndaelCryptography
{
   RijndaelManaged myRijndael;
   ASCIIEncoding textConverter;
   byte[] fromEncrypt;
   byte[] encrypted;
   byte[] toEncrypt;
   byte[] _key;
   byte[] _IV;

   internal byte[] Key
   {
      get{return _key;}
      set{_key = value;}
   }
   internal byte[] IV
   {
      get{return _IV;}
      set{_IV = value;}
   }
   internal byte[] Encrypted
   {
      get{return encrypted;}
   }

   internal RijndaelCryptography()
   {
      myRijndael       = new RijndaelManaged();
      myRijndael.Mode  = CipherMode.CBC;
      textConverter    = new ASCIIEncoding();
   }

   internal virtual void GenKey()
   {
      //Create a new key and initialization vector.
      myRijndael.GenerateKey();
      myRijndael.GenerateIV();

      //Get the key and IV.
      _key = myRijndael.Key;
      _IV = myRijndael.IV;
   }

   internal void Encrypt(string TxtToEncrypt)
   {
      //Get an encryptor.
      ICryptoTransform encryptor = myRijndael.CreateEncryptor(_key, _IV);

      //Encrypt the data.
      MemoryStream msEncrypt = new MemoryStream();
      CryptoStream csEncrypt = new CryptoStream(msEncrypt, encryptor,
                                                CryptoStreamMode.Write);

      //Convert the data to a byte array.
      toEncrypt = textConverter.GetBytes(TxtToEncrypt);

      //Write all data to the crypto stream and flush it.
      csEncrypt.Write(toEncrypt, 0, toEncrypt.Length);
      csEncrypt.FlushFinalBlock();

      //Get encrypted array of bytes.
      encrypted = msEncrypt.ToArray();
   }

   internal string Decrypt(byte[] crypted)
   {
      //Get a decryptor.
      ICryptoTransform decryptor = myRijndael.CreateDecryptor(_key, _IV);

      //Decrypting the encrypted byte array.
      MemoryStream msDecrypt = new MemoryStream(crypted);
      CryptoStream csDecrypt = new CryptoStream(msDecrypt, decryptor,
                                                CryptoStreamMode.Read);

      fromEncrypt = new byte[crypted.Length];

      //Read the data out of the crypto stream.
      csDecrypt.Read(fromEncrypt, 0, fromEncrypt.Length);

      //Convert the byte array into a string.
      return textConverter.GetString(fromEncrypt);


   }
}

Creating the Setup Project

Now that the installer class is done, I can make a Setup project and add the primary output. In the User Interface Editor, select the Start node under Install. On the Action menu, choose Add Dialog. In the Add Dialog dialog box, select the Textboxes (A) dialog, and then click OK to close the dialog box. On the Action menu, choose Move Up. Repeat until the Textboxes(A) dialog is above the Installation Folder node.

[propeties.jpg]

Go to the Custom Actions Editor and add the Primary output to the install and uninstall nodes. Click on the Primary output at the Install node and edit the properties. In the CustomActionData, type this:

/databaseServer=[EDITA1] /userName=[EDITA2] /userPass=[EDITA3]

I am getting the values of the text Boxes in the Installer class using the Context.Parameters:

conStr = GetLogin(
         Context.Parameters["databaseServer"],
         Context.Parameters["userName"],
         Context.Parameters["userPass"],
         "master");

All is done now; just build the two projects and you are ready to install the database. I hope you'll find this code useful; there are a lot of things that can be added to the code, such as storing the Key and IV of the Rijndael more safely or using in the ExecuteSql transactions.



About the Author

Stefan Prodan

I am a software analist & C# developer working for a software company in East Europe.

Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • On-demand Event Event Date: September 10, 2014 Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild." This loop of continuous delivery and continuous feedback is how the best mobile …

  • On-demand Event Event Date: September 17, 2014 Another day, another end-of-support deadline. You've heard enough about the hazards of not migrating to Windows Server 2008 or 2012. What you may not know is that there's plenty in it for you and your business, like increased automation and performance, time-saving technical features, and a lower total cost of ownership. Check out this webcast and join Rich Holmes, Pomeroy's practice director of virtualization, as he discusses the future state of your servers, …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds