CLR: run a SQL script from a .sql file

Public Function RunSQLScript(ByVal Filename As String, ByRef Errortext As String) As Boolean
Dim file As New FileInfo(Filename)
Dim script As String = String.Empty
Dim command As SqlCommand = Nothing
Try
script = file.OpenText().ReadToEnd()
Catch ex As Exception
Errortext = ex.Message
RunSQLScript = False
Exit Function
End Try
script = script.Replace(“GO”, “”)
‘Optional to Replace Comments with empty string
script = Regex.Replace(script, “([/*][*]).*([*][/])”, “”)
‘Optional to Replace Chain of spaces with one Space
script = Regex.Replace(script, “\\s{2,}”, ” “)
Try
command = DBCnn.CreateCommand
command.CommandType = CommandType.Text
command.CommandText = script
command.ExecuteNonQuery()
RunSQLScript = True
Catch ex As SqlException
Errortext = ex.Message
RunSQLScript = False
End Try
DBCnnClose()
End Function

Public Function RunSQLScript(ByVal Filename As String, ByRef Errortext As String) As Boolean
Dim file As New FileInfo(Filename)        Dim script As String = String.Empty        Dim command As SqlCommand = Nothing
Try
script = file.OpenText().ReadToEnd()
Catch ex As Exception
Errortext = ex.Message            RunSQLScript = False            Exit Function
End Try
script = script.Replace(“GO”, “”)        ‘Optional to Replace Comments with empty string        script = Regex.Replace(script, “([/*][*]).*([*][/])”, “”)        ‘Optional to Replace Chain of spaces with one Space        script = Regex.Replace(script, “\\s{2,}”, ” “)
Try
command = DBCnn.CreateCommand            command.CommandType = CommandType.Text            command.CommandText = script            command.ExecuteNonQuery()            RunSQLScript = True
Catch ex As SqlException
Errortext = ex.Message            RunSQLScript = False
End Try
DBCnnClose()
End Function

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s