At times, we developers need to run SQL scripts from our .NET applications, in, say Installer Applications. This is not always easy since large SQL scripts typically contain a GO statement to separate individual batches and our ADO.NET classes under System.Data namespace do not know how to handle it. The reason is that the “GO” statement is not a native T-SQL statement but rather used by SQL Server Management Studio to terminate the batches it is sending to the server. To tackle this situation, we have the following choices:
1. Split the script on “GO” command into smaller scripts and execute those individual scripts
A very primitive solution would be to split the script on “GO” text and run the individual sub-scripts in sequence. The problem is how to get a robust split mechanism. Generally, a line break before and after the GO works fine. Here’s how to do this:
//get the script
string scriptText = GetScript();
//split the script on "GO" commands
string[] splitter = new string[] { "\r\nGO\r\n" };
string[] commandTexts = scriptText.Split(splitter,
StringSplitOptions.RemoveEmptyEntries);
foreach (string commandText in commandTexts)
{
//execute commandText
}
The above code can produce unnecessary splitting in some situations, thus creating an incorrect SQL Command. A more better approach would be to use Regular Expressions. Again, the problem is how to create a pattern that is robust enough to tackle all sort of scripts. Lets look into the second solution now.
2. Use the Server class from SQL Server Management Objects (SMO)
For this, we need to add the following references in our project:
- Microsoft.SqlServer.Smo
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Management.Sdk.Sfc
After that, we can simply execute the entire script (with all the “GO” statements) using the code below:
string connectionString, scriptText; SqlConnection sqlConnection = new SqlConnection(connectionString); ServerConnection svrConnection = new ServerConnection(sqlConnection); Server server = new Server(svrConnection); server.ConnectionContext.ExecuteNonQuery(scriptText);
This is a fairly generic and robust solution and the great thing is that it does not require any change in our original script. Hooray!!!
