C#: Executing batch T-SQL Scripts containing GO statements

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!!!

Advertisements

45 Responses to “C#: Executing batch T-SQL Scripts containing GO statements”

  1. Liu Chang Says:

    I am using MSADO 1.5 which does not understand “GO”. The first solution saved my situation.

  2. Hugo Says:

    I had problems with the second solution. Sometimes, it returns no error but it doesn’t work.

  3. Syed Mehroz Alam Says:

    @Hogu

    Strange observation, I wasn’t able to verify this behavior.
    I ran a script with some wrong syntax, it threw a “Microsoft.SqlServer.Management.Common.ExecutionFailureException” with Message=”An exception occurred while executing a Transact-SQL statement or batch.”, and the InnerException contained a “System.Data.SqlClient.SqlException” with Message=”Could not find stored procedure ‘abc’.”

  4. Hugo Says:

    Lol, now I realize why I have no error. In mi .sql files (the string script) I had harcoded the database, but when I was connecting I was using another database! Lol x)

    It was strange that I had no errors (because the hardcoded database didn’t exists)

    The second solution now workss for me 🙂

  5. Executing batches of TSQL statements containing GO Says:

    […] The information here is grabbed from this article: Original post by Syed Mehroz Alam […]

  6. Graham Downs Says:

    Where do I find Microsoft.SqlServer.Management.Sdk.Sfc? On Visual Studio 2005, with SQL Server Management Studio 2005 installed.

  7. Syed Mehroz Alam Says:

    Graham,

    The Sdk.Sfc reference is needed in ‘some’ of the cases, can you try running your code without it?

  8. Graham Downs Says:

    Hi, Syed

    Yes, I got it working with the following modifications:

    using Microsoft.SqlServer;
    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;

    Here’s my code:

    using (SqlConnection sqlConnection = new SqlConnection(databaseConnectionString))
    {
    ServerConnection svrConnection = new ServerConnection(sqlConnection);
    Server server = new Server(svrConnection);
    string script = File.ReadAllText(Application.StartupPath + “\\filename.sql”);
    server.ConnectionContext.ExecuteNonQuery(script);
    }

  9. Waqar Ahmed Says:

    Great help thanku manku

  10. Kuljeet singh Says:

    Hi, Both of the solutions are fine.
    I implemented the 1st solution for executing the script but still there has some problem.
    Problem: Suppose your .sql file contains some space after “GO” or keyword “GO” is not in capitalize. Then the expression will not remove the GO keyword from the statements.
    Solution: I implement some solution which you can try for that.
    1st replace the text “Go” to Capitalize.
    2nd change the regex to “GO\s+\r\n”.

    Note: \s+ is for space after GO

  11. Xavier Averbouch Says:

    Working great!
    thank you.

  12. Abhay Says:

    How should i set Timeout to “0” so that it will keep executing while it finishes its task ???

  13. Julio Daetz Says:

    I have some problems with library.
    Add this references:
    Microsoft.SqlServer.Sml and Microsoft.SqlServer.ConnectionInfo.
    and working sucessfull.

  14. Brian Hart Says:

    How about the output messages, and errors etc that are displayed in the messages screen during query output? Also, what if the query outputs one (or more) results sets (i.e., from SELECT statements)

  15. Brian Hart Says:

    How about the output messages, and errors etc that are displayed in the messages screen during query output? Also, what if the query outputs one (or more) results sets (i.e., from SELECT statements)

  16. Sha Says:

    Thanks for the good post and it save me lot of time. 2nd solution worked out for me…

  17. awess Says:

    Se produjo una excepción en el inicializador de tipo de ‘System.Data.SqlClient.SqlConnection’. me sale ese error x.x

  18. Dragan Says:

    Thanks for the solution. One thing I’m having problems with, is deploying app to machine not having MSSQL SMO installed on it. I want to execute scripts having go statements but also would like to avoid installing MSSQL SMO package on target machine. I tried manually copying .dlls to bin folder of executable but there are always some dlls needed to run the app. Any help with this?

  19. T-SQL ‘GO’ and SqlCommand | devioblog Says:

    […] more practical way (mentioned here) to execute an arbitrary SQL string is to use SMO’s […]

  20. Numbers Says:

    What’s up, this weekend is nice in support of me, as this point in time i am reading this enormous educational post here at my residence.

  21. dragonvale eggs Says:

    I truly appreciate this post. I’ve been looking everywhere for this! Thank goodness I found it on Bing. You’ve made my day!
    Thanks again!

  22. that guy Says:

    that guy The Qualified Search Engine Optimisation Firm
    Generic keywords and phrases are particularly ineffective in lookup engines.
    Buyers enter unique keyword phrases or search term phrases in
    the significant look for engines when wanting for particular solutions or companies.
    If you want to convey the right message to your goal viewers, you have to use the suitable Search engine optimisation key phrases.
    For occasion, if you provide men’s outfits, you have to use keyword phrases this kind of as “men’s apparel”, “men’s trend”, “men’s clothes” and related. If you use generic search phrase these kinds of as sweaters, denims, sneakers, and underwear, your web page is remarkably unlikely to be uncovered in consumer searches. As an alternative, if you use “men’s sweaters”, “men’s jeans”, “men’s footwear” and many others., your website has more likelihood to acquire higher visibility in the SERPS.
    Participation, conversation, virality, Word of Mouth, engagement, and on and on and on. This is self explanatory. You have go through my blogs on Cell Promoting and on Social Media, I am confident. If you have not, you must.
    When it will come to developing a internet site, folks shell out several hours carrying out search phrase analysis, Visit Website, and optimization to make confident their internet site ranks perfectly. Marketing on eBay must be no exception. Very good Lookup Engine Optimization (Seo) is an incredibly underneath utilised tool in the auction group.
    This is the procedure of getting your search term checklist (Probably as well significant to start with) and repeating it all more than the web page. It helps make no sense to the user, just a bunch of words all above the area. I haven’t observed this considerably, and I hope I do not.

    Will not enable you in anyway.
    Get confirmed payday loans and individual loans when if you have
    poor and weak credit in United states of america. Landers
    are supplying various loans in various circumstances
    of United states of america men and women. Landers are proving a certain payday loans and supporting
    financially to all eligible people.
    You in all probability know that there are application
    courses that permit you automate your social media profile.
    For illustration, you can get thirty,000 followers on Fb
    or Twitter using mass close friend requests.
    ‘ Elevated page ranking (PR): Look for engines such as Google, Bing, and Yahoo depend in the web site ranking of your site and blog. With enhanced PR, your web site and site will be stated among the the much more usually visited targeted traffic websites. It increases the internet site visitors to your internet sites and blogs, which will guide to a lot more likelihood to receive prospects.
    Never check out to jam pack your auctions title with term spam or nonsense, just to attempt and seize a purchasers consideration. Potential buyers are not amazed by this lack of marketing and advertising. They is not going to assume 2 times about not wanting at your auction. Check This Out

  23. enlargement surgery cost Says:

    Excellent way of describing, and good piece of writing to
    get data concerning my presentation subject matter, which i am going to present in college.

  24. Microsoft points for free Says:

    Hi, I do believe this is an excellent web site. I stumbledupon it 😉 I may come back yet again since i have book-marked it.
    Money and freedom is the greatest way to change,
    may you be rich and continue to help other people.

  25. Youtube.com Says:

    Good info. Lucky me I found your website by accident
    (stumbleupon). I’ve book marked it for later!

  26. Faisal Says:

    After applying the second solution (using SMO) as proposed in this forum, I am getting constantly the following error:

    “Mixed mode assembly is built against version ‘v2.0.50727’ of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information”

    Actually I have created a Windows Form project on which the user gives Server Name and Database details and then I execute a .sql script file in the backend. The Windows Form project is called from a Web Setup installer. On executing the installer, the windows form dialog displays fine but when it comes to ExecuteNonQuery() line, it throws the above mentioned error. I have used following approach in app.config file,
    configuration.

    as mentioned in:
    http://stackoverflow.com/questions/3491279/v2-0-50727-of-the-runtime-and-cannot-be-loaded-in-the-4-0-runtime-without-addi

    but still no help, same problem coming again and again. I am desperate now and thinking of some other alternative.
    If anyone knows how to get it solved, then please guide me. Thanks

  27. Ward Says:

    As you play the game, you’ll also earn earn experience points that ‘level up’ your summoner, allowing you to buy enhanced abilities that improve the various spells you can cast as well as improve the effectiveness of your Champions in battle. Gamers also bring in gold in each match which they can use to purchase things which boost their talents even further. Moreover, you want to always make certain each of the lanes on the map are becoming “pushed” so that the other group is confronted with a hard choice – to defend a turret or to fight inside a team battle.

  28. 傘 ブランド Says:

    大きいサイズ ウェディングドレス

  29. DC Universe Online sevencash generator Says:

    Awesome issues here. I’m very glad to peer your article.

    Thanks a lot and I’m having a look ahead to contact you. Willl you please drop me a e-mail?

  30. free facebook hacking Says:

    Hi there just wanted to give you a quick heads up and let you know
    a few of the images aren’t loading correctly. I’m not sure why but I think its a linking issue.
    I’ve tried it in two different web browsers and both show the same results.

  31. how to get a girlfriend method Says:

    It’s important that you and your partner can communicate well.
    Regardless of race, genre, or era, this is the type of guy that women attach a high
    romantic value to. Did you know that 99% of men get turned down by women everyday.

  32. Ninja kingdom hack android Says:

    Hi there! I just wanted to ask if you ever have
    any trouble with hackers? My last blog (wordpress) was hacked and I ended up losing
    several weeks of hard work due to no back up. Do you have any
    methods to stop hackers?

  33. Free Fifa 14 Points Generator 100% Work | Uxxen Fitness Says:

    […] this artiсle and you would like to acquire more іnfo with regards to fifa 14 coiոs generator; mouse click the following web site, nicely visit the […]

  34. Abdus Salam Says:

    i had a problem is second solution i am getting this error

    Failed to connect to server Data Source=(local);Initial Catalog=DatabaseName;Integrated Security=True

  35. Best Website Solutions Says:

    i had a problem is second solution i am getting this error

    Failed to connect to server Data Source=(local);Initial Catalog=DatabaseName;Integrated Security=True

  36. used windows xp Says:

    I got this web site from my pal who informed me about this web site and at the moment
    this time I am visiting this website and reading very informative content
    at this time.

  37. Avatar star hack download Says:

    Hello there I am so excited I found your website, I really found you
    by mistake, while I was researching on Askjeeve for
    something else, Nonetheless I am here now and would just like to say thanks for a incredible post and
    a all round entertaining blog (I also love the theme/design), I don’t have time to read it all at
    the minute but I have bookmarked it and also added in your
    RSS feeds, so when I have time I will be back to read much more, Please do keep up the great work.

  38. Farm Heroes Saga Cheat Hack Tool 2014 | AnnanZe Says:

    […] any questiоns гegardіng whеre and wɑyѕ to make use of farm heroes saga hack toߋl – click the next web page -, yoս could call us at our wеb […]

  39. sport badeanzüge adidas Says:

    Aby ustalić, bez względu na to, czy może być cool lub hot kolor skóry, zobacz naczyń krwionośnych na wewnętrznej Twój ramię.
    Jeśli cool skóry, że żyły pokaże się kolorowe.
    w przypadku gdy masz komfortowe pory naskórka i skóry, będą
    one prawdopodobnie mieć zielonkawy odcień zamiast. Super kolor skóry zajrzeć najlepsze niesamowite abażur, na przykład blue i fioletowy, podczas ciepła odcienie
    skóry, wyświetlane najlepsze hot kolory jak czerwonawym kolorze,
    żółty i pomarańczy. Dla Easy czyste smugi-, bezpłatnej wyniki, złuszczają naskórka -Tanner osobistej.
    Stary, sucha skóra może spowodować siebie Tanner wchłonąć nierównomiernie.

    To może doprowadzić do smug, plam i aspekty kolor że stało się więcej ciemny niż powinni być.
    Jesteś w stanie pozbyć ten problem naskórka przed podjęciem decyzji o zastosowanie osobista Tanner.

    Przez eliminując martwe komórki skóry, może pomóc się upewnić,
    że Twój własny garbarza zajmuje się równie i daje
    że po prostu wspaniały, bezpłatne połysk .

  40. colon cleansing clinic Says:

    I read this piece of writing completely on the topic of the difference of latest and previous technologies,
    it’s remarkable article.

  41. charmingdate Says:

    What’s up to every one, the contents present at this site are in fact remarkable for people experience, well, keep up the nice work fellows.

  42. Olivia Says:

    Do you want unlimited articles for your blog ?
    I am sure you spend a lot of time posting content, but you can save it for other tasks, just type
    in google: kelombur’s favorite tool

  43. Krzysztof Morcinek Says:

    Your splitter can be an array like:
    string[] splitter = new string[] { “\r\nGO\r\n”, “\nGO\n” };

    These “\nGO\n” helps a lot when git checkouts file this way or when you run command on linux machine.

  44. car accident lawyer Says:

    I think you have a lot of knowledge in this subject and you reallpy know your stuff.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: