Setup and Deployment of Windows Application with SQL Server Database using Visual Studio 2010

CodeProject

Recently i got a chance to create a small WPF Application and then i have to create a MSI using Visual Studio 2010 using SQL Server 2008 Database. (I have to create a MSI which will create a Database in SQL Server 2008 Express and then create Tables, Store Procedures and also insert some Data in tables.

1. Create an Application with SQL Server 2008 (For example i have created a WPF Application which fill a DataGrid with Product Table.

2. Create two connection string in app.config file as below:

<connectionStrings>
      <!-- Connection sting to connect Master Database-->
        <add name="WPFTest.Properties.Settings.masterConnectionString"
            connectionString="Data Source=.\sqlexpress;Initial Catalog=master;Integrated Security=True"
            providerName="System.Data.SqlClient" />
      <!-- Connection sting to connect your Program Database-->
      <add name="WPFTest.Properties.Settings.TestConnectionString"
          connectionString="Data Source=.\sqlexpress;Initial Catalog=TestDB;Integrated Security=True"
          providerName="System.Data.SqlClient" />
    </connectionStrings>

First for Master Database and second for your Application Database.

3. Create a Installer class:

   a) On the Project menu, click Add New Item.

   b) In the Add New Item dialog box, select Installer Class. In the Name box, type SetupInstaller.cs.

4. Create a text file that contains a SQL statement to create Tables in Database:

   a) In Solution Explorer, select the <YourProject> project. On the Project menu, click Add New Item.

   b) In the Add New Item dialog box, click Text File. In the Name box, type tables.txt.

   c) Add the Create Tables Script in tables.txt file:

CREATE TABLE [dbo].[Product](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [ProductNumber] [nvarchar](25) NOT NULL,
    [Color] [nvarchar](15) NULL,
    [StandardCost] [money] NOT NULL,
    [ListPrice] [money] NOT NULL,
    [Size] [nvarchar](5) NULL,
    [Weight] [decimal](8, 2) NULL,
    [ProductCategoryID] [int] NULL,
    [ProductModelID] [int] NULL,
    [SellStartDate] [datetime] NOT NULL,
    [SellEndDate] [datetime] NULL,
    [DiscontinuedDate] [datetime] NULL,
    [ThumbNailPhoto] [varbinary](max) NULL,
    [ThumbnailPhotoFileName] [nvarchar](50) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY];

Note: Make sure you are adding ; (semicolon) at the end of each statement and also remove GO from all the script. You can add insert statement in this file.

To Generate SQL Server Database Script see the below link:

Create Script to Copy Database Schema and All The Objects

   d) In Solution Explorer, select tables.txt. In the Properties window, set the BuildAction property to Embedded Resource.

   e) If you have stored procedure in your Database then Add a new txt file (named as getproduct.txt) as described above and add the create stored procedure statement in the file.

CREATE PROCEDURE spGetProduct 
     
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM Product;
END

   f) In Solution Explorer, select getproduct.txt. In the Properties window, set the BuildAction property to Embedded Resource.

5. Add code to the installer class to read the text files

   a) In Solution Explorer, select SetupInstaller.cs. On the View menu, click Code.

   b) Add the following Namespaces statement at the top of the module:

using System.IO;
using System.Data.SqlClient;
using System.Reflection;
using System.Windows;

   c) Add the following code to the SetupInstaller class, after the declaration of base ():

