Connected mode. Class DbTransaction. Using transactions

Before studying this topic, it is recommended that you familiarize yourself with the following topics:

Search other resources:

1. Class DbTransaction. General concepts

If transactions are used when working with databases, then ADO .NET technology gives the programmer a number of tools (classes, enumerations), the main of which is the DbTransaction class. The DbTransaction class is used when a SQL command that changes data needs to commit or roll back a transaction. As you know, data changes are made by commands like:

INSERT – inserting a new row into the database;
UPDATE – updating a row;
DELETE – delete a row.

A transaction is committed or canceled by the Commit() and Rollback() methods described in paragraph 2.

From the DbTransaction class, the classes of specific providers are inherited:

SqlTransaction;
OleDbTransaction;
OdbcTransaction.

These classes are directly used when programming tasks related to transaction processing.

⇑

2. Methods of the DbTransaction class. Review

Each provider class that corresponds to a transaction implements three basic methods:

Rollback() – rolls back the transaction from the pending state;
Commit() – commits the transaction in the database;
Save() – creates a savepoint in the transaction that can be used to rollback part of the transaction and specifies the name of the savepoint.

The signature of these methods in the SqlTransaction, OleDbTransaction, OdbcTransaction classes is as follows:

void Rollback();
void Rollback(string transactionName);
void Commit();
void Save(string savePointName);

here

savePointName – name of the save point;
transactionName – name of the savepoint to which the transaction is rolled back when the Rollback() method is called.

As you can see from the method implementations, C# ADO .NET features allow you to remember a savepoint to which a transaction can be rolled back.

⇑

3. Property Connection. Database connection

The Connection property is also implemented in the DbTransaction class. This property returns the connection object associated with the transaction, or null if the transaction is no longer valid. The property declaration for the different providers is as follows:

SqlConnection SqlTransaction.Connection { get; }
OleDbConnection OleDbTransaction.Connection { get; }
OdbcConnection OdbcTransaction.Connection { get; }

⇑

4. An example of working with transactions (on the example of a SQL server)

To work with MS SQL Server transactions, the SqlTransaction class is used.

To execute a command in a SqlTransaction, you must set the Transaction property on an object of the SqlCommand class.

The SqlTransaction object is created using the BeginTransaction() method of the SqlConnection class.

After the start of the SqlTransaction for the SqlConnection class, all requests for the transaction must be completed. Otherwise, an InvalidOperationException will be thrown with the appropriate message.

To commit data to the database, the Commit() method of the SqlTransaction class is executed. To roll back a transaction, call the Rollback() method.

The following demonstrates a method that executes an INSERT transaction for a given connection

// The method that executes the INSERT transaction
void ExecuteSqlTransaction2(SqlConnection connection)
{
  // Change group name
  // 1. Declare internal variables
  int rows; // number of rows changed

  // 2. Form the text of the SQL query to change the table.
  //    Old group name: textBox1
  //    New group name: textBox2
  string sqlText = "INSERT INTO [Group] (NameGroup) VALUES (\'" +
    textBox1.Text + "\')";

  // 4. Change group name in database
  try
  {
    // 4.1. Open connection
    connection.Open();

    // 4.2. Create transaction
    using (SqlTransaction transaction =
      connection.BeginTransaction()) // method BeginTransaction()
    {
      // Create object with SQL command
      SqlCommand command = new SqlCommand(sqlText, connection, transaction);

      // Fill rows
      rows = command.ExecuteNonQuery();

      // Commit database changes
      transaction.Commit();

      // transaction.Rollback(); - rollback the transaction
      label2.Text = rows.ToString();
    }
  }
  catch (SqlException ex)
  {
    MessageBox.Show(ex.Message);
  }
  finally
  {
    // 4.5. Close connection
    connection.Close();

    // command SELECT * FROM [Group]
    ExecuteSqlCommandSelectGroup(connection);
  }
}

⇑

5. An example of creating and committing a transaction for a table of products

The following shows a simplified example of executing a SQL query that supports a transaction that inserts an additional row into the Product table in a database named MyDatabase.

The table has the following fields

ID_Product	Name	Price	Count	Date	Note
…	…	…	…	…	…

The purpose of the table fields is as follows:

ID_Product – unique identifier, product code;
Name – product name;
Price – product cost;
Count – number of product units;
Date – date of receipt;
Note – additional description (note) of the product.

The following code snippet shows the creation and commit of a transaction for a Windows Forms application (button click event handler).

private void button10_Click(object sender, EventArgs e)
{
  // 1. Create a Connection String
  SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder();
  sb.DataSource = "(localdb)\\ProjectModels";
  sb.InitialCatalog = "MyDatabase";
  sb.IntegratedSecurity = true;

  // 2. Create connection based on Connection String
  SqlConnection connection = new SqlConnection(sb.ConnectionString);

  // 3. Open the connection
  connection.Open();

  // 4. Create a transaction based on a connection
  SqlTransaction transaction = connection.BeginTransaction();

  // 5. Create a SQL command with a transaction
  string sqlCmd = "INSERT INTO Product (ID_Product, [Name], [Price], [Count], [Date], [Note]) " +
    "VALUES (16, 'Multicooker Bosh', 6500.00, 3, '2022-04-12', 'Bosh DT-280A')";
  SqlCommand command = new SqlCommand(sqlCmd, connection);

  // 6. Add transaction to sqlCmd command
  command.Transaction = transaction;

  // 7. Execute a Transact-SQL statement on the given connection,
  //    returns the number of rows changed
  int rowsAffected = command.ExecuteNonQuery();

  // 8. Commit the transaction
  command.Transaction.Commit();

  // 9. Print the number of rows changed
  label1.Text = "Rows affected = " + rowsAffected.ToString();

  // 10. Close the connection
  connection.Close();
}

The object that implements the transaction for connection is created by calling

SqlTransaction transaction = connection.BeginTransaction();

This object is then added to the Transaction property of the SqlCommand object by calling

SqlCommand command = new SqlCommand(sqlCmd, connection);
command.Transaction = transaction;

To execute the SQL command represented by the command object, use the statement

command.ExecuteNonQuery();

The transaction is committed by calling the Commit() method as shown below

command.Transaction.Commit();

After this call, the changes are committed to the database.

If after executing the SQL query it is necessary to cancel the transaction, then the Rollback() method is called

command.Transaction.Rollback();

⇑

6. An example of creating a savepoint and rolling back a transaction. Methods Save(), Rollback(string)

The savepoint is set using the method

void Save(string savePointName);

where savePointName is the name of this savepoint.

Method

void Rollback(string transactionName);

rolls back a transaction from a pending state and specifies the transaction or savepoint name. The savepoint must be saved in the Save() method first.

An example demonstrating an approximate sequence of actions when the Save() and Rollback() methods interact is the following:

// 1. Open connection
connection.Open();

// 2. Create a transaction based on a connection
SqlTransaction transaction = connection.BeginTransaction();

// 3. Create an SQL command with a transaction
string sqlCmd = "................................"; // command text is given here

// 4. Create object SqlCommand
SqlCommand command = new SqlCommand(sqlCmd, connection);

// 5. Add transaction to sqlCmd command
command.Transaction = transaction;

// 6. Create a save point
string savePointName = "Save Point 1";
transaction.Save(savePointName);

// 7. Call a query that changes the database
int rowsAffected = command.ExecuteNonQuery();

// 8. Here may be some other actions here
// ...

// 9. Rollback a transaction to a savepoint
transaction.Rollback(savePointName);

BestProg

C#. ADO .NET. Class DbTransaction. Using transactions