ExecuteSqlScripts - Executing an array of script files

' Execute an array of sql script files with batch statements,
'  by using the same SqlCommand object
'  - connString is the connection string for the destination database
'
' Example:
'   Dim connString As String = "server=(local);Persist Security 
' Info=False;Integrated Security=SSPI;database=TestDB"
'   Dim script1 As String = "C:\Scripts\CreateDbTables.sql"
'   Dim script2 As String = "C:\Scripts\CreateDbSprocs.sql"
'   Dim script3 As String = "C:\Scripts\InsertData.sql"
'   ExecuteSqlScripts(connString, script1, script2, script3)

Sub ExecuteSqlScripts(ByVal connString As String, ByVal ParamArray scriptFiles() _
    As String)
    ' open the connection and create a command that uses it
    Dim cn As New SqlConnection(connString)
    Dim cmd As New SqlCommand
    Try
        cn.Open()
        cmd.Connection = cn

        Dim scriptFile As String
        For Each scriptFile In scriptFiles
            ' get the content of the script file
            Dim sr As System.IO.StreamReader = Nothing
            Try
                sr = New System.IO.StreamReader(scriptFile)
                ' use the script file's content as CommandText for the command 
                ' created above
                cmd.CommandText = sr.ReadToEnd()
            Catch ex As Exception
                Throw ex
            Finally
                If Not sr Is Nothing Then sr.Close()
            End Try
            ' execute this script
            cmd.ExecuteNonQuery()
        Next
    Catch ex As Exception
        Throw ex
    Finally
        cn.Close()
    End Try
End Sub