Execute SQL script from C# and logging errors

Question

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!

Answer 1

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.

Answer 2

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

svrConnection.FireInfoMessageEventOnUserErrors = true;