FairCom ADO.NET Driver - Developer's Guide

Transactions and Isolation Levels

Database transaction operations are an important element of many data-driven applications. To start a transaction you must call method CtreeSqlConnection.BeginTransaction() to obtain a CtreeSqlTransaction object. Once you instantiated a CtreeSqlTransaction object, you can use it to commit or rollback transactions.

.NET VB Example

Sub CreateTable()

Dim conString As String = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”

Dim hConnection As New CtreeSqlConnection(conString)

Dim hCommand As New CtreeSqlCommand(hConnection)

Dim hTransaction As CtreeSqlTransaction

hConnection.Open()

hTransaction = hConnection.BeginTransaction(IsolationLevel.ReadCommitted)

hCommand.Transaction = hTransaction

hCommand.CommandText = “create table tab1 (name char(20), age integer)”

hCommand.ExecuteNonQuery()

hTransaction.Commit()

hConnection.Close()

End Sub

.NET C# Example

void CreateTable()

{

String conString = “User=ADMIN;Password=ADMIN;Database=ctreeSQL”;

CtreeSqlConnection hConnection = new CtreeSqlConnection(conString);

CtreeSqlCommand hCommand = new CtreeSqlCommand(hConnection);

CtreeSqlTransaction hTransaction;

hConnection.Open();

hTransaction = hConnection.BeginTransaction(IsolationLevel.ReadCommitted);

hCommand.Transaction = hTransaction;

hCommand.CommandText = “create table tab1 (name (char(20), age integer)”;

hCommand.ExecuteNonQuery();

hTransaction.Commit();

hConnection.Close();

}

.NET C++ Example

void CreateTable()

{

String* conString = S“User=ADMIN;Password=ADMIN;Database=ctreeSQL”;

CtreeSqlConnection* hConnection = new CtreeSqlConnection(conString);

CtreeSqlCommand* hCommand = new CtreeSqlCommand(hConnection);

CtreeSqlTransaction* hTransaction;

hConnection->Open();

hTransaction = hConnection->BeginTransaction(IsolationLevel::ReadCommitted);

hCommand->Transaction = hTransaction;

hCommand->CommandText = “create table tab1 (name (char(20), age integer)”;

hCommand->ExecuteNonQuery();

hTransaction->Commit();

hConnection->Close();

}

.NET Delphi Example

procedure CreateTable

var

conString : String;

hConnection : CtreeSqlConnection;

hCommand : CtreeSqlCommand;

hTransaction : CtreeSqlTransaction;

begin

conString := 'User=ADMIN;Password=ADMIN;Database=ctreeSQL';

hConnection := CtreeSqlConnection.Create(conString);

hCommand := CtreeSqlCommand(hConnection);

hCommand.Transaction = hTransaction;

hConnection.Open();

hTransaction = hConnection.BeginTransaction(IsolationLevel.ReadCommitted);

hCommand.CommandRText := 'create table tab1 (name (char(20), age integer)';

hCommand.ExecuteNonQuery();

hTransaction.Commit();

hConnection.Close();

end;

When the CtreeSqlTransaction object is created, by calling the BeginTransaction() method of CtreeSqlConnection object, you can optionally specify the transaction isolation level. The standard transaction isolation levels as defined by current SQL standards are as follows:

• Transaction Isolation level 0 - Read Uncommitted
  
  A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored. (Not supported with FairCom DB SQL)
  
• Transaction Isolation level 1 - Read Committed
  
  Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.
  
• Transaction Isolation level 2 - Repeatable Read
  
  Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible.
  
• Transaction Isolation level 3 - Serializable
  
  A range lock is placed on the DataSet preventing other users from updating or inserting rows into the dataset until the transaction is complete. (Not supported with FairCom DB SQL)