Getting Log info messages from stored procedure and handle the exception at the same time if any

Question

Basically I am iterating over some items in dictionary and I am trying to delete some records from database using TransactionScope in C#. I call my stored procedure which is wrapped inside a TransactionScope. Now when my stored procedure is executing, I am firing multiple calls to other stored procedures inside my main stored procedure.

When I am firing stored procedures, I am sending my progress log using Raiserror method with severity 10.

Well so far all good. It sends all raiserror messages from stored procedure.

My issue is if in case there is any exception occurs inside a stored procedure, then it goes into catch method inside the stored procedure. But on the C# side, it doesn't go into catch and instead it treats as a normal flow and goes ahead for next collection item to delete.

On C# side I am using SqlInfoMessageEventHandler which fires SqlInfoMessage written by me. I think I am making mistake implementing this event.

Now when I comment SqlInfoMessageEventHandler, then the catch in C# works fine and it rolls back all changes as expected.

My code is as follows.

public void Purge(string keyName, string[] keyValues, ILog logger)
{
    try
    {
       currentKeyName = keyName;

       for (var i = 0; i < keyValues.Length; i++)
       {
                currentKeyValue = keyValues[i];                    

                using (adoConnection = new SqlConnection())
                {
                    using (SqlCommand adoCommand = new SqlCommand())
                    {
                        adoCommand.CommandText = "procName";
                        adoCommand.CommandType = CommandType.StoredProcedure;
                        // Set up the parameters to be substituted into the SQL
                        // if app number then set the currentKeyValue and set empty for othe params                    

                        SqlParameter[] sqlParam = new SqlParameter[]
                        {
                            //fill up values for all params
                        };

                        adoCommand.Parameters.AddRange(sqlParam);

                        adoConnection.ConnectionString = connectionString;
                        adoConnection.InfoMessage += new SqlInfoMessageEventHandler(SQLInfoMessage);
                        adoConnection.FireInfoMessageEventOnUserErrors = true;
                        adoConnection.Open();

                        adoCommand.Connection = adoConnection;

                        logger.Info(String.Format("-------------------------------------------------------------------------------------------------------"));

                        logger.Info(String.Format("started for {0}: {1}", currentKeyName, currentKeyValue));
                        adoCommand.ExecuteNonQuery();
                        logger.Info(String.Format("finished successfully for {0}: {1}", currentKeyName, currentKeyValue));

                        logger.Info(String.Format("-------------------------------------------------------------------------------------------------------"));

                    }
                }
            }
        }
        catch (SqlException ex)
        {                
            if (logger.IsErrorEnabled) logger.Error(FormatSqlExceptionMessage(ex));                
    }
    catch (Exception ex)
    {
        if (logger.IsErrorEnabled) 
            logger.Error("Application Error.", ex);
    }
    finally
    {
        if (logger.IsDebugEnabled) 
            logger.Debug(String.Format("Closing Connection for: {0}", currentKeyValue));
    }
}

// Sql info method 
private void SQLInfoMessage(object sender, SqlInfoMessageEventArgs args)
{
    sleepTime = 100;

    if (args.Errors.Count > 0)
    {
        foreach (SqlError info in args.Errors)
        {
            if (info.Class > 9) // Severity
            {                        
                if (logger.IsErrorEnabled) 
                    logger.Error(String.Format("SQL: {0}", info.Message));
            }
            else
            {
                if (logger.IsInfoEnabled) 
                    logger.Info(String.Format("SQL: {0}", info.Message));
            }
        }
    }
    else
    {
        if (logger.IsInfoEnabled) 
            logger.Info(String.Format("SQL: {0}", args.Message));
    }
}

// TransactionScope Method 
foreach (KeyValuePair<string, List<string>> purgeRecord in purgeDictionary)
{
    foreach (string keyValue in purgeRecord.Value)
    {
        try
        {
            using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
            {
                Purge(purgeRecord.Key, new string[] { keyValue }, Logger);
                transactionScope.Complete();
            }
        }
        catch (SqlException ex)
        {
            Logger.Error("Error occurred In InitiatePurgePendingProcess()" + ex.Message + ex.InnerException);
        }
    }
}

Answer 1

If you want a SqlException to be raised, change FireInfoMessageEventOnUserErrors to false (the default). When true is specified, a SqlException is not raised and the message handler is invoked instead with the error.

adoConnection.FireInfoMessageEventOnUserErrors = false;