●DataAdapterのUpdateメソッドでデータベースに反映する – SQL 自動生成 (ch57DataGrid2.aspx)
DataGridから削除した行（レコード）をOracleデータベースから削除するのに、OracleDataAdapterのUpdateメソッドを使用します。Updateメソッドが使用するSQL文は、OracleCommandBuilderで自動生成します。OracleCommandBuilderは、OracleDataAdapterのSelectCommnadに格納されているSELECT文を基に追加、更新、削除のSQL文を生成します。
このサンプルでは、以下のノウハウを習得することができます。
▼DataGridから削除した行（レコード）をデータベースに反映するのにDataAdapterのUpdateメソッドを使用する方法
▼CommandBuilderでUpdateメソッドで使用するSQL文を自動作成する方法
▼DataGridから削除した行（レコード）をDataTableに反映させる方法
▼DataAdapterのUpdateメソッドで同時更新エラー発生時の再試行処理
▼DataAdapterのUpdateメソッドで使用するSQL文を手動で作成する方法
▼DataAdapterのUpdateメソッドで使用する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イベントを以下のように書き換えます。OracleConnectionとCoracleDataAdapterのインスタンスを生成したら、OracleCommandBuilderのインスタンスを生成して追加、更新、削除用のSQL文を生成します。
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles 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を以下のように書き換えます。DataTableのRowsコレクションのFindメソッドで削除するレコードを検索したら、DataRowオブジェクトのDeleteメソッドでレコードを削除します。DataTableから削除したレコードをOracleデータベースに反映するには、OracleDataAdapterのUpdateメソッドを使用します。
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
■解説
OracleDataAdapterのUpdateメソッドでDataSet(DataTable)に追加、更新、削除したレコードをOracleデータベースに反映するには、OracleCommandBuilderで追加、更新、削除するSQL文を生成します。OracleCommandBuilderは、OracleDataAdapterのSelectCommandプロパティに格納されているSELECT文を基にこれらのSQLを生成します。
OracleDataAdapterのUpdatelメソッドを使用するには、OracleデータベースのCustomers表から直接レコードを削除する代わりにDataTableに格納されているレコードを削除します。
DataTableからレコードを削除するには、DataSetのTablesコレクションからCustomers表のDataTableを作成します。DataTableから削除するレコードを検索するには、DataTableのRowsコレクションのFindメソッドを使用します。Findメソッドの引数には、Customers表の主キーを指定します。DataTableに主キーを追加するには、DataTableのPrimaryKeyプロパティにCustomerIDのDataColumnを設定します。
dt.PrimaryKey = New DataColumn() {dt.Columns("CustomerID")}
Findメソッドでレコードが見つかると戻り値としてDataRowが返されます。レコードが見つからないときは、Nullが返ります。レコードが見つかったときは、DataRowのDeleteメソッドでレコードを削除します。
OracleDataAdapterのUpdateメソッドを実行すると、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を削除して代わりにOracleDataAdapterのDeleteCommandを手動で生成するコードを追加します。SELECT文には「*」を指定してCustomers表からすべての列を抽出します。 Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs)
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を追加します。このメソッドは、OracleDataAdapterのUpdateメソッドが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文をプログラム内で確保していますが、ストアドプロシージャを使用すると楽観的ロックがさらに高速化されます。OracleDataAdapterのUpdateメソッドでストアドプロシージャを利用する方法については、後述します。 |