5

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!

naregkar
  • 363
  • 6
  • 16

4 Answers4

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.

Steve
  • 213,761
  • 22
  • 232
  • 286
  • Split with **GO** in the text could break the Script, Imagine that you have to SELECT CATE**GO**RY FROM WHATEVER – Satinder singh Jan 09 '13 at 12:55
  • 1
    Do you have noted that I use a Regex pattern that anchor the GO to the beginning of a line? http://msdn.microsoft.com/en-US/library/az24scfc(v=vs.90) – Steve Jan 09 '13 at 13:16
  • Thank you Steve for your workaround. I am executing each single statement and catching any error if occurs. – naregkar Jan 09 '13 at 14:20
  • 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 https://github.com/rusanu/DbUtilSqlCmd/blob/master/trunk/src/SqlCmd.cs – Remus Rusanu Jan 09 '13 at 14:23
  • @RemusRusanu I'm sure you are right, but of course this could be totally unnecessary if the script is controlled/created by the same person/organization that use it. And, probably this is the case, if I understand what that "upgradeDatabase.sql" file name is. – Steve Jan 09 '13 at 14:54
  • 2
    @Steve: true, but think at everyone that will stumble upon this post later and use the code w/o considering the 'details'. – Remus Rusanu Jan 09 '13 at 15:28
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
justinmvieira
  • 572
  • 4
  • 13
  • 1
    This works in conjunction with Andry's answer. You need sc.FireInfoMessageEventOnUserErrors = true; – Craig A May 14 '20 at 14:58
1

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

svrConnection.FireInfoMessageEventOnUserErrors = true;

Andry
  • 21
  • 1
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);
Arsen Mkrtchyan
  • 49,896
  • 32
  • 148
  • 184