ASP.NET + Oracle Part3 のホームへ戻る

DataAdapterUpdateメソッドでデータベースに反映するSQL自動生成 (ch56DataGrid2.aspx)

 

DataGridから[編集][確定]をクリックして編集した行(レコード)をOracleデータベースに反映するのに、OracleDataAdapterUpdateメソッドを使用します。Updateメソッドが使用する追加、更新、削除のSQL文は、OracleCommandBuilderで自動生成します。OracleCommandBuilderは、OracleDataAdapterSelectedCommnadに格納されているSELECT文を基に追加、更新、削除のSQL文を生成します。

 

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

 

DataGridから編集した行(レコード)をDataAdapterUpdateメソッドでデータベースに反映する方法

OracleCommandBuilderで追加、更新、削除用のSQL文を自動生成する方法

DataSetオブジェクトをセッションステートに保存する方法

DataGridから編集した行(レコード)をDataTableに反映する方法

DataAdapterUpdateメソッドで同時更新エラー発生時の処理

DataAdapterUpdateメソッドで使用するSQL文を手動で作成する方法

 

 

1. モジュールレベルの変数追加

 

ch56DataGrid1.aspxのコードビューを表示したら、Sub Page_Loadイベントの直前に以下の変数を追加します。ここで宣言した変数は、イベントとメソッドで共有します。

 

Private mcon As OracleConnection

Private mcb As OracleCommandBuilder

Private mda As OracleDataAdapter

Private mds As DataSet

Private Const mCacheKey = "Ch56DataGrid2DataSet"

 

 

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

 

Page_Loadイベントを以下のように書き換えます。OracleConnectionOracleDataAdapterのインスタンスを生成したら、OracleCommandBuilderのインスタンスを生成して追加、更新、削除用のSQLを自動生成します。

 

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

cccHandles MyBase.Load

  Dim strSQL As String = "SELECT CustomerID, CompanyName, " & _

    "ContactName, Phone FROM Customers" & _

    "WHERE CustomerID > 40 " & _

    "ORDER BY Customers"

 

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

  mda = New OracleDataAdapter(strSQL, mcon)

  mcb = New OracleCommandBuilder(mda)

 

  If Not IsPostBack Then

    BindGrid()

  Else

    mds = CType(Session(mCacheKey), DataSet)

  End If

End Sub

 

3. Sub BindGridの書き換え

 

Sub BindGridを以下のように書き換えます。DataSetをセッションステートに格納して高速化します。

 

Private Sub BindGrid()

  If mds Is Nothing Then

    mds = CreateDataSet()

    Session(mCacheKey) = mds

  End If

  With DataGrid1

    .DataSource = mds

    .DataKeyField = "CustomerID"

    .DataBind()

  End With

End Sub

 

4. Function CreateDataSetの書き換え

 

Function CreateDataSetを以下のように書き換えます。

 

Private Function CreateDataSet() As DataSet

  mds = New DataSet

  mda.Fill(mds, "Customers")

  Return mds

End Function

 

5. Function UpdateRecordの書き換え

 

Function UpdateRecordを以下のように書き換えます。DataTableRowsコレクションのFindメソッドでレコードを検索して更新します。DataTable上で更新したレコードをOracleデータベースに反映するには、OracleDataAdapterUpdateメソッドを実行します。

 

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 dt As DataTable = mds.Tables("Customers")

 

  dt.PrimaryKey = New DataColumn() {dt.Columns("CustomerID")}

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

  If Not (dr Is Nothing) Then

    dr("CompanyName") = strCompanyName

    dr("ContactName") = strContactName

    dr("Phone") = strPhone

    Try

      mda.Update(dt)

      intRetValue = 1

    Catch ex As Exception

      Response.Write(ex.Message.ToString)

    End Try

  End If

  Return intRetValue

End Function

 

 

■解説

 

OracleDataAdapterUpdateメソッドでDataSet(DataTable)に追加、更新、削除されたレコードをOracleデータベースに反映するには、OracleCommandBuilderでレコードを追加、更新、削除するSQL文を生成します。OracleCommandBuilderは、OracleDataAdapterSelectCommandプロパティに格納されているSELECT文を基にこれらのSQLを生成します。

 

このサンプルは、SelectCommandプロパティに以下のようなSELECT文を格納します。

 

SELECT CustomerID,

  CompanyName,

  ContactName,

  Phone

FROM Customers

WHERE CustomerID > 40

ORDER BY CustomerID

 

OracleCommandBuilderを実行すると、以下のようなSQL文が自動生成されます。

 

InsertCommand

