devxlogo

ExecuteSqlScripts – Executing an array of script files

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:ScriptsCreateDbTables.sql"'   Dim script2 As String = "C:ScriptsCreateDbSprocs.sql"'   Dim script3 As String = "C:ScriptsInsertData.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 TryEnd Sub

devx-admin

Share the Post: