●DataAdapterのUpdateメソッドでストアドプロシージャを使用する (ch58DataGrid5.aspx)

 

OracleDataAdapterオブジェクトのUpdateメソッドで、ストアドプロシージャを使用するように改善して高速化します。

 

Customers表から行(レコード)を抽出するには、パッケージ(CustomerPackage)に登録されているストアドプロシージャ(GetCustomersGT40)を使用します。Customers表に新規行を追加するにはストアドプロシージャ(InsertCustomersCurrVal)、行を更新するにはストアドプロシージャ(UpdateCustomersConcurrencyID)、行を削除するにはストアドプロシージャ(DeleteCustomersConcurrencyID)を使用します。

 

iSQL*PlusまたはSQL*Plusを起動して、事前にパッケージ仕様部(C:\vbora\sql\CustomerPackage.sql)とパッケージ本体部(C:\vbora\sql\CustomerPackageBody.sql)を作成してください。

 

パッケージ仕様部(CustomerPackage.sql)

CREATE OR REPLACE PACKAGE CustomerPackage AS

  TYPE rcurCustomers IS REF CURSOR;

  PROCEDURE GetCustomersGT40(

    orcurCustomers OUT rcurCustomers);

  PROCEDURE InsertCustomersCurrVal(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    oCustomerID OUT NUMBER);

  PROCEDURE UpdateCustomersConcurrencyID(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    iCustomerID IN NUMBER,

    iConcurrencyID IN NUMBER);

  PROCEDURE DeleteCustomersConcurrencyID(

    iCustomerID IN NUMBER,

    iConcurrencyID IN NUMBER);

END CustomerPackage;

 

パッケージ本体部(CustomerPackageBody.sql)

CREATE OR REPLACE PACKAGE BODY CustomerPackage AS

  PROCEDURE GetCustomersGT40(

    orcurCustomers OUT rcurCustomers) IS

  BEGIN

    OPEN orcurCustomers FOR

      SELECT *

FROM Customers

      WHERE CustomerID > 40

      ORDER BY CustomerID;

  END GetCustomersGT40;

  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 UpdateCustomersConcurrencyID(

    iCompanyName IN VARCHAR2,

    iContactName IN VARCHAR2,

    iPhone IN VARCHAR2,

    iCustomerID IN NUMBER,

    iConcurrencyID IN NUMBER) IS

  BEGIN

    UPDATE Customers

      SET CompanyName = iCompanyName,

        ContactName = iContactName,

        Phone = iPhone,

        ConcurrencyID = ConcurrencyID+1

      WHERE CustomerID = iCustomerID

        AND ConcurrencyID = iConcurrencyID;

  END UpdateCustomersConcurrencyID;

  PROCEDURE DeleteCustomersConcurrencyID(

    iCustomerID IN NUMBER,

    iConcurrencyID IN NUMBER) IS

  BEGIN

    DELETE

    FROM Customers

    WHERE CustomerID = iCustomerID

      AND ConcurrencyID = iConcurrencyID;

  END DeleteCustomersConcurrencyID;

END CustomerPackage;

 

このサンプルでは、以下のノウハウを習得することができます。

 

▼DataAdapterのUpdateメソッドでストアドプロシージャを使用する方法

▼DataAdapterのUpdateメソッドにトランザクション処理を適用する方法

▼DataAdapterのSelectCommandを手動で作成する方法

▼DataAdapterのInsertCommandを手動で作成する方法

▼DataAdapterのUpdateCommandを手動で作成する方法

▼DataAdapterのDeleteCommandを手動で作成する方法

▼OracleCommandオブジェクトのUpdateRowSourceプロパティを使用してDataTableとデータベースを同期させる方法

▼SELECT SEQUENCE.CURRVALの使い方

 

1. モジュールレベルの変数書き換え

 

ch58DataGrid2.aspxのコードビューを表示したら、Sub Page_Loadイベントの直前に以下の変数を追加します。

 

Private mcon As OracleConnection

Private mda As OracleDataAdapter

Private mdt As DataTable

Private Const mCacheKey = "Ch58DataGrid5DataTable"

 

2. Page_Loadイベントの書き換え

 

Page_Loadイベントを以下にように書き換えます。OracleCommandBuilderでSQL文を自動生成する代わりに、CreateSelectCommand、CreateInsertCommand、CreateUpdateCommand、CreateDeleteCommandメソッドを実行して手動で作成します。

 

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)

