6

I am trying to execute a script (.sql file) on a database from a C# Windows application. The SQL file contains 'GO' statements; this means I am using the object SMO.

I am trying to continue on error and also log any error which might occur during the execution of the script on the database. Is there any way to do this?

This is the code I'm using:

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
    ServerConnection svrConnection = new ServerConnection(sqlConnection);
    Server server = new Server(svrConnection);

    string script = File.ReadAllText("upgradeDatabase.sql");

    try
    {
        server.ConnectionContext.ExecuteNonQuery(script, ExecutionTypes.ContinueOnError);
     }
     catch (Exception ex)
     {
         //handling and logging for the errors are done here
     }
}

Any help is appreciated!

Improve this question

4 Answers 4

Reset to default
4

I think you have two problems here:

First, you call the ExecuteNonQuery method that accepts a string and not a StringCollection. I suppose that this method doesn't recognize the GO used to separate batch statements. Instead, the documentation of the method ExecuteNonQuery that accepts a StringCollection states that GOes are recognized

The second problem is the ExecutionTypes.ContinueOnError passed. In this case the same documentation states that you can't receive exceptions if something fails.

I think that the best approach should be to split your input text at the GO and then build a StringCollection to pass to the ExecuteNonQuery method and check the returned array of affected rows. Something like this (should be tested)

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
    ServerConnection svrConnection = new ServerConnection(sqlConnection);
    Server server = new Server(svrConnection);

    string script = File.ReadAllText("upgradeDatabase.sql");
    string[] singleCommand = Regex.Split(script, "^GO", RegexOptions.Multiline);
    StringCollection scl = new StringCollection();
    foreach(string t in singleCommand)
    {
        if(t.Trim().Length > 0) scl.Add(t.Trim());
    }
    try
    {
        int[] result = server.ConnectionContext.ExecuteNonQuery(scl, ExecutionTypes.ContinueOnError);
        // Now check the result array to find any possible errors??
     }
     catch (Exception ex)
     {
         //handling and logging for the errors are done here
     }
}

Of course an alternative is executing each single statement and remove the ContinueOnError flag. Then capture and record the possible exceptions. But this will be certainly slower.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Split with GO in the text could break the Script, Imagine that you have to SELECT CATEGORY FROM WHATEVER
Do you have noted that I use a Regex pattern that anchor the GO to the beginning of a line? msdn.microsoft.com/en-US/library/az24scfc(v=vs.90)
Thank you Steve for your workaround. I am executing each single statement and catching any error if occurs.
You need a pattern that matches GO as the only 'statement' on the line, case insensitive, and honors the optional GO nnn syntax. GO is also actually configurable, there are scripts out there that use ; as a batch delimiter (don't ask...). For example, see @"^\b*" + BatchDelimiter + @"\b*(\d*)" from github.com/rusanu/DbUtilSqlCmd/blob/master/trunk/src/SqlCmd.cs
@Steve: true, but think at everyone that will stumble upon this post later and use the code w/o considering the 'details'.
|
1

I'm not programmer (DBA), therefore I'm not sure. I assume following can be helpful

svrConnection.FireInfoMessageEventOnUserErrors = true;

Improve this answer

Comments

1

I really like this variant, which uses the InfoMessage event and handler:

        using(SqlConnection sc = new SqlConnection(connStr))
        {
            sc.InfoMessage += new SqlInfoMessageEventHandler(sc_InfoMessage);
            sc.FireInfoMessageEventOnUserErrors = true;
            Server db = new Server(new ServerConnection(sc));
            db.ConnectionContext.ExecuteNonQuery(commandFileText, ExecutionTypes.ContinueOnError); 
        }

You will need to add a reference to the following:

  • Microsoft.SqlServer.ConnectionInfo.dll
  • Microsoft.SqlServer.Management.Sdk.Sfc.dll
  • Microsoft.SqlServer.Smo.dll
  • Microsoft.SqlServer.SqlEnum.dll
Improve this answer

1 Comment

This works in conjunction with Andry's answer. You need sc.FireInfoMessageEventOnUserErrors = true;
0

you can use smo library, where is how to add to the project

FileInfo file = new FileInfo("upgradeDatabase.sql");
string script = file.OpenText().ReadToEnd();
SqlConnection conn = new SqlConnection(sqlConnectionString);
Server server = new Server(new ServerConnection(conn));
server.ConnectionContext.ExecuteNonQuery(script);
Improve this answer

4 Comments

With your answer, the script will stop execution at the first error. I expect the OP was after all the errors. (not downvoter)
hi @ArsenMkrt i am try to use your answer but i am getting error on Server server=new.. line can you please tell me which is assembly and namespace for Server.. Thanks
what is the message of exception?
May be you are getting an error beacuse you donot have smo dll package reffrerd in your project @Mohit

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.