C# - The SqlTransaction Class

The SqlTransaction class provides a way to manually work with transactions.

Properties and Methods of the SqlTransaction Class

SqlTransaction.IsolationLevel

Syntax

IsolationLevel IsolationLevel

Description

The IsolationLevel property specifies the transaction isolation level for the connection. Valid values for this property are specified in the IsolationLevel class in the System.Data namespace. If the value of the IsolationLevel property is not specified, the default value is ReadCommitted.

Example

SqlTransaction trans = conn.BeginTransaction(IsolationLevel.Serializable, "RemoveProducts");

msg.Text = trans.IsolationLevel.ToString();

SqlTransaction.Commit()

Syntax

Void Commit()

Description

The Commit() method finalizes a transaction. A transaction must be committed for the changes to take effect.

Example

//Begin Transaction
SqlTransaction trans = conn.BeginTransaction("RemoveProducts");


//Delete Product
SqlCommand cmd = new SqlCommand("", conn, trans);
cmd.CommandText = "Delete from Products where ID=5";
cmd.ExecuteNonQuery();

trans.Commit();

SqlTransaction.RollBack()

Syntax

Void Rollback()
Void Rollback(String transactionName)

Description

The Rollback() method is used to roll back (that is, cancel) a transaction. If a string that contains the name of a saved point is passed as an argument, the transaction is rolled back to that save point.

Example

//Begin Transaction
SqlTransaction trans = conn.BeginTransaction("RemoveProducts");


//Delete Product
SqlCommand cmd = new SqlCommand("", conn, trans);
cmd.CommandText = "Delete from Products where ID=5";
cmd.ExecuteNonQuery();

//Rollback transaction
trans.Rollback();

SqlTransaction.Save()

Syntax

Viod Save()

Description

The Save() method enables a developer to bookmark a point in the transaction. The transaction can be rolled back to this saved point at any time. After the SqlTransaction.Commit() method has been called, you will be unable to roll back a transaction to a saved point.

Example

//Begin Transaction
SqlTransaction trans = conn.BeginTransaction("RemoveProducts");


//Delete Product
SqlCommand cmd = new SqlCommand("", conn, trans);
cmd.CommandText = "Delete from Products where ID=5";
cmd.ExecuteNonQuery();


//Save Transaction
trans.Save("ProductRemoved");

//Delete another product
cmd.CommandText = "Delete from Products where ID=13";
cmd.ExecuteNonQuery();

//Rollback Transaction to Save Point
trans.Rollback("ProductRemoved");