[RunInstaller(true)]
    public partial class SetupInstaller : System.Configuration.Install.Installer
    {
        SqlConnection masterConnection = new SqlConnection();
        public SetupInstaller(): base()
        {
            InitializeComponent();
        }
        private string GetSql(string Name)
        {
 
            try
            {
                // Gets the current assembly.
                Assembly Asm = Assembly.GetExecutingAssembly();
 
                // Resources are named using a fully qualified name.
                Stream strm = Asm.GetManifestResourceStream(Asm.GetName().Name + "." + Name);
 
                // Reads the contents of the embedded file.
                StreamReader reader = new StreamReader(strm);
                return reader.ReadToEnd();
 
            }
            catch (Exception ex)
            {
                MessageBox.Show("In GetSQL: " + ex.Message);
                throw ex;
            }
        }
 
        private void ExecuteSql(string DatabaseName, string Sql)
        {
            System.Data.SqlClient.SqlCommand Command = new System.Data.SqlClient.SqlCommand(Sql, masterConnection);
 
            // Initialize the connection, open it, and set it to the "master" database
            masterConnection.ConnectionString = Properties.Settings.Default.masterConnectionString;
            Command.Connection.Open();
            Command.Connection.ChangeDatabase(DatabaseName);
            try
            {
                Command.ExecuteNonQuery();
            }
            finally
            {
                // Closing the connection should be done in a Finally block
                Command.Connection.Close();
            }
        }
 
        protected void AddDBTable(string strDBName)
        {
            try
            {
                // Creates the database.
                ExecuteSql("master", "CREATE DATABASE " + strDBName);
 
                // Creates the tables.
                ExecuteSql(strDBName, GetSql("tables.txt"));
 
                // Creates the stored procedure.
                ExecuteSql(strDBName, GetSql("getproduct.txt"));
 
            }
            catch (Exception ex)
            {
                // Reports any errors and abort.
                MessageBox.Show("In exception handler: " + ex.Message);
                throw ex;
            }
        }
 
 
        public override void Install(System.Collections.IDictionary stateSaver)
        {
            base.Install(stateSaver);
            AddDBTable("TestDB");
        }
 
        [System.Security.Permissions.SecurityPermission(System.Security.Permissions.SecurityAction.Demand)]
        public override void Uninstall(IDictionary savedState)
        {
            base.Uninstall(savedState);
            ExecuteSql("master", "DROP DATABASE TestDB");
        }
    }

6. Create a deployment project

   a) On the File menu, click Add, and then click New Project.

   b) In the Add New Project dialog box, open the Other Project Types node and select Setup and Deployment Projects in the Project Type pane. Then select Setup Project in the Templates pane. In the Name box, type WPFTestInstaller.

   c) In the Properties window, select the ProductName property and type DB Installer.

   d) In the File System Editor, select the Application Folder. On the Action menu, click Add, and then click Project Output.

   e) In the Add Project Output Group dialog box, select Primary output for the WPFTest project.

If you want to create desktop Shortcut/Start->All Programs with Icon then follow below steps:

   f) In the File System Editor, select the Application Folder. On the Action menu, click Add, and then click File.

   g) Browse and select a .ico file to make it as Icon of your Project. Then click OK.

   h) Right click on Primary output for WPFTest (Active) and select Create Shortcut to Primary output for WPFTest (Active).

   i) Rename Shortcut to Primary output for WPFTest (Active) to your Application name (WPF Test).

   j) Right click on WPF Test then select Properties Window, In the Icon section select dropdown and click on Browse.

   k) In the Icon Window click Browse then double click Application Folder and select your Icon file and click OK.OK.

   l) Drag the WPF Test and drop it into User's Desktop in File System Editor.

   m) Repeat the step h, i, j and k and then Drag the WPF Test and drop it into User's Program Menu in File System Editor.

7. Create a custom action

   a) Select the WPFTestInstaller project in Solution Explorer. On the View menu, point to Editor, and then click Custom Actions.

   b) In the Custom Actions Editor, select the Install node. On the Action menu, click Add Custom Action.

   c) In the Select item in project dialog box, double-click the Application Folder.

   d) Select Primary output from DBCustomAction (Active), then click OK to close the dialog box.

   e) Repeat the step b, c and d for Uninstall node of Custom Actions Editor.

   f) On the Build menu, click Build WPFTestInstaller.

8. Install/Uninstall the application on your development computer.

   a) Select the WPFTestInstaller project in Solution Explorer. On the Project menu, click Install.

   b) Select the WPFTestInstaller project in Solution Explorer. On the Project menu, click Uninstall.

9. To get the installer msi

   a) In the Solution Explorer right click on WPFTestInstaller and select Open Folder in Windows Explorer.

   b) In the Debug Folder you will get your DB Installer.msi.

Take this DB Installer.msi and you can install it to other systems which has .net 4.0 and SQL Server 2008 Express Installed.

Source Code: WPFTest.rar (510.28 kb)

To Create this Application i have taken the help of Microsoft Walkthrough

Using a Custom Action to Create a Database at Installation