4.8. Working with Transactions

Whenever we call the SaveChanges() method while adding, updating or deleting, Entity Framework starts and ends an implicit transaction. Since we use disconnected data access, all operations are carried out within one transaction. Entity Framework starts and ends a transaction automatically for each data retrieval. We will take the following example to illustrate how automatic transactions work.

Suppose we need to make a discount on goods selected in the grid. Without explicit transaction management, the code would be as follows:

var dbContext = AppVariables.getDbContext();
foreach (DataGridViewRow gridRows in dataGridView.SelectedRows) {
    int id = (int)gridRows.Cells["Id"].Value;
    // here there is an implicit start and the completion of the transaction
    var product = dbContext.PRODUCTS.Find(id);
    // discount 10%
    decimal discount = 10.0m;
    product.PRICE = product.PRICE * (100 - discount) /100;
}
// here there is an implicit start and the completion of the transaction
// all changes occur in one transaction
dbContext.SaveChanges();

Let’s say we select 10 products. Ten implicit transactions will be used for finding the products by their identifiers. One more transaction will be used to save the changes.

If we control transactions explicitly, we can use just one transaction for the same piece of work. For example:

var dbContext = AppVariables.getDbContext();
// explicit start of a default transaction
using (var dbTransaction = dbContext.Database.BeginTransaction()) {
  string sql =
    "UPDATE PRODUCT " +
    "SET PRICE = PRICE * ROUND((100 - @DISCOUNT)/100, 2) " +
    "WHERE PRODUCT_ID = @PRODUCT_ID";
  try {
    // create query parameters
    var idParam = new FbParameter("PRODUCT_ID", FbDbType.Integer);
    var discountParam = new FbParameter("DISCOUNT", FbDbType.Decimal);
    // create a SQL command to update records
    var sqlCommand = dbContext.Database.Connection.CreateCommand();
    sqlCommand.CommandText = sql;
    // specify which transaction to use
    sqlCommand.Transaction = dbTransaction.UnderlyingTransaction;
    sqlCommand.Parameters.Add(discountParam);
    sqlCommand.Parameters.Add(idParam);
    // prepare query
    sqlCommand.Prepare();
    // for all selected records in the grid
    foreach (DataGridViewRow gridRows in dataGridView.SelectedRows) {
      int id = (int)gridRows.Cells["Id"].Value;
      // initialize query parameters
      idParam.Value = id;
      discountParam.Value = 10.0m; // discount 10%
      // execute sql statement
      sqlCommand.ExecuteNonQuery();
    }
    dbTransaction.Commit();
  }
  catch (Exception ex) {
    dbTransaction.Rollback();
    MessageBox.Show(ex.Message, "error");
  }
}

Our code starts the transaction with the default parameters. To specify your own parameters for a transaction, you should use the UseTransaction method.

private void btnDiscount_Click(object sender, EventArgs e) {
  DiscountEditorForm editor = new DiscountEditorForm();
  editor.Text = "Enter discount";
  if (editor.ShowDialog() != DialogResult.OK)
    return;
  bool needUpdate = false;
  var dbContext = AppVariables.getDbContext();
  var connection = dbContext.Database.Connection;
  // explicit start of transaction
  using (var dbTransaction = connection.BeginTransaction(IsolationLevel.Snapshot)) {
    dbContext.Database.UseTransaction(dbTransaction);
    string sql =
      "UPDATE PRODUCT " +
      "SET PRICE = ROUND(PRICE * (100 - @DISCOUNT)/100, 2) " +
      "WHERE PRODUCT_ID = @PRODUCT_ID";
    try {
      // create query parameters
      var idParam = new FbParameter("PRODUCT_ID", FbDbType.Integer);
      var discountParam = new FbParameter("DISCOUNT", FbDbType.Decimal);
      // create a SQL command to update records
      var sqlCommand = connection.CreateCommand();
      sqlCommand.CommandText = sql;
      // specify which transaction to use
      sqlCommand.Transaction = dbTransaction;
      sqlCommand.Parameters.Add(discountParam);
      sqlCommand.Parameters.Add(idParam);
      // prepare statement
      sqlCommand.Prepare();
      // for all selected records in the grid
      foreach (DataGridViewRow gridRows in dataGridView.SelectedRows) {
        int id = (int)gridRows.Cells["PRODUCT_ID"].Value;
        // initialize query parameters
        idParam.Value = id;
        discountParam.Value = editor.Discount;
        // execute SQL statement
        needUpdate = (sqlCommand.ExecuteNonQuery() > 0) || needUpdate;
      }
      dbTransaction.Commit();
    }
    catch (Exception ex) {
      dbTransaction.Rollback();
      MessageBox.Show(ex.Message, "error");
      needUpdate = false;
    }
  }
  // refresh grid
  if (needUpdate) {
    // for all selected records in the grid
    foreach (DataGridViewRow gridRows in dataGridView.SelectedRows) {
      var product = (PRODUCT)bindingSource.List[gridRows.Index];
      dbContext.Refresh(RefreshMode.StoreWins, product);
    }
    bindingSource.ResetBindings(false);
  }
}

That’s it. Now only one transaction is used for the entire set of updates and there are no unnecessary commands for finding data.

All that is left to do is to add a dialog box for entering the value of the discount and code to update data in the grid. Try to do it on your own.