cccHandles MyBase.Load

  mcon = New OracleConnection(ConfigurationSettings.AppSettings("conStringOraNw"))

  mda = New OracleDataAdapter

 

  mda.SelectCommand = CreateSelectCommand()

  mda.InsertCommand = CreateInsertCommand()

  mda.UpdateCommand = CreateUpdateCommand()

  mda.DeleteCommand = CreateDeleteCommand()

 

  If Not IsPostBack Then

    BindGrid()

  Else

    mdt = CType(Session(mCacheKey), DataTable)

  End If

End Sub

 

3. UpdateCommandイベントの書き換え

 

DataGrid1_UpdateCommandイベントを以下のように書き換えます。 mdt = Nothingのコードをコメントにして、DataTableをリフレッシュしないようにします。

 

 

Private Sub DataGrid1_UpdateCommand(ByVal source As Object,

cccByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)

cccHandles DataGrid1.UpdateCommand

  Dim strCompanyName As String = CType(e.Item.Cells(1).Controls(0), TextBox).Text

  Dim strContactName As String = CType(e.Item.Cells(2).Controls(0), TextBox).Text

  Dim strPhone As String = CType(e.Item.Cells(3).Controls(0), TextBox).Text

 

  If Me.AddingNew Then

    InsertRecord(strCompanyName, strContactName, strPhone)

  Else

    Dim intCustomerID As Integer = DataGrid1.DataKeys(e.Item.ItemIndex)

    UpdateRecord(strCompanyName, strContactName, strPhone, intCustomerID)

  End If

 

  Me.AddingNew = False

  DataGrid1.EditItemIndex = -1

  ' mdt = Nothing ←ここをコメントにする

  BindGrid()

End Sub

 

 

5. Function CreateSelectCommandを追加

 

クラスモジュールの最後に、CreateSelectCommandを追加します。ストアドプロシージャ(CustomerPackage.GetCustomersGT40)を使用してCustomers表から行を抽出します。

 

Private Function CreateSelectCommand() As OracleCommand

  Dim cmd As New OracleCommand("CustomerPackage.GetCustomersGT40", mcon)

  cmd.CommandType = CommandType.StoredProcedure

  cmd.BindByName = True

  Dim pc As OracleParameterCollection = cmd.Parameters

  With pc

    .Add("orcurCustomers", OracleDbType.RefCursor, ParameterDirection.Output)

  End With

  Return cmd

End Function

 

6. Function CreateInsertCommandを追加

 

クラスモジュールの最後に、CreateInsertCommandを追加します。ストアドプロシージャ(CustomerPackage.InsertCustomersCurrVal)を使用してCustomers表に行を追加します。OracleCommandオブジェクトのUpdatedRowSourceプロパティにUpdateRowSource.OutputParametersを設定して、出力パラメータ(CustomerID)でDataTableのCustomerIDを更新します。つまり、DataTableのCustomerIDとOracleデータベースのCustomers表のCustomerIDを同期させます。

 

Private Function CreateInsertCommand() As OracleCommand

  Dim cmd As New OracleCommand("CustomerPackage.InsertCustomersCurrVal", mcon)

  cmd.CommandType = CommandType.StoredProcedure

  cmd.BindByName = True

  cmd.UpdatedRowSource = UpdateRowSource.OutputParameters

  Dim pc As OracleParameterCollection = cmd.Parameters

  Dim p As OracleParameter

  With pc

    .Add("iCompanyName", OracleDbType.Varchar2, 40, "CompanyName")

    .Add("iContactName", OracleDbType.Varchar2, 30, "ContactName")

    .Add("iPhone", OracleDbType.Varchar2, 24, "Phone")

    p = .Add("oCustomerID", OracleDbType.Int32, 10, "CustomerID")

    p.Direction = ParameterDirection.Output

  End With

  Return cmd

End Function

 

7. Function CreateUpdateCommandを追加

 

クラスモジュールの最後に、CreateUpdateCommandを追加します。ストアドプロシージャ(CustomerPackage.UpdateCustomersConcurrencyID)を使用してCustomers表の行を更新します。

 

Private Function CreateUpdateCommand() As OracleCommand

  Dim cmd As New OracleCommand("CustomerPackage.UpdateCustomersConcurrencyID", mcon)

  cmd.CommandType = CommandType.StoredProcedure

  cmd.BindByName = True

  Dim pc As OracleParameterCollection = cmd.Parameters

  Dim p As OracleParameter

  With pc

    .Add("iCompanyName", OracleDbType.NVarchar2, 40, "CompanyName")

    .Add("iContactName", OracleDbType.NVarchar2, 30, "ContactName")

    .Add("iPhone", OracleDbType.NVarchar2, 24, "Phone")

    p = .Add("iCustomerID", OracleDbType.Int32, 10, "CustomerID")

    p.SourceVersion = DataRowVersion.Original

    p = .Add("iConcurrencyID", OracleDbType.Int32, 10, "ConcurrencyID")

    p.SourceVersion = DataRowVersion.Original

  End With

  Return cmd

