DataAdapterのUpdateで更新する場合のトランザクション管理

CREATE OR REPLACE PACKAGE BODY CustomerPackage AS



  PROCEDURE InsertCustomersCurrVal(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    oCustomerID OUT NUMBER) IS 

  BEGIN

    INSERT INTO Customers

      (CompanyName, ContactName, Phone) 

      VALUES (iCompanyName, iContactName, iPhone);

    SELECT Customers_CustomerID_Seq.CURRVAL ←シーケンス番号取得

    INTO oCustomerID 

    FROM DUAL;

  END InsertCustomersCurrVal;

 

  PROCEDURE UpdateCustomersConcurrencyIDRc(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    iCustomerID IN NUMBER,

    iConcurrencyID IN NUMBER,

    oRowCount OUT NUMBER) IS 

  BEGIN

    UPDATE Customers

    SET CompanyName = iCompanyName,

        ContactName = iContactName,

        Phone = iPhone, 

        ConcurrencyID = ConcurrencyID+1

    WHERE CustomerID = iCustomerID 

        AND ConcurrencyID = iConcurrencyID;

    oRowCount := SQL%ROWCOUNT;

  END UpdateCustomersConcurrencyIDRc;

 

  PROCEDURE DeleteCustomersConcurrencyIDRc(

    iCustomerID IN NUMBER,

    iConcurrencyID IN NUMBER,

    oRowCount OUT NUMBER) IS 

  BEGIN

    DELETE 

    FROM Customers 

    WHERE CustomerID = iCustomerID

      AND ConcurrencyID = iConcurrencyID;

    oRowCount := SQL%ROWCOUNT;

  END DeleteCustomersConcurrencyIDRc;

END CustomerPackage;

Private Function UpdateRecord(ByVal strCompanyName As String, _
  ByVal strContactName As String, _
  ByVal strPhone As String, _
  ByVal intCustomerID As Integer) As Integer

  Dim intRetValue As Integer = 0
  Dim txn As OleDbTransaction
  Dim dr As DataRow = mdt.Rows.Find(intCustomerID)
  If Not (dr Is Nothing) Then
    dr("CompanyName") = strCompanyName
    dr("ContactName") = strContactName
    dr("Phone") = strPhone
    Try
      mcon.Open()
      txn = mcon.BeginTransaction
      mcb.GetInsertCommand.Transaction = txn
      mcb.GetUpdateCommand.Transaction = txn
      mcb.GetDeleteCommand.Transaction = txn
      mda.Update(mdt)
      txn.Commit()
      intRetValue = 1
    Catch ex As Exception
      If Not (txn Is Nothing) Then
        txn.Rollback()
      End If
      Response.Write(ex.Message.ToString)
    Finally
      mcon.Close()
    End Try
  End If
  Return intRetValue
End Function