How .NET Transactions are working on Sql Server Database When conducted a small analysis

Question

I did a simple test to understand how transactions work in .net

Sample code on which my transaction test was done is

_sqlHelper = new SqlHelper(true);
try
{
    _sqlHelper.ExecuteNonQuery(SpName.sp_UpdateRoomStatus.ToString()
        , Parameters.SqlParam<int?>("DepartmentId", this.DepartmentId, SqlDbType.Int)
        );

    _sqlHelper.ExecuteNonQuery(SpName.sp_UpdateRoomStatus.ToString()
        , Parameters.SqlParam<int?>("DepartmentId", this.DepartmentId, SqlDbType.Int)
        );

    _sqlHelper.CommitTransaction();
}
catch (SqlHelperException ex)
{
    _sqlHelper.RollBackTransaction();
}

Description about the test

When I writes new SqlHelper(true); a new transaction starts internally and connection has been created and opened to database.

_sqlHelper.ExecuteNonQuery(SpName.sp_UpdateRoomStatus.ToString()
    , Parameters.SqlParam<int?>("DepartmentId", this.DepartmentId, SqlDbType.Int)
    );

Now above function executes my procedure and do changes in a database.

I wrote same function call twice to execute procedure two times. This procedure do insertion in database. This procedure contains 3 insert queries in 3 different tables. Now I marked breakpoint on both function calls.

As soon as control comes on 1st breakpoint I simply lets its processing do which means 1st procedure has done insertion.

Now I unplugged my LAN wire since DB was on remote system. Which means connection lost. Hence transaction to be rolledback.

Now When I verified database after the whole process completed. I found data to be in consistent state hence proving that transaction is working.

I am confused at this moment that where did an actual insertion took place from 1st procedure call, since procedure executed successfully. How sql server get notified that transaction was going on and rollback has to be taken place.

It seems as if connection was established to database notifying that connection contains transaction and should get rolled back if connection gets lost.

Hence also stating that Changes made are maintained at Database rather than .net environment.

Answer 1

The database knows that the connection and the changes should be in a transaction - the actual changes and original data are stored in the database transaction log. When the transaction isn't properly committed and the connection is lost, the database automatically rolls back the transaction.

Basically, in SqlTransactions (not distributed) .Net does nothing clever, it is all handled by the database.