End Function

 

8. Function CreateDeleteCommandを追加

 

クラスモジュールの最後に、CreateDeleteCommandを追加します。ストアドプロシージャ(CustomerPackage.DeleteCustomersConcurrencyID)を使用してCustomers表から行を削除します。

 

Private Function CreateDeleteCommand() As OracleCommand

  Dim cmd As New OracleCommand("CustomerPackage.DeleteCustomersConcurrencyID", mcon)

  cmd.CommandType = CommandType.StoredProcedure

  cmd.BindByName = True

  Dim pc As OracleParameterCollection = cmd.Parameters

  Dim p As OracleParameter

  With pc

    p = .Add("iCustomerID", OracleDbType.Int32, 10, "CustomerID")

    p.SourceVersion = DataRowVersion.Original

    p = .Add("iConcurrencyID", OracleDbType.Int32, 10, "ConcurrencyID")

    p.SourceVersion = DataRowVersion.Original

  End With

  Return cmd

End Function

 

■解説

 

OracleDataAdapterオブジェクトのUpdateメソッドで使用するINSERT、UPDATE、DELETE用のSQL文をOracleCommandBuilderで自動生成する代わりに、ストアドプロシージャを使用して高速化します。また、OracleDataAdapterオブジェクトのFillメソッドで使用するSELECT文もストアドプロシージャに切り替えます。

 

OracleDataAdapterオブジェクトのSelectCommand、InsertCommand、UpdateCommand、DeleteCommandプロパティは、CreateSelectCommand、CreateInsertCommand、CreateUpdateCommand、CreateDeleteCommandメソッドを実行して書き換えます。

 

mda = New OracleDataAdapter

mda.SelectCommand = CreateSelectCommand()

mda.InsertCommand = CreateInsertCommand()

mda.UpdateCommand = CreateUpdateCommand()

mda.DeleteCommand = CreateDeleteCommand()

 

CreateSelectCommandメソッドは、パッケージ(CustomerPackage)に登録されているストアドプロシージャ(GetCustomersGT40)を使用します。このストアドプロシージャは、Customers表からCustomerIDが40以上の行（レコード）を抽出します。

 

TYPE rcurCustomers IS REF CURSOR;

 

PROCEDURE GetCustomersGT40(

  orcurCustomers OUT rcurCustomers) IS

BEGIN

  OPEN orcurCustomers FOR

    SELECT *

    FROM Customers

    WHERE CustomerID > 40

    ORDER BY CustomerID;

END GetCustomersGT40;

 

OracleCommandのインスタンスを生成したら、CommandTypeプロパティにCommandType.StoredProcedure、BindByNameプロパティにTrueを設定します。このストアドプロシージャには、REF CURSORの出力パラメータが宣言されていますので、ParametersコレクションのAddメソッドで出力パラメータorcurCustomersを追加します。最後に、OracleCommandのオブジェクトを戻り値として返します。

 

Private Function CreateSelectCommand() As OracleCommand

  Dim cmd As New OracleCommand("CustomerPackage.GetCustomersGT40", mcon)

  cmd.CommandType = CommandType.StoredProcedure

  cmd.BindByName = True

  Dim pc As OracleParameterCollection = cmd.Parameters

  With pc

    .Add("orcurCustomers", OracleDbType.RefCursor, ParameterDirection.Output)

  End With

  Return cmd

End Function

 

CreateInsertCommandメソッドは、パッケージ(CustomerPackage)に登録されているストアドプロシージャ(InsertCustomersCurrVal)を使用します。このストアドプロシージャは、Customers表に新規行（レコード）を追加します。Customers表のCustomerIDは、SEQUENCE + TRIGGERを使用して自動採番します。自動採番されたCustomersIDは、出力パラメータに設定して返します。

 

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;

 

OracleCommandのインスタンスを生成したら、CommandTypeプロパティにCommandType.StoredProcedure、BindByNameプロパティにTrueを設定します。さらに、UpdateRowSourceプロパティにUpdateRowSource.OutputParametersを設定してDataTableのCustomerIDを出力パラメータの値で更新するようにします。出力パラメータには、Customers表に追加された行のCustomerID（自動採番）が設定されます。出力パラメータに自動採番されたCustomerIDを返すようにすると、DataTableのCustomerIDとCustomers表のCustomerIDを同期させることができます。なお、DataTableとCustomers表を同期させるには、すでに解説したOracleDataAdapterのUpdatedRowイベントを利用する方法もあります。

 

