CODEWITHSUNDEEP
c#.netsql-serverado.nettransactions
David Espart
David Espart

Reputation: 11790

When does a db transaction written in ADO.NET actually begin?

One of the key things in database-intensive applications is to keep the transactions as short as possible.

Today I was wondering when this transaction would actually begin:

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
    {
        sqlConnection.Open();
/*(1)*/ SqlTransaction sqlTransaction = sqlConnection.BeginTransaction(IsolationLevel.ReadUncommitted); 

        //Perform some stuff
        //...

/*(2)*/ using (SqlCommand command = new SqlCommand(sqlQuery, sqlConnection, sqlTransaction))  
        {
             //Some other stuff
             //...
             try
             {
                 /*(3)*/sqlCommand.ExecuteNonQuery();
                 //More irrelevant code
                 //...
                 sqlCommand.CommandText = otherQuery;
                 sqlCommand.ExecuteNonQuery();
                 sqlTransaction.Commit();
             }
             catch(Exception)
             {
                 sqlTransaction.Rollback();
                 throw;
             }
        }
  }

In step (1), (2) or (3)? Ideally it should be in step 3.

Upvotes: 2

Views: 736

Answers (1)

Johannes Rudolph
Johannes Rudolph

Reputation: 35761

The transaction starts at point 3, the first time you exectue a command on the connection.

You can verify this using SQL Server Profiler.

Upvotes: 6

Related Questions