SQL setup during application installation

3 views
Skip to first unread message

CruznOz

unread,
Mar 8, 2004, 9:18:12 AM3/8/04
to
To those of you that think there are 'no stupid questions'... I am
attempting to deploy a VB .NET application that runs against MS SQL
2000. I need to find out what I need to do in order to deploy without
having to install SQL Client manager to establish the required
definitions for the SQL server.

Thanks!


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Dan Guzman

unread,
Mar 8, 2004, 9:49:54 AM3/8/04
to
You can run SQL scripts directly from your app code. If you already have an
installer, you can do this as a Custom Action.

The VB.Net example below will run a SQL script contained in a file.

Sub Main()
Try
Dim strConnectionString As String = _
"Data Source=MyServer" & _
";Integrated Security=SSPI" & _
";Initial Catalog=MyDatabase"
Dim sqlConnection As SqlClient.SqlConnection = _
New SqlClient.SqlConnection(strConnectionString)
sqlConnection.Open()
Dim sqlScriptFileName As String = _
"C:\MyScripts\SQLScript.sql"
ExecuteSqlScriptFile(sqlScriptFileName, sqlConnection)
sqlConnection.Close()
Catch sqlException As SqlClient.SqlException
Console.WriteLine("Connection open failed: " + _
sqlException.ToString)
End Try
End Sub

Sub ExecuteSqlScriptFile( _
ByVal SqlScriptFileName As String, _
ByVal SqlConnection As SqlConnection)

Dim sqlScriptLine As String
Dim sqlBatch As New System.IO.StringWriter

Dim sqlScriptStream As StreamReader = _
New System.IO.StreamReader(SqlScriptFileName)

Do While sqlScriptStream.Peek > -1
sqlScriptLine = sqlScriptStream.ReadLine()
If String.Compare(sqlScriptLine.Trim, "GO", True) = 0 Then
ExecuteSqlBatch(sqlBatch.ToString, SqlConnection)
sqlBatch = New System.IO.StringWriter
Else
sqlBatch.WriteLine(sqlScriptLine)
End If
Loop
sqlScriptStream.Close()
ExecuteSqlBatch(sqlBatch.ToString, SqlConnection)
End Sub

Sub ExecuteSqlBatch(ByVal SqlBatch As String, _
ByVal sqlConnection As SqlClient.SqlConnection)
If SqlBatch = "" Then Exit Sub
Try
'execute SQL batch
Dim sqlCommand As New SqlClient.SqlCommand(SqlBatch, _
sqlConnection)
sqlCommand.CommandTimeout = 0
sqlCommand.ExecuteNonQuery()
Catch sqlException As SqlClient.SqlException
Console.WriteLine("Script failed: " + sqlException.ToString)
End Try
End Sub

--
Hope this helps.

Dan Guzman
SQL Server MVP

"CruznOz" <wednes...@yahoo.com> wrote in message
news:404c80a4$0$197$7586...@news.frii.net...

Reply all
Reply to author
Forward
0 new messages