Using Database Transactions in Delphi for .NET
Last month, I explained and demonstrated how to use Database Transactions in Delphi VCL applications. In this article, I want to follow up covering Delphi for .NET targets.Since VCL for .NET is largely similar to VCL (when it comes to database development), the examples will be given using WinForms, as well as ASP.NET Web Forms and Web Services.
As example, we’ll use the SQL Server database with the ACCOUNT table from last time.

ADO.NET Transactions
Let’s start with the ADO.NET transactions, using an SqlConnection component to connect to the SQL Server database with the Account table.Using ADO.NET, we can use ADO.NET Command components to talk to the database.For the SqlConnection, we need to use to corresponding SqlCommand component.We can assign a SqlTransaction component to the Transaction property of an SqlCommand, and examine the IsolationLevel property which is set to ReadCommitted (4096) by default.Possible other values include Chaos (16), ReadUncommitted (256), RepeatableRead (65536) , Serializable (1048576), and Unspecified (-1) – see figure below:

IsolationLevel Chaos means that any uncommitted changes from transactions with a higher IsolationLevel cannot be overwritten.
IsolationLevel ReadUncommited means that no locks are performed on the data, so dirty reads can happen (data which is modified by others while it is being read).
IsloationLevel ReadCommitted means that (shared) locks are performed while the data is read (so we get consistent read values and no dirty reads), but values can be changed before the transaction is committed.This is the default IsolationLevel.
IsolationLevel RepeatableRead extends the ReadCommitted and makes sure that data cannot be changed while the transaction is still active (i.e.until a Commit or Rollback).This ensures that repeatable reads still get the same values (no dirty reads), although so-called phantom (new) rows are still possible.
IsolationLevel Serializable is the highest isolation level, and is implemented using a range lock on the dataset, to prevent other users or transactions from modifying or inserting data while the transaction is still active.
Finally, the IsloationLevel value Unspecified means that a different isolation level than the ones above is used, but the system is unable to determine the exact level.This usually indicated a database (driver) compatibility issue.

We cannot directly assign the IsolationLevel property of a SqlTransaction instance, but we can request the required level when we create a new SqlTransaction using a parameter to the BeginTransaction method of the SqlConnection component.The BeginTransaction call will return an instance of the SqlTransaction class, which can then be used to assign to the SqlCommands that update, insert or delete rows from the DataSet.Apart from BeginTransaction, the SqlConnection component also features the Commit and Rollback methods to complete the transaction.
For our example, we can use a SqlDataAdapter component, and use the SelectCommand property to send an SQL command to the database.I’ve specified a simple “SELECT * FROM Account” as value to the CommandText property of the SelectCommand.We can then use a SqlCommandBuilder to produce the corresponding CommandText for the UpdateCommand, InsertCommand and DeleteCommand.Finally, we need to assign the Transaction property of all four SqlCommands, as shown below:

  procedure TWinForm1.TWinForm1_Load(sender: System.Object;
    e: System.EventArgs);
  begin
  //  SqlDataAdapter1.SelectCommand.CommandText := 'SELECT * FROM Account';
  //  SqlDataAdapter1.SelectCommand.Connection := SqlConnection1;
    SqlDataAdapter1.Fill(DataSet1);

    DataGrid1.DataSource := DataSet1;
    DataGrid1.DataMember := DataSet1.Tables[0].TableName;
  end;

  
  procedure TWinForm1.Button1_Click(sender: System.Object;
    e: System.EventArgs);
  var
    MyTransaction: SqlTransaction;
    MyCommandBuilder: SqlCommandBuilder;

  begin
    MyCommandBuilder := SqlCommandBuilder.Create(SqlDataAdapter1);
    SqlDataAdapter1.UpdateCommand := MyCommandBuilder.GetUpdateCommand;
    SqlDataAdapter1.InsertCommand := MyCommandBuilder.GetInsertCommand;
    SqlDataAdapter1.DeleteCommand := MyCommandBuilder.GetDeleteCommand;

    SqlConnection1.Open;
    try
      MyTransaction := SqlConnection1.BeginTransaction(IsolationLevel.RepeatableRead);
      try
        SqlDataAdapter1.SelectCommand.Transaction := MyTransaction;
        SqlDataAdapter1.UpdateCommand.Transaction := MyTransaction;
        SqlDataAdapter1.DeleteCommand.Transaction := MyTransaction;
        SqlDataAdapter1.InsertCommand.Transaction := MyTransaction;

        SqlDataAdapter1.Update(DataSet1);

        MyTransaction.Commit
      except
        MyTransaction.Rollback;
        raise
      end
    finally
      SqlConnection1.Close
    end
  end;

