Custom Action for create the Database in Setup Project (visual studio)

Since I started to develop database applications for the desktop I was looking for some good solution for attach database to the computer automatically while the setup process is going on. I found several articles written for this task in the internet but this walkthrough guide on msdn was really helpful and successfully worked for me.

To create an installer class

  1. On the File menu, click New Project.
  2. In the New Project dialog box, select Visual Basic Projects in the Project Type pane, and then choose Class Library in the Templates pane. In the Name box, type DBCustomAction.
  3. On the Project menu, click Add New Item.
  4. In the Add New Item dialog box, choose Installer Class. In the Name box, type VbDeployInstaller.vb.

To create a data connection object

  1. In Server Explorer, select Data Connections. Right-click and choose Add Connection.
  2. In the Choose Data Source dialog box, select Microsoft SQL Server.
  3. In the Add Connection dialog box, do the following:
  4. In the Server name drop-down list, type or select a server name.
  5. Select Use Windows Authentication.
  6. In the database box, type master.
  7. Click OK to close the dialog box.
  8. From the Data menu, click Add New Data Source, then use the wizard to add the connection that you established in the previous steps. To verify that the data source is in the project, click Show Data Sources on the Data menu.

To create a text file that contains a SQL statement to create a database

  1. In Solution Explorer, select the DBCustomAction 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:

I have developed a Birthday Reminder Calender application since I use this SQL query for create the table called “tblPeople”

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
USE [BRDB]
 
/****** Object: Table [dbo].[tblPeople] Script Date: 03/11/2012 19:45:39 ******/
SET ANSI_NULLS ON
 
SET QUOTED_IDENTIFIER ON
 
SET ANSI_PADDING ON
 
