การใช้ Transaction ใน ADO.NET

ADO.NET Transaction เริ่มต้นด้วยการใช้ BeginTransaction method ของ Connection class ซึ่ง method นี้จะคืนค่า sqlTransaction object ถึงแม้จะใช้ OleDbConnection, OracleConnection ก็จะมี BeginTransaction method เหมือนกัน

เมื่อทำการ execute งานภายใน Transaction block เสร็จแล้ว จะต้องเรียก Commit หรือ Rollback method อย่างใดอย่างหนึ่งตามที่ต้องการ

ในการทำงานร่วมกับ ADO.NET Transaction ต้องเปิด connection และสร้าง instance ของ transaction จากนั้นก็เรียกใช้ method ต่างๆ ที่จำเป็นซึ่งจะอธิบายต่อไป

Transaction ที่ ADO.NET สนับสนุน คือ sqlTransaction class ซึ่งมี namespace คือ System.Data.SqlClient โดย sqlTransaction class มร property หลัก 2 ตัว คือ

• Connection คือ instance ของ sqlConnection ที่ instance ของ Transaction อ้างถึง
• IsolationLevel เป็นการระบุ IsolationLevel ของการทำ Transaction

Method ของ Transaction class ที่สำคัญประกอบด้วย

• Commit เป็นการเรียกให้ transaction ทำการ commit
• Rollback เป็นการเรียกให้ transaction ทำการ rollback
• Save เป็นการทำ save point ให้กับ transaction ทำให้สามารถ rollback ไปยังจุดที่ save point ไว้ได้

ขั้นตอนในการทำงานของ ADO.NET Transaction

1. ติดต่อฐานข้อมูล
2. สร้าง SqlCommand instance และระบุ parameters ที่จำเป็น
3. เปิดฐานข้อมูล
4. เรียก BeginTransaction method ของ Connection object เพื่อทำการเริ่มต้น Transaction
5. ทำการ execute ข้อความ sql statements โดยใช้ command instance
6. เรียก Commit method ของ Transaction object เพื่อให้ transaction เสร็จสมบูรณ์ หรือ เรียก rollback method เืพื่อยกเลิก transaction
7. ปิดการติดต่อกับฐานข้อมูล

code ด้านล่างเป็น code สั้นๆ ที่แสดงวิธีการทำงานของ transaction โดยใช้ ADO.NET

C#

string connectionString = ...; //Some connection string
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();

SqlTransaction sqlTransaction = sqlConnection.BeginTransaction();

SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Transaction = sqlTransaction;

try
{
 sqlCommand.CommandText = "Insert into Employee (EmpCode, EmpName) VALUES (1, 'Joydip')";
 sqlCommand.ExecuteNonQuery();
 sqlCommand.CommandText = "Insert into Dept (DeptCode, DeptName, EmpCode) VALUES (9, 'Software', 1)";
 sqlCommand.ExecuteNonQuery();
 sqlTransaction.Commit();
 //Usual code
}

catch(Exception e)
{
 sqlTransaction.Rollback();
 //Usual code
}

finally
{
 sqlConnection.Close();
}

VB.NET

Dim connectionString As String = "..."  'Some connection string
Dim _sqlConnection As New SqlConnection(connectionString)
_sqlConnection.Open()

Dim _sqlTransaction As SqlTransaction = _sqlConnection.BeginTransaction
Dim _sqlCommand As New SqlCommand
_sqlCommand.Transaction = _sqlTransaction

Try
  _sqlCommand.CommandText = "Insert into Employee (EmpCode, EmpName) VALUES (1, 'Joydip')"
  _sqlCommand.ExecuteNonQuery()
  _sqlCommand.CommandText = "Insert into Dept (DeptCode, DeptName, EmpCode) VALUES (9, 'Software', 1)"
  _sqlCommand.ExecuteNonQuery()
  _sqlTransaction.Commit()
Catch ex As Exception
  _sqlTransaction.Rollback()
Finally
  _sqlConnection.Close()
End Try

ส่วน code ต่อไปเป็น code ที่ใช้ "using" เพื่อกำหนดขอบเขตหรือ scope ของ object

C#

using (SqlConnection sqlConnection = new SqlConnection(connectionString))
{
   SqlCommand command = connection.CreateCommand();
   SqlTransaction transaction = null;
  
   try
   {
       sqlConnection.Open();
       transaction = sqlConnection.BeginTransaction();
      
       command.Transaction = transaction;
      
       command.CommandText = "Insert into employee (empID, empName) values (1, 'Joydip');
       command.ExecuteNonQuery();
      
       command.CommandText = "Insert into dept (deptID,deptName,empID) values (9,'Software',1)";
       command.ExecuteNonQuery();
      
       transaction.Commit();
   }
   catch(Exception ex)
   {
       transaction.Rollback();
       throw ex;
   }
   finally
   {
       sqlConnection.Close();
   }
}

VB.NET

Using _sqlconnection As New SqlConnection(connectionString)
   Dim command As SqlCommand = _sqlconnection.CreateCommand
   Dim _sqlTransaction As SqlTransaction = Nothing

   Try
       _sqlconnection.Open()
       _sqlTransaction = _sqlconnection.BeginTransaction

       command.Transaction = _sqlTransaction

       command.CommandText = "Insert into Employee (EmpCode, EmpName) VALUES (1, 'Joydip')"
       command.ExecuteNonQuery()

       command.CommandText = "Insert into Dept (DeptCode, DeptName, EmpCode) VALUES (9, 'Software', 1)"
       command.ExecuteNonQuery()

       _sqlTransaction.Commit()
   Catch ex As Exception
       _sqlTransaction.Rollback()
       Throw ex
   Finally
       _sqlconnection.Close()
   End Try
