A Library For Executing SQL Scripts With GO Separators and Template Parameters

code 0 comments suggest edit

One thing I’ve found with various open source projects is that many of them contain very useful code nuggets that could be generally useful to developers writing different kinds of apps. Unfortunately, in many cases, these nuggets are hidden. If you’ve ever found yourself thinking, Man, I wonder how that one open source app does XYZ because I could use that in this app, then you know what I mean.

One goal I have with Subtext is to try and expose code that I think would be useful to others. It’s part of the reason I started the Subkismet project.

Another useful library you might find useful in Subtext is our SQL Script execution library encapsulated in the Subtext.Scripting.dll assembly.

A loooong time ago, Jon Galloway wrote a post entitled Handling GO Separators in SQL Scripts - the easy way that tackled the subject of executing SQL Scripts that contain GO separators using SQL Server Management Objects (SMO). SMO handles GO separators, but it doesn’t (AFAIK) handle SQL template parameters.

So rather than go the easy way, we went the hard way and wrote our own library for parsing and executing SQL scripts that contain GO separators (much harder than it sounds) and template parameters. Here’s a code sample that demonstrates the usage.

string script = @"SELECT * FROM <table1, nvarchar(256), Products>
GO
SELECT * FROM <table2, nvarchar(256), Users>";

SqlScriptRunner runner = new SqlScriptRunner(script);
runner.TemplateParameters["table1"] = "Post";
runner.TemplateParameters["table2"] = "Comment";

using(SqlConnection conn = new SqlConnection(connectionString))
{
  conn.Open();
  using(SqlTransaction transaction = conn.BeginTransaction())
  {
    runner.Execute(transaction);
    transaction.Commit();
  }
}            

The above code uses the SqlScriptRunner class to parse the script into its constituent scripts (you can access them via a ScriptCollection property) and then sets the value of two template parameters before executing all of the constituent scripts within a transaction.

Currently, the class only has one Execute method which takes in a SqlTransaction instance. This is slightly cumbersome and it would be nice to have a version that didn’t need all this setup, but this was all we needed for Subtext.

When I started writing this post, I thought about making some overrides that would make this class even easier to use, but instead, I will provide a copy of the assembly and point people to our Subversion repository and hope that someone out there will find this useful and have enough incentive to submit improvements!

Also, be sure to check out our unit tests for this class to understand what I mean when I said it was harder than it look. As a hint, think about dealing with GO statements in comments and quotes. Also, GO doesn’t have to be the only thing on the line. Certain specific elements can come before or after a GO statement on a line.

In case you missed the link, DOWNLOAD IT HERE.

Tags: Subtext , SQL , Sql Script Runner

Found a typo or error? Suggest an edit! If accepted, your contribution is listed automatically here.

Comments

avatar

10 responses

  1. Avatar for Scott Stocker
    Scott Stocker November 4th, 2007

    Do you know if there is an equivalent to "GO" in MySql, Oracle, and other DBMS? It would be really cool to include that in the library and might make it easier to convert your SQL scripts for Subtext to work with MySql for example.

  2. Avatar for Rob Conery
    Rob Conery November 4th, 2007

    Too funny - I just committed the code we use in the CSK to the SubSonic Query bits. There's a new static method that pulls in a .SQL file, and executes it by parsing out the GO statements (in a transaction no less).
    Didn't I send you the memo? :).

  3. Avatar for Haacked
    Haacked November 5th, 2007

    @Rob, yeah, but does your code handle template parameters? Also, it'll be interesting to run your code through our unit test suites. There are a lot of edge cases that used to break our implementation.

  4. Avatar for secretGeek
    secretGeek November 5th, 2007

    "dealing with GO statements in comments and quotes"
    so i remember sql 7.0 there were bugs around how GO statements were handled in comments....
    i hope you've re-implemented the bugs so that people get proper backward compatability when connecting to down-level databases?
    lb

  5. Avatar for Parker
    Parker November 6th, 2007

    I'm happy I stumbled across your blog. I've been working on a SQL deployment tool that wrestles with this very issue. I am definitely going to take a look and see if it fits the bill. I have a question though. Under what circumstances will you run into a problem because action B, requires action A to be commit to the database? The chats I've had with a DBA have steered me away from trying to build a big transaction because of that issue. Thoughts?

  6. Avatar for Christopher Steen
    Christopher Steen November 6th, 2007

    Link Listing - November 6, 2007

  7. Avatar for Christopher Steen
    Christopher Steen November 6th, 2007

    Sharepoint Live Web Cast: Advanced SharePoint Document Workflow with Visual Studio 2008 [Via: Public...

  8. Avatar for Lorenzo Melato
    Lorenzo Melato November 24th, 2010

    Great post Phil! Thank you very much for your effort!

  9. Avatar for Jeremy Simmons
    Jeremy Simmons April 28th, 2013

    You can use assemblies that are part of SMO to do it too. No Haaacks required :) Read my question and answer on Stack Overflow: http://stackoverflow.com/qu...

  10. Avatar for Paula Andrea
    Paula Andrea February 25th, 2014

    This not working when creating a DB.