.Net Equivilant for sqlcmd or osql

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • =?Utf-8?B?SGFycnkgS2Vjaw==?=
    #1

    .Net Equivilant for sqlcmd or osql

    I am writing an application that automates running sql scripts against my
    database. Currently, I am using Process.Start(" sqlcmd") with a number of
    parameters to accomplish this, but I would like to have more control than
    running an outside process. I am hoping that there is a .Net class that I
    can call directly to run sql scripts, but so far, I have not had any luck
    finding one. Can anyone give me any advice to accomplish this task?
    Tags: sqlcmd c#
    • Marc Gravell
      #2
      Re: .Net Equivilant for sqlcmd or osql

      What is the database? Any of the DbCommand implementations (SqlCommand
      etc) should be able to pass down your script as text. Depending on what
      you are doing, there are also higher-level management objects, but these
      tend to be vendor-specific.

      Marc

        Comment

        • =?Utf-8?B?SGFycnkgS2Vjaw==?=
          #3
          Re: .Net Equivilant for sqlcmd or osql

          No, executing a DBCommand does not allow sql batches. My files are either
          create scripts for procedures or tables. When I use a DBCommand on a
          statement with more than one command, I get an exception. Osql and sqlcmd
          allow execution of batches containing mulitple statements.

          "Marc Gravell" wrote:
          What is the database? Any of the DbCommand implementations (SqlCommand
          etc) should be able to pass down your script as text. Depending on what
          you are doing, there are also higher-level management objects, but these
          tend to be vendor-specific.
          >
          Marc
          >

            Comment

            • Claire
              #4
              Re: .Net Equivilant for sqlcmd or osql

              For mysql, i had to do it by reading through the script one line at a time
              (upto the ; at end of a sql line) calling executenonquery for each one.
              Not sure about other database engines.

                Comment

                • Alberto Poblacion
                  #5
                  Re: .Net Equivilant for sqlcmd or osql

                  "Harry Keck" <HarryKeck@disc ussions.microso ft.comwrote in message
                  news:0C64CF08-4CBC-4388-A166-0EBABA32C4A6@mi crosoft.com...
                  No, executing a DBCommand does not allow sql batches. My files are either
                  create scripts for procedures or tables. When I use a DBCommand on a
                  statement with more than one command, I get an exception. Osql and sqlcmd
                  allow execution of batches containing mulitple statements.
                  There's a trick: Read your script file and split it on the GO statements.
                  Each piece between two "GO"s can be sent to the server by means of an
                  ExecuteNonQuery . Besides splitting on the GOs, I believe that you also have
                  to strip the comments, which you can do by reading the file line by line and
                  removing everything from "--" to the end of each line.

                    Comment

                    • Nicholas Paldino [.NET/C# MVP]
                      #6
                      Re: .Net Equivilant for sqlcmd or osql

                      I second this. Parsing on the semicolon is not the way that
                      applications like SQL Management Studio work.

                      Also, you do not have to strip the comments, it works just fine,
                      assuming you have carriage returns and the like.

                      Basically, attach a StreamReader to the FileStream that is opened to the
                      script. Read EACH line into a string buffer. If the line contains GO and
                      ONLY GO, then send everything that you have in the buffer (as you add each
                      new line, remember to add the newline back to the buffer). Then, clear the
                      buffer, and continue reading.

                      We've done the exact same thing where I work to take the scripts that
                      you would use in SQL Management Studio and execute them without running it.

                      --
                      - Nicholas Paldino [.NET/C# MVP]
                      - mvp@spam.guard. caspershouse.co m

                      "Alberto Poblacion" <earthling-quitaestoparaco ntestar@poblaci on.orgwrote
                      in message news:%23rixAKN7 IHA.2336@TK2MSF TNGP03.phx.gbl. ..
                      "Harry Keck" <HarryKeck@disc ussions.microso ft.comwrote in message
                      news:0C64CF08-4CBC-4388-A166-0EBABA32C4A6@mi crosoft.com...
                      >No, executing a DBCommand does not allow sql batches. My files are
                      >either
                      >create scripts for procedures or tables. When I use a DBCommand on a
                      >statement with more than one command, I get an exception. Osql and
                      >sqlcmd
                      >allow execution of batches containing mulitple statements.
                      >
                      There's a trick: Read your script file and split it on the GO
                      statements. Each piece between two "GO"s can be sent to the server by
                      means of an ExecuteNonQuery . Besides splitting on the GOs, I believe that
                      you also have to strip the comments, which you can do by reading the file
                      line by line and removing everything from "--" to the end of each line.
                      >

                        Comment

                        Previous template Next
                        Working...