ParametersコレクションのAddメソッドでiCompanyName、iContatName、iPhone、oCustomerIDのパラメータを追加します。oCustomerIDは、ParameterオブジェクトのDirectionプロパティにParameterDirection.Outputを設定して出力パラメータとします。出力パラメータは、DataTableのCustomerIDを更新するために利用します。最後に、OracleCommandのオブジェクトを戻り値として返します。

 

Private Function CreateInsertCommand() As OracleCommand

  Dim cmd As New OracleCommand("CustomerPackage.InsertCustomersCurrVal", mcon)

  cmd.CommandType = CommandType.StoredProcedure

  cmd.BindByName = True

  cmd.UpdatedRowSource = UpdateRowSource.OutputParameters

  Dim pc As OracleParameterCollection = cmd.Parameters

  Dim p As OracleParameter

  With pc

    .Add("iCompanyName", OracleDbType.Varchar2, 40, "CompanyName")

    .Add("iContactName", OracleDbType.Varchar2, 30, "ContactName")

    .Add("iPhone", OracleDbType.Varchar2, 24, "Phone")

    p = .Add("oCustomerID", OracleDbType.Int32, 10, "CustomerID")

    p.Direction = ParameterDirection.Output

  End With

  Return cmd

End Function

 

CreateUpdateCommandメソッドは、パッケージ(CustomerPackage)に登録されているストアドプロシージャ(UpdateCustomersConcurrencyID)を使用します。このストアドプロシージャは、Customers表の行（レコード）を更新します。WHERE句に主キー(CustomeID)と更新回数(ConcurrencyID)の条件を指定して、他のユーザーからすでに更新されているときは上書きしないように考慮しています。

 

PROCEDURE UpdateCustomersConcurrencyID(

  iCompanyName IN VARCHAR2,

  iContactName IN VARCHAR2,

  iPhone IN VARCHAR2,

  iCustomerID IN NUMBER,

  iConcurrencyID IN NUMBER) IS

BEGIN

  UPDATE Customers

  SET CompanyName = iCompanyName,

      ContactName = iContactName,

      Phone = iPhone,

      ConcurrencyID = ConcurrencyID+1

  WHERE CustomerID = iCustomerID

      AND ConcurrencyID = iConcurrencyID;

END UpdateCustomersConcurrencyID;

 

OracleCommandのインスタンスを生成したら、CommandTypeプロパティにCommandType.StoredProcedure、BindByNameプロパティにTrueを設定します。ParametersコレクションのAddメソッドでiCompanyName、iContatName、iPhone、iCustomerID、iConcurrencyIDのパラメータを追加します。パラメータiCustomerIDとiConcurrencyIDのSourceVersionプロパティには、DataRowVersion.Originalを設定して更新前の値を代入するようにします。最後に、OracleCommandのオブジェクトを戻り値として返します。

 

Private Function CreateUpdateCommand() As OracleCommand

  Dim cmd As New OracleCommand("CustomerPackage.UpdateCustomersConcurrencyID", mcon)

  cmd.CommandType = CommandType.StoredProcedure

  cmd.BindByName = True

  Dim pc As OracleParameterCollection = cmd.Parameters

  Dim p As OracleParameter

  With pc

    .Add("iCompanyName", OracleDbType.NVarchar2, 40, "CompanyName")

    .Add("iContactName", OracleDbType.NVarchar2, 30, "ContactName")

    .Add("iPhone", OracleDbType.NVarchar2, 24, "Phone")

    p = .Add("iCustomerID", OracleDbType.Int32, 10, "CustomerID")

    p.SourceVersion = DataRowVersion.Original

    p = .Add("iConcurrencyID", OracleDbType.Int32, 10, "ConcurrencyID")

    p.SourceVersion = DataRowVersion.Original

  End With

  Return cmd

End Function

 

CreateDeleteCommandメソッドは、パッケージ(CustomerPackage)に登録されているストアドプロシージャ(DeleteCustomersConcurrencyID)を使用します。このストアドプロシージャは、Customers表から行（レコード）を削除します。WHERE句に主キー(CustomeID)と更新回数(ConcurrencyID)の条件を指定して、他のユーザーからすでに更新されているときは削除しないように考慮しています。

 

PROCEDURE DeleteCustomersConcurrencyID(

  iCustomerID IN NUMBER,

  iConcurrencyID IN NUMBER) IS

BEGIN

  DELETE

  FROM Customers

  WHERE CustomerID = iCustomerID

    AND ConcurrencyID = iConcurrencyID;

END DeleteCustomersConcurrencyID;

 

