Tech Off Thread

5 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Run SqlScript from ado

Back to Forum: Tech Off
  • User profile image
    JoshB

    We're modifying our continuous integration process to recreate databases before running unit tests. To my surprise, I can't actually open our scripts and feed them into a command and execute. We've actually had to start an osql process and feed them in that way.

    Does anyone know of a way to run sql scripts from a text file through ado.net?

  • User profile image
    austink

    not an answer to your specific question, I know, but I personally use NANT for environment rebuild/deploys.  For integration testing (staging) the deploy is performed exactly as it would be when deploying the updates to production.  A job gets kicked off to refresh the test db with the latest copy of production.  This way I am not only testing the new code when moving itinto the test environments, but also the deploy itself.

    ie.  clean local area, get latest from source control, tag/label source control,  build solution, backup previous version, deploy new version to web, deploy latest scripts to db (pre-deploy scripts, functions, views, sprocs, post-deploy scripts).  

    For major deploys, a roll-back script is created and tested before production deploy takes place.


    specifically how are the sql scripts run against the db?... here is an example from my script...

    <target name="deployDB" depends="" description="update db with db objects retrieved during latest build">
      <echo message="Loading FUNCTIONS..." />
      <fileset id="Functions" basedir="${build.workpath}/Database/Functions/">
       <include name="*.sql" />
       <exclude name="retire_*.sql" />
      </fileset>
      <foreach item="File" property="Filename" >
       <in>
        <items refid="Functions" />
       </in>
       <do>
        <echo message="*** Loading Function ... ${Filename}" />
        <sql
         connstring="${database.connectionString}"
         batch="false"
         delimiter="GO"
         delimstyle="Line"
         print="true"
         source="${Filename}"
        />
       </do>
      </foreach>

    ...

    ...

    </target>



  • User profile image
    defstream

    Is there a reason just reading the SQL files into a string and then running ExecuteNonQuery on the SqlCommand object isn't an option?

  • User profile image
    JoshB

    It blows up on the GO command.

  • User profile image
    wacko

    well you could still do that and just remove the go statements... with something like

    string sql = file.readtoend()

    Regex regex = new Regex("^GO",Regex.IgnoreCase | Regex.Multiline)
    string[] lines = regex.split(sql)


    and then foreach the lines and then execute each line. if it has a value

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.