DB logging in transactionscope failed

Question

I try to do the following thing with my program. Create a new transactionscope, execute an insert command to do logging, execute a different command to do an update and then deliberately throw an exception.

As i expected the update command is rolled back, but how can I commit the insert command? It seems the insert command is rolled back also, but i want to commit logging records.

I don't use and don't want MSDTC anyway.

class Program
{
    private static string connectString1 = "myconnstring";
    static void Main(string[] args)
    {

        try
        {
            var opt = new TransactionOptions
            {
                IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted,
                Timeout = TransactionManager.MaximumTimeout
            };

            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, opt))
            {
                using (SqlConnection conn = new SqlConnection(connectString1))
                {
                    conn.Open();

                    Logging(conn);

                    SqlCommand command1 = new SqlCommand("update Company set City='BP' where Id = 2", conn);
                    int aff = command1.ExecuteNonQuery();
                    throw null;
                }
                scope.Complete();
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine("Message: {0}", ex.Message);
        }
    }

    public static void Logging(SqlConnection conn)
    {
        var opt = new TransactionOptions
        {
            IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted,
            Timeout = TransactionManager.MaximumTimeout
        };
        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress, opt))
        {
            SqlCommand command2 = new SqlCommand("insert into ErrorLog(AppURL,Title,Message) values ('a','b','c')", conn);
            int aff2 = command2.ExecuteNonQuery();
            scope.Complete();
        }
    }
}

I want to keep logging insert always, regardless if my transaction is rolled back. It's a simplified example, in production code there are several methods in the main transaction scope, all with logging included. — Laszlo Boke

Laszlo Boke Laszlo Boke · Accepted Answer · 2014-03-03T07:37:54

It seems if i don't pass in the existing connection but I create a new one in the suppressed scope then the log insert will be kept even if the outer transaction is rolled back....

    public static void Logging()
    {
        var opt = new TransactionOptions
        {
            IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted,
            Timeout = TransactionManager.MaximumTimeout
        };
        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress, opt))
        {
            using (SqlConnection conn2 = new SqlConnection(connectString1))
            {
                conn2.Open();
                SqlCommand command2 = new SqlCommand("insert into ErrorLog(AppURL,Title,Message) values ('a','b','c')", conn2);
                int aff2 = command2.ExecuteNonQuery();
                scope.Complete();
            }
        }
    }

DB logging in transactionscope failed

1 Answers