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

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

 

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

 

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

 

DataGridから削除した行(レコード)をデータベースに反映するのにDataAdapterUpdateメソッドを使用する方法

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

DataGridから削除した行(レコード)をDataTableに反映させる方法

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

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

DataAdapterUpdateメソッドで使用するSQL文に楽観的ロックを組み込む方法

 

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

 

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

 

Private mcon As OracleConnection

Private mcb As OracleCommandBuilder

Private mda As OracleDataAdapter

Private mds As DataSet

Private Const mCacheKey = "Ch57DataGrid2DataSet"

 

 

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

 

Page_Loadイベントを以下のように書き換えます。OracleConnectionCoracleDataAdapterのインスタンスを生成したら、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 DeleteRecordの書き換え

 

Function DeleteRecordを以下のように書き換えます。DataTableRowsコレクションのFindメソッドで削除するレコードを検索したら、DataRowオブジェクトのDeleteメソッドでレコードを削除します。DataTableから削除したレコードをOracleデータベースに反映するには、OracleDataAdapterUpdateメソッドを使用します。

 

Private Function DeleteRecord(ByVal intCustomerID As Integer) As String

  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.Delete()

    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

  End If

  Return intRetValue

End Function

 

 

■解説

 

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

 

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

 

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

 

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

 

Findメソッドでレコードが見つかると戻り値としてDataRowが返されます。レコードが見つからないときは、Nullが返ります。レコードが見つかったときは、DataRowDeleteメソッドでレコードを削除します。

 

OracleDataAdapterUpdateメソッドを実行すると、DataTableに追加、更新、削除したレコードがOracleデータベースに反映されます。Updateメソッドの引数には、DataSetまたはDataTableを指定します。ここでは、DataTableを指定しています。

 

DataTableから削除されたレコードをOracleデータベースから削除中に、排他制御エラーが発生する可能性がありますのでUpdateメソッドは、Try...Catch...End Tryのブロックから実行します。排他制御エラーが発生したときは、Catch ex As DBConcurrencyExceptionに制御が渡ります。その他のエラーが発生したときは、Catch ex As Exceptionに制御が渡ります。

 

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

 

Private Function DeleteRecord(ByVal intCustomerID As Integer) As String

  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.Delete()

    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

  End If

  Return intRetValue

End Function

 

STEP UP

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

 

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

 

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)

  ・・・

 

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

 

DELETE

FROM Customers

WHERE CustomerID=:1

  AND ConcurrencyID=:2

 

 

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

 

Page_LoadイベントからOracleCommandBuilderを削除して代わりにOracleDataAdapterDeleteCommandを手動で生成するコードを追加します。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()

  mda.DeleteCommand = CreateDeleteCommand()

 

  If Not IsPostBack Then

    BindGrid()

  Else

    mds = CType(Session(mCacheKey), DataSet)

  End If

End Sub

 

2. Function CreateDeleteCommandを追加

 

クラスモジュールにFunction CreateDeleteCommandを追加します。このメソッドは、OracleDataAdapterUpdateメソッドがDataTableから削除されたレコードをOracleデータベースに反映するためのDELETE文を手動で生成します。

 

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

 

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

 

Private Function CreateDeleteCommand() As OracleCommand

  Dim sbSQL As New System.Text.StringBuilder

 

  With sbSQL

    .Append("DELETE FROM Customers " & vbCrLf)

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

    .Append("ConcurrencyID=:2")

  End With

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

  Dim pc As OracleParameterCollection = cmd.Parameters

  Dim p As OracleParameter

  With pc

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

    p.SourceVersion = DataRowVersion.Original

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

    p.SourceVersion = DataRowVersion.Original

  End With

  Return cmd

End Function

 

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

 

 

 



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

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