The System. Transactions namespace has been added since. NET2.0, bringing a new transaction Programming Model for. NET applications.
This namespace provides several dependent TransactionXXX classes. Transaction is the base class of all Transaction processing classes and defines the attributes, methods, and events that can be used by all Transaction classes. CommittableTransaction is the only transaction class that supports submission. This class has a Commit () method, and all other transaction classes can only perform rollback.
This article introduces the usage of distributed explicit transactions based on the Transaction class through the example of bank transfer.
Create the following table in MySql:
Note that the Balance is an unsigned decimal type (for example)
Insert test data:
(Transfer successful test data ):
(Test data of transfer failure ):
Sample Code:
(1) SqlHelper. cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Transactions;
using System.Data;
Namespace Transaction Processing
{
public class SqlHelper
{
public static string GetConnection()
{
string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
return connStr;
}
public static int ExecuteNonQuery(Transaction transaction,string sql,params MySqlParameter[] parameters)
{
int result = -1;
using (MySqlConnection conn = new MySqlConnection(GetConnection()))
{
conn.Open();
if (null != transaction)
{
Conn. EnlistTransaction (transaction); // register the connection to the transaction
}
using (MySqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
result = cmd.ExecuteNonQuery();
}
}
return result;
}
public static DataTable ExecuteDataTable(string sql, params MySqlParameter[] parameters)
{
using (MySqlConnection conn = new MySqlConnection(GetConnection()))
{
using (MySqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
{
using (DataSet ds = new DataSet())
{
da.Fill(ds);
return ds.Tables[0];
}
}
}
}
}
}
}
(2) Bankaccountn. cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Data;
using System.Transactions;
Namespace Transaction Processing
{
public class Bankaccountn
{
public Bankaccountn(string bankaccountnId)
{
string sql = @"SELECT * FROM Bankaccountn WHERE BankaccountnId=@BankaccountnId;";
DataTable dt = SqlHelper.ExecuteDataTable(sql, new MySqlParameter("@BankaccountnId", bankaccountnId));
if (dt.Rows.Count <= 0)
{
Throw new Exception ("the account does not exist! ");
}
else if (dt.Rows.Count > 1)
{
Throw new Exception ("Exception message: an account with duplicate names exists! ");
}
else
{
this.bankaccountnId = dt.Rows[0]["BankaccountnId"] as string;
this.UserName = dt.Rows[0]["UserName"] as string;
this.Balance = Convert.ToDecimal(dt.Rows[0]["Balance"]);
}
}
private string bankaccountnId;
public string UserName
{
get;
private set;
}
public decimal Balance
{
get;
private set;
}
protected int Update(Transaction transaction)
{
string sql = @"UPDATE bankaccountn SET UserName = @UserName,Balance = @Balance
WHERE BankaccountnId= @BankaccountnId;";
return SqlHelper.ExecuteNonQuery(transaction, sql, new MySqlParameter("@BankaccountnId", this.bankaccountnId), new MySqlParameter("@UserName", this.UserName), new MySqlParameter("@Balance", this.Balance));
}
# Region expenditure + Epend (Transaction transaction, decimal money)
public void Epend(Transaction transaction, decimal money)
{
this.Balance -= money;
this.Update(transaction);
}
#endregion
# Region revenue + Income (Transaction transaction, decimal money)
public void Income(Transaction transaction, decimal money)
{
this.Balance += money;
this.Update(transaction);
}
#endregion
public bool TransferOfAccount(string incomeBankaccountnId, decimal money)
{
using (var transaction = new CommittableTransaction())
{
try
{
Bankaccountn incomeBankaccountn = new Bankaccountn(incomeBankaccountnId);
IncomeBankaccountn. Income (transaction, money); // account
This. Epend (transaction, money); // payment account expenditure
transaction.Commit();
return true;
}
catch
{
transaction.Rollback();
// Code for recording exception information
return false;
}
}
}
}
}
(3) test code
Bankaccountn one = new Bankaccountn("6666660123456789");
if (one.TransferOfAccount("6666669876543210", 200M))
{
Response. Write ("<script> alert ('transfer successful ') </script> ");
}
else
{
Response. Write ("<script> alert ('transfer failed') </script> ");
}
Code Analysis:
To create a distributed explicit Transaction based on the Transaction class, follow these steps:
1) instantiate a committed CommittableTransaction object;
2) the connection to be involved in the Transaction is registered to the CommittableTransaction object created in the previous step through the EnlistTransaction (transaction Transaction) of the MySqlConnection object;
3) if the transaction can be completed successfully, use the Commit () method of the CommittableTransaction object to submit the transaction processing result;
4) if an error occurs during transaction processing, call the Rollback () method of the CommittableTransaction object to cancel each modification. Is it as simple as the ADO. NET transaction in the previous section?