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

"Cor" <no*@non.com> wrote in message
news:uE****************@TK2MSFTNGP12.phx.gbl...

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

[Patrick Baker [MSFT]]

Since you are not creating the database you should just be able to use it,

The Sub ExecuteSql contains these calls

Command.Connection.Open()
Command.Connection.ChangeDatabase(DatabaseName)

That should be all you need. You can get rid of the ExecuteSql("master",
"CREATE DATABASE/USE" + strDBName)

See of this works

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

Patrick Baker - Visual Basic/Deployment Quality Assurance Team
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------

Reply-To: "DC Gringo" <dc******@visiontechnology.net>
From: "DC Gringo" <dc******@visiontechnology.net>
Subject: custom action to modify a database with web project installer
Date: Mon, 29 Dec 2003 18:20:34 -0500
Lines: 142
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <u6**************@TK2MSFTNGP09.phx.gbl>
Newsgroups: microsoft.public.dotnet.languages.vb
NNTP-Posting-Host: user98.chemonics.net 63.66.50.98
Path: cpmsftngxa07.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTN GXS01.phx.gbl!TK2MSFTNGXA0
5.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gb lXref: cpmsftngxa07.phx.gbl microsoft.public.dotnet.languages.vb:168236
X-Tomcat-NG: microsoft.public.dotnet.languages.vb

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().Nam e + "." + 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