Binaryworld - ExecuteSqlScripts - Executing an array of script files ... [ VB.net -> ADO.net ]

' 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