Execute SQL Script

Asked by mpdalton in Microsoft Visual Basic.Net

Tags: sql, script, execute

I have a client where i have installed MSDE.

I want to send the a vb.net application which will run some updates on the database. I have built the sql script files which i would normally run thru query analyser but that is not available thru MSDE.

I know i can use OSQL or ISQL to run these scripts but i really want to run them thru a VB.Net application.

here is the code i'm using which is generating an error beacuse i'm trying to execute more than one statement, anyone have any ideas ??

    Dim connectionString As String = ' CONNECTION STRING IS HERE
    Dim conn As New SqlClient.SqlConnection(connectionString)
    Dim cmd As New SqlClient.SqlCommand
    Dim objReader As System.IO.StreamReader

    Try
      conn.Open()
      cmd.CommandType = CommandType.Text
      cmd.Connection = conn

      objReader = New System.IO.StreamReader("C:\dbs\table.sql")
      cmd.CommandText = objReader.ReadToEnd
      cmd.ExecuteNonQuery()

      objReader = New System.IO.StreamReader("C:\dbs\view.sql")
      cmd.CommandText = objReader.ReadToEnd
      cmd.ExecuteNonQuery()

      objReader = New System.IO.StreamReader("C:\dbs\sp.sql")
      cmd.CommandText = objReader.ReadToEnd
      cmd.ExecuteNonQuery()

      objReader = New System.IO.StreamReader("C:\dbs\udf.sql")
      cmd.CommandText = objReader.ReadToEnd
      cmd.ExecuteNonQuery()

      objReader.Close()
      conn.Close()
      conn = Nothing
      MsgBox("Update Completed")
    Catch ex As Exception
      MsgBox(ex.Message)
    End Try