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