how to do error handling with using statements in C#

Question

im trying to do delete records from multiple database tables. For error handling i'm using a try/catch block as seen beneath.

try
{
    using (SqlCeConnection oConn = new SqlCeConnection(ConnectionString))
    {
        oConn.Open();
        using (SqlCeTransaction transaction = oConn.BeginTransaction())
        {
            //delete from multiple tables using ADO.NET
            transaction.Commit();
        }
    }
}
catch
{
   //error handling
}

problem is when an exception is raised the transaction is not rolled back. When reading multiple forums my conclusion is that 'using' statements should dispose the transaction and the connection. When disposed, the uncommitted transaction should be rolled back.

Can someone tell me what I'm doing wrong.

Answer 1

My guess is that you're not setting the Transaction property of your SqlCeCommand (which you don't show).

try
{
    using (SqlCeConnection oConn = new SqlCeConnection(ConnectionString))
    {
        oConn.Open();
        using (SqlCeCommand command = oConn.CreateCommand())
        {
            using (SqlCeTransaction transaction = oConn.BeginTransaction())
            {
               command.Transaction = transaction;
               //delete from multiple tables using ADO.NET using command
               transaction.Commit();
            }
        }

    }
}
catch
{
   //error handling
}

The SqlCeCommand won't automatically enlist in the transaction, you have to set it explicitly. As far as I'm aware, the Dispose() of the Transaction should roll it back, but you can call Rollback() yourself as others suggest just to be sure.