Problem with GO statements in SQL scripts

Recently, I was working on an installer application which, as part of its job, would update the database based on a SQL script. When running such scripts from the .NET environment, you would get a SqlException as it does not like the GO statements within your script. The way around that is to split your script into pieces separated by GO and run each piece separately.

You also want to make sure that either all the pieces run successfully or none is run; otherwise, it would be hard to know how far the update has gone. Using a SqlTransaction ensures that if any problem happens, the transaction is rolled back and it only gets committed if there was no problem.

The code that I ended up using is shown below. It first does the segmentation of the original script using RegEx (defined in System.Text.RegularExpressions) and then defines a SqlTransaction for the whole update:

Private Sub ExecuteSqlQuery()

    Dim i As Integer

    ' Read the sql file 
    Directory.SetCurrentDirectory(txtDownLoadLocation.Text)

    Dim fileStream As New FileStream(SqlFileName, FileMode.Open, FileAccess.Read)
    Dim streamReader As New StreamReader(fileStream)

    streamReader.BaseStream.Seek(0, SeekOrigin.Begin)

    Dim allSqlString As String = streamReader.ReadToEnd()

    streamReader.Close()
    fileStream.Close()

    ' Create segments of the string called "lines" each separated by "GO" 
    Dim regex As Regex = New Regex("^GO", RegexOptions.IgnoreCase)
    Dim lines As String() = Regex.Split(allSqlString)

    Dim connectionString As String = "Data Source=" & _
        txtDatabaseServer.Text & ";Integrated Security=SSPI;"
    Dim connection As SqlConnection = New SqlConnection(connectionString)
    Dim cmd As SqlCommand = New SqlCommand()
    cmd.Connection = connection

    connection.Open()

    ' Execute as a transaction, roll back if it fails
    Dim transaction As SqlTransaction = connection.BeginTransaction()
    cmd.Transaction = transaction

    For i = 0 To lines.Length - 1
        If lines(i).Length > 0 Then
            Try
                cmd.CommandText = lines(i)
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                transaction.Rollback()
                connection.Close()
                Exit Sub
            End Try
        End If
    Next

    transaction.Commit()
    connection.Close()
End Sub