INSERT INTO "CUSTOMERS"

  ( "CUSTOMERID",

    "COMPANYNAME",

    "CONTACTNAME",

    "PHONE")

VALUES ( :1, :2, :3, :4)

 

UpdateCommand

UPDATE "CUSTOMERS"

SET "CUSTOMERID"=:1,

    "COMPANYNAME"=:2,

    "CONTACTNAME"=:3,

    "PHONE"=:4

WHERE "CUSTOMERID"=:5

  AND ((:6 = 1 AND "COMPANYNAME" IS NULL) OR "COMPANYNAME"=:7)

  AND ((:8 = 1 AND "CONTACTNAME" IS NULL) OR "CONTACTNAME"=:9)

  AND ((:10 = 1 AND "PHONE" IS NULL) OR "PHONE"=:11)

 

DeleteCommand

DELETE

FROM "CUSTOMERS"

WHERE "CUSTOMERID"=:1

  AND ((:2 = 1 AND "COMPANYNAME" IS NULL) OR "COMPANYNAME"=:3)

  AND ((:4 = 1 AND "CONTACTNAME" IS NULL) OR "CONTACTNAME"=:5)

  AND ((:6 = 1 AND "PHONE" IS NULL) OR "PHONE"=:7)

 

UPDATEDELETE文のWHERE句に主キー(CustomerID)の他に、CompanyNameContactNamePhoneの条件式が追加されています。この条件式は、レコードの得意先、担当、電話の列が他のユーザーから変更されていなか調べています。つまり、Updateメソッドは、楽観的ロック[1]を適用してレコードを更新、削除します。

 

OracleDataAdapterUpdatelメソッドを使用するには、OracleデータベースのCustomers表を直接更新する代わりにDataTableに格納されているレコードを更新します。

 

DataSetTablesコレクションからCustomers表のDataTableを作成します。DataTableから更新するレコードを検索するには、DataTableRowsコレクションのFindメソッドを使用します。Findメソッドの引数には、Customers表の主キーを指定します。DataTableに主キーを追加するには、DataTablePrimaryKeyプロパティにCustomerIDDataColumnを設定します。

 

dt.PrimaryKey = New DataColumn() {dt.Columns("CustomerID")}

 

Findメソッドでレコードが見つかると戻り値としてDataRowが返されます。レコードが見つからないときは、Nullが返ります。レコードが見つかったときは、DataRowの得意先(CompanyName)、担当(ContactName)、電話(Phone)の列を更新します。

 

OracleDataAdapterUpdateメソッドを実行すると、DataTableに追加、更新、削除されたレコードがOracleのデータベースに反映されます。Updateメソッドの引数には、DataSetまたはDataTableを指定します。ここでは、DataTableを指定しています。DataTableのレコードを更新、削除中に排他制御エラーが発生する可能性がありますので、Updateメソッドは、Try...Catch...End Tryのブロックから実行します。排他制御エラーが発生すると、Catchに制御が渡ります。このサンプルでは、エラーメッセージを表示していますが、ここでエラーの詳細を調べて排他制御エラーのときは再試行のメッセージを表示するなどの対処が必要です。

 

排他制御エラーとその他のエラーを区別するには、Try...Catch...End Tryを以下のように書き換えます。

 

Try

  mda.Update(dt)

  intRetValue = 1

Catch ex As DBConcurrencyException

  Response.Write("排他制御エラー発生!<br>再試行してください.<br>")

Catch ex As Exception

  Response.Write(ex.Message.ToString)

End Try

 

UpdateRecordメソッドからは、戻り値として「1」または「0」が返されます。戻り値が「1」のときは、レコードが正常にOracleデータベースに反映されたことを意味します。戻り値が「0」のときは、エラー(排他制御エラーも含む)が発生したことを意味します。

 

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 dt As DataTable = mds.Tables("Customers")

 

  dt.PrimaryKey = New DataColumn() {dt.Columns("CustomerID")}

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

  If Not (dr Is Nothing) Then

    dr("CompanyName") = strCompanyName

    dr("ContactName") = strContactName

    dr("Phone") = strPhone

    Try

      mda.Update(dt)

      intRetValue = 1

    Catch ex As Exception

      Response.Write(ex.Message.ToString)

    End Try

  End If

  Return intRetValue

End Function

 

 

STEP UP

楽観的ロックを高速化するには (ch56DataGrid3.aspx)

 