OracleCommandのインスタンスを生成したら、CommandTypeプロパティにCommandType.StoredProcedure、BindByNameプロパティにTrueを設定します。ParametersコレクションのAddメソッドでiCustomerID、iConcurrencyIDのパラメータを追加します。パラメータiCustomerIDとiConcurrencyIDのSourceVersionプロパティには、DataRowVersion.Originalを設定して更新前の値を代入するようにします。最後に、OracleCommandのオブジェクトを戻り値として返します。

 

Private Function CreateDeleteCommand() As OracleCommand

  Dim cmd As New OracleCommand("CustomerPackage.DeleteCustomersConcurrencyID", mcon)

  cmd.CommandType = CommandType.StoredProcedure

  cmd.BindByName = True

  Dim pc As OracleParameterCollection = cmd.Parameters

  Dim p As OracleParameter

  With pc

    p = .Add("iCustomerID", OracleDbType.Int32, 10, "CustomerID")

    p.SourceVersion = DataRowVersion.Original

    p = .Add("iConcurrencyID", OracleDbType.Int32, 10, "ConcurrencyID")

    p.SourceVersion = DataRowVersion.Original

  End With

  Return cmd

End Function

 

STEP UP

トランザクション処理を追加する (ch58DataGrid6.aspx)

 

OracleDataAdapterオブジェクトのUpdateメソッドで、DataTableに追加、更新、削除されたレコードをOracleデータベースに反映するときトランザクションを適用するには、ch58DataGrid5.aspxのInsertRecord、UpdateRecord、DeleteRecordメソッドを以下のように書き換えます。

 

UpdateメソッドにTransactionを適用するには、OracleConnectionオブジェクトのOpenメソッドからCloseメソッドまでの一連の処理をTry...Catch...Finallyブロックに記述します。

 

Tryブロックでは、OracleConnectionオブジェクトのOpenメソッドでOracleデータベースを開きます。次に、BeginTransactionメソッドでトランザクションの開始を宣言します。OracleDataAdapterオブジェクトのUpdateメソッドを実行してDataTableに追加、修正、削除されたレコードをOracleデータベースに反映します。Updateメソッドが正常に終了したら、OracleTransactionオブジェクトのCommitメソッドで確定させます。

 

Catchブロックでは、OracleTransactionオブジェクトが作成されているとき、Rollbackメソッドでトランザクション開始時点に復元します。

 

Finallyブロックでは、OracleConnectionオブジェクトのCloseメソッドでOracleデータベースを閉じます。

 

Private Function InsertRecord(ByVal strCompanyName As String, _

  ByVal strContactName As String, _

  ByVal strPhone As String) As Integer

 

  Dim txn As OracleTransaction

  Dim intRetValue As Integer = 0

  Dim dr As DataRow = mdt.Rows(mdt.Rows.Count - 1)

  dr("CompanyName") = strCompanyName

  dr("ContactName") = strContactName

  dr("Phone") = strPhone

  Try

    mcon.Open()

    txn = mcon.BeginTransaction

    mda.Update(mdt)

    txn.Commit()

    intRetValue = 1

  Catch ex As Exception

    If Not (txn Is Nothing) Then

      txn.Rollback()

      mdt = Nothing

    End If

    Response.Write(ex.Message.ToString)

  Finally

    mcon.Close()

  End Try

  Return intRetValue

End Function

 

 

Private Function UpdateRecord(ByVal strCompanyName As String, _

  ByVal strContactName As String, _

  ByVal strPhone As String, _

  ByVal intCustomerID As Integer) As Integer

 

  Dim txn As OracleTransaction

  Dim intRetValue As Integer = 0

  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

      mda.Update(mdt)

      txn.Commit()

      intRetValue = 1

    Catch ex As Exception

      If Not (txn Is Nothing) Then

        txn.Rollback()

        mdt = Nothing

      End If

      Response.Write(ex.Message.ToString)

    Finally

      mcon.Close()

    End Try

  End If

  Return intRetValue

End Function

 

Private Function DeleteRecord(ByVal intCustomerID As Integer) As String

  Dim txn As OracleTransaction

  Dim intRetValue As Integer = 0

  Dim dr As DataRow = mdt.Rows.Find(intCustomerID)

  If Not (dr Is Nothing) Then

    dr.Delete()

    Try

      mcon.Open()

      txn = mcon.BeginTransaction

      mda.Update(mdt)

      txn.Commit()

      intRetValue = 1

    Catch ex As Exception

      If Not (txn Is Nothing) Then

        txn.Rollback()

        mdt = Nothing

      End If

      Response.Write(ex.Message.ToString)

    Finally

      mcon.Close()

    End Try

  End If

Return intRetValue

End Function