Execute a sql script using vb.net

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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

 

Answer 4

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

Answer 5

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)

Do

sb = New StringBuilder

Do

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

Answer 6

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.

Answer 7

I've tweaked Mark's code to my situation - I don't want to redistribute SMO to end user machines, while I still want to execute an SQL script.

I've come up with this - it at least works for one of my scripts... I had tried just getting rid of the GOs and running one big giant SQL statement, but my db drop/create script wouldn't play nice with it...

 

 Public

 Sub

 RunSQLServerScript(ByVal

 ScriptFile As

 String

, Optional

 ByVal

 database As

 String

 = "master"

)
  ' Run an SQL Script file in a slightly dirty way but no need to redistribute SMO and a lot of other gunk.  


  ' Known limitations:


  ' - does not deal with multiline /* */ comments, let alone nested comments


  Dim

 DataConnection As

 New

 System.Data.SqlClient.SqlConnection("SERVER=.\SQLEXPRESS;Initial Catalog="

 & database & ";Trusted_Connection=yes;"

)
  Dim

 SQLScriptCommand As

 System.Data.SqlClient.SqlCommand
  Dim

 sr As

 StreamReader = Nothing


  Dim

 sb As

 System.Text.StringBuilder = Nothing


  Dim

 line As

 String

 = ""


  Dim

 NormalizedLine As

 String

 = ""



  DataConnection.Open()
  Try


   sr = New

 StreamReader(ScriptFile)
   sb = New

 System.Text.StringBuilder
   Do


    line = sr.ReadLine()
    NormalizedLine = UCase(Trim(line))
    ' Let's read it until the next GO


    If

 ( _
     NormalizedLine Is

 Nothing

 Or

 _
     NormalizedLine.StartsWith("-- "

)
     ) Then


     'ignore line


     'Debug.Print("RunSQLServerScript: ignoring line: " & line)


    Else


     If

 NormalizedLine = "GO"

 Then


      If

 sb.Length > 0 Then


       If

 sb.ToString.Contains("DROP DATABASE"

) Or

 _
        sb.ToString.Contains("CREATE DATABASE"

) Or

 _
        sb.ToString.Contains("ALTER DATABASE"

) Then


        ' Don't run this inside a transaction, otherwise we might get


        ' ALTER DATABASE statement not allowed within multi-statement transaction


        Debug.Print("RunSQLServerScript: going to run SQL outside of transaction control:"

 & Environment.NewLine & _
         sb.ToString)
        SQLScriptCommand = DataConnection.CreateCommand
        SQLScriptCommand.Transaction = Nothing


        SQLScriptCommand.CommandType = Data.CommandType.Text
        SQLScriptCommand.CommandText = sb.ToString
        Try


         SQLScriptCommand.ExecuteNonQuery()
        Catch

 SQLex As

 System.Data.SqlClient.SqlException
         Debug.Print("SQL Exception: "

 & SQLex.Message & vbCrLf & SQLex.ToString)
         Throw

 New

 ApplicationException("RunSQLServerScript error: "

 & SQLex.Message)
        Catch

 ex As

 Exception
         Debug.Print("Exception: "

 & ex.Message & vbCrLf & ex.ToString)
         Throw

 New

 ApplicationException("RunSQLServerScript error: "

 & ex.Message)
        Finally


         sb.Clear()
        End

 Try


       Else


        Debug.Print("RunSQLServerScript: going to run SQL:"

 & Environment.NewLine & _
         sb.ToString)
        Dim

 OurTransaction = DataConnection.BeginTransaction 'weird but works.


        SQLScriptCommand = DataConnection.CreateCommand
        SQLScriptCommand.Transaction = OurTransaction
        SQLScriptCommand.CommandType = Data.CommandType.Text
        SQLScriptCommand.CommandText = sb.ToString
        Try


         SQLScriptCommand.ExecuteNonQuery()
         OurTransaction.Commit()
        Catch

 SQLex As

 System.Data.SqlClient.SqlException
         If

 SQLex.Message.Contains("The transaction is rolled back."

) Then


          Debug.Print("Ignored SQL Exception: "

 & SQLex.Message)
         Else


          If

 Not

 (OurTransaction.Connection Is

 Nothing

) Then


           OurTransaction.Rollback() 'No half objects left if possible       .


          End

 If


          Debug.Print("SQL Exception: "

 & SQLex.Message & vbCrLf & SQLex.ToString)
          Throw

 New

 ApplicationException("RunSQLServerScript error: "

 & SQLex.Message)
         End

 If

         
        Catch

 ex As

 Exception
         If

 Not

 (OurTransaction.Connection Is

 Nothing

) Then


          OurTransaction.Rollback() 'No half objects left if possible       .


         End

 If


         Debug.Print("Exception: "

 & ex.Message & vbCrLf & ex.ToString)
         Throw

 New

 ApplicationException("RunSQLServerScript error: "

 & ex.Message)
        Finally


         sb.Clear()
        End

 Try


       End

 If


      End

 If


     Else


      If

 sb.Length > 0 Then


       sb.Append(ControlChars.CrLf & line) 'explicitly no environment.newline as we're dealing with windows sql server


      Else


       sb.Append(line)
      End

 If


     End

 If


    End

 If


   Loop

 Until

 line Is

 Nothing


   ' last lines if no go statement follows them


   If

 sb.Length > 0 Then


    Debug.Print("RunSQLServerScript: going to run SQL:"

 & Environment.NewLine & _
     sb.ToString)
    Dim

 OurTransaction = DataConnection.BeginTransaction 'weird but works.


    SQLScriptCommand = DataConnection.CreateCommand
    SQLScriptCommand.Transaction = OurTransaction
    SQLScriptCommand.CommandType = Data.CommandType.Text
    SQLScriptCommand.CommandText = sb.ToString
    Try


     SQLScriptCommand.ExecuteNonQuery()
     OurTransaction.Commit()
    Catch

 SQLex As

 System.Data.SqlClient.SqlException
     If

 SQLex.Message.Contains("The transaction is rolled back."

) Then


      Debug.Print("Ignored SQL Exception: "

 & SQLex.Message)
     Else


      If

 Not

 (OurTransaction.Connection Is

 Nothing

) Then


       OurTransaction.Rollback() 'No half objects left if possible       .


      End

 If


      Debug.Print("SQL Exception: "

 & SQLex.Message & vbCrLf & SQLex.ToString)
      Throw

 New

 ApplicationException("RunSQLServerScript error: "

 & SQLex.Message)
     End

 If


    Catch

 ex As

 Exception
     If

 Not

 (OurTransaction.Connection Is

 Nothing

) Then


      OurTransaction.Rollback() 'No half objects left if possible       .


     End

 If


     Debug.Print("Exception: "

 & ex.Message & vbCrLf & ex.ToString)
     Throw

 New

 ApplicationException("RunSQLServerScript error: "

 & ex.Message)
    End

 Try


    sb.Clear()
   End

 If


  Catch

 AppExc As

 ApplicationException
   ' Pass it on to the next victim


   Throw


  Catch

 ex As

 Exception
   Throw

 New

 ApplicationException("RunSQLServerScript error: "

 & ex.Message)
  Finally


   If

 sr IsNot

 Nothing

 Then

 sr.Close()
   Reset()
  End

 Try


 End

 Sub

 

Answer 8

pq o codigo sai todo zuado aqui 

sacanagaem