Step 1: Create a Setup project right click and select
Sql server 2008 as prerequisite. In VS2008 Sql Server 2005 available as
prerequisite.
Here are the steps to
install sql server database with setup using custom action.
Step 2: 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.
Step3: 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 4: 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 5: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.
. 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 6: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.