Multiple Datasets
When using multiple datasets that need to be updated in a single transaction, you will probably use multiple SqlDataAdapters.This is no problem, since you can assign the SqlTransaction instance to any number of SqlCommand components.This can be seen in the listing below:

  procedure TWinForm1.TWinForm1_Load(sender: System.Object;
    e: System.EventArgs);
  begin
  //  SqlDataAdapter1.SelectCommand.CommandText := 'SELECT * FROM Account';
  //  SqlDataAdapter1.SelectCommand.Connection := SqlConnection1;
    SqlDataAdapter1.Fill(DataSet1);

  //  SqlDataAdapter1.SelectCommand.CommandText := 'SELECT * FROM Bank';
  //  SqlDataAdapter1.SelectCommand.Connection := SqlConnection1;
    SqlDataAdapter1.Fill(DataSet2);

    DataGrid1.DataSource := DataSet1;
    DataGrid1.DataMember := DataSet1.Tables[0].TableName;
  end;

  
  procedure TWinForm1.Button1_Click(sender: System.Object;
    e: System.EventArgs);
  var
    MyTransaction: SqlTransaction;
    MyCommandBuilder: SqlCommandBuilder;

  begin
    MyCommandBuilder := SqlCommandBuilder.Create(SqlDataAdapter1);
    SqlDataAdapter1.UpdateCommand := MyCommandBuilder.GetUpdateCommand;
    SqlDataAdapter1.InsertCommand := MyCommandBuilder.GetInsertCommand;
    SqlDataAdapter1.DeleteCommand := MyCommandBuilder.GetDeleteCommand;

    MyCommandBuilder := SqlCommandBuilder.Create(SqlDataAdapter2);
    SqlDataAdapter2.UpdateCommand := MyCommandBuilder.GetUpdateCommand;
    SqlDataAdapter2.InsertCommand := MyCommandBuilder.GetInsertCommand;
    SqlDataAdapter2.DeleteCommand := MyCommandBuilder.GetDeleteCommand;

    SqlConnection1.Open;
    try
      MyTransaction := SqlConnection1.BeginTransaction(IsolationLevel.RepeatableRead);
      try
        SqlDataAdapter1.SelectCommand.Transaction := MyTransaction;
        SqlDataAdapter1.UpdateCommand.Transaction := MyTransaction;
        SqlDataAdapter1.DeleteCommand.Transaction := MyTransaction;
        SqlDataAdapter1.InsertCommand.Transaction := MyTransaction;

        SqlDataAdapter2.SelectCommand.Transaction := MyTransaction;
        SqlDataAdapter2.UpdateCommand.Transaction := MyTransaction;
        SqlDataAdapter2.DeleteCommand.Transaction := MyTransaction;
        SqlDataAdapter2.InsertCommand.Transaction := MyTransaction;

        SqlDataAdapter1.Update(DataSet1);
        SqlDataAdapter2.Update(DataSet2);

        MyTransaction.Commit
      except
        MyTransaction.Rollback;
        raise
      end
    finally
      SqlConnection1.Close
    end
  end;

