C# 實現 SQL Server 交易回復

/// <summary>
/// SQL Server事務樣本類,示範Sql的事務操作
/// 作者:李斌(Ｃａｍｕｓ)
/// </summary>
public class SQLServerTransactionDemo
{
private SQLServerTransactionDemo(){}

/// <summary>
/// 擷取SQL Server事務樣本類的執行個體方法
/// </summary>
/// <returns>SQL Server事務樣本類的執行個體</returns>
public static SQLServerTransactionDemo GetHandle()
{
 return new SQLServerTransactionDemo();
}

#region 運行SQL Server事務Run方法
/// <summary>
/// 運行SQL Server事務
/// </summary>
public void Run()
{
// 訪問Microsoft SQL Server樣本資料庫Northwind,假設Microsoft SQL Server的sa密碼為空白
string
connectionString=@"Server=(Local);Database=Northwind;UID=sa;PWD=;Persist Security Info=false;";

//建立Connection對象
System.Data.SqlClient.SqlConnection sqlConnection = null;
System.Data.SqlClient.SqlTransaction sqlTransaction = null;
try
{
sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);
sqlConnection.Open();//開啟Connection

// 開始本地事務,Connection.BeginTransaction()
// IsolationLevel.ReadCommitted:
// 在正在讀取資料時保持共用鎖定，以避免髒讀，但是在事務結束之前可以更改資料，從而導致不可重複的讀取或幻像資料。
sqlTransaction = sqlConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted,"SQLTransaction"）;

//建立Command對象
System.Data.SqlClient.SqlCommand sqlCommand = sqlConnection.CreateCommand();
// 指派Connection和Transaction對象給Command對象
sqlCommand.Connection = sqlConnection;
sqlCommand.Transaction = sqlTransaction;

//開始執行事務,該事務由命令1 2 3組成.
// 執行資料庫命令1
sqlCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (200, \'Description\')";
sqlCommand.ExecuteNonQuery();
// 執行資料庫命令2
sqlCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (201, \'Description\')";
sqlCommand.ExecuteNonQuery();
// 執行資料庫命令3!!!與命令1相同,會出錯
sqlCommand.CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (200, \'Description\')";
sqlCommand.ExecuteNonQuery();
// 提交事務
sqlTransaction.Commit();
Console.WriteLine("兩條資料庫命令已經執行完成."）;
}
catch(Exception e)
{
try
{
if(sqlTransaction != null)
{
// 復原事務
sqlTransaction.Rollback("SQLTransaction"）;
}
}
catch (System.Data.SqlClient.SqlException ex)
{
// 復原事務失敗
if (sqlTransaction.Connection != null)
{
Console.WriteLine("執行復原事務時出現 " + ex.GetType() + " 違例!" + ex.Message);
}
}
  
Console.WriteLine("在執行資料庫命令時出現 " + e.GetType() + " 違例!" + e.Message);
Console.WriteLine("兩條資料庫命令均未完成."）;
}
finally
{
//關閉Connection
if(sqlConnection != null)
{
sqlConnection.Close();
}
}
}
#endregion

public static void Main(string[] args)
{
SQLServerTransactionDemo.GetHandle().Run();
}
}