index > .NET Framework Data Access and Storage > Execute a sql script using vb.net

Execute a sql script using vb.net

Hi folks...

In the past, I have broken the sql script down into single batches, then executed each batch in turn using the ExecuteNonQuery method. Is there anyway to actually execute a sql script directly? Does a method exist where we can just pass it the location of the sql script and let it do the rest?

Cheers

Mark

Mark The Archer Evans

you can place this command in the SqlCommand() constructor or set the SqlCommand.CommandText property to the command which SQL Server will accept.

example attaching a database in SQL Server can be done using the SqlCommand() if you give it the correct syntax and so on.

Need 2 be back @ MS - MS All the way! Follower since 1995 MS Super Evangelist| MSDN Forums Moderator

ahmedilyas

Instead of a script use a stored procedure of encapsulate the script in the sproc....then from the command all you have to do is call the stored procedure and pass any neccessary arguements

MSDN Technical Forums Moderator - Happy Coding :)

DMan1

Mark The Archer Evans wrote:

Hi folks...

In the past, I have broken the sql script down into single batches, then executed each batch in turn using the ExecuteNonQuery method. Is there anyway to actually execute a sql script directly? Does a method exist where we can just pass it the location of the sql script and let it do the rest?

Cheers

Mark

Hi,

No, I don't think you can just specify a file and then execute the sql scripts inside the file. You must open your file and store its content in a string var and pass it in a ExecuteNonQuery. To make it simplier you can do this:

command1.ExecuteNonQuery(File.OpenText("C:\Test.sql").ReadToEnd());

cheers,

Paul June A. Domag

Visit my Online Blog - http://webmag.mvps.org

Paul Domag

One addition to the Paul's post. You should be careful about GO statements inside of the files. They are not part of the Transact SQL syntax and will not be executed, but will generate errors executed from the application. You should remove them from the file or split execution into separate chunks between the GO statements

Val Mazur (MVP) http://xport.mvps.org

VMazur

I have used the example below inside my MSSQL2005 class, I just wondered if there was an easier way. At the end of the day, I didn't want to have to modify the sql scripts coming from sql server 2005 manager (just being lazy really). The transaction is handled to ensure that the script executes in full or not at all. Thanks for your input chaps.

Regards

Mark

Public Overrides Sub Execute_SqlScript(ByVal fileName As String, ByVal database As String)

Dim sr As StreamReader = Nothing

Dim sb As StringBuilder = Nothing

Dim line As String = ""

Try

Command_Type = CommandType.Text

Command_Create()

Connection_Open()

Transaction_Begin()

sr = New StreamReader(fileName)

sb = New StringBuilder

line = sr.ReadLine()

If (line = "GO" Or line Is Nothing) Then Exit Do

sb.Append(ControlChars.CrLf & line)

Loop

If line Is Nothing Then Exit Do

objSQLCommand.CommandText = sb.ToString

objSQLCommand.ExecuteNonQuery()

Loop Until line Is Nothing

If Me.Transaction_Exists Then Me.Transaction_Commit()

Catch ex As Exception

Transaction_Rollback()

Throw New aExceptions.BespokeException(ex, _

aExceptions.BespokeException.enmType.SERVER_SQLERROR, _

"MSSQL2005: Execute_SqlScript(2)")

Finally

If sr IsNot Nothing Then sr.Close()

Reset()

End Try

End Sub

Mark The Archer Evans

You do not need to loop. .NET Managed Provider or SQL Server allows to execute batch of the statements if they are separated by semicolon. Basically you need to prepare one string that has semicolon-separated SQL statements and execute it in one shot.

Val Mazur (MVP) http://xport.mvps.org

VMazur

reply 7