Using a custom action to create a database during installation

From http://msdn.microsoft.com/library

The following walkthrough demonstrates the use of a custom action and CustomactiondataProperty to create a database and database table during installation.

NoteThis walkthrough requires SQL server on the computer where you will deploy the application.

To create an installer class

1. OnFileMenu, pointNew, And then chooseProject.
2. InNew projectDialog box, selectVisual Basic ProjectsInProject TypePane, and then chooseClass LibraryInTemplatesPane. InNameBox, typeDbcustomaction.
3. ClickOKTo close the dialog box.
4. OnProjectMenu, chooseAdd new item.
5. InAdd new itemDialog box, chooseInstaller class. InNameBox, typeDbcustomaction.
6. ClickOKTo close the dialog box.

To create a data connection object

1. In Server Explorer, selectData connections. Right-click and chooseAdd connection.
2. InData Link PropertiesDialog box, do the following:
   1. Enter the server name.
   2. SelectUse Windows NT Integrated Security.
   3. In the database box, typeMaster.
   4. ClickOKTo close the dialog box.
3. Drag the new connection and drop it onDbcustomaction. VBDesigner to createSqlconnection1 object.

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

1. In Solution Explorer, selectDbcustomactionProject. OnProjectMenu, chooseAdd new item.
2. InAdd new itemDialog box, chooseText File. InNameBox, typeSQL .txt(Must be in lower case ).
3. ClickOKTo close the dialog box.
4. Add the following to the SQL .txt file:

   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];

5. In Solution Explorer, selectSQL .txt. In the Properties window, setBuildactionPropertyEmbedded Resource.

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

1. In Solution Explorer, selectDbcustomaction. VB. OnViewMenu, chooseCode.
2. Add the followingImportsStatement at the top of the module:

   Imports System.IOImports System.Reflection

3. Add the following code to the class:

   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 TryEnd FunctionPrivate Sub ExecuteSql(ByVal DatabaseName As String, ByVal Sql As String)   Dim Command As New SqlClient.SqlCommand(Sql, sqlConnection1)   Command.Connection.Open()   Command.Connection.ChangeDatabase(DatabaseName)   Try      Command.ExecuteNonQuery()   Finally      ' Finally, blocks are a great way to ensure that the connection       ' is always closed.      Command.Connection.Close()   End TryEnd SubProtected 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 TryEnd SubPublic Overrides Sub Install(ByVal stateSaver As System.Collections.IDictionary)   MyBase.Install(stateSaver)   AddDBTable(Me.Context.Parameters.Item("dbname"))End Sub

4. OnBuildMenu, chooseBuild dbcustomaction.

To create a deployment project

1. OnFileMenu, chooseAdd Project,New project.
2. InAdd ProjectDialog box, selectSetup and deployment projectsInProject TypePane, and then chooseSetup ProjectInTemplatesPane. InNameBox, typeDbcustomaction Installer.
3. ClickOKTo close the dialog box.
4. In the Properties window, selectProductnameProperty and typeDB Installer.
5. In the file system Editor, selectApplication folder. OnActionMenu, chooseAdd,Project output.
6. InAdd project output GroupDialog box, select the primary output forDbcustomactionProject. ClickOKTo close the dialog box.

To create a custom installation Dialog

1. SelectDbcustomaction InstallerProject in Solution Explorer. OnViewMenu, pointEditor, And chooseUser Interface.
2. In the User Interface Editor, selectStartNode underInstall. OnActionMenu, chooseAdd Dialog.
3. InAdd DialogDialog box, selectTextboxes ()Dialog, then clickOKTo close the dialog box.
4. OnActionMenu, chooseMove up. Repeat untilTextboxes ()Dialog is abveInstallation FolderNode.
5. In the Properties window, selectBannertextProperty and typeSpecify Database Name.
6. SelectBodytextProperty and typeThis dialog allows you to specify the name of the database to be created on the database server.
7. SelectEdit1labelProperty and typeName of DB:.
8. SelectEdit1propertyProperty and typeCustomtexta1.
9. SelectEdit2visible,Edit3visible, AndEdit4visibleProperties and set themFalse.

To create a custom action

1. SelectDbcustomaction InstallerProject in Solution Explorer. OnViewMenu, pointEditor, And chooseCustom Actions.
2. In the Custom Actions Editor, selectInstallNode. OnActionMenu, chooseAdd Custom Action.
3. InSelect item in ProjectDialog box, double-clickApplication folder.
4. SelectPrimary output from dbcustomaction (active)Item, then clickOKTo close the dialog box.
5. In the Properties window, selectCustomactiondataProperty and type/Dbname = [customtexta1].
6. OnBuildMenu, chooseBuild dbcustomactioninstaller.

To install on your development computer

• SelectDbcustomaction InstallerProject in Solution Explorer. OnProjectMenu, chooseInstall.

  This will run the installer on your development computer.

  NoteYou must have install permissions on the computer in order to run the installer.

To deploy to another computer

1. In Windows Explorer, navigate to your project directory and find the built Installer. The default path will be/Documents and Settings/Yourloginname/Dbcustomaction installer/Project Configuration/Dbcustomaction Installer. MSI. The defaultProject ConfigurationIs debug.
2. Copy the dbcustomaction Installer. MSI file and all other files and subdirectories in the directory to another computer.
   

   NoteTo install on a computer that is not on a network, copy the files to traditional media such as CD-ROM.

   On the target computer, double-clickSetup.exeFile to run the installer.

   NoteYou must have install permissions on the computer in order to run the installer.

To uninstall the application

1. In the Windows Control Panel, double-clickAdd/Remove Programs.
2. InAdd/Remove ProgramsDialog box, selectDbcustomaction InstallerAnd clickRemove, Then clickOKTo close the dialog box.
   

   TipTo uninstall from your development computer, onProjectMenu, chooseUninstall.

See also

Custom Actions Management in deployment | customactiondata property | connecting to data sources with ADO. Net | error handling in custom actions