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.