How do I run large SQL scripts that contain many keywords, including "GO" using C#?

Question

I'm creating a web application that serves as a front end to do SQL Replication.

I have many scripts stored in the properties of the program. Let's use the first one as an example. The first script is the script that you get from creating a publication on the publisher server.

USE [<<SOURCE_DATABASE_NAME>>]
EXEC sp_replicationdboption @dbname = N'<<SOURCE_DATABASE_NAME>>',
    @optname = N'publish', @value = N'true'
GO
USE [<<SOURCE_DATABASE_NAME>>]
EXEC [<<SOURCE_DATABASE_NAME>>].sys.sp_addlogreader_agent @job_login = N'XXX\Admin',
    @job_password = NULL, @publisher_security_mode = 0,
    @publisher_login = N'Admin', @publisher_password = N'<<PASSWORD>>',
    @job_name = NULL
GO

USE [<<SOURCE_DATABASE_NAME>>]
EXEC sp_addpublication @publication = N'<<SOURCE_DATABASE_NAME>>',
    @description = N'Transactional publication of database ''<<SOURCE_DATABASE_NAME>>'' from Publisher ''<<SOURCE_SERVER_NAME>>''.',
    @sync_method = N'concurrent', @retention = 0, @allow_push = N'true',
    @allow_pull = N'true', @allow_anonymous = N'false',
    @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true',
    @compress_snapshot = N'false', @ftp_port = 21,
    @allow_subscription_copy = N'false', @add_to_active_directory = N'false',
    @repl_freq = N'continuous', @status = N'active',
    @independent_agent = N'true', @immediate_sync = N'false',
    @allow_sync_tran = N'false', @allow_queued_tran = N'false',
    @allow_dts = N'false', @replicate_ddl = 1,
    @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false',
    @enabled_for_het_sub = N'false'
GO

EXEC sp_addpublication_snapshot @publication = N'<<SOURCE_DATABASE_NAME>>',
    @frequency_type = 1, @frequency_interval = 1,
    @frequency_relative_interval = 1, @frequency_recurrence_factor = 0,
    @frequency_subday = 8, @frequency_subday_interval = 1,
    @active_start_time_of_day = 0, @active_end_time_of_day = 235959,
    @active_start_date = 0, @active_end_date = 0,
    @job_login = N'ICS\Admin', @job_password = NULL,
    @publisher_security_mode = 0, @publisher_login = N'Admin',
    @publisher_password = N'<<PASSWORD>>'

Instead of running this script in SQL Management Studio, I want to use my web application to run it.

I tried:

public static void CreatePublication(string server, string query)
{
    string finalConnString = Properties.Settings.Default.rawConnectionString.Replace("<<DATA_SOURCE>>", server).Replace("<<INITIAL_CATALOG>>", "tempdb");

     using (SqlConnection conn = new SqlConnection(finalConnString))
     {
         using (SqlCommand cmd = new SqlCommand(query, conn))
         {
             conn.Open();

             cmd.ExecuteNonQuery();
         }
      }
}

public static string ConstructCreatePublicationScript(string rawPublicationScript, string rawAddArticleScript,
            string password, string sourceServerName, string sourceDatabaseName, List<string> selectedTables)
{
    string createPublicationScript = "";
    string addArticleScript = "";

    createPublicationScript = rawPublicationScript.Replace("<<PASSWORD>>", password)
            .Replace("<<SOURCE_SERVER_NAME>>", sourceServerName)
            .Replace("<<SOURCE_DATABASE_NAME>>", sourceDatabaseName);

    createPublicationScript = createPublicationScript + "\n\n";

    foreach (string selectedTable in selectedTables)
    {
        addArticleScript = rawAddArticleScript.Replace("<<SOURCE_DATABASE_NAME>>", sourceDatabaseName)
             .Replace("<<SOURCE_TABLE_NAME>>", selectedTable);

             createPublicationScript = createPublicationScript + addArticleScript + "\n\n";
     }
            //write script to file

            return createPublicationScript;
     }

But ran into this error:

SqlException was caught

Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.

My question is, how can I run this whole script above in C#? Should I just get rid of the "GO" keywords?

Jaunita

have you thought about placing this into a StoredProc..? – Voltaism

the reason why I'm not using stored procedures here is because then I would have to add this SP in all servers and ALL databases. In my scenario it's actually easier to do in-line SQL as opposed to creating stored procedures. Get what i mean? – Jaunita