CREATE TABLE [dbo].[tblPeople](
[ID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Birthday] [varchar](50) NOT NULL,
[Phone] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[Address] [varchar](max) NULL,
CONSTRAINT [PK_tblPeople] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
SET ANSI_PADDING OFF
USE [BRDB]

/****** Object: Table [dbo].[tblPeople] Script Date: 03/11/2012 19:45:39 ******/
SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[tblPeople](
[ID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[Birthday] [varchar](50) NOT NULL,
[Phone] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[Address] [varchar](max) NULL,
CONSTRAINT [PK_tblPeople] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

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

To add code to the installer class to read the text file

  1. In Solution Explorer, select VbDeployInstaller.vb. On the View menu, choose Code.
  2. Add the following Imports statement at the top of the module:
Although I have changed the original code in here. Some codes removed parameters for gather user information while setup process. And some additional functions added for drop the Database when the system uninstalls.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
Imports System.IO
Imports System.Reflection
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports System.Configuration.Install
 
Public Class DeployInstaller
    Dim masterConnection As New System.Data.SqlClient.SqlConnection
    Public Sub New()
        MyBase.New()
 
        'This call is required by the Component Designer.
        InitializeComponent()
 
        'Add initialization code after the call to InitializeComponent
 
    End Sub
 
    Private Function GetSql(ByVal Name As String) As String
        Try
 
            ' Gets the current assembly.
            Dim Asm As [Assembly] = [Assembly].GetExecutingAssembly()
 
            ' Resources are named using a fully qualified name.
            Dim strm As Stream = Asm.GetManifestResourceStream(
              Asm.GetName().Name + "." + Name)
 
            ' Reads the contents of the embedded file.
            Dim reader As StreamReader = New StreamReader(strm)
            Return reader.ReadToEnd()
 
        Catch ex As Exception
            MsgBox("In GetSQL: " & ex.Message)
            Throw ex
        End Try
    End Function
 
    Private Sub ExecuteSql(ByVal DatabaseName As String, ByVal Sql As String)
        Dim Command As New SqlClient.SqlCommand(Sql, masterConnection)
 
        ' Initialize the connection, open it, and set it to the "master" database
        masterConnection.ConnectionString = My.Settings.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()
        End Try
    End Sub
 
    Protected Sub AddDBTable(ByVal strDBName As String)
        Try
            ' Creates the database.
            ExecuteSql("master", "CREATE DATABASE " + strDBName)
 
            ' Creates the tables.
            ExecuteSql(strDBName, GetSql("sql.txt"))
 
        Catch ex As Exception
            ' Reports any errors and abort.
            MsgBox("In exception handler: " & ex.Message)
            Throw ex
        End Try
    End Sub
 
    Protected Sub DropDB(ByVal strDBName As String)
        Try
            ' Creates the database.
            ExecuteSql("master", "DROP DATABASE " + strDBName)
 
            ' Creates the tables.
            'ExecuteSql(strDBName, GetSql("sql.txt"))
        Catch ex As Exception
            ' Reports any errors and abort.
            MsgBox("In exception handler: " & ex.Message)
            Throw ex
        End Try
    End Sub
 
    Public Overrides Sub Install(ByVal stateSaver As System.Collections.IDictionary)
 
        MyBase.Install(stateSaver)
        AddDBTable("BRDB")
    End Sub
 
    Public Overrides Sub Uninstall(ByVal stateSaver As  _
        System.Collections.IDictionary)
 
        MyBase.Uninstall(stateSaver)
        DropDB("BRDB")
    End Sub
End Class
Imports System.IO
Imports System.Reflection
Imports System.Data.SqlClient
Imports System.ComponentModel
Imports System.Configuration.Install

Public Class DeployInstaller
    Dim masterConnection As New System.Data.SqlClient.SqlConnection
    Public Sub New()
        MyBase.New()

        'This call is required by the Component Designer.
        InitializeComponent()

        'Add initialization code after the call to InitializeComponent

    End Sub

    Private Function GetSql(ByVal Name As String) As String
        Try

            ' Gets the current assembly.
            Dim Asm As [Assembly] = [Assembly].GetExecutingAssembly()

            ' Resources are named using a fully qualified name.
            Dim strm As Stream = Asm.GetManifestResourceStream(
              Asm.GetName().Name + "." + Name)

            ' Reads the contents of the embedded file.
            Dim reader As StreamReader = New StreamReader(strm)
            Return reader.ReadToEnd()

        Catch ex As Exception
            MsgBox("In GetSQL: " & ex.Message)
            Throw ex
        End Try
    End Function

    Private Sub ExecuteSql(ByVal DatabaseName As String, ByVal Sql As String)
        Dim Command As New SqlClient.SqlCommand(Sql, masterConnection)

        ' Initialize the connection, open it, and set it to the "master" database
        masterConnection.ConnectionString = My.Settings.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()
        End Try
    End Sub

    Protected Sub AddDBTable(ByVal strDBName As String)
        Try
            ' Creates the database.
            ExecuteSql("master", "CREATE DATABASE " + strDBName)

            ' Creates the tables.
            ExecuteSql(strDBName, GetSql("sql.txt"))

        Catch ex As Exception
            ' Reports any errors and abort.
            MsgBox("In exception handler: " & ex.Message)
            Throw ex
        End Try
    End Sub

    Protected Sub DropDB(ByVal strDBName As String)
        Try
            ' Creates the database.
            ExecuteSql("master", "DROP DATABASE " + strDBName)

            ' Creates the tables.
            'ExecuteSql(strDBName, GetSql("sql.txt"))
        Catch ex As Exception
            ' Reports any errors and abort.
            MsgBox("In exception handler: " & ex.Message)
            Throw ex
        End Try
    End Sub

    Public Overrides Sub Install(ByVal stateSaver As System.Collections.IDictionary)

        MyBase.Install(stateSaver)
        AddDBTable("BRDB")
    End Sub

    Public Overrides Sub Uninstall(ByVal stateSaver As  _
        System.Collections.IDictionary)

        MyBase.Uninstall(stateSaver)
        DropDB("BRDB")
    End Sub
End Class
  1. On the Build menu, choose Build DBCustomAction.

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 DBCustomAction project.

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 /dbname=[CUSTOMTEXTA1].
  6. On the Build menu, choose Build DBCustomAction_Setup.

To install on your development computer

  1. Select the DBCustomAction_Setup project in Solution Explorer. On the Project menu, choose Install.
  2. This will run the installer on your development computer.

Tada! Yes I did it. šŸ™‚ This setup can beĀ distributedĀ where ever without no matter of installing prerequisites and attach or copy database files manually. This is the Setup ProjectĀ I developed for my application. But in this file I have removed the prerequisites due to it is too heavy to upload in here. So you cannot setup it properly unless you got .NET 4.0 Framework and SQL Server on your computer.

Leave a Reply