BeginTransaction(IsolationLevel) Method

In This Topic

Begins a database transaction with the specified isolation level.

Syntax

Visual Basic
C#

'Declaration
 
Public Overloads Shadows Function BeginTransaction( _
   ByVal il As IsolationLevel _
) As PgSqlTransaction

public new PgSqlTransaction BeginTransaction( 
   IsolationLevel il
)

Parameters

il

Return Value

An object representing the new transaction.

Remarks

To commit or roll back the transaction, you must explicitly use the Commit or Rollback methods.

PostrgeSQLDirect .NET supports System.Data.IsolationLevel.ReadCommitted and System.Data.IsolationLevel.Serializable only. To ensure that the provider transaction management model performs correctly, avoid using other transaction management models.

Example

The following example creates a PgSqlConnection and a PgSqlTransaction. It also demonstrates how to use the BeginTransaction(), Commit, and Rollback methods.

csharp

public void RunPgSqlTransaction(string myConnString)
{
  PgSqlConnection pgConnection = new PgSqlConnection(myConnString);
  pgConnection.Open();

  PgSqlCommand pgCommand = new PgSqlCommand();
  pgCommand.Connection = pgConnection;
  PgSqlTransaction myTrans;

  // Start a local transaction
  myTrans = pgConnection.BeginTransaction(IsolationLevel.ReadCommitted);
  // Assign transaction object for a pending local transaction
  pgCommand.Transaction = myTrans;

  try
  {
    pgCommand.CommandText = "INSERT INTO Dept(DeptNo, DName) Values(50, 'DEVELOPMENT')";
    pgCommand.ExecuteNonQuery();
    pgCommand.CommandText = "INSERT INTO Dept(DeptNo, DName) Values(60, 'PRODUCTION')";
    pgCommand.ExecuteNonQuery();
    myTrans.Commit();
    Console.WriteLine("Both records are written to database.");
  }
  catch(Exception e)
  {
    myTrans.Rollback();
    Console.WriteLine(e.ToString());
    Console.WriteLine("Neither record was written to database.");
  }
  finally
  {
    pgConnection.Close();
  }
}

Visual Basic

Public Sub RunPgSqlTransaction(myConnString As String)
  Dim pgConnection As New PgSqlConnection(myConnString)
  pgConnection.Open()

  Dim pgCommand As New PgSqlCommand()
  pgCommand.Connection = pgConnection
  Dim myTrans As PgSqlTransaction

  ' Start a local transaction
  myTrans = pgConnection.BeginTransaction(IsolationLevel.ReadCommitted)
  ' Assign transaction object for a pending local transaction
  pgCommand.Transaction = myTrans

  Try
    pgCommand.CommandText = "INSERT INTO Dept(DeptNo, DName) Values(50, 'DEVELOPMENT')"
    pgCommand.ExecuteNonQuery()
    pgCommand.CommandText = "INSERT INTO Dept(DeptNo, DName) Values(60, 'PRODUCTION')"
    pgCommand.ExecuteNonQuery()
    myTrans.Commit()
    Console.WriteLine("Both records are written to database.")
  Catch e As Exception
    myTrans.Rollback()
    Console.WriteLine(e.ToString())
    Console.WriteLine("Neither record was written to database.")
  Finally
    pgConnection.Close()
  End Try
End Sub

Reference

PgSqlConnection Class
PgSqlConnection Members
Overload List
PgSqlCommand Class
PgSqlTransaction Class
Commit Method
Rollback Method

Send Feedback