第10章 データベースの更新の実行
「わからないことを信じているから、苦しむのさ。迷信はだめだ。」
スティービーワンダー
スティービーワンダーは、もちろんデータベースの更新のことを言っていたわけではないでしょうが、この言葉は確かにこのテーマにも当てはまります。ADO.NETのデータベース更新機能は非常に強力ですが、.NETがベータ版だったころに内外のニュースグループに寄せられた疑問や、セミナーなどで出された質問からすると、その強力な機能を本当に理解している開発者はごくわずかしかいないようです。
これまで目にしたADO.NETコードの中には、更新ロジックの生成をCommand Builderオブジェクトに頼っているものが少なくありません。開発者が自分で更新ロジックを用意した方がよいという注記が付いているコードもありますが、なぜそうするべきなのか、どうすればそうできるのかという説明はほとんどないのが現状です。
コードのしくみを聞かれると、肩をすくめ、ぎこちない笑顔で「とにかく動けばいいじゃない」と答える人があまりにも多いのではないでしょうか。この章と次の章では、そのような「迷信」まがいのコーディングからの卒業を目指したいと思います。
.gif){kind=small} |
現に、「とにかく動けばいい」というようなスローガンを掲げているテクニカルサポートもあります。残念ではありますが、これが現実です。 |
ADO.NETを使ってデータベースを更新する方法を理解すればするほど、独自の更新ロジックを生成したり、ストアドプロシージャによって更新を実行したりする作業が快適になります。この章では、DataAdapterを使って、DataSet内の保留状態の変更内容をデータベースに反映させる方法を取り上げます。さらに、パフォーマンスや機能を犠牲にすることなく、ツールを使って開発時間を短縮する方法、そしてどんな場合にそれが有効かについても見ていきましょう。
型指定のないDataSetオブジェクトや厳密に型指定されたDataSetオブジェクトを作成して、DataAdapterオブジェクトから返されるデータを格納する方法については、既に見たとおりです。また、DataSetの内容を変更する方法も既に取り上げました。この章では、その流れを踏まえ、DataAdapterオブジェクトを使って、DataSetに格納されている変更内容をデータベースに反映させるための基本的な方法を見ていきます。
たとえば、Northwindサンプルデータベースに1つの注文データがあるとします。図10.1は、SQL Serverのクエリアナライザでその注文データを抽出するためのクエリです。ここで、顧客から電話があり、この注文データの内容を変更したいという希望を伝えられたとしましょう。豆腐（Tofu）は人気がなく、唐辛子ソース（Hot Pepper Sauce）は飛ぶように売れており、チャイ（Chai Tea）の売れ行きも上々です。
.gif)
▲図10.1 Northwindデータベース内の1つの注文データの内容
「第5章 DataAdapterオブジェクトを使用したデータの取得」では、クエリ結果をDataSetに格納する方法を取り上げました。それを応用すれば、顧客の注文データをDataSetに取り込むためのアプリケーションを作成できます。また、「第6章 DataSetオブジェクトの処理」で見たとおり、そのアプリケーションでは、顧客の要求に応じてDataSet内のデータを変更することもできます。しかし、既に触れたように、DataSetの内容を変更しても、それだけでデータベース内の対応する行が変更されるわけではありません。
「第5章 DataAdapterオブジェクトを使用したデータの取得」で見たとおり、DataAdapterには、保留状態の変更内容をデータベースに反映させるためのUpdateメソッドが用意されています。そこで、注文データの変更をデータベースに反映するには、アプリケーションで次のようなコードを使用します。
Visual Basic .NET
' 注文データの内容をDataTableに取り込む。
Dim strConn, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=(local)￥NetSDK;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _
"FROM [Order Details] WHERE OrderID = 10503 " & _
"ORDER BY ProductID"
Dim da As New OleDbDataAdapter(strSQL, strConn)
Dim tbl As New DataTable("Order Details")
da.Fill(tbl)
' 注文データの内容を変更する。
tbl.Rows(0).Delete()
tbl.Rows(1)("Quantity") = CShort(tbl.Rows(1)("Quantity")) * 2
tbl.Rows.Add(New Object() {10503, 1, 24, 18})
' 保留状態の変更内容をデータベースに送信する。
Try
da.Update(tbl)
Console.WriteLine("新しい変更内容がデータベースに正しく反映されました。")
Catch ex As Exception
Console.WriteLine("DataAdapter.Updateの呼び出しで" & _
"例外が発生しました:" & vbCrLf & ex.Message)
End Try
Visual C# .NET
// 注文データの内容をDataTableに取り込む。
string strConn, strSQL;
strConn = "Provider=SQLOLEDB;Data Source=(local)￥￥NetSDK;" +
"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " +
"FROM [Order Details] WHERE OrderID = 10503 " +
"ORDER BY ProductID";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
DataTable tbl = new DataTable("Order Details");
da.Fill(tbl);
// 注文データの内容を変更する。
tbl.Rows[0].Delete();
tbl.Rows[1]["Quantity"] = (short) (tbl.Rows[1]["Quantity"]) * 2;
tbl.Rows.Add(new object[] {10503, 1, 24, 18});
// 保留状態の変更内容をデータベースに送信する。
try
{
da.Update(tbl);
Console.WriteLine("新しい変更内容がデータベースに正しく反映されました。");
}
catch (Exception ex)
{
Console.WriteLine("DataAdapter.Updateの呼び出しで例外が発生しました:￥n"
+ ex.Message);
}
ところが、このコードは正常にコンパイルされますが、注文データへの変更はデータベースに正しく反映されず、「更新には、削除された行を含むDataRowコレクションが渡されたとき、有効なDeleteCommandが必要です」という例外が出されます。
Microsoft .NET Frameworkがベータ版で評価されたとき、このような例外に戸惑いを感じた開発者が大勢いました。ADOなどの以前のデータアクセステクノロジには、変更内容をデータベースに自動的に反映させる機能が含まれていました。ADO.NETの場合は、DataAdapterオブジェクトを使ってデータベースを更新できますが、DataAdapterに更新ロジックが自動的に組み込まれるわけではありません。
では、ADO.NETのDataAdapterに更新ロジックを追加するには、どうすればよいのでしょうか。大きく分けて3つの方法があります。1つは、自分でコードを書くという方法、もう1つは、ADO.NETによって更新ロジックを自動生成するという方法、そして3つ目は、Visual Studio .NETのデータアダプタ構成ウィザードなどのコード生成ツールを使用するという方法です。
この章では、この3つの方法を取り上げて、それぞれの長所と短所を見ていきます。
10.1 | 歴史的な経緯
ADO.NETを使って保留状態の変更内容をデータベースに適用する方法を取り上げる前に、ADO.NETの前身であるADOではどうだったかということに少し触れておきましょう。ADO.NETでは更新ロジックが自動的に生成されませんが、ADOでは自動生成される機能がありました。ADOのカーソルエンジンが更新を「自動的に」実行する方法をざっと見ておけば、ADO.NETの開発チームがなぜやり方を変えて、開発者たちが自分で更新ロジックを書くように方針を転換したのかという経緯を理解できます。さらに、ADOのカーソルエンジンが変更内容をデータベースに反映させるしくみを理解すれば、ADO.NETで独自の更新ロジックを生成する方法を容易に理解できるようになります。
ADOカーソルエンジンでは、ADO.NETのDataSetによく似た機能がサポートされています。ADOのクライアントサイドのRecordsetオブジェクトは、オフラインのデータキャッシュとしての働きをしましたが、それだけではなく、データベースを更新するという役目も果たしました。
次に示すのは、先ほどの注文データの内容を取り出し、その内容を変更してから、保留状態の変更内容をデータベースに適用するコードです。
従来のVisual BasicとADO 2.x
Dim strConn As String, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=(local)￥NetSDK;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _
"FROM [Order Details] WHERE OrderID = 10503 " & _
"ORDER BY ProductID"
Dim cn As New ADODB.Connection
cn.Open(strConn)
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open strSQL, cn, adOpenStatic, adLockBatchOptimistic, _
adCmdText
rs.Delete
rs.MoveNext
rs.Fields("Quantity") = 2 * rs.Fields("Quantity")
rs.Update
rs.AddNew
rs.Fields("OrderID") = 10503
rs.Fields("ProductID") = 1
rs.Fields("Quantity") = 24
rs.Fields("UnitPrice") = 18
rs.Update
rs.UpdateBatch
rs.Close
cn.Close
このコードには、ADOのRecordsetを使ってデータベースを更新する処理の長所と短所がよく表われています。これから、その長所と短所を見ていきましょう。
10.1.1|ADO Recordsetオブジェクトを使用した更新の利点
この方法の利点としてまず挙げられるのは、コードの量が少なくてすむということです。要は、Recordsetを開き、その内容を修正し、変更内容をデータベースに適用するだけです。ほんの数行のコードで多くの処理を実行できるわけです。
このコードには、更新ロジックが含まれていません。更新ロジックは、実行時に自動的に生成されます。これは、もう1つの大きな利点です。ADOでは、コードの中に更新ロジックを用意する必要がありません。現に、このコードは、SQL言語のことをほとんど知らなくても書けます。ADOカーソルエンジンの更新機能は、同時実行制御やロックやSQL UPDATEクエリの生成方法を知らなくても使用できるということです。そのような知識のない開発者でも実際に動作するデータアクセスアプリケーションを作成できるということは、ADOの設計が優れていることの証拠でもあります。これほど多くの開発者がADOカーソルエンジンを使ってデータベースを更新していながら、ADOカーソルエンジンが実際にどうやってそれを処理しているかについて何も知らないというのは、確かに驚くべきことです（これは皮肉でも何でもありません）。
10.1.2|ADO Recordsetオブジェクトを使用した更新の欠点
残念ながら、ADOカーソルエンジンの更新機能には欠点もあります。それは、パフォーマンスがよくないことと、制御の余地が少ないということです。ADO 1.5の登場以来、数え切れないほど多くの開発者が、ADOカーソルエンジンを使ってデータベースを更新してきたことからすると、これは大きな問題ではないのかもしれません。しかし、ただ黙って見過ごすわけにはいかないのもまた事実です。これらの欠点をはっきり理解するために、ADOカーソルエンジンがデータベースを更新するしくみを簡単に見ておきましょう。
RecordsetオブジェクトのUpdateBatchメソッドを呼び出すと、ADOカーソルエンジンは、Recordsetの中から変更行を検索し、それぞれの変更行の変更内容を、データベース内の対応する行を変更するためのSQLクエリに変換します。前にも触れたとおり、データベースの内容を変更するために、独自のUPDATE、INSERT、DELETEなどのSQLクエリを生成する開発者がいますが、ADOカーソルエンジンも、それと同じようなステートメントを作成するわけです。
SQL Serverには、データベースに対するSQL呼び出しを管理するために、SQLプロファイラというツールが用意されています。このツールを使って、ADOカーソルエンジンがデータベースを更新するために生成するクエリを見てみると、一連のパラメータクエリを伴ったSQL Serverのsp_executesqlストアドプロシージャが呼び出されていることがわかります。このストアドプロシージャは、次のようなクエリに相当します。
DELETE FROM [Order Details] WHERE OrderID = 10503 AND ProductID = 14
UPDATE [Order Details] SET Quantity = 40
WHERE OrderID = 10503 AND ProductID = 65 AND Quantity = 20
INSERT INTO [Order Details] (OrderID, ProductID, Quantity, UnitPrice)
VALUES (10503, 1, 24, 18)
最初に見たクエリと、コードの中でRecordsetに加える変更内容とをもう一度確認した後で上記のクエリを見ると、流れがよくわかるかもしれません。実際に自分で書くことはできないとしても、その意図は理解できるはずです。要するに、データがどこから来たのかがわかっていれば、Recordset内の変更内容をSQLクエリに変換する作業は非常に簡単だということです。
開発者にしてみれば、このデータがどこから来たのかは明らかですが、ADOカーソルエンジンは、その情報をどのように入手したのでしょうか。ADOカーソルエンジンは、クエリ結果を取り出した時点で、その他のメタデータをデータベースから取得しました。先ほどのUPDATEクエリを組み立てるには、基本テーブルと結果セット内の各列の名前のほかに、クエリで参照されているテーブルの主キーに関する情報が必要です。
ADOのFieldオブジェクトのPropertiesコレクションを使用すれば、次のようにしてその種のメタデータを自分で確認できます。
With rs.Fields("Quantity")
Debug.Print "BaseTableName = " & .Properties("BaseTableName")
Debug.Print "BaseColumnName = " & .Properties("BaseColumnName")
Debug.Print "KeyColumn = " & .Properties("KeyColumn")
End With
このときに、ADOカーソルエンジンの更新機能の大きな短所として最初に取り上げた点が関係してきます。つまり、パフォーマンスです。ADOカーソルエンジンがテーブルや列や主キーのデータを取得するためのデータベースクエリを実行すれば、その分だけパフォーマンスが低下します。データアクセスコードを作成する開発者は、基本的にデータがどこから来たのかを知っていますが、ADOには、コードの中でその情報を用意するための手段がありません。したがって、Recordsetを開くたびに、この種のメタデータを毎回データベースから取り込むことになってしまうわけです。
ADOカーソルエンジンは、「ブラックボックス」のようなテクノロジです。開発者が自分で更新ロジックを定義することはできません。これが実は、ADOカーソルエンジンの更新機能の大きな短所として2番目に取り上げた点です。ADOカーソルエンジンの更新ロジック自体がすばらしいのは確かですが、更新ロジックを制御する余地はほとんど（あるいは、まったく）ありません。Recordsetの中にキャッシュされている更新内容をストアドプロシージャの呼び出しによって実行することもできません。ADOカーソルエンジンが生成する更新ロジックを使用したくない開発者は、無力な状態で放り出されてしまうことになります。
10.2 | 更新を実行するためのADO.NET Command オブジェクトの使用
既に見たとおり、ADOカーソルエンジンは、データベースを更新するためのパラメータクエリを作成しますが、ADO.NETでも、それと同じようなパラメータクエリを作成できます（この点は、「第4章 データベースのクエリの実行」で見たとおりです）。この章であとから取り上げますが、そのようなパラメータ付きのCommandオブジェクトを使えば、ADO.NETのDataSet内に保存されている変更内容をデータベースに適用できます。
ここで使用するADO.NETのCommandオブジェクトは、ADOの対応機能ほど動的ではありません。ここでは流れを簡単にするため、更新を処理するCommand、挿入を処理するCommand、削除を処理するCommandをそれぞれ1つずつ作成します。それらのオブジェクトのベースになるのは、次のパラメータクエリです。
UPDATE [Order Details]
SET OrderID = ?, ProductID = ?, Quantity = ?, UnitPrice = ?
WHERE OrderID = ? AND ProductID = ? AND
Quantity = ? AND UnitPrice = ?
INSERT INTO [Order Details] (OrderID, ProductID, Quantity, UnitPrice)
VALUES (?, ?, ?, ?)
DELETE FROM [Order Details]
WHERE OrderID = ? AND ProductID = ? AND
Quantity = ? AND UnitPrice = ?
|
このUPDATEクエリとINSERTクエリでは、元のクエリに含まれる各列の新しい値をデータベースに適用しますが、特にUPDATEクエリでは、元のクエリの各列を参照するためにWHERE句を使っています。この方法には、長所もあれば短所もあります。その点については、この章であとから取り上げます。 |
次に示すのは、その3つのパラメータ付きのCommandオブジェクトを作成するコードです。それぞれのコードは、cnというOleDbConnectionオブジェクトが外部で定義されていることを前提としています。
Visual Basic .NET
Private Function CreateUpdateCommand() As OleDbCommand
Dim strSQL As String
strSQL = "UPDATE [Order Details] " & _
" SET OrderID = ?, ProductID = ?, " & _
" Quantity = ?, UnitPrice = ? " & _
" WHERE OrderID = ? AND ProductID = ? AND " & _
" Quantity = ? AND UnitPrice = ?"
Dim cmd As New OleDbCommand(strSQL, cn)
Dim pc As OleDbParameterCollection = cmd.Parameters
pc.Add("OrderID_New", OleDbType.Integer)
pc.Add("ProductID_New", OleDbType.Integer)
pc.Add("Quantity_New", OleDbType.SmallInt)
pc.Add("UnitPrice_New", OleDbType.Currency)
pc.Add("OrderID_Orig", OleDbType.Integer)
pc.Add("ProductID_Orig", OleDbType.Integer)
pc.Add("Quantity_Orig", OleDbType.SmallInt)
pc.Add("UnitPrice_Orig", OleDbType.Currency)
Return cmd
End Function
Private Function CreateInsertCommand() As OleDbCommand
Dim strSQL As String
strSQL = "INSERT INTO [Order Details] " & _
" (OrderID, ProductID, Quantity, UnitPrice) " & _
" VALUES (?, ?, ?, ?)"
Dim cmd As New OleDbCommand(strSQL, cn)
Dim pc As OleDbParameterCollection = cmd.Parameters
pc.Add("OrderID", OleDbType.Integer)
pc.Add("ProductID", OleDbType.Integer)
pc.Add("Quantity", OleDbType.SmallInt)
pc.Add("UnitPrice", OleDbType.Currency)
Return cmd
End Function
Private Function CreateDeleteCommand() As OleDbCommand
Dim strSQL As String
strSQL = "DELETE FROM [Order Details] " & _
" WHERE OrderID = ? AND ProductID = ? AND " & _
" Quantity = ? AND UnitPrice = ?"
Dim cmd As New OleDbCommand(strSQL, cn)
Dim pc As OleDbParameterCollection = cmd.Parameters
pc.Add("OrderID", OleDbType.Integer)
pc.Add("ProductID", OleDbType.Integer)
pc.Add("Quantity", OleDbType.SmallInt)
pc.Add("UnitPrice", OleDbType.Currency)
Return cmd
End Function
Visual C# .NET
static OleDbCommand CreateUpdateCommand()
{
string strSQL;
strSQL = "UPDATE [Order Details] " +
" SET OrderID = ?, ProductID = ?, " +
" Quantity = ?, UnitPrice = ? " +
" WHERE OrderID = ? AND ProductID = ? AND " +
" Quantity = ? AND UnitPrice = ?";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
OleDbParameterCollection pc = cmd.Parameters;
pc.Add("OrderID_New", OleDbType.Integer);
pc.Add("ProductID_New", OleDbType.Integer);
pc.Add("Quantity_New", OleDbType.SmallInt);
pc.Add("UnitPrice_New", OleDbType.Currency);
pc.Add("OrderID_Orig", OleDbType.Integer);
pc.Add("ProductID_Orig", OleDbType.Integer);
pc.Add("Quantity_Orig", OleDbType.SmallInt);
pc.Add("UnitPrice_Orig", OleDbType.Currency);
return cmd;
}
static OleDbCommand CreateInsertCommand()
{
string strSQL;
strSQL = "INSERT INTO [Order Details] " +
" (OrderID, ProductID, Quantity, UnitPrice) " +
" VALUES (?, ?, ?, ?)";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
OleDbParameterCollection pc = cmd.Parameters;
pc.Add("OrderID", OleDbType.Integer);
pc.Add("ProductID", OleDbType.Integer);
pc.Add("Quantity", OleDbType.SmallInt);
pc.Add("UnitPrice", OleDbType.Currency);
return cmd;
}
static OleDbCommand CreateDeleteCommand()
{
string strSQL;
strSQL = "DELETE FROM [Order Details] " +
" WHERE OrderID = ? AND ProductID = ? AND " +
" Quantity = ? AND UnitPrice = ?";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
OleDbParameterCollection pc = cmd.Parameters;
pc.Add("OrderID", OleDbType.Integer);
pc.Add("ProductID", OleDbType.Integer);
pc.Add("Quantity", OleDbType.SmallInt);
pc.Add("UnitPrice", OleDbType.Currency);
return cmd;
}
このようなパラメータ付きのCommandオブジェクトを使ってデータベースを更新する処理は、単純明快です。まず必要なのは、DataTable内の変更行を特定し、その各行の変更の種類（更新/挿入/削除）を確認し、各行の内容に基づいて、それぞれ該当するコマンドのパラメータの値を設定することです。
Command内に格納されているクエリを実行するためにExecuteNonQueryメソッドを呼び出したら、そのメソッドの戻り値から、更新が正常に実行されたかどうかを確認できます。保留状態の変更内容をデータベースに正しく反映できた場合は、DataRowのAcceptChangesメソッドを呼び出し、更新が失敗した場合は、その失敗を通知するテキストをDataRowオブジェクトのRowErrorプロパティに設定します。
Visual Basic .NET
Private Sub SubmitChangesByHand()
Dim cmdUpdate As OleDbCommand = CreateUpdateCommand()
Dim cmdInsert As OleDbCommand = CreateInsertCommand()
Dim cmdDelete As OleDbCommand = CreateDeleteCommand()
Dim row As DataRow
Dim intRowsAffected As Integer
Dim dvrs As DataViewRowState
dvrs = DataViewRowState.ModifiedCurrent _
Or DataViewRowState.Deleted Or DataViewRowState.Added
For Each row In tbl.Select("", "", dvrs)
Select Case row.RowState
Case DataRowState.Modified
intRowsAffected = SubmitUpdate(row, cmdUpdate)
Case DataRowState.Added
intRowsAffected = SubmitInsert(row, cmdInsert)
Case DataRowState.Deleted
intRowsAffected = SubmitDelete(row, cmdDelete)
End Select
If intRowsAffected = 1 Then
row.AcceptChanges()
Else
row.RowError = "更新に失敗しました。"
End If
Next row
End Sub
Private Function SubmitUpdate(ByVal row As DataRow, _
ByVal cmd As OleDbCommand) As Integer
Dim pc As OleDbParameterCollection = cmd.Parameters
pc("OrderID_New").Value = row("OrderID")
pc("ProductID_New").Value = row("ProductID")
pc("Quantity_New").Value = row("Quantity")
pc("UnitPrice_New").Value = row("UnitPrice")
pc("OrderID_Orig").Value = row("OrderID", _
DataRowVersion.Original)
pc("Quantity_Orig").Value = row("Quantity", _
DataRowVersion.Original)
pc("ProductID_Orig").Value = row("ProductID", _
DataRowVersion.Original)
pc("UnitPrice_Orig").Value = row("UnitPrice", _
DataRowVersion.Original)
Return cmd.ExecuteNonQuery
End Function
Private Function SubmitInsert(ByVal row As DataRow, _
ByVal cmd As OleDbCommand) As Integer
Dim pc As OleDbParameterCollection = cmd.Parameters
pc("OrderID").Value = row("OrderID")
pc("ProductID").Value = row("ProductID")
pc("Quantity").Value = row("Quantity")
pc("UnitPrice").Value = row("UnitPrice")
Return cmd.ExecuteNonQuery
End Function
Private Function SubmitDelete(ByVal row As DataRow, _
ByVal cmd As OleDbCommand) As Integer
Dim pc As OleDbParameterCollection = cmd.Parameters
pc("OrderID").Value = row("OrderID", DataRowVersion.Original)
pc("ProductID").Value = row("ProductID", DataRowVersion.Original)
pc("Quantity").Value = row("Quantity", DataRowVersion.Original)
pc("UnitPrice").Value = row("UnitPrice", DataRowVersion.Original)
Return cmd.ExecuteNonQuery
End Function
Visual C# .NET
static void SubmitChangesByHand()
{
OleDbCommand cmdUpdate = CreateUpdateCommand();
OleDbCommand cmdInsert = CreateInsertCommand();
OleDbCommand cmdDelete = CreateDeleteCommand();
DataViewRowState dvrs;
dvrs = DataViewRowState.ModifiedCurrent |
DataViewRowState.Deleted | DataViewRowState.Added;
int intRowsAffected = 0;
foreach (DataRow row in tbl.Select("", "", dvrs))
{
switch (row.RowState)
{
case DataRowState.Modified:
intRowsAffected = SubmitUpdate(row, cmdUpdate);
break;
case DataRowState.Added:
intRowsAffected = SubmitInsert(row, cmdInsert);
break;
case DataRowState.Deleted:
intRowsAffected = SubmitDelete(row, cmdDelete);
break;
}
if (intRowsAffected == 1)
row.AcceptChanges();
else
row.RowError = "更新に失敗しました。";
}
}
static int SubmitUpdate(DataRow row, OleDbCommand cmd)
{
OleDbParameterCollection pc = cmd.Parameters;
pc["OrderID_New"].Value = row["OrderID"];
pc["ProductID_New"].Value = row["ProductID"];
pc["Quantity_New"].Value = row["Quantity"];
pc["UnitPrice_New"].Value = row["UnitPrice"];
pc["OrderID_Orig"].Value = row["OrderID",
DataRowVersion.Original];
pc["ProductID_Orig"].Value = row["ProductID",
DataRowVersion.Original];
pc["Quantity_Orig"].Value = row["Quantity",
DataRowVersion.Original];
pc["UnitPrice_Orig"].Value = row["UnitPrice",
DataRowVersion.Original];
return cmd.ExecuteNonQuery();
}
static int SubmitInsert(DataRow row, OleDbCommand cmd)
{
OleDbParameterCollection pc = cmd.Parameters;
pc["OrderID"].Value = row["OrderID"];
pc["ProductID"].Value = row["ProductID"];
pc["Quantity"].Value = row["Quantity"];
pc["UnitPrice"].Value = row["UnitPrice"];
return cmd.ExecuteNonQuery();
}
static int SubmitDelete(DataRow row, OleDbCommand cmd)
{
OleDbParameterCollection pc = cmd.Parameters;
pc["OrderID"].Value = row["OrderID", DataRowVersion.Original];
pc["ProductID"].Value = row["ProductID",
DataRowVersion.Original];
pc["Quantity"].Value = row["Quantity", DataRowVersion.Original];
pc["UnitPrice"].Value = row["UnitPrice",
DataRowVersion.Original];
return cmd.ExecuteNonQuery();
}
|
このコードでは、DataTableオブジェクトのSelectメソッドを使って変更行をループ処理しています。ここで、DataTableオブジェクトのRowsコレクション内の各項目を調べるために、ForループやFor Eachループを使用しなかったことには理由があります。保留状態の削除操作をデータベースに正しく反映できた場合、そのDataRowのAcceptChangesメソッドを呼び出すと、その項目が親コレクションから完全に除去されます。その点、Selectメソッドから返されるのは、DataRowオブジェクトの配列ですが、その配列に入っているのは基本的に、変更行へのポインタなので、DataTableオブジェクトのDataRowオブジェクトのコレクションから項目を完全に除去してしまっても、コードは正常に実行されることになります。 |
さて、このコードを実際に使ってみることにしましょう。
このあとに示すのは、注文データの詳細をDataTableに取り込み、注文データの内容を変更してから、データベースに変更内容を適用するコードです。このコードからもわかるとおり、これまでに見てきたコードの断片では、保留状態の変更内容をデータベースに正しく適用できます。ここでは、この章の初めの方で定義したプロシージャを利用しています。また、DataTableの現在の内容を書き出すプロシージャも含まれているので、注文の内容が正しく更新されたかどうかを確認できます。さらに、注文の元の内容を作成し直すためのResetOrderプロシージャも組み込まれているので、このコードは何度でも実行できるようになっています。
Visual Basic .NET
Dim cn As OleDbConnection
Dim da As OleDbDataAdapter
Dim tbl As DataTable = GenTable()
Sub Main()
Dim strConn, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=(local)￥NetSDK;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _
"FROM [Order Details] WHERE OrderID = 10503 " & _
"ORDER BY ProductID"
cn = New OleDbConnection(strConn)
da = New OleDbDataAdapter(strSQL, cn)
cn.Open()
ResetOrder()
da.Fill(tbl)
DisplayOrder("データベースの元の内容")
ModifyOrder()
DisplayOrder("DataSet内の変更データ")
SubmitChangesByHand()
tbl.Clear()
da.Fill(tbl)
DisplayOrder("データベースの新しい内容")
cn.Close()
End Sub
Private Sub ModifyOrder()
Dim row As DataRow
row = tbl.Rows(0)
row.Delete()
row = tbl.Rows(1)
row("Quantity") = CType(row("Quantity"), Int16) * 2
row = tbl.NewRow
row("OrderID") = 10503
row("ProductID") = 1
row("Quantity") = 24
row("UnitPrice") = 18.0
tbl.Rows.Add(row)
End Sub
Public Sub DisplayOrder(ByVal strStatus As String)
Dim row As DataRow
Dim col As DataColumn
Console.WriteLine(strStatus)
Console.WriteLine(" OrderID ProductID " & _
"Quantity UnitPrice")
For Each row In tbl.Select("", "ProductID")
For Each col In tbl.Columns
Console.Write(vbTab & row(col) & vbTab)
Next
Console.WriteLine()
Next
Console.WriteLine()
End Sub
Private Sub ResetOrder()
Dim strSQL As String
Dim cmd As OleDbCommand = cn.CreateCommand()
strSQL = "DELETE FROM [Order Details] WHERE OrderID = 10503"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()
strSQL = "INSERT INTO [Order Details] " & _
" (OrderID, ProductID, Quantity, UnitPrice) " & _
" VALUES (10503, 14, 70, 23.25) "
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()
strSQL = "INSERT INTO [Order Details] " & _
" (OrderID, ProductID, Quantity, UnitPrice) " & _
" VALUES (10503, 65, 20, 21.05)"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()
End Sub
Public Function GenTable() As DataTable
Dim tbl As New DataTable("Order Details")
Dim col As DataColumn
With tbl.Columns
col = .Add("OrderID", GetType(Integer))
col.AllowDBNull = False
col = .Add("ProductID", GetType(Integer))
col.AllowDBNull = False
col = .Add("Quantity", GetType(Int16))
col.AllowDBNull = False
col = .Add("UnitPrice", GetType(Decimal))
col.AllowDBNull = False
End With
tbl.PrimaryKey = New DataColumn() {tbl.Columns("OrderID"), _
tbl.Columns("ProductID")}
Return tbl
End Function
Visual C# .NET
static OleDbConnection cn;
static OleDbDataAdapter da;
static DataTable tbl;
static void Main(string[] args)
{
string strConn, strSQL;
strConn = "Provider=SQLOLEDB;Data Source=(local)￥￥NetSDK;" +
"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " +
"FROM [Order Details] WHERE OrderID = 10503 " +
"ORDER BY ProductID";
cn = new OleDbConnection(strConn);
da = new OleDbDataAdapter(strSQL, cn);
tbl = GenTable();
cn.Open();
ResetOrder();
da.Fill(tbl);
DisplayOrder("データベースの元の内容");
ModifyOrder();
DisplayOrder("DataSet内の変更データ");
SubmitChangesByHand();
tbl.Clear();
da.Fill(tbl);
DisplayOrder("データベースの新しい内容");
cn.Close();
}
static void ModifyOrder()
{
DataRow row;
row = tbl.Rows[0];
row.Delete();
row = tbl.Rows[1];
row["Quantity"] = (Int16) row["Quantity"] * 2;
row = tbl.NewRow();
row["OrderID"] = 10503;
row["ProductID"] = 1;
row["Quantity"] = 24;
row["UnitPrice"] = 18.0;
tbl.Rows.Add(row);
}
static void DisplayOrder(string strStatus)
{
Console.WriteLine(strStatus);
Console.WriteLine(" OrderID ProductID " +
"Quantity UnitPrice");
foreach(DataRow row in tbl.Select("", "ProductID"))
{
foreach(DataColumn col in tbl.Columns)
Console.Write("￥t" + row[col] + "￥t");
Console.WriteLine();
}
Console.WriteLine();
}
static void ResetOrder()
{
string strSQL;
OleDbCommand cmd = cn.CreateCommand();
strSQL = "DELETE FROM [Order Details] WHERE OrderID = 10503";
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
strSQL = "INSERT INTO [Order Details] " +
" (OrderID, ProductID, Quantity, UnitPrice) " +
" VALUES (10503, 14, 70, 23.25) " ;
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
strSQL = "INSERT INTO [Order Details] " +
" (OrderID, ProductID, Quantity, UnitPrice) " +
" VALUES (10503, 65, 20, 21.05)";
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
}
static DataTable GenTable()
{
DataTable tbl = new DataTable("Order Details");
DataColumn col;
col = tbl.Columns.Add("OrderID", typeof(int));
col.AllowDBNull = false;
col = tbl.Columns.Add("ProductID", typeof(int));
col.AllowDBNull = false;
col = tbl.Columns.Add("Quantity", typeof(Int16));
col.AllowDBNull = false;
col = tbl.Columns.Add("UnitPrice", typeof(Decimal));
col.AllowDBNull = false;
tbl.PrimaryKey = new DataColumn[] {tbl.Columns["OrderID"],
tbl.Columns["ProductID"]};
return tbl;
}
このようにして、保留状態の変更内容をデータベースに適用するための大量のコードを記述しました。パラメータ付きのCommandオブジェクトを生成するためのコードは、最初に見たクエリに固有のものですが、SubmitChangesByHandプロシージャのコードは、汎用的です。つまり、DataTable内にキャッシュされている変更行を調べ、変更のあった各DataRow内に保存されている変更の種類を確認し、その保留状態の変更内容をデータベースに反映させるクエリを実行するための関数を呼び出し、その関数の戻り値に基づいてDataRowの状態を設定する汎用的なコードです。
実を言えば、これまでの作業は、DataAdapterオブジェクトから得られる更新機能を自分で作成したというだけの話です。そのDataAdapterオブジェクトの更新機能を次に見てみましょう。
10.3 | 更新を実行するためのADO.NET DataAdapterオブジェクトの使用
「第5章 DataAdapterオブジェクトを使用したデータの取得」では、DataAdapterを使ってクエリ結果をDataTableに取り込む方法を取り上げましたが、これはDataAdapterの機能の半分にすぎません。このオブジェクトには、DataSet内の保留状態の変更内容をデータベースに適用する機能もあります。
DataAdapterオブジェクトによって変更内容をデータベースに適用するための更新ロジックを生成する方法としては、次の3つがあります。
- コードを使ってDataAdapterオブジェクトを手作業で設定する方法
- 実行時にCommandBuilderを使用する方法
- デザイン時にデータアダプタ構成ウィザードを使用する方法
この3つの方法には、それぞれ長所と短所があります。これから、それぞれの方法を詳しく見ていきましょう。
10.4 | DataAdapter オブジェクトの手動での構成
DataAdapterオブジェクトには、Commandオブジェクトを指定するためのプロパティが4つ用意されています。既に見たとおり、SelectCommandプロパティには、DataAdapterがDataTableにデータを取り込むためのCommandを指定できます。一方、その他の3つのプロパティ（UpdateCommand、InsertCommand、Delete Command）には、DataAdapterが保留状態の変更内容をデータベースに適用するためのCommandオブジェクトを指定できます。
このアーキテクチャは、ADOのオブジェクトモデルからの大きな変更点です。謎めいた「ブラックボックス」のようなテクノロジはもうありません。DataAdapterが保留状態の変更内容をデータベースに適用する方法を開発者自身が指定し、DataAdapterが使用するCommandオブジェクトを開発者自身が用意する必要があるわけです。
DataAdapterオブジェクトのUpdateメソッドは、非常に柔軟です。パラメータとして、DataSet、DataSetとテーブル名の組み合わせ、DataTable、DataRowオブジェクトの配列のいずれかを指定できます。DataAdapterオブジェクトのUpdateメソッドをどんな方法で呼び出すにしても、DataAdapterは、保留状態の変更内容を指定のCommandによって実行しようとします。先ほどのSubmitChangesByHandプロシージャの中で実行していたすべての作業は、DataAdapterオブジェクトのUpdateメソッドを1回呼び出すだけで実現できます。
10.4.1|パラメータのバインドの概要
先ほどのSubmitChangesByHandプロシージャは、それほど複雑ではなく、それほど多くのことを実行するわけでもありません。むしろ、面倒な作業は、SubmitUpdate、SubmitInsert、SubmitDeleteのいずれかの関数に任せていました。それらの関数は、変更行の内容に基づいて、それぞれ該当するクエリのパラメータ値を設定します。DataAdapterを使って、保留状態の変更内容をデータベースに適用する場合も、同じパラメータクエリを使用します。
UPDATE [Order Details]
SET OrderID = ?, ProductID = ?, Quantity = ?, UnitPrice = ?
WHERE OrderID = ? AND ProductID = ? AND
Quantity = ? AND UnitPrice = ?
INSERT INTO [Order Details] (OrderID, ProductID, Quantity, UnitPrice)
VALUES (?, ?, ?, ?)
DELETE FROM [Order Details]
WHERE OrderID = ? AND ProductID = ? AND
Quantity = ? AND UnitPrice = ?
ただし、DataAdapterオブジェクトのCommandオブジェクトにParameterオブジェクトを追加する場合は、ADO.NETのParameterオブジェクトの2つのプロパティ（つまり、DataAdapterによる更新用に特化したSourceColumnとSourceVersion）を使用することになります。
この2つのプロパティは基本的に、1つのParameterをDataTable内の1つのDataColumnにバインドします。DataAdapterは、この2つのプロパティを使って、 ParameterオブジェクトのValueプロパティの設定方法を決定してから、クエリを実行します（これは、SubmitUpdate、SubmitInsert、SubmitDeleteの各関数を使った場合と同じ流れです）。図10.2をご覧ください。
.gif)
▲図10.2 ParameterオブジェクトとDataColumnオブジェクトのバインド
次に示すのは、パラメータ付きのCommandオブジェクトを作成した上で、ParameterオブジェクトのSourceColumnプロパティとSourceVersionプロパティを設定するコードです。SourceVersionプロパティの既定値はDataRowVersion.Currentなので、このプロパティを明示的に設定する必要があるのは、Parameterオブジェクトを列の元の値にバインドする場合に限られます。
Visual Basic .NET
Private Function CreateDataAdapterUpdateCommand() As OleDbCommand
Dim strSQL As String
strSQL = "UPDATE [Order Details] " & _
" SET OrderID = ?, ProductID = ?, " & _
" Quantity = ?, UnitPrice = ? " & _
" WHERE OrderID = ? AND ProductID = ? AND " & _
" Quantity = ? AND UnitPrice = ?"
Dim cmd As New OleDbCommand(strSQL, cn)
Dim pc As OleDbParameterCollection = cmd.Parameters
pc.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")
pc.Add("ProductID_New", OleDbType.Integer, 0, "ProductID")
pc.Add("Quantity_New", OleDbType.SmallInt, 0, "Quantity")
pc.Add("UnitPrice_New", OleDbType.Currency, 0, "UnitPrice")
Dim param As OleDbParameter
param = pc.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("ProductID_Orig", OleDbType.Integer, 0, _
"ProductID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("Quantity_Orig", OleDbType.SmallInt, 0, _
"Quantity")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("UnitPrice_Orig", OleDbType.Currency, 0, _
"UnitPrice")
param.SourceVersion = DataRowVersion.Original
Return cmd
End Function
Private Function CreateDataAdapterInsertCommand() As OleDbCommand
Dim strSQL As String
strSQL = "INSERT INTO [Order Details] " & _
" (OrderID, ProductID, Quantity, UnitPrice) " & _
" VALUES (?, ?, ?, ?)"
Dim cmd As New OleDbCommand(strSQL, cn)
Dim pc As OleDbParameterCollection = cmd.Parameters
pc.Add("OrderID", OleDbType.Integer, 0, "OrderID")
pc.Add("ProductID", OleDbType.Integer, 0, "ProductID")
pc.Add("Quantity", OleDbType.SmallInt, 0, "Quantity")
pc.Add("UnitPrice", OleDbType.Currency, 0, "UnitPrice")
Return cmd
End Function
Private Function CreateDataAdapterDeleteCommand() As OleDbCommand
Dim strSQL As String
strSQL = "DELETE FROM [Order Details] " & _
" WHERE OrderID = ? AND ProductID = ? AND " & _
" Quantity = ? AND UnitPrice = ?"
Dim cmd As New OleDbCommand(strSQL, cn)
Dim pc As OleDbParameterCollection = cmd.Parameters
Dim param As OleDbParameter
param = pc.Add("OrderID", OleDbType.Integer, 0, "OrderID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("ProductID", OleDbType.Integer, 0, "ProductID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("Quantity", OleDbType.SmallInt, 0, "Quantity")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("UnitPrice", OleDbType.Currency, 0, "UnitPrice")
param.SourceVersion = DataRowVersion.Original
Return cmd
End Function
Visual C# .NET
static OleDbCommand CreateDataAdapterUpdateCommand()
{
string strSQL;
strSQL = "UPDATE [Order Details] " +
" SET OrderID = ?, ProductID = ?, " +
" Quantity = ?, UnitPrice = ? " +
" WHERE OrderID = ? AND ProductID = ? AND " +
" Quantity = ? AND UnitPrice = ?";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
OleDbParameterCollection pc = cmd.Parameters;
pc.Add("OrderID_New", OleDbType.Integer, 0, "OrderID");
pc.Add("ProductID_New", OleDbType.Integer, 0, "ProductID");
pc.Add("Quantity_New", OleDbType.SmallInt, 0, "Quantity");
pc.Add("UnitPrice_New", OleDbType.Currency, 0, "UnitPrice");
OleDbParameter param;
param = pc.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("ProductID_Orig", OleDbType.Integer, 0,
"ProductID");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("Quantity_Orig", OleDbType.SmallInt, 0,
"Quantity");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("UnitPrice_Orig", OleDbType.Currency, 0,
"UnitPrice");
param.SourceVersion = DataRowVersion.Original;
return cmd;
}
static OleDbCommand CreateDataAdapterInsertCommand()
{
string strSQL;
strSQL = "INSERT INTO [Order Details] " +
" (OrderID, ProductID, Quantity, UnitPrice) " +
" VALUES (?, ?, ?, ?)";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
OleDbParameterCollection pc = cmd.Parameters;
pc.Add("OrderID", OleDbType.Integer, 0, "OrderID");
pc.Add("ProductID", OleDbType.Integer, 0, "ProductID");
pc.Add("Quantity", OleDbType.SmallInt, 0, "Quantity");
pc.Add("UnitPrice", OleDbType.Currency, 0, "UnitPrice");
return cmd;
}
static OleDbCommand CreateDataAdapterDeleteCommand()
{
string strSQL;
strSQL = "DELETE FROM [Order Details] " +
" WHERE OrderID = ? AND ProductID = ? AND " +
" Quantity = ? AND UnitPrice = ?";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
OleDbParameter param;
OleDbParameterCollection pc = cmd.Parameters;
param = pc.Add("OrderID", OleDbType.Integer, 0, "OrderID");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("ProductID", OleDbType.Integer, 0, "ProductID");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("Quantity", OleDbType.SmallInt, 0, "Quantity");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("UnitPrice", OleDbType.Currency, 0, "UnitPrice");
param.SourceVersion = DataRowVersion.Original;
return cmd;
}
これで、SubmitChangesByHand、SubmitUpdate、SubmitInsert、SubmitDeleteの各プロシージャの代わりに、次のコードを使用できるようになります。
Visual Basic .NET
Private Sub SubmitChangesViaDataAdapter()
da.UpdateCommand = CreateDataAdapterUpdateCommand()
da.InsertCommand = CreateDataAdapterInsertCommand()
da.DeleteCommand = CreateDataAdapterDeleteCommand()
da.Update(tbl)
End Sub
Visual C# .NET
static void SubmitChangesViaDataAdapter()
{
da.UpdateCommand = CreateDataAdapterUpdateCommand();
da.InsertCommand = CreateDataAdapterInsertCommand();
da.DeleteCommand = CreateDataAdapterDeleteCommand();
da.Update(tbl);
}
10.4.2|更新を実行するためのストアドプロシージャの使用
ADOを使ってデータベースのデータを抽出していた開発者たちの間では、RecordsetオブジェクトのUpdateBatchメソッドがストアドプロシージャによるデータベース更新に対応していない、という不満がありました。
既に見たとおり、DataAdapterを使用すれば、開発者が自分で更新ロジックを定義できます。先ほどのコードもそうですが、自分でCommandオブジェクトを作成し、保留状態の変更内容をデータベースに反映させるためのDataAdapterにそのCommandオブジェクトを指定すればよいわけです。先ほどと同じようなコードを使えば、ストアドプロシージャによるデータベース更新も可能になります。
まず、Northwindデータベースの中に、Order Detailsテーブルの行を変更/挿入/削除するストアドプロシージャを定義する必要があります。SQLクエリアナライザに次のコードをペーストして実行すれば、そのストアドプロシージャを作成できます（このストアドプロシージャをコードから呼び出すわけです）。ただし、MSDEしかない場合は、SQLクエリアナライザを使用できません。その場合は、CreateSprocsというプロシージャ（あとからコードの中でも使用します）を呼び出して、ストアドプロシージャを作成してください。
USE Northwind
GO
CREATE PROCEDURE spUpdateDetail
(@OrderID_New int, @ProductID_New int,
@Quantity_New smallint, @UnitPrice_New money,
@OrderID_Orig int, @ProductID_Orig int,
@Quantity_Orig smallint, @UnitPrice_Orig money)
AS
UPDATE [Order Details]
SET OrderID = @OrderID_New, ProductID = @ProductID_New,
Quantity = @Quantity_New, UnitPrice = @UnitPrice_New
WHERE OrderID = @OrderID_Orig AND ProductID = @ProductID_Orig AND
Quantity = @Quantity_Orig AND UnitPrice = @UnitPrice_Orig
GO
CREATE PROCEDURE spInsertDetail
(@OrderID int, @ProductID int,
@Quantity smallint, @UnitPrice money)
AS
INSERT INTO [Order Details]
(OrderID, ProductID, Quantity, UnitPrice)
VALUES (@OrderID, @ProductID, @Quantity, @UnitPrice)
GO
CREATE PROCEDURE spDeleteDetail
(@OrderID int, @ProductID int,
@Quantity smallint, @UnitPrice money)
AS
DELETE FROM [Order Details]
WHERE OrderID = @OrderID AND ProductID = @ProductID AND
Quantity = @Quantity AND UnitPrice = @UnitPrice
これで、Order Detailsテーブルを更新するためのストアドプロシージャを作成できました。次に、DataAdapterオブジェクトのUpdateメソッドを呼び出したときに自動的にそのストアドプロシージャを呼び出すCommandオブジェクトを作成します。
次に示すコードには、そのストアドプロシージャの呼び出しを指定したCommandオブジェクトを作成する関数を組み込んであります。また、データベースの中にそのストアドプロシージャを作成するためのプロシージャも含めました。あとは、新しいCommandオブジェクトをDataAdapterに結び付けるだけです。そのために組み込んであるのが、SubmitChangesViaStoredProceduresプロシージャです。
Visual Basic .NET
Private Sub SubmitChangesViaStoredProcedures()
da.UpdateCommand = CreateUpdateViaSPCommand()
da.InsertCommand = CreateInsertViaSPCommand()
da.DeleteCommand = CreateDeleteViaSPCommand()
da.Update(tbl)
End Sub
Private Function CreateUpdateViaSPCommand() As OleDbCommand
Dim cmd As New OleDbCommand("spUpdateDetail", cn)
cmd.CommandType = CommandType.StoredProcedure
Dim pc As OleDbParameterCollection = cmd.Parameters
pc.Add("OrderID_New", OleDbType.Integer, 0, "OrderID")
pc.Add("ProductID_New", OleDbType.Integer, 0, "ProductID")
pc.Add("Quantity_New", OleDbType.SmallInt, 0, "Quantity")
pc.Add("UnitPrice_New", OleDbType.Currency, 0, "UnitPrice")
Dim param As OleDbParameter
param = pc.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("ProductID_Orig", OleDbType.Integer, 0, _
"ProductID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("Quantity_Orig", OleDbType.SmallInt, 0, _
"Quantity")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("UnitPrice_Orig", OleDbType.Currency, 0, _
"UnitPrice")
param.SourceVersion = DataRowVersion.Original
Return cmd
End Function
Private Function CreateInsertViaSPCommand() As OleDbCommand
Dim cmd As New OleDbCommand("spInsertDetail", cn)
cmd.CommandType = CommandType.StoredProcedure
Dim pc As OleDbParameterCollection = cmd.Parameters
pc.Add("OrderID", OleDbType.Integer, 0, "OrderID")
pc.Add("ProductID", OleDbType.Integer, 0, "ProductID")
pc.Add("Quantity", OleDbType.SmallInt, 0, "Quantity")
pc.Add("UnitPrice", OleDbType.Currency, 0, "UnitPrice")
Return cmd
End Function
Private Function CreateDeleteViaSPCommand() As OleDbCommand
Dim cmd As New OleDbCommand("spDeleteDetail", cn)
cmd.CommandType = CommandType.StoredProcedure
Dim pc As OleDbParameterCollection = cmd.Parameters
Dim param As OleDbParameter
param = pc.Add("OrderID", OleDbType.Integer, 0, "OrderID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("ProductID", OleDbType.Integer, 0, "ProductID")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("Quantity", OleDbType.SmallInt, 0, "Quantity")
param.SourceVersion = DataRowVersion.Original
param = pc.Add("UnitPrice", OleDbType.Currency, 0, "UnitPrice")
param.SourceVersion = DataRowVersion.Original
Return cmd
End Function
Private Sub CreateSprocs()
Dim cmd As OleDbCommand = cn.CreateCommand
Dim strSQL As String
strSQL = "CREATE PROCEDURE spUpdateDetail " & vbCrLf & _
" (@OrderID_New int, @ProductID_New int, " & vbCrLf & _
" @Quantity_New smallint, " & vbCrLf & _
" @UnitPrice_New money, " & vbCrLf & _
" @OrderID_Orig int, " & vbCrLf & _
" @ProductID_Orig int, " & vbCrLf & _
" @Quantity_Orig smallint, " & vbCrLf & _
" @UnitPrice_Orig money) " & vbCrLf & _
"AS " & vbCrLf & _
"UPDATE [Order Details] " & vbCrLf & _
" SET OrderID = @OrderID_New, " & vbCrLf & _
" ProductID = @ProductID_New, " & vbCrLf & _
" Quantity = @Quantity_New, " & vbCrLf & _
" UnitPrice = @UnitPrice_New " & vbCrLf & _
" WHERE OrderID = @OrderID_Orig AND " & vbCrLf & _
" ProductID = @ProductID_Orig AND " & vbCrLf & _
" Quantity = @Quantity_Orig AND " & vbCrLf & _
" UnitPrice = @UnitPrice_Orig"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()
strSQL = "CREATE PROCEDURE spInsertDetail " & vbCrLf & _
" (@OrderID int, @ProductID int, " & vbCrLf & _
" @Quantity smallint, @UnitPrice money) " & vbCrLf & _
"AS " & vbCrLf & _
"INSERT INTO [Order Details] " & vbCrLf & _
" (OrderID, ProductID, Quantity, UnitPrice) " & vbCrLf & _
" VALUES (@OrderID, @ProductID, @Quantity, @UnitPrice)"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()
strSQL = "CREATE PROCEDURE spDeleteDetail " & vbCrLf & _
" (@OrderID int, @ProductID int, " & vbCrLf & _
" @Quantity smallint, @UnitPrice money) " & vbCrLf & _
"AS " & vbCrLf & _
"DELETE FROM [Order Details] " & vbCrLf & _
" WHERE OrderID = @OrderID AND " & vbCrLf & _
" ProductID = @ProductID AND " & vbCrLf & _
" Quantity = @Quantity AND UnitPrice = @UnitPrice"
cmd.CommandText = strSQL
cmd.ExecuteNonQuery()
End Sub
Visual C# .NET
static void SubmitChangesViaStoredProcedures()
{
da.UpdateCommand = CreateUpdateViaSPCommand();
da.InsertCommand = CreateInsertViaSPCommand();
da.DeleteCommand = CreateDeleteViaSPCommand();
da.Update(tbl);
}
static OleDbCommand CreateUpdateViaSPCommand()
{
OleDbCommand cmd = new OleDbCommand("spUpdateDetail", cn);
cmd.CommandType = CommandType.StoredProcedure;
OleDbParameterCollection pc = cmd.Parameters;
pc.Add("OrderID_New", OleDbType.Integer, 0, "OrderID");
pc.Add("ProductID_New", OleDbType.Integer, 0, "ProductID");
pc.Add("Quantity_New", OleDbType.SmallInt, 0, "Quantity");
pc.Add("UnitPrice_New", OleDbType.Currency, 0, "UnitPrice");
OleDbParameter param;
param = pc.Add("OrderID_Orig", OleDbType.Integer, 0, "OrderID");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("ProductID_Orig", OleDbType.Integer, 0, "ProductID");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("Quantity_Orig", OleDbType.SmallInt, 0, "Quantity");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("UnitPrice_Orig", OleDbType.Currency, 0, "UnitPrice");
param.SourceVersion = DataRowVersion.Original;
return cmd;
}
static OleDbCommand CreateInsertViaSPCommand()
{
OleDbCommand cmd = new OleDbCommand("spInsertDetail", cn);
cmd.CommandType = CommandType.StoredProcedure;
OleDbParameterCollection pc = cmd.Parameters;
pc.Add("OrderID", OleDbType.Integer, 0, "OrderID");
pc.Add("ProductID", OleDbType.Integer, 0, "ProductID");
pc.Add("Quantity", OleDbType.SmallInt, 0, "Quantity");
pc.Add("UnitPrice", OleDbType.Currency, 0, "UnitPrice");
return cmd;
}
static OleDbCommand CreateDeleteViaSPCommand()
{
OleDbCommand cmd = new OleDbCommand("spDeleteDetail", cn);
cmd.CommandType = CommandType.StoredProcedure;
OleDbParameterCollection pc = cmd.Parameters;
OleDbParameter param;
param = pc.Add("OrderID", OleDbType.Integer, 0, "OrderID");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("ProductID", OleDbType.Integer, 0, "ProductID");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("Quantity", OleDbType.SmallInt, 0, "Quantity");
param.SourceVersion = DataRowVersion.Original;
param = pc.Add("UnitPrice", OleDbType.Currency, 0, "UnitPrice");
param.SourceVersion = DataRowVersion.Original;
return cmd;
}
static void CreateSprocs()
{
OleDbCommand cmd = cn.CreateCommand();
string strSQL;
strSQL = "CREATE PROCEDURE spUpdateDetail ￥n￥r" +
" (@OrderID_New int, @ProductID_New int, ￥n￥r" +
" @Quantity_New smallint, @UnitPrice_New money, ￥n￥r" +
" @OrderID_Orig int, @ProductID_Orig int, ￥n￥r" +
" @Quantity_Orig smallint, @UnitPrice_Orig money) ￥n￥r" +
"AS ￥n￥r" +
"UPDATE [Order Details] ￥n￥r" +
" SET OrderID = @OrderID_New, ￥n￥r" +
" ProductID = @ProductID_New, ￥n￥r" +
" Quantity = @Quantity_New, ￥n￥r" +
" UnitPrice = @UnitPrice_New ￥n￥r" +
" WHERE OrderID = @OrderID_Orig AND ￥n￥r" +
" ProductID = @ProductID_Orig AND ￥n￥r" +
" Quantity = @Quantity_Orig AND ￥n￥r" +
" UnitPrice = @UnitPrice_Orig";
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
strSQL = "CREATE PROCEDURE spInsertDetail ￥n￥r" +
" (@OrderID int, @ProductID int, ￥n￥r" +
" @Quantity smallint, @UnitPrice money) ￥n￥r" +
"AS ￥n￥r" +
"INSERT INTO [Order Details] ￥n￥r" +
" (OrderID, ProductID, Quantity, UnitPrice) ￥n￥r" +
" VALUES (@OrderID, @ProductID, @Quantity, @UnitPrice)";
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
strSQL = "CREATE PROCEDURE spDeleteDetail ￥n￥r" +
" (@OrderID int, @ProductID int, ￥n￥r" +
" @Quantity smallint, @UnitPrice money) ￥n￥r" +
"AS ￥n￥r" +
"DELETE FROM [Order Details] ￥n￥r" +
" WHERE OrderID = @OrderID AND ￥n￥r" +
" ProductID = @ProductID AND ￥n￥r" +
" Quantity = @Quantity AND UnitPrice = @UnitPrice";
cmd.CommandText = strSQL;
cmd.ExecuteNonQuery();
}
10.4.3|独自の更新ロジックの作成
ではここで、コードの中に自分で更新ロジックを用意する利点と欠点をまとめておきましょう。
■■ 利点
自分で更新ロジックを用意する最大の利点は、制御とパフォーマンスです。ADO.NETのDataAdapterでは、Microsoftの従来のデータアクセステクノロジよりも、更新ロジックを制御できる余地が大きくなっています。テーブルに対してじかに更新を適用するほかに、スマートな方法でストアドプロシージャを活用できるようにもなりました。
さらに、データアクセステクノロジに頼らずにデータの出所を判別できるため、どんな結果セットでも更新用に利用できます。ADOカーソルエンジンの場合は、データベースの更新に必要なメタデータをコードの中で用意することができませんでした。つまり、カーソルエンジンが入手する情報がすべてだったわけです。ところが、ADO.NETでは、ストアドプロシージャから返される結果、一時テーブルへのクエリから返される結果、結合クエリから返される結果など、どんな手段で用意したデータであってもDataSetのデータとして利用できますし、そのデータの変更内容をデータベースに適用できます。
さらに、コードの中に自分で更新ロジックを用意すると、アプリケーションのパフォーマンスが向上します。ADOカーソルエンジンを使ってデータベースを更新するコードは、コーディングの量自体は少なくてすみますが、その一方で、ADOカーソルエンジンが、ソーステーブル名、ソース列名、ソーステーブルの主キー情報をデータベースクエリによって抽出する必要があります。データベースシステムのテーブルからメタデータを取得し、そのメタデータに基づいて更新ロジックを生成する方法は、単に更新ロジックをローカルコードから読み込む方法に比べて時間がかかります。
■■ 欠点
自分で更新ロジックを用意する欠点は、ADOカーソルエンジンを使用するメリットの裏返しでもあります。まず、自分で更新ロジックを用意するためにコードの量が多くなります。少し振り返って、ADO.NETのDataAdapterを使ってデータベースを更新するコードと、ADOカーソルエンジンを使ったコードを比べてみてください。ADO.NETの場合は、コードを書くのに時間がかかりすぎて、いやけがさすかもしれません。
もう1つのデメリットは、多くの開発者が更新ロジックのコーディングに慣れていないということです。クエリの中でテーブル名を区切り文字で囲む必要があるだろうか、どんなパラメータマーカーを使ったらよいだろうか、UpdateCommandとDeleteCommandのCommandTextのWHERE句にはどんな列を指定するべきだろうか、日付時刻値のパラメータではOleDbTypeプロパティをどう設定したらよいだろうか…。もちろん、だれでもそういうことで頭を悩ませたくはありません。
ところが、更新ロジックを生成するためのさらにスマートな方法があります。次に、その方法を取り上げましょう。
10.5 | 更新ロジックを生成するためのCommandBuilderオブジェクトの使用
ADO.NETのオブジェクトモデルでは、開発者が自分で更新ロジックを定義できるばかりか、ADOカーソルエンジンによく似た動的な更新ロジック生成機能が用意されています。 それが、CommandBuilderオブジェクトです。CommandBuilderオブジェクトのインスタンスを作成し、そのインスタンスをDataAdapterオブジェクトに関連付ければ、そのCommandBuilderは、DataAdapterオブジェクトのSelectCommandに指定されているクエリに基づいて更新ロジックを生成します。
では、CommandBuilderの働きを見るために、CommandBuilderを使って実際に更新ロジックを生成してみましょう。Order Detailsテーブルを更新する先ほどのコードで使った更新ロジックです。次に示すのは、コンストラクタにOleDbDataAdapterを指定してOleDbCommandBuilderのインスタンスを作成し、新しい行をデータベースに送信するためにCommandBuilderが生成したCommandのテキストを書き出すコードです。
Visual Basic .NET
Dim strConn, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=(local)￥NetSDK;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _
"FROM [Order Details] WHERE OrderID = 10503 " & _
"ORDER BY ProductID"
Dim da As New OleDbDataAdapter(strSQL, strConn)
Dim cb As New OleDbCommandBuilder(da)
Console.WriteLine(cb.GetInsertCommand.CommandText)
Visual C# .NET
string strConn, strSQL;
strConn = "Provider=SQLOLEDB;Data Source=(local)￥￥NetSDK;" +
"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " +
"FROM [Order Details] WHERE OrderID = 10503 " +
"ORDER BY ProductID";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
Console.WriteLine(cb.GetInsertCommand().CommandText);
このクエリのテキストは、新しい行をデータベースに送信するために先ほど手作業で作成したクエリに驚くほどよく似ています。
INSERT INTO Order Details( OrderID , ProductID , Quantity , UnitPrice )
VALUES ( ? , ? , ? , ? )
10.5.1|CommandBuilder オブジェクトが更新ロジックを生成する方法
CommandBuilderがUPDATE、INSERT、DELETEの各クエリを生成するためのロジックは、それほど複雑ではありません。ADOカーソルエンジンの場合と同じように、CommandBuilderも、データベースクエリによって、基本テーブルと列の名前とクエリ結果のキー情報を取り込みます。CommandBuilderによって更新ロジックを生成できるのは、次のすべての条件が満たされている場合です。
- クエリがただ1つのテーブルからデータを返す場合
- そのテーブルに主キーがある場合
- その主キーがクエリ結果に含まれている場合
既に触れたとおり、主キーが存在すれば、クエリ結果に基づいてCommandBuilderが生成する更新ロジックで1つの行しか更新されないことになります。では、CommandBuilderで、クエリの対象となるテーブルの数に制限があるのはなぜでしょうか。その点については、この章であとから取り上げます。
CommandBuilderオブジェクトは、DataAdapterオブジェクトのSelectCommandによって、更新ロジックに必要なメタデータを取得します。この点については、「第4章 データベースのクエリの実行」でも簡単に触れました。CommandオブジェクトのExecuteReaderを使用すれば、クエリ結果と一緒にそのようなメタデータを抽出できます。次に示すのは、そのようなコードの一例です。
Visual Basic .NET
Dim strConn, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=(local)￥NetSDK;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _
"FROM [Order Details] WHERE OrderID = 10503 " & _
"ORDER BY ProductID"
Dim cn As New OleDbConnection(strConn)
Dim cmd As New OleDbCommand(strSQL, cn)
cn.Open()
Dim rdr As OleDbDataReader
rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly Or _
CommandBehavior.KeyInfo)
Dim tbl As DataTable = rdr.GetSchemaTable
rdr.Close()
cn.Close()
Dim row As DataRow
Dim col As DataColumn
For Each row In tbl.Rows
For Each col In tbl.Columns
Console.WriteLine(col.ColumnName & ": " & row(col).ToString)
Next col
Console.WriteLine()
Next row
Visual C# .NET
string strConn, strSQL;
strConn = "Provider=SQLOLEDB;Data Source=(local)￥￥NetSDK;" +
"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " +
"FROM [Order Details] WHERE OrderID = 10503 " +
"ORDER BY ProductID";
OleDbConnection cn = new OleDbConnection(strConn);
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
cn.Open();
OleDbDataReader rdr;
rdr = cmd.ExecuteReader(CommandBehavior.SchemaOnly |
CommandBehavior.KeyInfo);
DataTable tbl = rdr.GetSchemaTable();
rdr.Close();
cn.Close();
foreach (DataRow row in tbl.Rows)
{
foreach (DataColumn col in tbl.Columns)
Console.WriteLine(col.ColumnName + ": " +
row[col].ToString());
Console.WriteLine();
}
このコードを実行すると、CommandBuilderが更新ロジックを生成するために必要とする各列のデータがすべて書き出されます。つまり、列の名前は何か、その列の基本テーブルと基本列の名前は何か、その列は基本テーブルの主キーになっているか、その列の値はlongデータ型（ラージテキストやバイナリ）なのか、浮動小数点列の有効桁数と小数部桁数は何か、といったデータです。
10.5.2|CommandBuilder を使用する利点と欠点
CommandBuilderを使ったコードと、自分で更新ロジックを用意するために使用したコードを比べてみると、CommandBuilderオブジェクトを使用する2つの利点が浮かび上がってきます。1つは、CommandBuilderオブジェクトを使えばコードの量が少なくてすむということ、そしてもう1つは、UPDATE、INSERT、DELETEの各クエリのSQL構文についてあまり知らなくても更新ロジックを生成できるということです。
さらに、CommandBuilderは、自分で更新ロジックを生成しようとして問題にぶつかったときにも役立ちます。CommandBuilderでうまく更新ロジックを生成できたら、CommandオブジェクトのCommandTextプロパティの値や、Parameterオブジェクトのさまざまなプロパティを参考にできます。
また、CommandBuilderは、更新をサポートしなければならないのにデザイン時にはクエリの構造がわからない、といった場合にもたいへん便利です。
一方、ADOカーソルエンジンと同じように、CommandBuilderも実行時に自動的に更新ロジックを生成するので、ADOカーソルエンジンと同じ問題や制限があります。
CommandBuilderは、実行時のパフォーマンスがあまり高くありません。コードの中に自分で更新ロジックを用意した場合に比べて、CommandBuilderが更新ロジックの生成に必要なメタデータを抽出して処理するには時間がかかります。また、CommandBuilderでは、更新ロジックの生成を制御する余地がありません。オプティミスティックな同時実行制御の種類を指定することもできません。さらに、CommandBuilderは、ストアドプロシージャによる更新にも対応していません。
では、デザイン時に簡単に更新ロジックを生成する手段はないのでしょうか。
10.6 | 更新ロジックを生成するためのデータアダプタ構成ウィザードの使用
「第5章 DataAdapterオブジェクトを使用したデータの取得」で見たとおり、データアダプタ構成ウィザードは、OLE DB/SQL Server .NETデータプロバイダを使ってDataAdapterオブジェクトを作成するための機能を備えていますが、実は、更新ロジックを生成してコードの中に格納するときにも、このウィザードを使用できます。
データアダプタ構成ウィザードの1つの目的は、デザイン時に更新ロジックを自動生成して、効率的な更新コードを簡単に作成することです。確かに野心的な目標ではありますが、このウィザードはその点でほとんどの状況に対応できます（もっとも、「ウィザード」にしては使い方が難しいという印象もありますが…）。
まず、デザイナが用意されているプロジェクトアイテム（Windowsフォーム、Webフォーム、Webサービス、コンポーネントなど）を含んだプロジェクトをVisual Studio .NETで開き、そのデザイナにOleDbDataAdapterを追加し、Northwindデータベースへの接続を指定してから、このウィザードのSQLステートメントのページで次のクエリを入力します。
SELECT OrderID, ProductID, UnitPrice, Quantity
FROM [Order Details]
WHERE OrderID = ? ORDER BY ProductID
［次へ］をクリックすると、［ウィザードの結果の表示］画面が表示され、図10.3のような結果が出力されます。
.gif)
▲図10.3 データアダプタ構成ウィザードの［ウィザードの結果の表示］画面
10.6.1|DataAdapter の構造の調査
図10.3からわかるとおり、このウィザードは、新しいDataAdapterのUPDATE、INSERT、DELETEの各クエリを生成しました。［完了］ボタンをクリックしてこのページを閉じてから、コンポーネントトレイで新しいDataAdapterを選択し、［プロパティ］ウィンドウを表示して、DataAdapterオブジェクトのDeleteCommandを探します。CommandTextプロパティを選択してから、そのプロパティの値の右側にあるボタンをクリックすると、クエリビルダが表示され、DataAdapterオブジェクトのDeleteCommandのCommandTextが書き出されます（図10.4を参照）。
.gif)
▲図10.4 ウィザードによって生成されたDeleteCommand
この画面からわかるとおり、保留状態の削除操作を実行するためにデータアダプタ構成ウィザードが生成したクエリは、先ほど手作業で作成したクエリと同じです。さらに、DataAdapterオブジェクトのInsertCommandとUpdateCommandに移動して、ウィザードが生成した更新ロジックの残りの部分を確認してみてください。
10.6.2|更新ロジックの構築の選択肢
このウィザードの［SQLステートメントの生成］画面には、［詳細オプション］ボタンがあります。このボタンをクリックすると、一連のオプションを含んだダイアログボックスが表示されます（図10.5を参照）。これらのオプションによって、データアダプタ構成ウィザードから生成される更新ロジックの細かい点を制御できます。
データベースからデータを取り出すためだけにDataAdapterを使用する場合は、［INSERT、UPDATE、およびDELETEステートメントの生成］オプションをオフにして、デザイン時にも実行時にも少しの時間を節約できます。
既定の設定では、保留状態の更新や削除を実行するクエリのWHERE句にBLOB以外のすべての列が追加されますが、［オプティミスティック同時実行制御］オプションをオフにすると、そのWHERE句に主キー列だけが組み込まれます。
.gif)
▲図10.5 データアダプタ構成ウィザードの［SQL生成の詳細オプション］画面
SQL Serverなど、一部のデータベースでは、データ行を返すバッチクエリがサポートされています。そのようなデータベースと通信するDataAdapterを作成する場合は、［データセットの更新］オプションが表示されます（既定でオンになっています）。 このオプションをオンのままにしておくと、データベースを更新した直後に変更行の内容を再び取り出すためのクエリが生成されます。つまり、DataAdapter.Updateの呼び出し後のDataSetには、timestamp値や自動インクリメント値など、新しいサーバー生成値が取り込まれるということです。
この機能については、次の「第11章 さらに進んだ更新のシナリオ」で詳しく説明します。また、結果を返すバッチクエリをサポートしていないデータベースについても、同じような機能を実現する方法を取り上げることになっています。
10.6.3|更新を実行するためのストアドプロシージャの使用
データアダプタ構成ウィザードは、ストアドプロシージャを使ってSQL Serverデータベースを更新するDataAdapterオブジェクトを作成する場合にも役立ちます。このウィザードの［クエリの種類の選択］画面には、［既存のストアドプロシージャを使用］オプションが表示されます（図10.6を参照）。そのオプションを選択してから、［次へ］をクリックします。
.gif)
▲図10.6 データアダプタ構成ウィザードの［クエリの種類の選択］画面
次の画面では、DataAdapterの各Commandオブジェクトのストアドプロシージャを選択します。最初に選択するのは、DataAdapterオブジェクトのSelectCommandのストアドプロシージャです。図10.7にあるように、選択可能なストアドプロシージャがドロップダウンリストボックスに表示されます。ストアドプロシージャを選択すると、そのストアドプロシージャから返される列がリストの右側に表示されます。
.gif)
▲図10.7 DataAdapterオブジェクトのSelectCommandのストアドプロシージャを選択する
DataAdapterオブジェクトのSelectCommandの設定が終わったら、更新用のCommandオブジェクトのストアドプロシージャを指定します。更新用のストアドプロシージャのパラメータにSourceColumnプロパティを設定するには、ウィザードの右側のドロップダウンリストを使用します（図10.8を参照）。
.gif)
▲図10.8 InsertCommandのパラメータにSourceColumnプロパティを設定する
|
データアダプタ構成ウィザードでは、ParameterオブジェクトのSourceVersionプロパティを設定できません。そのプロパティの既定値はCurrentなので、パラメータを変更列の元の値にバインドする場合は、［プロパティ］ウィンドウで、パラメータのSourceVersionプロパティの値を変更する必要があります。 |
Visual Studio .NETのEnterprise Editionの場合は、DataAdapterオブジェクトのSelectCommand、UpdateCommand、InsertCommand、DeleteCommandの新しいSQL Serverストアドプロシージャを生成するために、SQLクエリを指定することもできます。データアダプタ構成ウィザードの［クエリの種類の選択］画面で［新しいストアドプロシージャの作成］オプションを選択すると、データベースのデータを返すSQLクエリを入力するための画面が表示されます（図10.9を参照）。
.gif)
▲図10.9 新しいストアドプロシージャのSQLクエリを指定する
次の画面では、ウィザードから生成されるストアドプロシージャの名前を指定します。また、この画面の［SQLスクリプトのプレビュー］ボタンをクリックすると、ストアドプロシージャの作成用として生成されたSQLスクリプトを書き出したダイアログボックスが表示されます（図10.10を参照）。サンプルデータベースを対象としたアプリケーションを作成していた場合は、このダイアログボックスからSQLスクリプトをファイルに保存し、あとから実稼働データベースに対してそのスクリプトを実行する、という使い方ができます。
.gif)
▲図10.10 新しいストアドプロシージャを作成するためのSQLスクリプトを表示した画面
このウィザードが完了すると、データベース内に新しいストアドプロシージャが作成され、新しいDataAdapterがそのストアドプロシージャを使用するように設定されます。
10.6.4|ウィザードを使用する利点と欠点
既に触れたとおり、データアダプタ構成ウィザードを使用する目的の1つは、効率的な更新コードを簡単に作成するための更新ロジックを生成することです。このウィザードでは、CommandBuilderオブジェクトの場合よりも多くのオプションを指定できます。また、開発者がまず書かないような長いコードも生成してくれます。
このウィザードも、CommandBuilderオブジェクトと同じように、更新ロジックを生成するためにスキーマ情報をデータベースから取得しますが、このウィザードの場合は、スキーマ情報をデザイン時に一度だけ取得した後、その情報に基づいて新しく生成したロジックをコードの中に保存します。したがって、CommandBuilderオブジェクトのように実行時にパフォーマンスが下がるということはありません。
しかし、データアダプタ構成ウィザードも完全ではありません。Visual Studio .NETの最初のリリースでは、OLE DB/SQL Server .NETデータプロバイダのDataAdapterオブジェクトだけが、このウィザードの対象になっています。また、同時実行制御のオプションも限られています。このウィザードが生成した更新用のCommandオブジェクトに手を加えることは可能ですが、そのようにして加えた変更は、DataAdapterを再構成したときに失われてしまいます。しかし、このような制限があるとはいえ、このウィザードはやはり非常に便利で強力なツールであることに違いはありません。
10.7 | 更新に関するその他の問題
これで、DataSet内に格納されている変更内容に基づいてデータベースを更新する基本的な方法がわかりました。しかし、自分で更新ロジックを用意する場合は（INSERT、UPDATE、DELETEの各クエリとして用意するとしても、ストアドプロシージャ呼び出しの形で用意するとしても）、基本的なことに加えてさらにいくつかの点を理解しておく必要があります。
たとえば、別のユーザーが加えた変更を間違って上書きしてしまわないようにするための同時実行制御は、どのように設定したらよいでしょうか。同時実行制御のチェックでNULL値はどう処理するのでしょうか。トランザクションの中で更新を実行するには、どうしたらよいのでしょうか。データベースに更新を適用するときに、DataAdapterのTableMappingsコレクションはどんな役割を果たすのでしょうか。
では、こうした点を1つずつ見ていきましょう。
10.7.1|オプティミスティックな同時実行オプション
マルチユーザーのデータベースアプリケーションで、データベースの更新時にオプティミスティックな同時実行制御を活用する場合は、更新クエリの中でオプティミスティックな同時実行制御のチェックを正しく実行することが重要になります。たとえば、そのようなアプリケーションで、2人のユーザーが同じデータ行を要求し、同じデータ行を更新しようとしたとしましょう。どういうことになるでしょうか。その結果は、更新クエリの作成方法によって決まります。
SQL更新クエリのオプティミスティックな同時実行制御には、4つの基本的なオプションがあります。
■■ 主キー列だけを含めるオプション
これは、SQLのUPDATEクエリとDELETEクエリに主キーだけを含めるというオプションです。この場合の更新は、「最新操作の優先」という形になります。どちらの更新操作も成功しますが、データベースに両方の更新内容が共存することは不可能なので、結果的には片方だけが残ります。つまり、あとから適用された更新によって、それ以前の更新が上書きされるということです。
この流れをまとめると、次のようになります。
- ユーザーAが行を取り出す
- ユーザーBがそれと同じ行を取り出す
- ユーザーBがその行を変更し、変更内容をデータベースに適用する（更新は成功する）
- ユーザーAがその行を変更し、変更内容をデータベースに適用する（更新は成功し、ユーザーBの変更内容を上書きする）
ユーザーAは、最初のクエリで行を取得してから、データベースに変更内容を適用するまでの間に、別のユーザーによってデータベースの内容が変更されたことに気づきもしません。
「最新操作の優先」が望ましい場合は、このオプションを選択できます。しかし、ユーザー同士の間で無意識による上書きを防止したければ、このオプションでは不都合です。
オプティミスティック同時実行制御のこのオプションは、CommandBuilderオブジェクトには用意されていませんが、データアダプタ構成ウィザードからは利用できます。そのためには、［詳細オプション］ページで、［オプティミスティック同時実行制御］チェックボックスをオフにします。
■■ WHERE句にすべての列を含めるオプション
では、「最新操作の優先」による更新では都合が悪い場合は、どうすればよいでしょうか。つまり、ユーザーAがデータベースクエリで行を取得してからの行の変更内容をデータベースに適用するまでの間にデータベースに対して加えられた変更を、ユーザーAが上書きしないようにするというケースです。
CommandBuilderもデータアダプタ構成ウィザードも、WHERE句にすべての列を含めるというのが既定の動作になっています。このロジックを使用すれば、行を取り出してから保留状態の変更内容をデータベースに反映させるまでの間に他のユーザーが適用した変更内容が上書きされなくなります。
たとえば、ユーザーAとユーザーBが顧客データの同じ行を取り出すとします。ユーザーBがContactName列に変更を加えて、その変更内容をデータベースに適用する場合、アプリケーションは、UPDATEクエリのWHERE句にすべての列を含めるので、UPDATEクエリは次のようになります。
UPDATE Customers
SET CustomerID = 'ABCDE', CompanyName = 'Original Company Name',
ContactName = 'New Contact', Phone = '800-555-1212'
WHERE CustomerID = 'ABCDE' AND
CompanyName = 'Original Company Name' AND
ContactName = 'Original Contact' AND
Phone = '800-555-1212'
ところが、その間に、ユーザーAも顧客データのその同じ行を取り込み、CompanyName列の値を変更します。ユーザーAがその行を取り出したのは、ユーザーBがContactName列への変更内容をデータベースに適用する前だったので、ユーザーAのUPDATEクエリは次のようになります。
UPDATE Customers
SET CustomerID = 'ABCDE', CompanyName = 'New Company Name',
ContactName = 'Original Contact', Phone = '800-555-1212'
WHERE CustomerID = 'ABCDE' AND
CompanyName = 'Original Company Name' AND
ContactName = 'Original Contact' AND
Phone = '800-555-1212'
この場合、ユーザーAがデータベースを更新しようとした時点では、データベース内のこのデータ行のContactName列の値が既にユーザーBによって変更されているため、このクエリのWHERE句の基準に該当する行はテーブルの中にもう存在しません。したがって、データベース内のその顧客データ行の更新はできないということになります。DataAdapterは、そのクエリによって更新された行の数を確認するためにさらに別のクエリを実行し、その結果から目的の行が実際には更新されなかったことを判別し、その結果に合わせてDataRowの状態を設定する、という流れになります。このような競合を検出して解決する方法については、「第11章 さらに進んだ更新のシナリオ」で詳しく取り上げます。
これは、CommandBuilderオブジェクトで使用される同時実行制御オプションであり、データアダプタ構成ウィザードの既定の設定でもあります。
|
基本的に、データベースでは、BLOB値同士の比較ができません。BLOB列にはメガバイト単位の巨大なデータが格納されることもあるため、BLOB列の比較操作は（不可能ではないにしても）極度に効率が落ちてしまいます。CommandBuilderやデータアダプタ構成ウィザードなどのコード生成ツールでは、更新用クエリのWHERE句にBLOB列は含まれません。開発者が自分で更新ロジックを用意する場合も、この点を押さえておく必要があります。 |
■■ 主キー列とtimestamp列を含めるオプション
timestamp列を活用すると、更新クエリのWHERE句が簡単になります。SQL Serverのtimestamp列の値は、実際には日付/時刻の情報ではなく、データベース内で一意のバイナリデータです。
SQL Serverのテーブルにtimestamp列を定義すると、行の内容に変更が加えられるたびに、その行のtimestamp列の値が変更されることになります。Customersテーブルにtimestamp列を追加しておけば、先ほどのクエリを次のように変更できます。
UPDATE Customers
SET CustomerID = 'ABCDE', CompanyName = 'Original Company Name',
ContactName = 'New Contact', Phone = '800-555-1212'
WHERE CustomerID = 'ABCDE' AND
TimestampColumn = 0x00000000000000CC
行が更新されるたびにtimestamp列の値が新しく生成されるので、更新クエリのWHERE句に主キー列とtimestamp列を組み合わせて指定すれば、他のユーザーによる変更の上書きを防止できます。
ほとんどのデータベースでは、これと似たようなデータ型がサポートされています。一意のバイナリ値を使用するデータベースもあれば、日付/時刻の値を使用するデータベースもあります。バックエンドデータベースのデータ型の詳細や、行の内容が変更されるたびにその種の値が更新されるようにデータベースを設定する方法については、それぞれのデータベースシステムの資料を参照してください。
オプティミスティック同時実行制御のこのオプションを使って更新ロジックを生成する機能は、今のところ、CommandBuilderでもデータアダプタ構成ウィザードでもサポートされていません。
|
SQL Server 2000から、データ型の名称として、rowversionとtimestampが同義語になりました。SQL Serverの資料では、キーワードとしてtimestampよりもrowversionを使う方が望ましいとされていますが、本書では、現時点で広く認知されているtimestampの方を採用しています。 |
個人的には、このようにして同時実行制御のチェックに主キー列とtimestamp列を組み合わせる方法を愛用しています。この方法は、更新ロジックがシンプルであり、1回の更新処理で照合する列の数が少なくてすむからです。
■■ 主キー列と変更列を含めるオプション
ADOカーソルエンジンの場合は、更新クエリのWHERE句に主キー列と変更列の元の値だけを含めるというのが既定の動作になっています。また、UPDATEクエリのSET句にも、変更列だけを含めるようになっています。
では、先ほどのマルチユーザーアプリケーションで、この更新オプションを使うとどうなるでしょうか。たとえば、ユーザーAとユーザーBが同じ顧客データ行を同時に取り出したとします。2人のユーザーはそれぞれ違う列を変更します。ユーザーAはCompanyName列、ユーザーBはContactName列です。この状態で、まずユーザーBがContactName列の変更をデータベースに反映させます。ユーザーBのUPDATEクエリは、次のとおりです。
UPDATE Customers
SET ContactName = 'New Contact'
WHERE CustomerID = 'ABCDE' AND
ContactName = 'Original Contact'
その後、ユーザーAがCompanyName列の保留状態の変更内容をデータベースに適用します。ユーザーAのUPDATEクエリは、次のとおりです。
UPDATE Customers
SET CompanyName = 'New Company Name'
WHERE CustomerID = 'ABCDE' AND
CompanyName = 'Original Company Name'
最初、行は次のような内容でした。
CustomerID CompanyName ContactName
---------- --------------------- ----------------
ABCDE Original Company Name Original Contact
まずユーザーBによって次のように変更されます。
CustomerID CompanyName ContactName
---------- --------------------- ----------------
ABCDE Original Company Name New Contact
さらにユーザーAによって次のように変更されます。
CustomerID CompanyName ContactName
---------- --------------------- ----------------
ABCDE New Company Name New Contact
このように、どちらの更新操作も成功し、ユーザーBによる変更はユーザーAによる変更によって上書きされません。
ところが、ADO.NETのDataAdapterの構造は、このオプションによる更新に適していません。このオプションでは、保留状態の変更内容が含まれる行の変更列に基づいてクエリの構造を修正する必要がありますが、DataAdapterは、更新クエリのパラメータ値を行単位で渡すだけで、パラメータクエリの実際の構造は変更しないからです。
理論的には、該当するCommandオブジェクトの構造を動的に変更するコードを作成し、DataAdapterオブジェクトのRowUpdatingイベントを処理するときにそのコードを使用するという方法も可能です。確かに、この更新オプションにもそれなりの利点がありますが、やはり欠点の方が大きいと思います。
10.7.2|NULL値の処理
NorthwindデータベースのCustomersテーブルに含まれているRegion列には、15文字以下の文字列またはNULLが入ります。現に、Region列の値がNULLになっている行はかなりあります。その種の行を取り出すためのクエリとして、次のようなクエリが思い浮かぶかもしれません。
SELECT CustomerID, CompanyName, ContactName, Phone
FROM Customers WHERE Region = NULL
ところが、このクエリをADO.NETで使用しても、SQLクエリアナライザで実行しても、返される行数はゼロです。つまり、1行も返されません。
データベースの世界でNULLは特殊な値であり、特にクエリの中でNULL値の比較を実行する場合は特殊な動作をします。ANSI規格では、=演算子による比較の対象にNULL値を指定することはできません。むしろ、その種のクエリでは、IS NULLを使用する必要があります。CustomersテーブルでRegion列の値がNULLになっている行を取り込むクエリは、次のとおりです。
SELECT CustomerID, CompanyName, ContactName, Phone
FROM Customers WHERE Region IS NULL
それにしても、DataAdapterを使ってデータベースを更新する処理と、このNULL値はどんな関係があるのでしょうか。一例として、Order Detailsテーブルの行の変更内容をデータベースに適用するために作成した先ほどのCommandオブジェクトのCommandTextを見てみましょう。
UPDATE [Order Details]
SET OrderID = ?, ProductID = ?, Quantity = ?, UnitPrice = ?
WHERE OrderID = ? AND ProductID = ? AND
Quantity = ? AND UnitPrice = ?
このクエリで参照している列は、いずれもNULL値を受け入れないので、このクエリのWHERE句は比較的シンプルです。しかし、仮にQuantity列とUnitPrice列がNULL値を受け入れるとしたら、どうなるでしょうか。たとえば、Quantity列の値がNULLになっている行のそのNULL値を20に変更する場合、パラメータを実際の値に書き換えると、クエリは次のようになります。
UPDATE [Order Details]
SET OrderID = 12345, ProductID = 1, Quantity = 20, UnitPrice = 18
WHERE OrderID = 12345 AND ProductID = 1 AND
Quantity = Null AND UnitPrice = 18
この場合は、どの行も変更されません。問題は、WHERE句のQuantity = Nullという部分です。データベース内の目的の行のQuantity列はNULLですが、Null = Nullはfalseになってしまうため、その行は更新されません。
では、同時実行制御のチェックでNULL値を正しく指定するには、クエリのWHERE句をどう変更すればよいのでしょうか。NULLを受け入れる列の場合、その列を指定するときに、クエリの次の部分を変更します。
ColumnName = ?
変更後の指定は、次のようになります。
(ColumnName = ? OR ((ColumnName IS NULL) AND (? IS NULL)))
これで、列の値とパラメータの値が同じ非NULL値の場合や、列の値とパラメータの値が両方ともNULLの場合に、このWHERE句がtrueになります。
たとえば、DataAdapterから、CustomersテーブルのCustomerID、CompanyName、ContactName、Phoneの各列を対象としたクエリを実行するとしましょう。CustomerID列とCompanyName列はNULLを受け入れませんが、ContactName列とPhone列はNULLを受け入れるので、更新クエリのWHERE句でNULLチェックを正しく設定する必要があります。データアダプタ構成ウィザードを使って更新ロジックを作成すると、変更行をデータベースに適用する次のようなクエリが生成されます。このクエリでは、正しいNULLチェックが設定されています。
UPDATE Customers
SET CustomerID = ?, CompanyName = ?, ContactName = ?, Phone = ?
WHERE (CustomerID = ?) AND (CompanyName = ?) AND
(ContactName = ? OR ? IS NULL AND ContactName IS NULL)
AND (Phone = ? OR ? IS NULL AND Phone IS NULL)
既に確認しましたが、データアダプタ構成ウィザードが生成する更新ロジックは非常によくできています。開発者が自分で更新ロジックを用意する場合も、このウィザードが生成するコードを参考にすれば、コードのダブルチェックができます。
10.7.3|トランザクション内での更新の実行
すべての更新操作を1つの作業単位の中で実行し、すべてが成功するか、すべてが失敗するかのどちらかにしたい場合は、どうすればよいでしょうか。答えは簡単です。すべての更新を1つのトランザクションの中に組み込むということです。しかし、DataAdapterには、Transactionプロパティが用意されていません。
DataAdapterは、実際に更新を実行するわけではありません。むしろ、実際の更新操作は、Commandオブジェクトに任せてしまいます（だからこそ、DataAdapterには、UpdateCommand、InsertCommand、DeleteCommandの各プロパティが用意されています）。そのCommandオブジェクトにはTransactionプロパティが用意されているので、DataAdapterを使ってデータベースに対する更新をトランザクションとして実行するには、DataAdapterが使用するCommandオブジェクトのTransactionプロパティを設定すればよいわけです。
次に、そのようなコードの一例を示します。
Visual Basic .NET
Dim strConn, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=(local)￥NetSDK;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _
"FROM [Order Details] WHERE OrderID = 10503 " & _
"ORDER BY ProductID"
Dim tbl As New DataTable()
Dim cn As New OleDbConnection(strConn)
Dim da As New OleDbDataAdapter(strSQL, cn)
' DataAdapterの更新ロジックを定義する。
' 接続を開いて、クエリの結果を取り込む。
cn.Open()
da.Fill(tbl)
' DataTableの内容を変更する。
' 新しいトランザクションを作成する
Dim txn As OleDbTransaction = cn.BeginTransaction()
' DataAdapterの各CommandのTransactionプロパティを設定する。
da.UpdateCommand.Transaction = txn
da.InsertCommand.Transaction = txn
da.DeleteCommand.Transaction = txn
' 変更をデータベースに適用する。
da.Update(tbl)
' 変更をコミットし、接続を閉じる。
txn.Commit()
cn.Close()
Visual C# .NET
string strConn, strSQL;
strConn = "Provider=SQLOLEDB;Data Source=(local)￥￥NetSDK;" +
"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " +
"FROM [Order Details] WHERE OrderID = 10503 " +
"ORDER BY ProductID";
DataTable tbl = new DataTable();
OleDbConnection cn = new OleDbConnection(strConn);
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
// DataAdapterの更新ロジックを定義する。
// 接続を開いて、クエリの結果を取り込む。
cn.Open();
da.Fill(tbl);
// DataTableの内容を変更する。
// 新しいトランザクションを作成する
OleDbTransaction txn = cn.BeginTransaction();
// DataAdapterの各CommandのTransactionプロパティを設定する。
da.UpdateCommand.Transaction = txn;
da.InsertCommand.Transaction = txn;
da.DeleteCommand.Transaction = txn;
// 変更をデータベースに適用する。
da.Update(tbl);
// 変更をコミットし、接続を閉じる。
txn.Commit();
cn.Close();
トランザクション内でデータベースを更新する処理は、CommandBuilderオブジェクトを使って更新ロジックを生成するときには少し難しくなります。更新ロジックが生成されるのは、CommandBuilderのインスタンスを作成した時点ではなく、DataAdapterオブジェクトのUpdateメソッドを呼び出した時点です。CommandBuilderを使ってトランザクション内でデータベースを更新する場合は、この動作が問題になります。
次のようなコードを使って保留状態の変更内容をデータベースに反映させようとすると、ADO.NETから例外が出されます。
Visual Basic .NET
Dim strConn, strSQL As String
...
Dim tbl As New DataTable()
Dim cn As New OleDbConnection(strConn)
Dim da As New OleDbDataAdapter(strSQL, cn)
Dim cb As New OleDbCommandBuilder(da)
cn.Open()
da.Fill(tbl)
Dim txn As OleDbTransaction = cn.BeginTransaction()
da.Update(tbl)
txn.Commit()
cn.Close()
Visual C# .NET
string strConn, strSQL;
...
DataTable tbl = new DataTable();
OleDbConnection cn = new OleDbConnection(strConn);
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
cn.Open();
da.Fill(tbl);
OleDbTransaction txn = cn.BeginTransaction();
da.Update(tbl);
txn.Commit();
cn.Close();
DataAdapter.Updateを呼び出すと、CommandBuilderは、DataAdapterオブジェクトのSelectCommandを使ってデータベースから必要なメタデータを取り込むことになります。ところが、SelectCommandプロパティのCommandオブジェクトと新しく作成したトランザクションとを関連付ける指定がコードの中に記述されていないので、CommandBuilderは、SelectCommandを使用することができず、CommandBuilderから例外が出されることになります。
この場合、DataAdapterオブジェクトのUpdateメソッドの直前に次の1行を追加すると、コードはエラーになりません。
da.SelectCommand.Transaction = txn
ところが、これでは、CommandBuilderがデータベースからスキーマ情報を取り込む処理がトランザクション内に組み込まれることになってしまいます。基本的には、トランザクション内でのデータベース処理は最小限に押さえた方がよいので、トランザクションの開始前に、CommandBuilderから更新ロジックを生成するという流れが適切だということになります。そのためには、CommandBuilderオブジェクトのGetUpdateCommand（またはGetInsertCommand/GetDeleteCommand）メソッドを呼び出します。
そのあとで、CommandBuilderが生成したCommandオブジェクトを新しいTransactionオブジェクトに関連付ければ、トランザクション内でDataAdapterによるデータベース更新だけが実行されることになります。そのためのコードは、次のとおりです。
Visual Basic .NET
Dim strConn, strSQL As String
...
Dim tbl As New DataTable()
Dim cn As New OleDbConnection(strConn)
Dim da As New OleDbDataAdapter(strSQL, cn)
Dim cb As New OleDbCommandBuilder(da)
cn.Open()
cb.GetUpdateCommand()
da.Fill(tbl)
Dim txn As OleDbTransaction = cn.BeginTransaction()
cb.GetUpdateCommand.Transaction = txn
cb.GetInsertCommand.Transaction = txn
cb.GetDeleteCommand.Transaction = txn
da.Update(tbl)
txn.Commit()
cn.Close()
Visual C# .NET
string strConn, strSQL;
...
DataTable tbl = new DataTable();
OleDbConnection cn = new OleDbConnection(strConn);
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
cn.Open();
cb.GetUpdateCommand();
da.Fill(tbl);
OleDbTransaction txn = cn.BeginTransaction();
cb.GetUpdateCommand().Transaction = txn;
cb.GetInsertCommand().Transaction = txn;
cb.GetDeleteCommand().Transaction = txn;
da.Update(tbl);
txn.Commit();
cn.Close();
10.7.4|TableMappingsコレクションの使用
「第5章 DataAdapterオブジェクトを使用したデータの取得」で見たとおり、DataAdapterオブジェクトのFillメソッドによってDataSetにデータを取り込む場合は、DataAdapterオブジェクトのTableMappingsコレクションによってそのときの処理を制御できます。次に示すのは、DataAdapterのFillメソッドを呼び出して、新しいDataTableを作成し、そのTableNameプロパティをTableに設定するコードです。
Visual Basic .NET
Dim strConn, strSQL As String
strConn = "Provider=SQLOLEDB;Data Source=(local)￥NetSDK;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " & _
"FROM [Order Details] WHERE OrderID = 10503 " & _
"ORDER BY ProductID"
Dim da As New OleDbDataAdapter(strSQL, strConn)
Dim ds As New DataSet()
da.Fill(ds)
Visual C# .NET
string strConn, strSQL;
strConn = "Provider=SQLOLEDB;Data Source=(local)￥￥NetSDK;" +
"Initial Catalog=Northwind;Trusted_Connection=Yes;";
strSQL = "SELECT OrderID, ProductID, Quantity, UnitPrice " +
"FROM [Order Details] WHERE OrderID = 10503 " +
"ORDER BY ProductID";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
DataSet ds = new DataSet();
da.Fill(ds);
新しいDataTableのTableNameをOrder Detailsに設定するには、このコードを2つの方法で変更できます。1つは、Fillメソッドをオーバーロードして、TableNameを指定するという方法です。
Visual Basic .NET
...
Dim da As New OleDbDataAdapter(strSQL, strConn)
Dim ds As New DataSet()
da.Fill(ds, "Order Details")
Visual C# .NET
...
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
DataSet ds = new DataSet();
da.Fill(ds, "Order Details");
もう1つは、DataAdapterオブジェクトのTableMappingsコレクションに1つの項目を追加して、Order DetailsのDataTableが対象になっていることをDataAdapterに対して指定するという方法です。
Visual Basic .NET
...
Dim da As New OleDbDataAdapter(strSQL, strConn)
da.TableMappings.Add("Table", "Order Details")
Dim ds As New DataSet()
da.Fill(ds)
Visual C# .NET
...
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
da.TableMappings.Add("Table", "Order Details");
DataSet ds = new DataSet();
da.Fill(ds);
データベースを更新するときにも、TableMappingsコレクションは、これとよく似た働きをします。DataAdapterオブジェクトのUpdateメソッドにDataSetオブジェクトだけを指定した場合、DataAdapterは、そのTableMappingsコレクションに基づいて、DataSet内のどのDataTableを調べたらよいのかを判別します。
Visual Basic .NET
...
Dim da As New OleDbDataAdapter(strSQL, strConn)
da.TableMappings.Add("Table", "Order Details")
' 更新ロジックを定義する。
Dim ds As New DataSet()
da.Fill(ds)
' 一連の行を変更する。
da.Update(ds)
Visual C# .NET
...
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
// 更新ロジックを定義する。
da.TableMappings.Add("Table", "Order Details");
DataSet ds = new DataSet();
da.Fill(ds);
// 一連の行を変更する。
da.Update(ds);
DataAdapterオブジェクトのTableMappingsコレクションにデータが入っていない状態であれば、UpdateメソッドにDataSetとテーブル名の組み合わせを指定するか、DataTableオブジェクトを指定する必要があります。
Visual Basic .NET
...
Dim da As New OleDbDataAdapter(strSQL, strConn)
' 更新ロジックを定義する。
Dim ds As New DataSet()
da.Fill(ds, "Order Details")
' 一連の行を変更する。
da.Update(ds, "Order Details")
' または
...
Dim da As New OleDbDataAdapter(strSQL, strConn)
' 更新ロジックを定義する。
Dim tbl As New DataTable()
da.Fill(tbl)
' 一連の行を変更する。
da.Update(tbl)
Visual C# .NET
...
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
// 更新ロジックを定義する。
DataSet ds = new DataSet();
da.Fill(ds, "Order Details");
// 一連の行を変更する。
da.Update(ds, "Order Details");
// または
...
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
// 更新ロジックを定義する。
DataTable tbl = new DataTable();
da.Fill(tbl);
// 一連の行を変更する。
da.Update(tbl);
このように、DataTableを指定するロジックは、DataAdapter.Fillの場合とDataAdapter.Updateの場合とで統一しておくのが原則です。
10.7.5|最善の更新方法
ADO.NETでは、データベースを更新する方法がいろいろ用意されています。まず、CommandBuilderオブジェクトを使って実行時に更新ロジックを生成するという方法があります。また、コードの中に自分で更新ロジックを用意して、INSERT、UPDATE、DELETEの各クエリ、またはストアドプロシージャによってデータベースに変更を適用することもできます。さらに、データアダプタ構成ウィザードを使用すれば、デザイン時にそのようなコードを簡単に生成できます。では、どの方法を使うのが一番よいのでしょうか。
その答えは、アプリケーションのさまざまな要素によって決まります。たとえば、パフォーマンスが一番高いのは、DataAdapterオブジェクトからストアドプロシージャの呼び出しによって更新を実行する方法ですが、ストアドプロシージャをサポートしていないデータベース（Microsoft Accessなど）が対象の場合は、INSERT、UPDATE、DELETEの各クエリを使うことになります。それぞれのアプリケーションでどの方法を採用するかを決めるには、それらの要素を考慮する必要があります。
一般論としては、できるかぎりストアドプロシージャによって変更を実行することを強くお勧めします。ただし、複数のバックエンドデータベースへの対応を優先する場合は、クエリに基づく更新（INSERT、UPDATE、DELETE）を使用します。いずれにしても、開発者が自分で更新ロジックを用意するということです。データアダプタ構成ウィザードなどのコード生成ツールを使えば、確かに開発時間を短縮できます。しかし、実行時に更新ロジックを生成するという方法は、最後の手段です。この章で1つだけ覚えておくとすれば、それは次の点です。つまり、絶対に必要な場合以外は、アプリケーションでCommandBuilderオブジェクトを使わない、ということです。
さらに高度な更新シナリオもたくさんあります。たとえば、新しく生成された自動インクリメント値を取り込むには、どうしたらよいのでしょうか。DataSetから、複数の関連テーブルの行の新規追加/削除をデータベースに適用するには、どうすればよいのでしょうか。どうすれば、更新の失敗を検出して処理できるでしょうか。どうしたら、ADO.NETで分散トランザクションを処理できるでしょうか。このような高度な更新シナリオについては、「第11章 さらに進んだ更新のシナリオ」で詳しく取り上げます。
10.8 | OleDbCommandBuilder オブジェクトリファレンス
この章では、ADO.NETのCommandBuilderオブジェクトを取り上げたので、ここにOleDbCommandBuilderオブジェクトのプロパティとメソッドをまとめておきます。
CommandBuilderオブジェクトについては、まず押さえておくべき点があります。つまり、OleDbCommandBuilderオブジェクトとSqlCommandBuilderオブジェクトは、同じ基本クラスから派生したものではない、という点です。現に、最初のリリースのADO.NETには、CommandBuilderという基本クラスがそもそも存在しません。
バックエンドデータベースから必要なメタデータを取り込み、そのデータを更新ロジックに変換するコードを作成することは、それほど簡単ではありません。それが簡単であれば、最初からCommandBuilderオブジェクトなどは必要なかったはずです。.NETデータプロバイダのCommandBuilderクラスを作成するコードは決して単純ではありませんし、実行時にCommandBuilderオブジェクトを使えばパフォーマンスが落ちるのはわかりきったことなので、いくつかのサードパーティ製の.NETデータプロバイダにCommandBuilderクラスが含まれていないのも驚くべきことではありません。
10.8.1|OleDbCommandBuilder オブジェクトのプロパティ
表10.1は、OleDbCommandBuilderオブジェクトのプロパティをまとめたものです。
▼表10.1 OleDbCommandBuilderオブジェクトのプロパティ
| プロパティ | データ型 | 説明 |
|---|---|---|
| DataAdapter | DataAdapter | CommandBuilderが更新ロジックを生成する対象となるDataAdapterを返す |
| QuotePrefix | String | CommandBuilderが列名とテーブル名を区切るために使用するプレフィックス |
| QuoteSuffix | String | CommandBuilderが列名とテーブル名を区切るために使用するサフィックス |
■■ DataAdapter
CommandBuilderオブジェクトのDataAdapterプロパティでは、CommandBuilderオブジェクトに関連付けられているDataAdapterを調査/変更できます。このプロパティは、CommandBuilderオブジェクトのコンストラクタでも設定できます。
■■ QuotePrefix 、QuoteSuffix
CommandBuilderオブジェクトのQuotePrefixプロパティとQuoteSuffixプロパティの値は、CommandBuilderオブジェクトがクエリの中でテーブル名と列名を区切るために使用する文字列です。いずれの場合も、既定値は空文字列です。
10.8.2|OleDbCommonBuilder オブジェクトのメソッド
表10.2は、OleDbCommandBuilderオブジェクトのメソッドをまとめたものです。
▼表10.2 OleDbCommandBuilder オブジェクトのメソッド
| メソッド | 説明 |
|---|---|
| DeriveParameters | ストアドプロシージャを呼び出すCommandのパラメータ情報を取り込む |
| GetDeleteCommand | DataAdapterオブジェクトのDeleteCommandのロジックを含んだCommandを返す |
| GetInsertCommand | DataAdapterオブジェクトのInsertCommandのロジックを含んだCommandを返す |
| GetUpdateCommand | DataAdapterオブジェクトのUpdateCommandのロジックを含んだCommandを返す |
| RefreshSchema | CommandBuilderに対し、その更新ロジックを生成し直す必要があることを通知する |
■■ DeriveParameters
CommandBuilderオブジェクトの機能は、DataAdapterオブジェクトの更新ロジックを生成するだけではありません。CommandBuilderによって、ストアドプロシージャのためのパラメータ情報を取り込むこともできます。次に示すのは、CommandBuilderオブジェクトのDeriveParametersメソッドを使って、ストアドプロシージャ呼び出しのためのパラメータ情報を取り込み、そのデータを書き出すコードです。
Visual Basic .NET
Dim strConn As String
strConn = "Provider=SQLOLEDB;Data Source=(local)￥NetSDK;" & _
"Initial Catalog=Northwind;Trusted_Connection=Yes;"
Dim cn As New OleDbConnection(strConn)
Dim cmd As New OleDbCommand("CustOrdersOrders", cn)
cmd.CommandType = CommandType.StoredProcedure
Dim cb As New OleDbCommandBuilder()
cn.Open()
cb.DeriveParameters(cmd)
cn.Close()
Dim param As OleDbParameter
For Each param In cmd.Parameters
Console.WriteLine(param.ParameterName)
Console.WriteLine(vbTab & param.Direction.ToString)
Console.WriteLine(vbTab & param.OleDbType.ToString)
Console.WriteLine()
Next param
Visual C# .NET
string strConn;
strConn = "Provider=SQLOLEDB;Data Source=(local)￥￥NetSDK;" +
"Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = new OleDbConnection(strConn);
OleDbCommand cmd = new OleDbCommand("CustOrdersOrders", cn);
cmd.CommandType = CommandType.StoredProcedure;
OleDbCommandBuilder cb = new OleDbCommandBuilder();
cn.Open();
cb.DeriveParameters(cmd);
cn.Close();
foreach (OleDbParameter param in cmd.Parameters)
{
Console.WriteLine(param.ParameterName);
Console.WriteLine("￥t" + param.Direction.ToString());
Console.WriteLine("￥t" + param.OleDbType.ToString());
Console.WriteLine();
}
ストアドプロシージャを呼び出すコマンドのパラメータコレクションを作成するときに、Size、Precision、Scaleの各プロパティに設定する値がわからなければ、デザイン時に一度このようなコードを使用すると便利です。
|
DeriveParametersメソッドを使用するためには、指定したCommandオブジェクトのConnectionが開いた状態で使用可能になっている必要があります。 |
■■ GetDeleteCommand 、GetInsertCommand 、GetUpdateCommand
CommandBuilderオブジェクトのGetUpdateCommand、GetInsertCommand、GetDeleteCommandの各メソッドを使えば、CommandBuilderが生成したロジックを確認できます。
この3つのメソッドも、デザイン時に役立ちます。小さなサンプルアプリケーションのコードの中でCommandBuilderを作成してからそれらのメソッドを使えば、CommandBuilderから生成されたCommandTextとパラメータ情報を表示できます。そのクエリとパラメータ情報を使った更新ロジックは、実際のコードの中でいろいろと活用できます。
■■ RefreshSchema
アプリケーションの中でDataAdapterオブジェクトのクエリの構造を変更する場合は、CommandBuilderオブジェクトのRefreshSchemaメソッドを使用すると便利です。
DataAdapterオブジェクトのSelectCommandのCommandTextプロパティに変更があっても、イベントは生成されません。CommandBuilderオブジェクトは、更新ロジックを生成した時点でその役割を終えます。DataAdapterオブジェクトのクエリの構造に変更を加えた結果、CommandBuilderによって更新ロジックを生成し直す必要がある場合は、CommandBuilderオブジェクトのRefreshSchemaメソッドを呼び出します。
RefreshSchemaメソッドを呼び出しても、CommandBuilderがその時点ですぐに更新ロジックを生成するわけではありません。むしろ、CommandBuilderの中に、現在のロジックが正確ではないということを示すフラグがセットされるだけです。DataAdapterオブジェクトのUpdateメソッドか、CommandBuilderオブジェクトのGet<Update/Insert/Delete>Commandメソッドのいずれかを呼び出した時点ではじめて、CommandBuilderから更新ロジックが生成されることになります。
10.9 | よく尋ねられる質問
| Q. | DataAdapterには、クエリ結果をDataSetに取り込む機能と、DataSetに格納されている変更内容をデータベースに適用する機能の両方があるということですが、その2つの作業のために同じオブジェクトを使用する必要があるでしょうか。もしその必要があるのであれば、多層構造のアプリケーションの中間層では、クライアントからデータ抽出の呼び出しがあった時点から、データ更新の呼び出しがあるまでの間、DataAdapterオブジェクトをずっと保持しておかなければならないことになります。本当にそうなのでしょうか。 |
| A. | DataSetにデータを取り込む作業とデータベースを更新する作業の両方に同じDataAdapterを使うことはできますが、それは必須というわけではありません。
たとえば、中間層のオブジェクトに、新しいDataSetを返すメソッドと、DataSet内の保留状態の変更内容をデータベースに適用するメソッドの2つがある場合、それぞれのメソッドに別々のDataAdapterオブジェクトを使用してもかまいません。DataSetにデータを取り込むだけであれば、DataAdapterに更新ロジックは不要です。その逆に、DataAdapterを使ってデータベースを更新するだけであれば、DataAdapterにSelectCommandを定義する必要はありません。 DataAdapterに定義しなければならないのは、実際に実行する必要のあるCommandオブジェクトだけです。たとえば、DataAdapterによって新しい行をデータベースに送信するだけであれば（つまり、既存の行を変更したり削除したりする必要がなければ）、InsertCommandを定義するだけで十分です。この場合は、SelectCommand、UpdateCommand、DeleteCommandの各プロパティに格納されているCommandオブジェクトを実行しないので、最初からそれらのプロパティを設定する必要はありません。 ただし、1つの注意点があります。CommandBuilderオブジェクトを使ってDataAdapterに更新ロジックを定義する場合は、DataAdapterにSelectCommandを定義しておかないと、CommandBuilderから更新ロジックを生成することはできません。 |
| Q. | 結合クエリの結果を1つのDataTableに取り込み、そのDataTable内のデータを変更した後、DataAdapterを使ってその変更内容をデータベースに適用したいのですが、そのためのロジックは、データアダプタ構成ウィザードでもCommandBuilderオブジェクトでも作成できません。どうしたらよいでしょうか。 |
| A. | まず、「第7章 リレーショナルデータの処理」の「7.1.1 結合クエリ」の部分をもう一度ご覧ください。
どちらのコンポーネントでもその種の更新ロジックを生成できないのは、結合クエリから返されるデータの変更操作が実際に何を意味するのかが（それらのコンポーネントにとって）はっきりしないためです。たとえば、注文の詳細データを抽出するためにこれまで使ってきたクエリを次のように変更して、品目の製品名も取り込むとします。 このクエリの結果をDataTableに取り込んでから、1つの行を変更した場合、データベースの内容はどう変更することになるのでしょうか。このクエリを記述した開発者にとって、最終的な意図ははっきりしています。つまり、データベースのOrder Detailsテーブル内の対応する行を変更するということです（ProductsテーブルのProductName列は、変更には影響しない参考情報にすぎません）。しかし、データアダプタ構成ウィザードやCommandBuilderオブジェクトは、その意図をつかめません。 ADOカーソルエンジンは、結合クエリの場合にも自動的に更新ロジックを生成しますが、そのロジックにはどうしてもずれが出てきます。たとえば、先ほどのクエリを使ってADOのRecordsetを生成し、Order Detailsテーブルに由来する列だけを変更すると、ADOカーソルエンジンは、Order Detailsテーブル内の対応する行だけを変更しようとするので、ここまでは問題ありません。 ところが、品目の製品を変更しようとして、ProductID列（Order Detailsテーブル）とProductName列（Productsテーブル）の両方に変更を加え、画面上で行データが正しく見えるようにした場合、ADOカーソルエンジンは、Order DetailsテーブルのProductID列とProductsテーブルのProductName列を変更しようとします。これはどう考えても、開発者の意図からずれています。 一方、ADO.NETは、ADOのような「ブラックボックス」テクノロジではないため、開発者が自分で更新ロジックを用意できます。つまり、この場合に変更したいのはOrder Detailsテーブルだけなので、ProductName列の変更は無視するような更新ロジックを自分で定義すればよいわけです。 では、どうすればそのような更新ロジックを生成できるでしょうか。実際の結合クエリでは、CommandBuilderもデータアダプタ構成ウィザードもあまり役に立ちませんが、この場合は、ProductName列をとりあえずそのままにして、どちらかのツールで更新ロジックを生成した後、ProductName列の操作をクエリに追加し直すという方法があります（まさに裏技といった感じではありますが…）。 ところで、この答えの冒頭で「第7章 リレーショナルデータの処理」のことに触れましたが、その中の「7.2.6 式ベースのDataColumnオブジェクトでのDataRelationオブジェクトの使用」では、複数のDataTableオブジェクトと1つのDataRelationを使って結合クエリと同じような結果を抽出するコードを載せておきました。この方法には、更新ロジックが非常に簡単になるという大きなメリットもあります。各DataTableのデータは、データベース内の各テーブルに対応するので、CommandBuilderでもデータアダプタ構成ウィザードでも、正しい更新ロジックを生成できます。 |
| Q. | オプティミスティックな同時実行制御についての説明はありましたが、ペシミスティックな同時実行制御のことは出てこなかったようです。ADO.NETでペシミスティックな同時実行制御を設定するには、どうしたらよいでしょうか。 |
| A. | ペシミスティックな同時実行制御とは、行を変更する前にその行をロックすることを意味します。DataSetの内容はデータベースから切断されているため、DataSet内の行を変更する前にデータベース内のデータをロックする簡単な方法はありません。しかし、それと似たような機能をトランザクションによって実現できます。
たとえば、画面に表示されるデータにユーザーが変更を加える前に、データベース内のデータをロックすれば、そのユーザーの加える変更が正しくデータベースに反映されるようになります。そのためには、トランザクションを開き、そのトランザクション内で次のクエリを実行して、データベース内の対象行をロックし、他のユーザーからの変更を禁止するわけです。 このクエリは、SQL Server 2000の構文で記述してあります。この構文をサポートしていないデータベースもあるので、SQL Server 2000以外のデータベースを使っている場合は、それぞれのデータベースの資料で、クエリによるデータロックの方法を確認してください。 この方法には、大きな欠点もあります。たとえば、ユーザーがあるデータベースアプリケーションの［変更の送信］ボタンをクリックするのを忘れたまま、台所に行ってドーナツをつまんでコーヒーを飲むとしたら、どうなるでしょうか。データベース内の対象行は、ロックされたままになります。ロックするデータが多くなればなるほど、またロックの時間が長くなればなるほど、アプリケーションの処理能力は落ちてしまいます。 ここで、著者自身の失敗談を書いておきましょう。何年も前のことですが、ここで取り上げたような方法を実際に採用したことがありました。これは別に若気の至りということでもなければ、お金欲しさでやったことでもありません。単に会社からこの「機能」を頼まれたということです。とにかくその会社では、画面上で加えた変更をデータベースにコミットできずに、あとから変更内容を入力し直すというようなことがいやだったわけです。 しかし、その会社には、変更のコミットをしょっちゅう忘れる社員がいました（その人のことを仮にスティーブと呼ぶことにしましょう。なんと社員の半分はスティーブという名前でした）。そんなとき、他のユーザーはデータベースのデータを変更できなくなるので、私のところにやってきます。私はスティーブを探さなければならず、それにはけっこう時間がかかることもありました。この機能はそもそもその会社が希望したのだということをいくら説明しても、だれも納得してくれませんでした。 まあ、当時の私は学生だったので、ペシミスティックなロックのことも含めて、いろいろなことを経験から学んだ時期でした。私にとってはいい薬になりました。だれかが傷ついたわけでもありません（一番傷つかなかったのはスティーブでしょう）。 |
| Q. | DataSetにBLOB列が含まれている場合、そのDataSetを使ってデータベースを更新しても大丈夫でしょうか。 |
| A. | どのクエリでそれほど問題になりませんし、特にストアドプロシージャを使ってデータベースを変更する場合には「必要悪」であるとも言えますが、BLOB列が含まれている場合には、これが非常にやっかいです。なぜでしょうか。
たとえば、社員情報のデータベースのEmployeesテーブルに、社員の名前、ID番号、役職、写真の各列が含まれているとします。問題はその写真の列です。その列には、大量のバイナリ情報（JPEGファイルの内容）が入っているとしましょう。 このような状況で、すべての列をDataTableに取り込んでから、1つの行の役職の列だけを変更したとしても、DataAdapterは、データベース内の対応する行を更新するためのクエリにすべての列の現在の値を書き出すことになります。つまり、文字列を値とする小さな列を修正しただけでも、その社員の写真のバイナリデータの内容全体がデータベースに送信されることになるわけです。 また、このほかにも、データを複数のテーブルに分割するという方法もあります（図10.11を参照）。この図のように、2つのDataTableオブジェクトに分割し、その2つのつながりを示すDataRelationを用意するわけです。親DataTableには、Employeesテーブルの主な列（EmployeeID、LastName、FirstName）を組み込み、子DataTableには、BLOBのPhoto（写真データ）列と、親DataTableとのつながりを確保するためのEmployeeID列を組み込みます。
▲図10.11 BLOB列に基づいてDataTableを分割したようす このような構造のDataSetを使い、それぞれのDataTableにDataAdapterオブジェクトを1つずつ用意しておけば、役職の列だけを変更した場合に、更新クエリに写真の列も組み込まれるということがなくなります。これにより、写真の列の内容が変更された場合にのみ、写真の列の内容がデータベースに送られるようになるわけです。 ただし、バイナリデータをファイルに保存し、そのファイルの位置情報をデータベースに格納してある場合は、ここで取り上げたような説明が当てはまらないのは言うまでもありません。 |
| Q. | この章には、CommandBuilderを使ってOrder Detailsテーブルに変更内容を適用するコードが載っていました。そのコードを使ってみたのですが、「キーワード'Order.'付近に正しくない構文があります。」というエラーになります。どこがおかしいのでしょうか。 |
| A. | 質問に対して質問で答えるのはどうかと思いますが、ここでは1つだけ質問させてください－テーブル名にスペースを入れるのは、いったいなぜでしょうか。「それはそうでしょ。テーブル名と列名にスペースを入れられなかったら、人生真っ暗だよ」などと言う開発者にはいまだかつてお目にかかったことがありません（ちょっと脇道にそれて失礼しました）。
データベースのテーブル名や列名が予約語そのものであったり、テーブル名や列名にスペースなどの特殊文字が含まれている場合、データベースでは、そのようなテーブル名や列名を区切り文字で囲む必要があります。しかし、本書の執筆時点で、CommandBuilderオブジェクトはデータベースクエリによってそのような区切り文字を調べることはしません。CommandBuilderを使ってそのようなテーブル名や列名を含むクエリの更新ロジックを生成する場合、CommandBuilderオブジェクトのQuotePrefixプロパティとQuoteSuffixプロパティの値を指定しないと、更新は失敗します。 いろいろなバックエンドデータベースが対象なので、それらのプロパティの値を指定したくない場合は、どうしたらよいでしょうか。OLE DB .NETデータプロバイダを使っている場合は、データベースから実際の区切り文字を取得するために、OleDbConnectionオブジェクトのGetOleDbSchemaTableメソッドを使用するという方法があります。Microsoft OLE DBプロバイダによって、SQL Server、Oracle、Accessと通信するという環境では、次のコードがうまく動作するのを確認しています。 Visual Basic .NET Visual C# .NET もちろん、最初から区切り文字を必要としないようなテーブル名や列名を使っていれば、このような問題は発生しません。 |
| Q. | ADO.NETのDataAdapterを使って新しい行をデータベースに送信したときのことです。このデータベースには、既定値を定義した列がありますが、新しい行のその列には、その既定値ではなくNULL値が入ってしまいます。ADOの場合は既定値が入っていたのですが、これはどういうことでしょうか。 |
| A. | SQL Serverなどのデータベースでは、データベースの列に対して既定値を定義できます。「第6章 DataSetオブジェクトの処理」で見たとおり、ADO.NETのDataColumnオブジェクトのDefaultValueプロパティは、そのデータベース機能に厳密に対応するわけではありません。そのため、ADO.NETでは、データベースの既定値が自動的に生成されないようになっています。また、さらに別の要素も関係しています。
INSERTクエリで列を省略していたり、列値の代わりにDEFAULTキーワードを指定した場合は、新しい行のその列に既定値が生成されますが、ADO.NETの更新ロジックでは、列が省略されることも、DEFAULTキーワードが使用されることもありません。 ADO.NETの前身のADOでは、行単位で動的に更新が生成されます。データベースに更新を適用する時点で生成されるINSERTステートメントでは、変更されていない列は省略されていました。そのため、ADOによってデータベースに新しい行を追加すると、自動的に既定値が設定されましたが、ADO.NETで追加する行の場合はそのようになりません。 ADO.NETの場合、一番簡単な解決策は、新しい行を追加した時点で目的の列に自動的に既定値を設定するコードをアプリケーションに追加することです。 |
.gif)