Why don't go statements work within a SQL script I run from my .net program?
This is simply because "go" isn't SQL. It's only able to be interpreted by management studio, so when you run it from your application, it's going to throw you errors saying it's invalid SQL (and rightfully so).
But there are times when you just need it right? I mean, say you've written an enormous SQL script to upgrade a database. You've spent days on the thing, and it runs perfectly within Management studio with full error checking etc.
So you embed the script within your application, read it into an SqlCommand(), and suddenly you get heaps of errors. Frustrating huh? Even if you take out all of your "go" statements, the script is then broken, because you create a table at the top of the script, and then try to insert values into it at the bottom where it still hasn't been created!
But there is a simple way around this.
The first thing you need to do is do a replace-all in your Sql Script. Open your script up and replace "go" wtih "go--runtoline". Make sure you have 'match whole word' on, otherwise you'll mess up a heap of stuff. When you run the script it should run exactly the same as before you did the replace.
Next thing is in your code, you want to split up the script into a set of sub routines as such:
private void Execute(string sql)
{
string[] queries = Regex.Split(sql, "go--runtoline");
using (SqlConnection conn = new SqlConnection("connection string"))
{
conn.Open();
SqlCommand com = new SqlCommand("begin transaction", conn);
com.ExecuteNonQuery();
foreach (string queryChunk in queries)
{
com.CommandText = queryChunk;
try { com.ExecuteNonQuery(); }
catch (Exception e)
{
com.CommandText = "rollback transaction";
com.ExecuteNonQuery();
throw e;
}
finally { conn.Close(); }
}
com.CommandText = "commit transaction";
com.ExecuteNonQuery();
conn.Close();
}
}
And that should give you the same behaviour as running your "go" based script in sql server.
posted @ Thursday, November 01, 2007 7:37 AM