■ データベースの更新の実行 ■ ■ 概要 『解らない事を信じて居るから、苦しむのさ。迷信は駄目だ。』スティービーワンダー スティービーワンダーは、勿論データベースの更新の事を謂って居た訳ではないだろうが、此の言葉は 確かに此のテーマにも当て嵌る。ADO.NET のデータベース更新機能は非常に強力だが、.NET がベー タ版だった頃に内外のニュースグループに寄せられた疑問や、セミナー等で出された質問からすると、 其の強力な機能を本当に理解して居る開発者は極く僅かしか居ない様で有る。
此れ迄眼に仕たADO.NET コードの中には、更新ロジックの生成を Command Builder オブジェクトに 頼って居る物が少なく無い。開発者が自分で更新ロジックを用意した方が良いと謂う注記が付いて居る コードも有るが、何故そうする可きなのか、何うすれば然う出来るのかと謂う説明は殆ど無いのが現状 で有る。 コードの仕組を聴かれると、「兎に角、動けば良いじゃない」と答える人が余りにも多いのではないだ ろうか。此処では、其の様な「迷信」紛いのコーディングからの卒業を目指し度いと思う。 ※ 現に、「兎に角動けば良い」と謂う様なスローガンを掲げて居るテクニカルサポートも有る。残念で は有るが、此れが現実で有る。 ADO.NET を使ってデータベースを更新する方法を理解すればする程、独自の更新ロジックを生成した り、ストアドプロシージャに依って更新を実行したりする作業が快適に成る。此処では、DataAdapter を使って、DataSet 内の保留状態の変更内容をデータベースに反映させる方法を取り上げる。更に、パ フォーマンスや機能を犠牲にする事無く、ツールを使って開発時間を短縮する方法、然して何んな場合 に其れが有効かに付いても観て行く事にする。 型指定の無い DataSet オブジェクトや厳密に型指定された DataSet オブジェクトを作成して、 DataAdapter オブジェクトから返されるデータを格納する方法や、DataSet の内容を変更する方法に付 いては、既に理解して居る物と仕て、此処では、其等を踏まえ、DataAdapter オブジェクトを使って、 DataSet に格納されて居る変更内容をデータベースに反映させる為の基本的な方法を観て行く。 例えば、Northwind サンプルデー タベースに 1 つの注文データが有 るとする。図10.1 は、SQL Server のクエリアナライザで其の注文デ ータを抽出する為のクエリで有る。 此処で、顧客から電話が有り、此の 注文データの内容を変更し度いと 謂う希望を伝えられたとする。豆腐 （Tofu）は人気が無く、唐辛子ソー ス（Hot Pepper Sauce）は飛ぶ様 に売れて居り、チャイ（Chai Tea）
の売れ行きも上々で有る。 ▲ 図 10.1 Northwind データベース内の 1 つの注文データの内容
デ
DataAdapter オブジェクトを使用して、クエリ結果を DataSet に格納する事に依り、顧客の注文デー タをDataSet に取り込む為のアプリケーションを作成出来る。亦、DataSet オブジェクトを処理する事 に依り、顧客の要求に応じてDataSet 内のデータを変更する事も出来る。併し、DataSet の内容を変更 しても、其れ丈でデータベース内の対応する行が変更される訳では無い。 DataAdapter には、保留状態の変更内容をデータベースに反映させる為の Update メソッドが用意され て居る。其処で、注文データの変更をデータベースに反映するには、アプリケーションで次の様なコー ドを使用する。 Visual Basic ' 注文データの内容を 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
C#
// 注文データの内容を 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 つの方法を取り上げて、夫々れの長所と短所を観て行く事にする。 ■ 歴史的な経緯 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
Visual Basic
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 を使ってデータベースを更新する処理の長所と短所が良く表われ て居る。此れから、其の長所と短所を観て行く事にする。 ADO Recordset オブジェクトを使用した更新の利点 此の方法の利点と仕て先ず挙げられるのは、コードの量が少なくて済むと謂う事で有る。要は、 Recordset を開き、其の内容を修正し、変更内容をデータベースに適用する丈で有る。本の数行のコー ドで多くの処理を実行出来る訳で有る。 此のコードには、更新ロジックが含まれて居ない。更新ロジックは、実行時に自動的に生成される。此 れは、もう1 つの大きな利点で有る。ADO では、コードの中に更新ロジックを用意する必要が無い。 現に、此のコードは、SQL 言語の事を殆ど知らなくても書ける。ADO カーソルエンジンの更新機能は、 同時実行制御やロックやSQL UPDATE クエリの生成方法を知らなくても使用出来ると謂う事で有る。 其の様な知識の無い開発者でも実際に動作するデータアクセスアプリケーションを作成出来ると謂う
事は、ADO の設計が優れて居る事の証拠でも有る。此れ程多くの開発者が ADO カーソルエンジンを使 ってデータベースを更新して居乍ら、ADO カーソルエンジンが実際に何うやって其れを処理して居る かに付いて何も知らないと謂うのは、確かに驚く可き事で有る（此れは皮肉でも何でも無い）。 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 コレクションを使用すれば、次の様にして其の種のメタデー タを自分で確認出来る。
Visual Basic
With rs.Fields("Quantity")
Debug.Print "BaseTableName = " & .Properties("BaseTableName") Debug.Print "BaseColumnName = " & .Properties("BaseColumnName") Debug.Print "KeyColumn = " & .Properties("KeyColumn")
此の時に、ADO カーソルエンジンの更新機能の大きな短所と仕て最初に取り上げた点が関係して来る。 詰まり、パフォーマンスで有る。ADO カーソルエンジンがテーブルや列や主キーのデータを取得する 為のデータベースクエリを実行すれば、其の分丈パフォーマンスが低下する。データアクセスコードを 作成する開発者は、基本的にデータが何処から来たのかを知って居るが、ADO には、コードの中で其 の情報を用意する為の手段が無い。従って、Recordset を開く度に、此の種のメタデータを毎回データ ベースから取り込む事に成って仕舞う訳で有る。 ADO カーソルエンジンは、「ブラックボックス」の様なテクノロジで有る。開発者が自分で更新ロジッ クを定義する事は出来ない。此れが実は、ADO カーソルエンジンの更新機能の大きな短所と仕て 2 番 目に取り上げた点で有る。ADO カーソルエンジンの更新ロジック自体が素晴らしいのは確かだが、更 新ロジックを制御する余地は殆ど（或いは、全く）無い。Recordset の中にキャッシュされて居る更新 内容をストアドプロシージャの呼出に依って実行する事も出来ない。ADO カーソルエンジンが生成す る更新ロジックを使用し度くない開発者は、無力な状態で放り出されて仕舞う事に成る。 ■ 更新を実行する為の ADO.NET Command オブジェクトの使用 既に観た通り、ADO カーソルエンジンは、データベースを更新する為のパラメータクエリを作成する が、ADO.NET でも、其れと同じ様なパラメータクエリを作成出来る。本稿で後述するが、其の様なパ ラメータ付きの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
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)
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 C#
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
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
C#
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 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
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
C#
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 オブジェクトの更新機能を次に観て観る事にする。
■ 更新を実行する為の ADO.NET DataAdapter オブジェクトの使用
DataAdapter オブジェクトを使用してクエリ結果を DataTable に取り込む事は、DataAdapter の機能 の半分に過ぎない。此のオブジェクトには、DataSet 内の保留状態の変更内容をデータベースに適用す る機能も有る。 DataAdapter オブジェクトに依り変更内容をデータベースに適用する為の更新ロジックを生成する方 法と仕ては、次の3 つが有る。 ・コードを使用してDataAdapter オブジェクトを手作業で設定する方法 ・実行時にCommandBuilder を使用する方法 ・デザイン時にデータアダプタ構成ウィザードを使用する方法 此の3 つの方法には、夫々れ長所と短所が有る。此れから、夫々れの方法を詳しく観て行く事にする。 ■ DataAdapter オブジェクトの手動での構成 DataAdapter オブジェクトには、Command オブジェクトを指定する為のプロパティが 4 つ用意されて 居る。既に観た通り、SelectCommand プロパティには、DataAdapter が DataTable にデータを取り込 む為のCommand を指定出来る。一方、其の他の 3 つのプロパティ（UpdateCommand、InsertCommand、 DeleteCommand）には、DataAdapter が保留状態の変更内容をデータベースに適用する為の Command オブジェクトを指定出来る。 此のアーキテクチャは、ADO のオブジェクトモデルからの大きな変更点で有る。謎めいた「ブラック ボックス」の様なテクノロジは最早無い。DataAdapter が保留状態の変更内容をデータベースに適用す る方法を開発者自身が指定し、DataAdapter が使用する Command オブジェクトを開発者自身が用意 する必要が有る訳で有る。
DataAdapter オブジェクトの Update メソッドは、非常に柔軟で有る。パラメータと仕て、DataSet、 DataSet とテーブル名の組み合わせ、DataTable、DataRow オブジェクトの配列の孰れかを指定出来る。 DataAdapter オブジェクトの Update メソッドを何んな方法で呼び出すにしても、DataAdapter は、 保留状態の変更内容を指定のCommand に依って実行しようとする。先程の SubmitChangesByHand プロシージャの中で実行して居た総ての作業は、DataAdapter オブジェクトの Update メソッドを 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 を参照され度い。
▲ 図 10.2 Parameter オブジェクトと DataColumn オブジェクトのバインド
次に示すのは、パラメータ付きの Command オブジェクトを作成した上で、Parameter オブジェクト のSourceColumn プロパティと SourceVersion プロパティを設定するコードで有る。SourceVersion プ ロパティの既定値はDataRowVersion.Current なので、此のプロパティを明示的に設定する必要が有る のは、Parameter オブジェクトを列の元の値にバインドする場合に限られる。
Visual Basic
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
C#
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;
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
Private Sub SubmitChangesViaDataAdapter( )
da.UpdateCommand = CreateDataAdapterUpdateCommand( ) da.InsertCommand = CreateDataAdapterInsertCommand( ) da.DeleteCommand = CreateDataAdapterDeleteCommand( ) da.Update(tbl) End Sub C#
static void SubmitChangesViaDataAdapter( ) { da.UpdateCommand = CreateDataAdapterUpdateCommand( ); da.InsertCommand = CreateDataAdapterInsertCommand( ); da.DeleteCommand = CreateDataAdapterDeleteCommand( ); da.Update(tbl); } 更新を実行する為のストアドプロシージャの使用 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
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
C#
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" +
" @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( ); } 独自の更新ロジックの作成 其れでは此処で、コードの中に自分で更新ロジックを用意する利点と欠点を纏めて置く。 ・利点 自 分 で 更 新 ロ ジ ッ ク を 用 意 す る 最 大 の 利 点 は 、 制 御 と パ フ ォ ー マ ン ス で 有 る 。ADO.NET の DataAdapter では、Microsoft の従来のデータアクセステクノロジよりも、更新ロジックを制御出来る 余地が大きく成って居る。テーブルに対して直に更新を適用する他に、スマートな方法でストアドプロ シージャを活用出来る様にも成った。 更に、データアクセステクノロジに頼らずにデータの出所を判別出来る為、何んな結果セットでも更新 用に利用出来る。ADO カーソルエンジンの場合は、データベースの更新に必要なメタデータをコード の中で用意する事が出来なかった。詰まり、カーソルエンジンが入手する情報が総てだった訳で有る。 処が、ADO.NET では、ストアドプロシージャから返される結果、一時テーブルへのクエリから返され る結果、結合クエリから返される結果等、何んな手段で用意したデータで有ってもDataSet のデータと 仕て利用出来るし、其のデータの変更内容をデータベースに適用出来る。
更に、コードの中に自分で更新ロジックを用意すると、アプリケーションのパフォーマンスが向上する。 ADO カーソルエンジンを使ってデータベースを更新するコードは、コーディングの量自体は少なくて 済むが、其の一方で、ADO カーソルエンジンが、ソーステーブル名、ソース列名、ソーステーブルの 主キー情報をデータベースクエリに依って抽出する必要が有る。データベースシステムのテーブルから メタデータを取得し、其のメタデータに基づいて更新ロジックを生成する方法は、単に更新ロジックを ローカルコードから読み込む方法に比べて時間が懸かる。 ・欠点 自分で更新ロジックを用意する欠点は、ADO カーソルエンジンを使用するメリットの裏返しでも有る。 先ず、自分で更新ロジックを用意する為にコードの量が多く成る。少し振り返って、ADO.NET の DataAdapter を使ってデータベースを更新するコードと、ADO カーソルエンジンを使ったコードを比 べて観て欲しい。ADO.NET の場合は、コードを書くのに時間が懸かり過ぎて嫌気が差すかも知れない。 今 1 つのデメリットは、多くの開発者が更新ロジックのコーディングに慣れて居ないと謂う事で有る。 クエリの中でテーブル名を区切り文字で囲む必要が有るだろうか、何んなパラメータマーカーを使った ら良いだろうか、UpdateCommand と DeleteCommand の CommandText の WHERE 句には何んな列 を指定する可きだろうか、日付時刻値のパラメータでは OleDbType プロパティを何う設定したら良い だろうか…。勿論、誰でも然う謂う事で頭を悩ませ度くは無い。 処が、更新ロジックを生成する為の更にスマートな方法が有る。次に、其の方法を取り上げる。 ■ 更新ロジックを生成する為の CommandBuilder オブジェクトの使用 ADO.NET のオブジェクトモデルでは、開発者が自分で更新ロジックを定義出来る許りか、ADO カー ソルエンジンに良く似た動的な更新ロジック生成機能が用意されて居る。其れが、CommandBuilder オブジェクトで有る。CommandBuilder オブジェクトのインスタンスを作成し、其のインスタンスを DataAdapter オブジェクトに関連付ければ、其の CommandBuilder は、DataAdapter オブジェクトの SelectCommand に指定されて居るクエリに基づいて更新ロジックを生成する。 其れでは、CommandBuilder の働きを観る為に、CommandBuilder を使って実際に更新ロジックを生 成して観る事にする。Order Details テーブルを更新する先程のコードで使った更新ロジックで有る。 次に示すのは、コンストラクタにOleDbDataAdapter を指定して OleDbCommandBuilder のインスタ ンスを作成し、新しい行をデータベースに送信する為にCommandBuilder が生成した Command のテ キストを書き出すコードで有る。 Visual Basic
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)
C#
string strConn, strSQL;
strConn = "Provider=SQLOLEDB;Data Source=(local)￥￥NetSDK;" + "Initial Catalog=Northwind;Trusted_Connection=Yes;";
