Executing a SQL script using ADO.NET
Recently, while creating an installer application, I had the need to execute a sql script while my application was being installed. A while back, I posted a technique that would accomplish this.
The drawback to that technique was that it required the osql.exe on the machine I was installing. In most cases this would work, but I wanted to find a way to run a sql script, without any outside dependencies (other than the .NET framework that my installer would lay down).
This is what I came up with:
public void ExecuteSql(SqlConnection connection, string sqlFile) { string sql = ""; using (FileStream strm = File.OpenRead(sqlFile)) { StreamReader reader = new StreamReader(strm); sql = reader.ReadToEnd(); } Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline); string[] lines = regex.Split(sql); SqlTransaction transaction = connection.BeginTransaction(); using (SqlCommand cmd = connection.CreateCommand()) { cmd.Connection = connection; cmd.Transaction = transaction; foreach (string line in lines) { if (line.Length > 0) { cmd.CommandText = line; cmd.CommandType = CommandType.Text; try { cmd.ExecuteNonQuery(); } catch (SqlException) { transaction.Rollback(); throw; } } } } transaction.Commit(); }
The key here is the regex that looks for a line that starts with ‘GO’. This needs to be done because ADO.NET will throw a SqlException if the script contains ‘GO’. I’m now taking advantage of this technique in my installers, eliminating the osql dependency.
Also, you’ll notice that I make use of ADO.NET’s fantastic transaction capabilities. The SqlTransaction class represents a T-SQL transaction to be made in a SQL Server database.
By using it in the way that I have, if any portion of the sql script fails, the whole thing aborts (see the Rollback call in the exception handler). Since we would be using this to install or update a database, we would want to use this so that we do not leave the database in an unusable state. When all commands in the file successfully have executed, we call Commit on the transaction which sends the transaction through.
Great piece of code. One minor comment. I had an ALTER Table Statement as part of an SQL to crete a SP and it Failed. However, it worked just fine when I put the entire command on one line. Thanks for posting it.
Just what I was looking for. Thanks!
I had to make a minor change in the SQL script. The regex interpreted a GOTO at the beginning of a line as a GO. I put some whitespace in front of the GOTO and that solved it.
DUDE! You are johnny on the spot! You saved my life. :) Thanks!
[...] Until recently, there have been two ways to handle this problem - execute SQL scripts by shelling to OSQL, or splitting the script on GO separators and running them in sequence. Both solutions kind of worked, but SQL Server Management Objects (SMO) has a better solution for us: Server.ConnectionContext.ExecuteNonQuery(), which parses T-SQL statements and “gets” the GO statement as a batch separator. And the crowd goes wild!!! [...]
Excellent !! Just what I was looking for.
Can anyone tell me how to get a declare statement to work in ADO.NET?
Handling “GO” Separators in SQL Scripts - the easy way
Doesn’t that ONLY apply to SQL server, thus causing database dependency? I’m new to C# alltogether, but this regex example looks less dependant to my untrained eyes.
Here’s an updated regular expression that will handle white space around the GO keyword:
^\s*GO\s*$
works great, one thing, If you run a script that create and or drop tables you have to remove the Transaction section.
HOw do you get back the response/messages the SQL generates while executing. FOr example : SQL Server generates the following message for dependancy SP
Cannot add rows to sysdepends for the current object because it depends on the missing object ‘uspTestDep’. The object will still be created.
I want to display on .NET screen.
Do you have any ideas?
I would suspect that you just wrap the calls in a try/catch. Something like that would probably come back as an exception.
I am getting errors if my stored procedure has any comment marks in it “–” has anyone run into this is there a way around this so i can run a sql script just like i would in query analyzer to create stored procedure
Just what I was looking for. Thanks!
Any one tell, how to generate scripts with data …
Thanks in adv..
@li
U ROK!! Saved my life and LOTS of hours of searching…i changed it to so that it opens the connection tho, cause its not there…just in case u want to add that..
@dave: I didnt have it open the connection because the connection was passed in. The assumption was the that method that calls this had already opened the connection.
The script above is already tied to a database, even without using SMO. Structures like SqlCommand and SqlTransaction are specific ado.net implementations for MS Sql Server. You could make an argument that using SMO would not allow this script to execute for a different flavor of MS Sql Server. If you want a true data agnostic implementation either us ADO.Net’s provider model (DbCommand, DbTransaction) or us the more generic Provider Model pattern.
Like all quick regex solutions intended to match productions of fairly complex languages, this can fail miserably if a different kind of GO is placed alone in one line. It could be part of a string (if T-SQL allows multiline strings, or it could be a column in a table).
I think having to make your commands single-line (which would incidentally fix the issue I mentioned) would be a terrible chore.
The bottom-line here is that you don’t want to write something that can come and bite you in the ass at an unknown time in the future. This tool is only guaranteed to work in synergy with a policy: “make sure all the GOs matchable by this regex in all the scripts are really those GOs…”. And you know, that’s a policy you don’t want.
Anyway, I’m sure the life of many is easier now, but at some point, somewhere, someone’s script is gonna blow and someone will have to go back to massage it. Hopefully not the client :)
Just what a am looking for except in VB
Can you post a VB version of this?