none
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

    Tuesday, September 12, 2006 7:12 PM

Answers

  •  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

     

    Wednesday, September 13, 2006 3:52 PM

All replies

  • 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.

    Tuesday, September 12, 2006 7:24 PM
  • 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
    Tuesday, September 12, 2006 7:56 PM
  •  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

     

    Wednesday, September 13, 2006 3:52 PM
  • 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
    Wednesday, September 13, 2006 11:10 PM
    Moderator
  • 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

    Friday, September 15, 2006 5:27 PM
  • 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.
    Sunday, September 17, 2006 9:11 PM
    Moderator
  • 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
    
    
    
    

     

    Friday, February 18, 2011 3:22 PM
  • pq o codigo sai todo zuado aqui 

    sacanagaem 

    Tuesday, February 14, 2012 1:57 PM