End Using

ADO.NET 2.0 มีคุณสมบัติเพิ่มเข้ามาใหม่มากมาย โดยมีความยืดหยุ่นและง่ายในการใช้งานมากขึ้น สำหรับ transaction มี namespace ใหม่เรียกว่า System.Transaction โดยเป็นการปรับปรุงการทำ Transaction แบบกระจาย (Distributed Transaction)

System.Transaction ประกอบด้วย TransactionScope class ที่สามารถกำหนด object ไว้ภายใน scope เพื่อสนับสนุนการทำ transaction เมื่อ transaction ทำงานเสร็จจะทำการ mark ให้เป็น complete ได้โดยการเรียกใช้ method ชื่อ TransactionScope.Complete

Transaction จะทำำการ rollback เมื่อ instance ของ transaction ถูกทิ้งโดยการเรียก Dispose method

code ด้านล่างเป็นเพียง code ตัวอย่างส่วนหนึ่งสำหรับการใช้ System.Transaction
C#

using (System.Transactions.TransactionScope transactionScope = new System.Transactions.TransactionScope())
{
     SqlConnection sqlConnection = newSqlConnection(connectionString);
     string sqlString = "Update emp set empName = 'Joydip Kanjilal' where empID = 9";
     SqlCommand cmd = newSqlCommand(sqlString , sqlConnection);
     sqlConnection.Open();
     cmd.ExecuteNonQuery();
     sqlConnection.Close();
}

VB.NET

Using _transactionScope As New System.Transactions.TransactionScope
   Dim _sqlConnection As New SqlConnection(connectionstring)
   Dim _sqlString As String = "Update emp set empName = 'Joydip Kanjilal' where empID = 9"
   Dim cmd As SqlCommand = New SqlCommand(_sqlString, _sqlConnection)
   _sqlConnection.Open()
   cmd.ExecuteNonQuery()
   _sqlConnection.Close()

End Using

TransactionScope ยังสนับสนุนการทำ distributed transaction อีกด้วย สามารถใช้งาน transaction สำหรับการติดต่อหลายฐานข้อมูล ตัวอย่าง code ด้านล่างใช้ TransactionScope class เพื่อทำ transaction กับหลายฐานข้อมูล

C#

using (TransactionScope transactionScope = new TransactionScope())
{
  using (SqlConnection codesDatabaseConnection = new SqlConnection(codesDatabaseConnectionString))
  {
     SqlCommand sqlCommandCodes = codesDatabaseConnection.CreateCommand();
     sqlCommandCodes.CommandText = "Insert Into codes (codeID,codeText) values (1,'Test')";
     codesDatabaseConnection.Open();
     sqlCommandCodes.ExecuteNonQuery();
     codesDatabaseConnection.Close();
  }

  using (SqlConnection statesDatabaseConnection = new SqlConnection(statesDatabaseConnectionString))
  {
     SqlCommand sqlCommandStates = statesDatabaseConnection.CreateCommand();
     sqlCommandStates.CommandText = "Insert into States(stateID,stateName) values (1, 'Test')";
     statesDatabaseConnection.Open();
     sqlCommandStates.ExecuteNonQuery();
     statesDatabaseConnection.Close();
  }

  transactionScope.Complete();
}

VB.NET

Using _transactionScope As New System.Transactions.TransactionScope
   Using _codesDatabaseConnection As New SqlConnection(codesDatabaseconnectionString)
       Dim _sqlCommandCodes As SqlCommand = _codesDatabaseConnection.CreateCommand
       _sqlCommandCodes.CommandText = "Insert Into codes (codeID,codeText) values (1,'Test')"
       _codesDatabaseConnection.Open()
       _sqlCommandCodes.ExecuteNonQuery()
       _codesDatabaseConnection.Close()
   End Using

   Using _statesDatabaseConnection As New SqlConnection(statesDatabaseConnectionString)
       Dim _sqlCommandStates As SqlCommand = _statesDatabaseConnection.CreateCommand
       _sqlCommandStates.CommandText = "Insert into States(stateID,stateName) values (1, 'Test')"
       _statesDatabaseConnection.Open()
       _sqlCommandStates.ExecuteNonQuery()
       _statesDatabaseConnection.Close()
   End Using
   _transactionScope.Complete()
End Using

จุดที่ต้องสังเกต
sqlTransatcion object ต้องคืนค่าี่มาจาก BeginTransaction method และนำไปกำหนดที่ Transaction property ของ Command object ถ้าไม่ได้ทำตามนี้ InvalidOperationException จะถูก throw ออกมา
Connection instance ต้องเรียก Open method ก่อนที่จะเริ่ม transaction ใหม่ ถ้าไม่ได้ทำตามนี้ InvalidOperationException จะถูก throw ออกมา
ดังนั้น เพื่อที่จะปรับปรุงการทำงานของแอพพฃิเคชั่นให้ดีึ้ขึ้น ควรเก็บ transaction ให้สั้นที่สุดเท่าที่จะทำได้ ซึ่งจะช่วยลดการ lock และสามารถทำงานได้อย่างต่อเนื่องมากขึ้น นอกจากนั้นคุณควรวิเคราะห์ว่า batch statement ของคุณต้องการ transaction คือไม่ โดยพยายามอย่าใช้ transaction ที่ไม่จำเป็นเนื่องจากจะกระทบประสิทธิภาพตามที่ได้กล่าวไว้ข้างต้น