Use a transaction to process multiple SQL statements at a time

When performing an operation, you must modify the data in multiple tables at the same time, and each data entry must be successfully operated. To prevent some data operations from being successful, we need to use transactions for data disorder.

The transaction is: if all operations are successful, the transaction succeeds. If one data operation fails, the transaction is rolled back.

 

Background write transactions:

/// <Summary>
/// Execute multiple SQL statements to implement database transactions.
/// </Summary> access Database
/// <Param name = "SQLStringList"> Multiple SQL statements </param>
Public static void ExecuteSqlTran (List <string> SQLStringList)
{
Using (OleDbConnection conn = new OleDbConnection (SqlHelper. ConString ))
{
Conn. Open ();
OleDbCommand cmd = new OleDbCommand ();
Cmd. Connection = conn;
OleDbTransaction tx = conn. BeginTransaction ();
Cmd. Transaction = tx;
Try
{
For (int n = 0; n <SQLStringList. Count; n ++)
{
String strsql = SQLStringList [n]. ToString ();
If (strsql. Trim (). Length> 1)
{
Cmd. CommandText = strsql;
Cmd. ExecuteNonQuery ();
}
}
Tx. Commit ();
}
Catch (System. Data. OleDb. OleDbException E)
{
Tx. Rollback ();
Throw new Exception (E. Message );
}
}
}

/// <Summary>
/// Execute multiple SQL statements to implement database transactions.
/// </Summary> sql2000 database
/// <Param name = "SQLStringList"> Multiple SQL statements </param>
Public static void ExecuteSqlTran (List <string> SQLStringList)
{
Using (SqlConnection conn = new SqlConnection (SqlHelper. ConString ))
{
Conn. Open ();
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
SqlTransaction tx = conn. BeginTransaction ();
Cmd. Transaction = tx;
Try
{
For (int n = 0; n <SQLStringList. Count; n ++)
{
String strsql = SQLStringList [n]. ToString ();
If (strsql. Trim (). Length> 1)
{
Cmd. CommandText = strsql;
Cmd. ExecuteNonQuery ();
}
}
Tx. Commit ();
}
Catch (System. Data. SqlClient. SqlException E)
{
Tx. Rollback ();
Throw new Exception (E. Message );
}
}
}

 

 

Foreground call:

Try
{
// 1. Check the member balance
Decimal usermoney = SqlDal. EntityUserinfo. GetModel (int. Parse (cookieUserID). Money;
// 2. Modify the balance
Decimal Zmoney = usermoney + moAD. Commission;
// Write an SQL statement
List <string> SQLStringList = new List <string> ();
String clickSql = "insert into [user] (name, age) values ('" + nickname + "', '" + 4 years old + "')";
String userSql = "update [class] set [name] = '" + kindergarten + "'where id =" + 2 + "";
SQLStringList. Add (clickSql );
SQLStringList. Add (userSql );
SqlDal. SqlHelper. ExecuteSqlTran (SQLStringList );

// Database operation successful
// Prompt
CommonClass. Xmls xmls1 = new CommonClass. Xmls ();
String path1 = CommonClass. Unit. GetMapPath (@ "/Admin/Configs/SysSettingInfo. config ");
String ClickTishi = xmls1.GetXmlNode (path1, "SysSettingInfo/ClickTishi ");
// Replace the character
ClickTishi = ClickTishi. Replace ("[$]", moAD. Commission. ToString ("0.00 "));
Context. Response. Write (ClickTishi); // output
}
Catch (Exception ex)
{
// Response. Write (ex. Message );
Context. Response. Write ("operation failed! "+ Ex. Message); // output
}

Http://hi.baidu.com/solo_liang/blog/item/cde8ff012c0aed034bfb51a9.html