none
ADO.NET SqlTransaction not rolling back when client crashes RRS feed

  • Question

  • Hi, I have some sample code where I crash an application by putting a breakpoint in the middle of a transaction and stop debugging when the breakpoint is hit. After I do this I check the database and the SQL statements that were run before the breakpoint have been committed even though the transaction Commit() function was never called. I would have expected the transaction to be automatically rolled back in this scenario. is there any way to ensure that the transaction will rollback in this situation e.g. if the web server crashed unexpectedly?

    Thanks in advance,

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
    connection.Open();

    SqlCommand command = connection.CreateCommand();
    SqlTransaction transaction;

    // Start a local transaction.
    transaction = connection.BeginTransaction("SampleTransaction");

    command.Connection = connection;
    command.Transaction = transaction;

    try
    {
    command.CommandText =
    "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";
    command.ExecuteNonQuery();
                
    // PUT A BREAKPOINT HERE THEN STOP THE DEBUGGER
    command.CommandText =
    "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')";
    command.ExecuteNonQuery();

    // Attempt to commit the transaction.
    transaction.Commit();
    }
    catch (Exception ex)
    {
    // Attempt to roll back the transaction.
    try
    {
    transaction.Rollback();
    }
    catch (Exception ex2)
    {
    }
    }
    }
    Tuesday, November 18, 2008 5:28 PM

All replies

  • Can you provide more info here? What's the version of .NET Framework you're using and machine spec?

     

    Wednesday, November 19, 2008 12:38 AM
    Answerer
  • Hi, I am using .NET Framework 2.0, machine is running Windows XP with 2 GB RAM and 2.4 GHz processor.

    Thanks in advance
    Wednesday, November 19, 2008 12:52 AM