Generally, It is required to install sql server database with visual studio setup. Here are the steps to install sql server database with setup using custom action.

Step 1: create a text file that contains a SQL statement to create a database, Tables, functions and stored procedures

1. In Solution Explorer, select the project. On the Project menu, choose Add New Item.

2. In the Add New Item dialog box, choose Text File. In the Name box, type sql.txt (must be in lower case).

3. Add the following to the sql.txt file:

CREATE DATABASE [<Database Name>]
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE [<Database Name>]
GO

CREATE TABLE [dbo].[Employees] (
[Name] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Rsvp] [int] NULL ,
[Requests] [nvarchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY];


ALTER TABLE [dbo].[Employees] WITH NOCHECK ADD
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[Name]
) ON [PRIMARY];

<Table, Stored procedures, Functions …………….etc>

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



Step2: Add installer class

• On the Project menu, click Add New Item.

• In the Add New Item dialog box, choose Installer Class and give name

You need to add following dll in your project

Microsoft.SqlServer.ConnectionInfo

Microsoft.SqlServer.Smo


Add following namespace in installer class:

using Microsoft.SqlServer.Management.Smo;

using Microsoft.SqlServer.Management.Common;




Add the following code to the installer class

[RunInstaller(true)]
public partial class CustomInstaller : Installer
{

private string logFilePath = "C:\\SetupLog.txt";
public CustomInstaller()       
{
//This call is required by the Component Designer.
//Add initialization code after the call to InitializeComponent
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)
{
Log(ex.ToString());
throw ex;
}
}
private void ExecuteSql(string serverName,string dbName, string Sql)
{
string connStr = "Data Source=" + serverName + ";Initial Catalog=" + dbName + ";Integrated Security=True";
using (SqlConnection conn = new SqlConnection(connStr))
{
try
{
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(Sql);
}
catch (Exception ex)
{
Log(ex.ToString());
}
}
}
protected void AddDBTable(string serverName)
{
try
{
// Creates the database and installs the tables.          
string strScript = GetSql("sql.txt");
ExecuteSql(serverName, "master", strScript);           
}
catch (Exception ex)
{
//Reports any errors and abort.
Log(ex.ToString());
throw ex;
}
}

public override void Install(System.Collections.IDictionary stateSaver)
{
base.Install(stateSaver);
Log("Setup started");
AddDBTable(this.Context.Parameters["servername"]);

}
public void Log(string str)
{
StreamWriter Tex;
try
{
Tex = File.AppendText(this.logFilePath);
Tex.WriteLine(DateTime.Now.ToString() + " " + str);
Tex.Close();
}
catch
{}
}

}

Step 3: To create a deployment project

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

2. 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 DBCustomAction_Setup.

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

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

5. In the Add Project Output Group dialog box, select Primary output for the Existing (say DBCustomAction) project.



Step 4:To create a custom installation dialog

1. Select the DBCustomAction_Setup project in Solution Explorer. On the View menu, point to Editor, and choose User Interface.

2. In the User Interface Editor, select the Start node under Install. On the Action menu, choose Add Dialog.

3. In the Add Dialog dialog box, select the Textboxes (A) dialog, then click OK.

4. On the Action menu, choose Move Up. Repeat until the Textboxes (A) dialog is above the Installation Folder node.

5. In the Properties window, select the BannerText property and type Specify Database Server Name.

6. Select the BodyText property and type This dialog allows you to specify the name of the database server.

7. Select the Edit1Label property and type Name of database server:.

8. Select the Edit1Property property and type CUSTOMTEXTA1.

9. Select the Edit2Visible, Edit3Visible, and Edit4Visible properties and set them to False.



Step 5:To create a custom action

1. Select the DBCustomAction_Setup project in Solution Explorer. On the View menu, point to Editor, and then click Custom Actions.

2. In the Custom Actions Editor, select the Install node. On the Action menu, choose Add Custom Action.

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

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

5. Make sure that Primary output from DBCustomAction (Active) item is selected in the Custom Actions Editor. In the Properties window, select the CustomActionData property and type /servername=[CUSTOMTEXTA1].

6. On the Build menu, choose Build DBCustomAction_Setup.

To install on your development computer

• Select the DBCustomAction_Setup project in Solution Explorer. On the Project menu, choose Install.



This will run the installer on your machine.
Hope! It helps

