●DataAdapterのUpdateメソッドでデータベースに反映する‐SQL自動生成 (ch56DataGrid2.aspx)
DataGridから[編集]→[確定]をクリックして編集した行（レコード）をOracleデータベースに反映するのに、OracleDataAdapterのUpdateメソッドを使用します。Updateメソッドが使用する追加、更新、削除のSQL文は、OracleCommandBuilderで自動生成します。OracleCommandBuilderは、OracleDataAdapterのSelectedCommnadに格納されているSELECT文を基に追加、更新、削除のSQL文を生成します。
このサンプルでは、以下のノウハウを習得することができます。
▼DataGridから編集した行（レコード）をDataAdapterのUpdateメソッドでデータベースに反映する方法
▼OracleCommandBuilderで追加、更新、削除用のSQL文を自動生成する方法
▼DataSetオブジェクトをセッションステートに保存する方法
▼DataGridから編集した行（レコード）をDataTableに反映する方法
▼DataAdapterのUpdateメソッドで同時更新エラー発生時の処理
▼DataAdapterのUpdateメソッドで使用する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イベントを以下のように書き換えます。OracleConnectionとOracleDataAdapterのインスタンスを生成したら、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 UpdateRecordの書き換え
Function UpdateRecordを以下のように書き換えます。DataTableのRowsコレクションのFindメソッドでレコードを検索して更新します。DataTable上で更新したレコードをOracleデータベースに反映するには、OracleDataAdapterのUpdateメソッドを実行します。
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
■解説
OracleDataAdapterのUpdateメソッドでDataSet(DataTable)に追加、更新、削除されたレコードをOracleデータベースに反映するには、OracleCommandBuilderでレコードを追加、更新、削除するSQL文を生成します。OracleCommandBuilderは、OracleDataAdapterのSelectCommandプロパティに格納されている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)
UPDATEとDELETE文のWHERE句に主キー(CustomerID)の他に、CompanyName、ContactName、Phoneの条件式が追加されています。この条件式は、レコードの得意先、担当、電話の列が他のユーザーから変更されていなか調べています。つまり、Updateメソッドは、楽観的ロック[1]を適用してレコードを更新、削除します。
OracleDataAdapterのUpdatelメソッドを使用するには、OracleデータベースのCustomers表を直接更新する代わりに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の得意先(CompanyName)、担当(ContactName)、電話(Phone)の列を更新します。
OracleDataAdapterのUpdateメソッドを実行すると、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を削除して代わりにOracleDataAdapterのUpdateCommandを手動で生成するコードを追加します。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() If Not
IsPostBack Then BindGrid()
Else mds = CType(Session(mCacheKey),
DataSet) End If End Sub 2. Function
CreateUpdateCommandを追加 クラスモジュールにFunction
CreateUpdateCommandを追加します。このメソッドは、OracleDataAdapterのUpdateメソッドが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文をプログラム内で確保していますが、ストアドプロシージャを使用すると楽観的ロックがさらに高速化されます。OracleDataAdapterのUpdateメソッドでストアドプロシージャを利用する方法については、後述します。 |