Click here to Skip to main content
11,639,847 members (60,032 online)
Click here to Skip to main content

Deploy SQL Server databases easily with an Installer class

, 6 Apr 2005 CPOL 164.3K 7.1K 169
Rate this:
Please Sign up or sign in to vote.
Deploy MS SQL Server databases using System.Configuration.Install and a VS.NET Setup Project.

Sample Image - sqlscriptinstall.jpg

Introduction

If you made an application that is using an SQL Server database that needs to be located on the client server, VS.NET Setup Project doesn't help too much, you could go for InstallShild or other product that will make things easy but the costs will get higher. So searching for a free solution I've 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 is porting it to c# with some new features that I've found useful. All you need to do is to make a class derived from System.Configuration.Install and add to the solution two Embedded Resources named install.txt & uninstall.txt. 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 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 we declare a string that will have a default value and can be overwrite by the Install method:

string conStr="packet size=4096;integrated security=SSPI;"+
        "data source=\"(local)\";persist security info=False;"+
        "initial catalog=master";

I use two functions that will return the connection string and the script content:

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 I use two functions that will run the install.txt and uninstall.txt onto the SQL server. The ExecuteSQL has a regex that splits the script after GO so that I can execute 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 we can override the 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 in clear, 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 ASPstate.

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);
}

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, 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. Edit the properties of the Textboxes(A) form like this:

Textboxes(A) propeties

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 like storing safer the Key and IV of the Rijndael or using in the ExecuteSql transactions. Looking foreword to your comments.

History

  • Version 1.0

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Stefan Prodan
CEO VeriTech.io
Romania Romania
Co-Founder at VeriTech.io. Passionate about software architecture, SOA, domain driven design, continuous integration, .NET and Javascript programming. I write on www.stefanprodan.com.

You may also be interested in...

Comments and Discussions

 
QuestionVote Pin
Member 1149013822-Apr-15 23:29
memberMember 1149013822-Apr-15 23:29 
QuestionSQL Express Pin
Member 432365910-Sep-14 21:49
memberMember 432365910-Sep-14 21:49 
QuestionValue cannot be null: steram Pin
Member 930335018-Jul-13 7:42
memberMember 930335018-Jul-13 7:42 
GeneralMy vote of 1 Pin
leelaprasad0092-Jan-13 19:48
memberleelaprasad0092-Jan-13 19:48 
GeneralRe: My vote of 1 Pin
supernorb6-Apr-13 10:23
membersupernorb6-Apr-13 10:23 
GeneralMy vote of 5 Pin
Hernán Hegykozi29-Oct-12 13:02
memberHernán Hegykozi29-Oct-12 13:02 
good article!
GeneralRe: My vote of 5 Pin
supernorb6-Apr-13 10:23
membersupernorb6-Apr-13 10:23 
Questiondatbase connection live - then try to un intall Pin
dacku_198724-Aug-11 3:04
memberdacku_198724-Aug-11 3:04 
GeneralMy vote of 5 Pin
Kanasz Robert13-Nov-10 6:03
memberKanasz Robert13-Nov-10 6:03 
QuestionHow to use SqlInstall.dll Pin
pavan_contractor6-Jul-09 1:14
memberpavan_contractor6-Jul-09 1:14 
Generalexport database on to another machine Pin
waquasalig16-Oct-08 0:23
memberwaquasalig16-Oct-08 0:23 
Generalvalue cannot be null parameter name: stream Pin
jothy_cse14-Jul-08 22:03
memberjothy_cse14-Jul-08 22:03 
AnswerRe: value cannot be null parameter name: stream Pin
Sivarajselvakumar17-May-11 0:01
memberSivarajselvakumar17-May-11 0:01 
GeneralThanks Pin
girish_tinnu16-Apr-08 22:02
membergirish_tinnu16-Apr-08 22:02 
GeneralAgain about GO Pin
Win32nipuh13-Aug-07 19:54
memberWin32nipuh13-Aug-07 19:54 
GeneralRe: Again about GO Pin
Stefan Prodan13-Aug-07 21:39
memberStefan Prodan13-Aug-07 21:39 
GeneralRe: Again about GO Pin
Win32nipuh14-Aug-07 19:36
memberWin32nipuh14-Aug-07 19:36 
GeneralRegEx Change Pin
The Punisher28-Feb-07 11:56
memberThe Punisher28-Feb-07 11:56 
QuestionHow to stop installation if database is not validate? Pin
Yulaw13-Jul-06 2:24
memberYulaw13-Jul-06 2:24 
AnswerRe: How to stop installation if database is not validate? Pin
kovalov9-May-07 14:59
memberkovalov9-May-07 14:59 
GeneralRe: How to stop installation if database is not validate? Pin
mikker_12331-May-07 7:07
membermikker_12331-May-07 7:07 
QuestionGO problem ;) Pin
beatles169218-Feb-06 21:21
memberbeatles169218-Feb-06 21:21 
QuestionReferences? Pin
Eduard Ralph8-Dec-05 21:59
memberEduard Ralph8-Dec-05 21:59 
GeneralGreat article got my 5 + Customize the database name Pin
Cohen Shwartz Oren4-Dec-05 22:08
memberCohen Shwartz Oren4-Dec-05 22:08 
GeneralMSI Repair Problem Pin
djspirit7-Nov-05 0:45
memberdjspirit7-Nov-05 0:45 
QuestionWeb Services Pin
Asi BS11-Oct-05 0:39
memberAsi BS11-Oct-05 0:39 
GeneralRegex &quot;^Go&quot; doesn't work Pin
shakoosh1-Aug-05 5:28
membershakoosh1-Aug-05 5:28 
GeneralRe: Regex &quot;^Go&quot; doesn't work Pin
MMaslyk22-Jun-06 2:03
memberMMaslyk22-Jun-06 2:03 
GeneralRe: Better Regex - Regex &quot;^Go&quot; doesn't work Pin
AlrightyThen26-Sep-07 8:30
memberAlrightyThen26-Sep-07 8:30 
GeneralPerformance Pin
Neil Mosafi24-Apr-05 22:43
sussNeil Mosafi24-Apr-05 22:43 
GeneralRe: Performance Pin
Stefan Prodan25-Apr-05 2:01
memberStefan Prodan25-Apr-05 2:01 
GeneralRe: Performance Pin
Neil Mosafi25-Apr-05 2:50
sussNeil Mosafi25-Apr-05 2:50 
GeneralUnattended install Pin
Guido_d12-Apr-05 4:18
memberGuido_d12-Apr-05 4:18 
GeneralRe: Unattended install Pin
Guido_d12-Apr-05 5:18
memberGuido_d12-Apr-05 5:18 
GeneralRe: Unattended install Pin
Stefan Prodan12-Apr-05 9:34
memberStefan Prodan12-Apr-05 9:34 
GeneralRe: Unattended install Pin
Gyi4-Mar-09 3:24
memberGyi4-Mar-09 3:24 
GeneralTimely material Pin
Dylan Thomas6-Apr-05 5:44
sussDylan Thomas6-Apr-05 5:44 
GeneralCool Pin
Sergey Solozhentsev6-Apr-05 4:39
memberSergey Solozhentsev6-Apr-05 4:39 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150731.1 | Last Updated 6 Apr 2005
Article Copyright 2005 by Stefan Prodan
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid