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, chooseAdd 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 givename
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());
// Reads the contents of the embedded file.
StreamReader reader = new StreamReader(strm);
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 Typesnode 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 Propertieswindow, 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 theAction 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 BodyTextproperty 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.