Transactions and Savepoints
When using transactions for multiple datasets, you may want to use Savepoints as well.A Savepoint is a useful feature that allows you to roll back a portion of a transaction (like a transaction inside a transaction), without having to rollback the entire transaction itself.This may be useful when you use two datasets (like I used in the previous listing), and the update of the first dataset succeeds, but the update on the second dataset fails.You can then decide to make a savepoint at the moment the update on the first dataset has succeeded, and the update on the second dataset has not yet started.Now, when the error occurs while updating the second dataset, you can perform a rollback to the savepoint, which will bring you back to the situation where the update of the first dataset succeeded (but that of the second dataset has not yet started).Then you can provide an error dialog to the user, allow some changes to be made, and attempt the update of the second dataset (again).This avoids having to rollback the entire transaction.
The code – without the actual user feedback dialog – can be seen below:

  procedure TWinForm1.Button1_Click(sender: System.Object;
    e: System.EventArgs);
  var
    MyTransaction: SqlTransaction;
    MyCommandBuilder: SqlCommandBuilder;

  begin
    MyCommandBuilder := SqlCommandBuilder.Create(SqlDataAdapter1);
    SqlDataAdapter1.UpdateCommand := MyCommandBuilder.GetUpdateCommand;
    SqlDataAdapter1.InsertCommand := MyCommandBuilder.GetInsertCommand;
    SqlDataAdapter1.DeleteCommand := MyCommandBuilder.GetDeleteCommand;

    MyCommandBuilder := SqlCommandBuilder.Create(SqlDataAdapter2);
    SqlDataAdapter2.UpdateCommand := MyCommandBuilder.GetUpdateCommand;
    SqlDataAdapter2.InsertCommand := MyCommandBuilder.GetInsertCommand;
    SqlDataAdapter2.DeleteCommand := MyCommandBuilder.GetDeleteCommand;

    SqlConnection1.Open;
    try
      MyTransaction := SqlConnection1.BeginTransaction(IsolationLevel.RepeatableRead);
      try
        SqlDataAdapter1.SelectCommand.Transaction := MyTransaction;
        SqlDataAdapter1.UpdateCommand.Transaction := MyTransaction;
        SqlDataAdapter1.DeleteCommand.Transaction := MyTransaction;
        SqlDataAdapter1.InsertCommand.Transaction := MyTransaction;

        SqlDataAdapter2.SelectCommand.Transaction := MyTransaction;
        SqlDataAdapter2.UpdateCommand.Transaction := MyTransaction;
        SqlDataAdapter2.DeleteCommand.Transaction := MyTransaction;
        SqlDataAdapter2.InsertCommand.Transaction := MyTransaction;

        SqlDataAdapter1.Update(DataSet1);

        MyTransaction.Save('MySavePoint');
        try
          SqlDataAdapter2.Update(DataSet2)
        except
          MyTransaction.Rollback('MySavePoint');
          // dialog to user - modify changes to DataSet 2
          SqlDataAdapter2.Update(DataSet2) // try again
        end;

        MyTransaction.Commit
      except
        MyTransaction.Rollback;
        raise
      end
    finally
      SqlConnection1.Close
    end
  end;

BDP Transactions
The Borland Data Provider for .NET offers a little extension over ASP.NET, in that it supports any database (provided you have a suitable BDP driver) with one set of components.There is no significant difference in the way BdpConnection calls BeginTransaction, Commit or Rollback, and the BdpTransaction is similar in functionality as the SqlTransaction.
The main difference is the fact that we no longer have to use an explicit BdpCommandBuilder, but we can call the AutoUpdate method of the BdpDataAdapter.The problem with that approach is that the BdpCommandBuilder will allow us to assign the BdpTransaction instance to the different BdpCommands, but the use of the AutoUpdate method will not assign the Transaction properties of the BdpCommands.
For BDP transaction support, we may have to use the DataSync and DataHub components, which then even offer heterogeneous database transactions! This was described in more detail in The Delphi Magazine issue #115 (and will be reprinted shortly), when the DataSync and DataHub components were first introduced in Delphi 2005.

ASP.NET
We can also use ADO.NET transaction in ASP.NET Web Service and Web Server applications.However, the major limitation is the fact that we should make sure to commit or rollback the transaction as soon as possible.In fact, we should make sure to call Commit or Rollback in the same request that used the BeginTransaction method to start the connection.
For ASP.NET Web Services, this was also mentioned in more detail in The Delphi Magazine issue #95, when I covered the ASP.NET Web Service [TransactionOption] attribute (using the Delphi 7 for .NET preview command-line compiler).