Retrieving SQL error message from transaction.Commit()

Question

I have a piece of code that iterates through a list of SQL commands that should be processed all as part of one transaction. I want to have a way of knowing if that transaction was successful for error logging and handling purposes. At this moment in time for some reason I have a problem seeing any kind of actual exception or unsuccessful commit. I am using the code below right now. The try catch is from the MSDN page recommendation. Please feel free to poke any other holes in this that you can see that are not 100% about my question. These are all commands of SqlCommands of the stored proc type with parameters added to it prior to adding it to the list of SQL commands.

public static async Task UpdateSQL(string inputQuery, 
    string inputId, List<SqlCommand> commandList, TraceWriter log)
{
    try
    {
        string str = ConfigurationManager.ConnectionStrings
            ["connString"].ConnectionString;

    log.Info($"Running query: {inputQuery}");
    int commandRows = 0;
    using (SqlConnection conn = new SqlConnection(str))
    {
        conn.Open();
        SqlTransaction tr = conn.BeginTransaction();

        try
        {
            SqlCommand cmd = new SqlCommand(inputQuery, conn);
            cmd.Transaction = tr;
            foreach (var command in commandList)
            {
                command.Connection = conn;
                command.Transaction = tr;
                log.Info($"{command.CommandText} query running"); //last log output if unsuccesful (based on end result in database)
                commandRows += await command.ExecuteNonQueryAsync();
            }
            log.Info($"total rows updated: {commandRows}");

            tr.Commit();
            conn.Close();
        }
        catch(Exception ex)
        {
            Console.WriteLine($"Commit Exception Type: {ex.GetType()}");
            Console.WriteLine($"  Message: {ex.Message}");

            try
            {
                tr.Rollback();
                conn.Close();
                log.Info($"{inputId} transaction rolled back");
            }
            catch (Exception ex2)
            {
                // rollback fail Exception
                log.Info($"Rollback Exception Type: {ex2.GetType()}");
                log.Info($"  Message: {ex2.Message}");
                conn.Close();
            }
        }
    }
}

There is no need to do the explicit conn.Close() (and of them) - the using block will do that for you. — mjwills
– mjwills, Commented Jun 8, 2018 at 22:10
At this moment in time for some reason I have a problem seeing any kind of actual exception or unsuccessful commit. This is unclear. Do you mean it always works and you are hoping it doesn't so you can see what happens? Or something else? — mjwills
– mjwills, Commented Jun 8, 2018 at 22:11
@mjwillis great. added just in case. so there is no need to conn.Close() anywhere in this piece of code as long as the using statement is used as it is here? That would be great. Well most of the time it is successful but through debugging it appeared that if a set of commands wasn't successful it would just print the log.Info($"{command.CommandText} query running"); part to the log and it seemed it never actually get to the Console.WriteLine($"Commit Exception Type: {ex.GetType()}"); Console.WriteLine($" Message: {ex.Message}"); part — kyarbles
– kyarbles, Commented Jun 8, 2018 at 22:13
so there is no need to conn.Close() anywhere in this piece of code as long as the using statement is used as it is here? Yes. — mjwills
– mjwills, Commented Jun 8, 2018 at 22:40
If you receive a list of SqlCommand what is the purpose of the cmd = new SqlCommand(inputQuery) line? — Steve
– Steve, Commented Jun 8, 2018 at 22:50

InfoLearner · Accepted Answer · 2018-06-08 23:28:28Z

Try and catch SqlException

Then do this:

StringBuilder sqlErrorMessages = new StringBuilder("Sql Exception:\n");

    foreach (SqlError error in ex.Errors)
    {
        sqlErrorMessages.AppendFormat("Mesage: {0}\n", error.Message)
            .AppendFormat("Severity level: {0}\n", error.Class)
            .AppendFormat("State: {0}\n", error.State)
            .AppendFormat("Number: {0}\n", error.Number)
            .AppendFormat("Procedure: {0}\n", error.Procedure)
            .AppendFormat("Source: {0}\n", error.Source)
            .AppendFormat("LineNumber: {0}\n", error.LineNumber)
            .AppendFormat("Server: {0}\n", error.Server)
            .AppendLine(new string('-',error.Message.Length+7));

    }

You'll only get an exception in C# if your error's severity is 16 or above. If you are using a PRINT, you won't get an exception in .NET.

If you can edit the raise error code, this would cause a SqlException in C#:

RAISERROR('Some error message', 16, 1) You can then get to each individual error in the SqlException.Errors collection.

Just a side-note - SQL Server will continue to run commands after the RAISERROR if you don't RETURN directly afterwards. If you don't return, you can get multiple errors back.

Collectives™ on Stack Overflow

Retrieving SQL error message from transaction.Commit()

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related