How to Execute sql script in vb.net
-
hi friends, i am suffering with a problem in database programming. my need is to execute a sql scritp using vb.net. i have a file having extension .sql. i want this file to be executed programically. pls help me.
-
hi friends, i am suffering with a problem in database programming. my need is to execute a sql scritp using vb.net. i have a file having extension .sql. i want this file to be executed programically. pls help me.
' Create a stream to read the sql file Dim reader As StreamReader = New StreamReader(path) ' Read the contents of the file into a string variable Dim sqlScript as String = reader.ReadToEnd() ' Execute the sql on the database Dim command as New SqlCommand(sqlScript, New SqlConnection(connStr)) command.ExecuteNonQuery()
-
' Create a stream to read the sql file Dim reader As StreamReader = New StreamReader(path) ' Read the contents of the file into a string variable Dim sqlScript as String = reader.ReadToEnd() ' Execute the sql on the database Dim command as New SqlCommand(sqlScript, New SqlConnection(connStr)) command.ExecuteNonQuery()
There's one additional aspect to add to this. If your file was created in Enterprise Manager using "Generate SQL Script" then it's going to have "GO" commands through out it. You'll need to add code to remove that by parsing it line by line. If this is the case, then use something like this:
'Open Script from file name. Dim oStreamReader As System.IO.StreamReader Dim sb As New System.Text.StringBuilder Dim sTemp As String = "" oStreamReader = New IO.StreamReader(ScriptName) 'Loop Through Lines until done Do Dim cmd As New SqlClient.SqlCommand sb = New System.Text.StringBuilder Do sTemp = oStreamReader.ReadLine If sTemp > "" And sTemp <> "GO" Then sb.Append(sTemp + vbCrLf) End If Loop Until (sTemp = "GO" Or oStreamReader.Peek = -1) ''Execute chunk against database 'oStreamReader.ReadLine() If sb.ToString > "" Then Try cmd.CommandText = sb.ToString cmd.Connection = cn cmd.ExecuteNonQuery() cmd.Dispose() Catch ex As SqlClient.SqlException MessageBox.Show(ex.Message) End Try End If 'MessageBox.Show(sb.ToString) sb = Nothing 'oStreamReader.ReadLine() Loop Until (oStreamReader.Peek = -1) cn.Close()I just wrote an app last week that did this exact thing. -
There's one additional aspect to add to this. If your file was created in Enterprise Manager using "Generate SQL Script" then it's going to have "GO" commands through out it. You'll need to add code to remove that by parsing it line by line. If this is the case, then use something like this:
'Open Script from file name. Dim oStreamReader As System.IO.StreamReader Dim sb As New System.Text.StringBuilder Dim sTemp As String = "" oStreamReader = New IO.StreamReader(ScriptName) 'Loop Through Lines until done Do Dim cmd As New SqlClient.SqlCommand sb = New System.Text.StringBuilder Do sTemp = oStreamReader.ReadLine If sTemp > "" And sTemp <> "GO" Then sb.Append(sTemp + vbCrLf) End If Loop Until (sTemp = "GO" Or oStreamReader.Peek = -1) ''Execute chunk against database 'oStreamReader.ReadLine() If sb.ToString > "" Then Try cmd.CommandText = sb.ToString cmd.Connection = cn cmd.ExecuteNonQuery() cmd.Dispose() Catch ex As SqlClient.SqlException MessageBox.Show(ex.Message) End Try End If 'MessageBox.Show(sb.ToString) sb = Nothing 'oStreamReader.ReadLine() Loop Until (oStreamReader.Peek = -1) cn.Close()I just wrote an app last week that did this exact thing.you can use
Imports System.IO public sub RunSQL Dim conn As New SqlClient.SqlConnection Dim sqlCmd As New SqlClient.SqlCommand Dim sqltxt As String conn.ConnectionString = ("Data Source= " + DS + " ;Initial Catalog= " + DB + ";Integrated Security=True") conn.Open() sqltxt = System.IO.File.ReadAllText("SP.sql") sqlCmd.CommandText = sqltxt sqlCmd.Connection = conn sqlCmd.ExecuteNonQuery() conn.Close()this code will read the file once and execute it