You could parse the scripts into a list of scripts and run them all individually. – Glorious

hard coding SQL is not a good thing also in regards to the program that's running the script(s) is this for a web application or for a win forms application..? – Voltaism

@DJKRAZE this is a web application. – Jaunita

so you rather distribute the same scripts multiple times hard coded in your SRC, than just deploy this to a more secure environment i.e Stored Procedure..? I think that your approach is Flawed ... – Voltaism

@DJKRAZE, the reason why I'm better off hard coding these scripts in the application is because if I don't and put it in a stored procedure, I have to deploy this stored procedure to ALL servers and ALL databases. That's a lot. – Jaunita

All I am saying is find a way to eliminate potential SQL INJECTION hard coding is really not a good thing.. how many instances of the web application will be deployed will there be only one web server or will the same application be deployed to several different web server locations..? – Voltaism

I understand. Only one instance of the application will be deployed. The application lets a user select a server, database, and then the objects to replicate (just like going into SQL and doing it there). That means that all servers and databases in all of theses servers are options. Therefore I'd have to put the stored procedure everywhere. – Jaunita

Answer 1

Change your ConstructCreatePublicationScript to return a List<string> where each string element is a piece of your complete script splitted at the GO statement. This is necessary because the GO is the separator used by Management Studio and not a SQL statement

public static List<string> ConstructCreatePublicationScript(string rawPublicationScript, string rawAddArticleScript)
{
    .....

    List<string> result = new List<string>();
    result.AddRange(Regex.Split(createPublicationScript, "^GO$", RegexOptions.Multiline));
    return result;
}

then change your execution code to receive the list and execute each single string

public static void CreatePublication(string server, List<string> queries)    
{    
    string finalConnString = Properties.Settings.Default.rawConnectionString.Replace("<<DATA_SOURCE>>", server).Replace("<<INITIAL_CATALOG>>", "tempdb");    

     using (SqlConnection conn = new SqlConnection(finalConnString))    
     {    
         conn.Open();    
         foreach(string query in queries)
         {
             using (SqlCommand cmd = new SqlCommand(query, conn))    
             {    
                 cmd.ExecuteNonQuery();    
             }
         }    
      }    
}

Samanthasamanthia

this looks very appealing. though as it was mentioned above, I should probably use REGEX to do the splitting because a word in the script might contain "go" – Jaunita

cool! by the way, i AM allowed to use the keyword "USE" in an in-line SQL command, correct? for example: using (SqlCommand cmd = new SqlCommand("USE <db_name> SELECT * ...", conn)). Can i do that? – Jaunita

Added the splitting with Regex. If you remove the GO from everywhere in your script it should works, but splitting will give you a better control if you introduce some error checking and transaction management. – Samanthasamanthia

@Testifier I would suggest just allowing a very limited form of splitting, such as requiring that GO is the only word on a line and add a disclaimer about /* */ comments not being supported. If the input is controlled this is a trivial task and avoids a more complicated regular expression trying to avoid all those "odd cases" (which it can't avoid anyway). – Prognostic

@pst you are right. This could quickly become a nightmare. Fortunately, the OP says that these scripts are properties of its program, so I suppose that he has complete control on the script format. – Samanthasamanthia

Just removing "go" from the script would put all the separate batches in the same scope, so that might keep it from working properly. – Calorifacient

Answer 2

The GO command is not an SQL command, it's a command in SQL Management Studio. It separates batches in a script.

To run the script as SQL, split it on "GO" and execute each string by itself.

(You might want to use a regular expression like \bGO\b or \sGO\s to do the split, so that you catch only occurances that is not part of a word, if you would happen to have an identifier that contains "go".)

Calorifacient

Use "^GO$" instead and use RegexOptions.Multiline. Word breaks are not enough. You would accidentally catch 'Let's GO'. – Tabard

if I eliminate the GOs, would this affect the script in any way? if its just a matter of not using the GO keywords, i can get rid of them and then run the entire script. or do I HAVE to run each "batch" by itself? – Jaunita

I would recommend limiting to ^\s*GO\s*$, or equivalent line-check (and even this will break with multi-line comments), it can be quite complicated to let GO be found in an arbitrary location .. as evidenced by the "parsing" (and issue reports) found in projects like RoundhousE. – Prognostic

Answer 3

read the file. when you come across "GO" submit the query to server (without "GO" itself) and then go on reading.

Bounty

so you're just saying to eliminate GO? would this affect this script in any way? – Jaunita