ch56DataGrid2.aspxのサンプルは、OracleCommandBuilderが自動生成したUPDATE文を使用してDataTableのレコードをOracleデータベースに反映しています。OracleCommandBuilderが生成したSQL文は、Customers表の列を比較して他のユーザーから更新されていないか調べます。たとえば、SELECT文に「*」を指定するとUPDATE文のWHERE句にはCutomers表のすべての列が追加されます。

 

UPDATE "CUSTOMERS"

SET "CUSTOMERID"=:1,

    "COMPANYNAME"=:2,

    "CONTACTNAME"=:3,

    "PHONE"=:4

WHERE "CUSTOMERID"=:5

  AND ((:6 = 1 AND "COMPANYNAME" IS NULL) OR "COMPANYNAME"=:7)

  AND ((:8 = 1 AND "CONTACTNAME" IS NULL) OR "CONTACTNAME"=:9)

  AND ((:10 = 1 AND "PHONE" IS NULL) OR "PHONE"=:11)

 ・・・

 

ここでは、WHERE句ですべての列を比較する代わりに主キーの得意先ID(CustomerID)と更新回数(ConcurrencyID)の列のみ比較して、楽観的ロックを実現します。

 

UPDATE Customers

SET CompanyName=:1,

    ContactName=:2,

    Phone=:3,

    ConcurrencyID=ConcurrencyID+1

WHERE CustomerID=:4

  AND ConcurrencyID=:5"

 

 

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

 

Page_LoadイベントからOracleCommandBuilderを削除して代わりにOracleDataAdapterUpdateCommandを手動で生成するコードを追加します。SELECT文には「*」を指定してCustomers表からすべての列を抽出します。

 

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

cccHandles MyBase.Load

  Dim strSQL As String = "SELECT * FROM Customers " & _

    "WHERE CustomerID > 40 " & _

    "ORDER BY CustomerID"

 

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

  mda = New OracleDataAdapter(strSQL, mcon)

  mda.UpdateCommand = CreateUpdateCommand()

  If Not IsPostBack Then

    BindGrid()

  Else

    mds = CType(Session(mCacheKey), DataSet)

  End If

End Sub

 

2. Function CreateUpdateCommandを追加

 

クラスモジュールにFunction CreateUpdateCommandを追加します。このメソッドは、OracleDataAdapterUpdateメソッドがDataTableの更新されたレコードをOracleデータベースに反映するときのUPDATE文を手動で生成します。

 

UPDATE文のパラメータ「:1」、「:2」、「:3」には、DataTableの得意先(CompanyName)、担当者(ContactName)、電話(Phone)の変更後の値を代入します。WHERE句のパラメータ「:4」と「:5」には、DataTableの得意先ID(CoustomerID)と更新回数(ConcurrencyID)の変更前の値を代入します。WHERE句のパラメータに変更前の値を代入することにより、レコードが他のユーザーから更新されているか調べることができます。

 

DataTable[2]から変更前の値を取得するには、OracleParameterオブジェクトのSourceVersionプロパティにDataRowVersion.Originalを設定します。SourceVersionプロパティのデフォルトは、DataRowVersion.Currentに設定されています。

 

Private Function CreateUpdateCommand() As OracleCommand

  Dim sbSQL As New System.Text.StringBuilder

 

  With sbSQL

    .Append("UPDATE Customers " & vbCrLf)

    .Append("SET CompanyName=:1, " & vbCrLf)

    .Append("ContactName=:2, " & vbCrLf)

    .Append("Phone=:3, " & vbCrLf)

    .Append("ConcurrencyID=ConcurrencyID+1 " & vbCrLf)

    .Append("WHERE CustomerID=:4 AND " & vbCrLf)

    .Append("ConcurrencyID=:5")

  End With

  Dim cmd As New OracleCommand(sbSQL.ToString, mcon)

  Dim pc As OracleParameterCollection = cmd.Parameters

  Dim p As OracleParameter

  With pc

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

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

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

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

    p.SourceVersion = DataRowVersion.Original

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

    p.SourceVersion = DataRowVersion.Original

  End With

  Return cmd

End Function

 

ここで紹介したサンプルは、UPDATE文をプログラム内で確保していますが、ストアドプロシージャを使用すると楽観的ロックがさらに高速化されます。OracleDataAdapterUpdateメソッドでストアドプロシージャを利用する方法については、後述します。

 

 

 



[1] ロックには、悲観的ロックと楽観的ロック方式がありますが、Webアプリケーションのようにステートレスのプロトコルを採用している場合は、楽観的ロックを適用するのが一般的です。

[2] DataTableDataRowには、OriginalCurrentProposed3種類のデータが格納されています。

ASP.NET + Oracle Part3 のホームへ戻る