custom action to modify a database with web project installer

[DC Gringo]

Hi, I'm trying to use a custom action to modify a database (rather than
create one) using the VS.NET '03's help example called "Custom Action to
Create Database During Installation".

I've made two modifications to the sample in the document...both are in the
"Protected Sub AddDBTable" (towards the bottom).

I've changed

' Creates the database.
ExecuteSql("master", "CREATE DATABASE" + strDBName)

to

' Changes the database.
ExecuteSql("master", "USE " + strDBName)

The error I'm getting on installation is: "in exception handler: incorrect
syntax near 'USE'."

If there's an easier way to do this, please let me know. Basically, I'm
simply wanting to include a database change script that runs along with a
web project installer.



Here is all my code:

Imports System.ComponentModel
Imports System.Configuration.Install
Imports System.IO
Imports System.Reflection


<RunInstaller(True)> Public Class DBChangeCustomAction
Inherits System.Configuration.Install.Installer

#Region " Component Designer generated code "

Public Sub New()
MyBase.New()

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

'Add any initialization after the InitializeComponent() call

End Sub

'Installer overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Required by the Component Designer
Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Component Designer
'It can be modified using the Component Designer.
'Do not modify it using the code editor.
Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation
id=""MyWorkstation"";packet size=4096;integrated security=SSPI;data
source=My" & _
"Server;persist security info=False;initial catalog=master"

End Sub

#End Region

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, 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 Try
End Sub

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

' Creates the tables.
ExecuteSql(strDBName, GetSql("sqlChange.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(Me.Context.Parameters.Item("dbname"))
End Sub


End Class

 

[Cor]

Hi DC,

Although I am one of the worst in SQL,

I use it as (rougly pasted and corrected watch typoes)
\\\
Dim Conn As String = "Server=(local); DataBase=; Integrated Security=SSPI"
Dim strSQL As String = "CREATE DATABASE MyDatabase"
Dim cmd As New SqlCommand(strSQL, Conn)
Conn.Open()
cmd.ExecuteNonQuery()
cmd.CommandText = _
"USE MyDatabase" & vbCrLf & _
"CREATE TABLE tbl..................
///

I hope this helps?

Cor

 

[DC Gringo]

Cor,

Thanks for the response.

Actually, I'm able to have the package create a new database and then run a
script to create and/or alter objects. What I'm trying to do here is simple
skip the CREATE DATABASE statement, and instead simple go right to the
creation/alteration of database objects.

_____
DC G