Comments:  5

  • Luis

    Amazing, but can it be done to enter not only the servername, but the databasename, username and password? I know that you can use Edit2 to Edit4 for that, but how do you reference it on CustomActionData? /database=[CUSTOMTEXT2]; /username=[CUSTOMTEXT3]; /password=[CUSTOMTEXT4]?

  • Rajesh Kumar

    Thanks for posting nice article. I am trying to use this in my current project but getting two errors,

    1. “The type or name space Installer could not be found”.
    2. “The type or name space RunInstaller could not be found”.

    Here is my code. Am I missing something?

    <br>namespace gstreturnnew<br>{<br>    partial class Installdb<br>    {<br>        /// &lt;summary&gt;<br>        /// Required designer variable.<br>        /// &lt;/summary&gt;<br>        private System.ComponentModel.IContainer components = null;<br><br>        /// &lt;summary&gt; <br>        /// Clean up any resources being used.<br>        /// &lt;/summary&gt;<br>        /// &lt;param name="disposing"&gt;true if managed resources should be disposed; otherwise, false.&lt;/param&gt;<br>        protected override void Dispose(bool disposing)<br>        {<br>            if (disposing && (components != null))<br>            {<br>                components.Dispose();<br>            }<br>            base.Dispose(disposing);<br>        }<br><br>        [RunInstaller(true)]<br>        public partial class CustomInstaller : Installer<br>        {<br><br>            private string logFilePath = "C:\SetupLog.txt";<br>            public CustomInstaller()<br>            {<br>                //This call is required by the Component Designer.<br>                //Add initialization code after the call to InitializeComponent<br>                InitializeComponent();<br>            }<br><br>            private string GetSql(string Name)<br>            {<br>                try<br>                {<br><br>                    // Gets the current assembly.<br>                    Assembly Asm = Assembly.GetExecutingAssembly();<br><br>                    // Resources are named using a fully qualified name.<br>                    Stream strm = Asm.GetManifestResourceStream(Asm.GetName().Name + "." + Name);<br><br>                    // Reads the contents of the embedded file.<br>                    StreamReader reader = new StreamReader(strm);<br><br>                    return reader.ReadToEnd();<br>                }<br>                catch (Exception ex)<br>                {<br>                    Log(ex.ToString());<br>                    throw ex;<br>                }<br>            }<br>            private void ExecuteSql(string serverName, string dbName, string Sql)<br>            {<br>                string connStr = "Data Source=" + serverName + ";Initial Catalog=" + dbName + ";Integrated Security=True";<br>                using (SqlConnection conn = new SqlConnection(connStr))<br>                {<br>                    try<br>                    {<br>                        Server server = new Server(new ServerConnection(conn));<br>                        server.ConnectionContext.ExecuteNonQuery(Sql);<br>                    }<br>                    catch (Exception ex)<br>                    {<br>                        Log(ex.ToString());<br>                    }<br>                }<br>            }<br>            protected void AddDBTable(string serverName)<br>            {<br>                try<br>                {<br>                    // Creates the database and installs the tables.<br>                    string strScript = GetSql("dbscript.txt");<br>                    ExecuteSql(serverName, "master", strScript);<br>                }<br>                catch (Exception ex)<br>                {<br>                    //Reports any errors and abort.<br>                    Log(ex.ToString());<br>                    throw ex;<br>                }<br>            }<br><br>            public override void Install(System.Collections.IDictionary stateSaver)<br>            {<br>                base.Install(stateSaver);<br>                Log("Setup started");<br>                AddDBTable(this.Context.Parameters["servername"]);<br><br>            }<br>            public void Log(string str)<br>            {<br>                StreamWriter Tex;<br>                try<br>                {<br>                    Tex = File.AppendText(this.logFilePath);<br>                    Tex.WriteLine(DateTime.Now.ToString() + " " + str);<br>                    Tex.Close();<br>                }<br>                catch<br>                { }<br>            }<br><br>        }<br><br>        #region Component Designer generated code<br><br>        /// &lt;summary&gt;<br>        /// Required method for Designer support - do not modify<br>        /// the contents of this method with the code editor.<br>        /// &lt;/summary&gt;<br>        private void InitializeComponent()<br>        {<br>            components = new System.ComponentModel.Container();<br>        }<br><br>        #endregion<br>    }<br>}<br>
  • Anish

    hi i would like to know how can SMO dlls be made working on a 32 bit machine with sql2005.Now i am getting an excption which reads

    System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.BatchParserClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

    File name: 'Microsoft.SqlServer.BatchParserClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

    please help me …

  • anish

    I am getting an error when i run the setup on another machine

    It reads as

    System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.BatchParserClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

    File name: 'Microsoft.SqlServer.BatchParserClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

    pls help

  • Georgejoseph

    For custom actions that are installation components (ProjectInstaller classes), the CustomActionData property takes a format of /name=value. Each name must be unique and have only one value. Multiple values must be separated by a single space: /name1=value1 /name2=value2. If the value has a space in it, it must be surrounded by quotation marks: /name=”a value”.
    Windows Installer properties can be passed using the bracketed syntax: /name=[PROPERTYNAME]. For Windows Installer properties such as [TARGETDIR] that return a directory, in addition to the brackets you must include quotation marks and a trailing backslash: /name=”[TARGETDIR]“.

    http://msdn.microsoft.com/en-us/library/2w2fhwzz%28v=vs.80%29.aspx