データベースの更新の実行

Transcription

1 データベース データベースの更新の実行 概要 解らない事を信じて居るから 苦しむのさ 迷信は駄目だ スティービーワンダー スティービーワンダーは 勿論データベースの更新の事を謂って居た訳ではないだろうが 此の言葉は確かに此のテーマにも当て嵌る 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 つの注文データの内容 -1-

2 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 = " & _ "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 = " + "ORDER BY ProductID"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strconn); DataTable tbl = new DataTable("Order Details"); da.fill(tbl); // 注文データの内容を変更する -2-

3 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 オブジェクトは オフラインのデータキャッシュと仕ての働きをしたが 其れ丈では無く データベースを更新すると謂う役目も果たした 次に示すのは 先程の注文データの内容を取り出し 其の内容を変更してから 保留状態の変更内容をデータベースに適用するコードで有る -3-

4 従来の 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 = " & _ "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") = 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 クエリの生成方法を知らなくても使用出来ると謂う事で有る 其の様な知識の無い開発者でも実際に動作するデータアクセスアプリケーションを作成出来ると謂う -4-

5 事は 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 = AND ProductID = 14 UPDATE [Order Details] SET Quantity = 40 WHERE OrderID = 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") End With -5-

6 此の時に 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) Dim pc As OleDbParameterCollection = cmd.parameters -6-

7 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); -7-

8 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 -8-

9 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 -9-

10 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( ); -10-

11 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 = " & _ "ORDER BY ProductID" cn = New OleDbConnection(strConn) da = New OleDbDataAdapter(strSQL, cn) cn.open( ) ResetOrder( ) da.fill(tbl) DisplayOrder(" データベースの元の内容 ") ModifyOrder( ) DisplayOrder("DataSet 内の変更データ ") SubmitChangesByHand( ) -11-

12 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") = 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 -12-

13 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 = " + "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); -13-

14 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 内に保存されて居る変更の種類を確認し 其の保留状態の変更内容をデータベースに反映させるクエリを実行する為の関数を呼び出し 其の関数の戻 -14-

15 り値に基づいて 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 を使って 保留状態の変更内容をデータベースに適用する場合も 同じパラメータクエリを使用する -15-

16 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) -16-

17 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 -17-

18 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; OleDbParameterCollection pc = cmd.parameters; -18-

19 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 と謂うプロシージャ ( 後からコードの中でも使用する ) を呼び出して ストアドプロシージャを作成する事に成る -19-

20 USE Northwind GO CREATE PROCEDURE spupdatedetail money) AS UPDATE [Order Details] SET OrderID ProductID Quantity UnitPrice WHERE OrderID AND ProductID AND Quantity AND UnitPrice GO CREATE PROCEDURE spinsertdetail (@OrderID money) AS INSERT INTO [Order Details] (OrderID, ProductID, Quantity, UnitPrice) @UnitPrice) GO CREATE PROCEDURE spdeletedetail (@OrderID money) AS DELETE FROM [Order Details] WHERE OrderID AND ProductID AND Quantity AND 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) -20-

21 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 -21-

22 strsql = "CREATE PROCEDURE spupdatedetail " & vbcrlf & _ " (@OrderID_New int, " & vbcrlf & _ smallint, " & vbcrlf & _ money, " & vbcrlf & _ int, " & vbcrlf & _ int, " & vbcrlf & _ smallint, " & vbcrlf & _ money) " & vbcrlf & _ "AS " & vbcrlf & _ "UPDATE [Order Details] " & vbcrlf & _ " SET OrderID " & vbcrlf & _ " ProductID " & vbcrlf & _ " Quantity " & vbcrlf & _ " UnitPrice " & vbcrlf & _ " WHERE OrderID AND " & vbcrlf & _ " ProductID AND " & vbcrlf & _ " Quantity AND " & vbcrlf & _ " UnitPrice cmd.commandtext = strsql cmd.executenonquery( ) strsql = "CREATE PROCEDURE spinsertdetail " & vbcrlf & _ " (@OrderID int, " & vbcrlf & _ money) " & vbcrlf & _ "AS " & vbcrlf & _ "INSERT INTO [Order Details] " & vbcrlf & _ " (OrderID, ProductID, Quantity, UnitPrice) " & vbcrlf & _ " @UnitPrice)" cmd.commandtext = strsql cmd.executenonquery( ) strsql = "CREATE PROCEDURE spdeletedetail " & vbcrlf & _ " (@OrderID int, " & vbcrlf & _ money) " & vbcrlf & _ "AS " & vbcrlf & _ "DELETE FROM [Order Details] " & vbcrlf & _ " WHERE OrderID AND " & vbcrlf & _ " ProductID AND " & vbcrlf & _ " Quantity AND 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; -22-

23 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, n r" + money, n r"

24 int, n r" + money) n r" + "AS n r" + "UPDATE [Order Details] n r" + " SET OrderID n r" + " ProductID n r" + " Quantity n r" + " UnitPrice n r" + " WHERE OrderID AND n r" + " ProductID AND n r" + " Quantity AND n r" + " UnitPrice cmd.commandtext = strsql; cmd.executenonquery( ); strsql = "CREATE PROCEDURE spinsertdetail n r" + " (@OrderID int, n r" + money) n r" + "AS n r" + "INSERT INTO [Order Details] n r" + " (OrderID, ProductID, Quantity, UnitPrice) n r" + " @UnitPrice)"; cmd.commandtext = strsql; cmd.executenonquery( ); strsql = "CREATE PROCEDURE spdeletedetail n r" + " (@OrderID int, n r" + money) n r" + "AS n r" + "DELETE FROM [Order Details] n r" + " WHERE OrderID AND n r" + " ProductID AND n r" + " Quantity AND UnitPrice cmd.commandtext = strsql; cmd.executenonquery( ); 独自の更新ロジックの作成 其れでは此処で コードの中に自分で更新ロジックを用意する利点と欠点を纏めて置く 利点 自分で更新ロジックを用意する最大の利点は 制御とパフォーマンスで有る ADO.NET の DataAdapter では Microsoft の従来のデータアクセステクノロジよりも 更新ロジックを制御出来る余地が大きく成って居る テーブルに対して直に更新を適用する他に スマートな方法でストアドプロシージャを活用出来る様にも成った 更に データアクセステクノロジに頼らずにデータの出所を判別出来る為 何んな結果セットでも更新用に利用出来る ADO カーソルエンジンの場合は データベースの更新に必要なメタデータをコードの中で用意する事が出来なかった 詰まり カーソルエンジンが入手する情報が総てだった訳で有る 処が ADO.NET では ストアドプロシージャから返される結果 一時テーブルへのクエリから返される結果 結合クエリから返される結果等 何んな手段で用意したデータで有っても DataSet のデータと仕て利用出来るし 其のデータの変更内容をデータベースに適用出来る -24-

25 更に コードの中に自分で更新ロジックを用意すると アプリケーションのパフォーマンスが向上する 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 = " & _ "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;"; -25-

26 strsql = "SELECT OrderID, ProductID, Quantity, UnitPrice " + "FROM [Order Details] WHERE OrderID = " + "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 (?,?,?,? ) CommandBuilder オブジェクトが更新ロジックを生成する方法 CommandBuilder が UPDATE INSERT DELETE の各クエリを生成する為のロジックは 其れ程複雑では無い ADO カーソルエンジンの場合と同じ様に CommandBuilder も データベースクエリに依って 基本テーブルと列の名前とクエリ結果のキー情報を取り込む CommandBuilder に依って更新ロジックを生成出来るのは 次の総ての条件が満たされて居る場合で有る クエリが只 1 つのテーブルからデータを返す場合 其のテーブルに主キーが有る場合 其の主キーがクエリ結果に含まれて居る場合 既に触れた通り 主キーが存在すれば クエリ結果に基づいて CommandBuilder が生成する更新ロジックで 1 つの行しか更新されない事に成る 其れでは CommandBuilder で クエリの対象と成るテーブルの数に制限が有るのは何故だろうか 其の点に付いては 後述する CommandBuilder オブジェクトは DataAdapter オブジェクトの SelectCommand に依って 更新ロジックに必要なメタデータを取得する Command オブジェクトの ExecuteReader を使用すれば クエリ結果と一緒に其の様なメタデータを抽出出来る 次に示すのは 其の様なコードの一例で有る 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 = " & _ "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 -26-

27 C# 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 = " + "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 データ型 ( ラージテキストやバイナリ ) なのか 浮動小数点列の有効桁数と小数部桁数は何かと謂ったデータで有る CommandBuilder を使用する利点と欠点 CommandBuilder を使ったコードと 自分で更新ロジックを用意する為に使用したコードを比べて観ると CommandBuilder オブジェクトを使用する 2 つの利点が浮かび上がって来る 1 つは CommandBuilder オブジェクトを使えばコードの量が少なくて済むと謂う事 然して今 1 つは UPDATE INSERT DELETE の各クエリの SQL 構文に付いて余り知らなくても更新ロジックを生成出来ると謂う事で有る 更に CommandBuilder は 自分で更新ロジックを生成しようと仕て問題に打つかった時にも役立つ CommandBuilder で旨く更新ロジックを生成出来れば Command オブジェクトの CommandText プロパティの値や Parameter オブジェクトの様々なプロパティを参考に出来る 亦 CommandBuilder は 更新をサポートしなければ成らないのに デザイン時にはクエリの構造が解らないと謂った場合にも非常に便利で有る 一方 ADO カーソルエンジンと同じ様に CommandBuilder も実行時に自動的に更新ロジックを生成するので ADO カーソルエンジンと同じ問題や制限が有る CommandBuilder は 実行時のパフォーマンスが余り高く無い コードの中に自分で更新ロジックを用意した場合に比べて CommandBuilder が更新ロジックの生成に必要なメタデータを抽出して処理するには時間が懸かる 亦 CommandBuilder では 更新ロジックの生成を制御する余地が無い オプティミスティックな同時実行制御の種類を指定する事も出来ない 更に CommandBuilder は ストアドプロシージャに依る更新にも対応して居ない -27-

28 其れでは デザイン時に簡単に更新ロジックを生成する手段は無いのだろうか 更新ロジックを生成する為のデータアダプタ構成ウィザードの使用 データアダプタ構成ウィザードは 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 の様な結果が出力される 図 10.3 データアダプタ構成ウィザードの [ ウィザードの結果の表示 ] 画面 -28-

29 DataAdapter の構造の調査 図 10.3 から解る通り 此のウィザードは 新しい DataAdapter の UPDATE INSERT DELETE の各クエリを生成した [ 完了 ] ボタンをクリックして此のページを閉じてから コンポーネントトレイで新しい DataAdapter を選択し [ プロパティ ] ウィンドウを表示して DataAdapter オブジェクトの DeleteCommand を探す CommandText プロパティを選択してから 其のプロパティの値の右側に有るボタンをクリックすると クエリビルダが表示され DataAdapter オブジェクトの DeleteCommand の CommandText が書き出される ( 図 10.4 を参照 ) 図 10.4 ウィザードに依って生成された DeleteCommand 此の画面から解る通り 保留状態の削除操作を実行する為にデータアダプタ構成ウィザードが生成したクエリは 先程手作業で作成したクエリと同じで有る 更に DataAdapter オブジェクトの InsertCommand と UpdateCommand に移動して ウィザードが生成した更新ロジックの残りの部分を確認して観て欲しい 更新ロジックの構築の選択肢 此のウィザードの [SQL ステートメントの生成 ] 画面には [ 詳細オプション ] ボタンが有る 此のボタンをクリックすると 一連のオプションを含んだダイアログボックスが表示される ( 図 10.5 を参照 ) 此等のオプションに依って データアダプタ構成ウィザードから生成される更新ロジックの細かい点を制御する事が出来る データベースからデータを取り出す為丈に DataAdapter を使用する場合は [INSERT UPDATE 及び DELETE ステートメントの生成 ] オプションをオフにして デザイン時にも実行時にも少しの時間を節約出来る -29-

30 既定の設定では 保留状態の更新や削除を実行するクエリの WHERE 句に BLOB 以外の総ての列が追加されるが [ オプティミスティック同時実行制御 ] オプションをオフにすると 其の WHERE 句に主キー列丈が組み込まれる 図 10.5 データアダプタ構成ウィザードの [SQL 生成の詳細オプション ] 画面 SQL Server 等 一部のデータベースでは データ行を返すバッチクエリがサポートされて居る 其の様なデータベースと通信する DataAdapter を作成する場合は [ データセットの更新 ] オプションが表示される ( 既定でオンに成って居る ) 此のオプションをオンの儘に仕て置くと データベースを更新した直後に変更行の内容を再び取り出す為のクエリが生成される 詰まり DataAdapter.Update の呼出後の DataSet には timestamp 値や自動インクリメント値等 新しいサーバー生成値が取り込まれると謂う事で有る 更新を実行する為のストアドプロシージャの使用 データアダプタ構成ウィザードは ストアドプロシージャを使って SQL Server データベースを更新する DataAdapter オブジェクトを作成する場合にも役立つ 此のウィザードの [ クエリの種類の選択 ] 画面には [ 既存のストアドプロシージャを使用 ] オプションが表示される ( 図 10.6 を参照 ) 其のオプションを選択してから [ 次へ ] をクリックする 図 10.6 データアダプタ構成ウィザードの [ クエリの種類の選択 ] 画面 -30-

31 次の画面では DataAdapter の各 Command オブジェクトのストアドプロシージャを選択する 最初に選択するのは DataAdapter オブジェクトの SelectCommand のストアドプロシージャで有る 図 10.7 に有る様に 選択可能なストアドプロシージャがドロップダウンリストボックスに表示される ストアドプロシージャを選択すると 其のストアドプロシージャから返される列がリストの右側に表示される 図 10.7 DataAdapter オブジェクトの SelectCommand のストアドプロシージャを選択する DataAdapter オブジェクトの SelectCommand の設定が終われば 更新用の Command オブジェクトのストアドプロシージャを指定する 更新用のストアドプロシージャのパラメータに SourceColumn プロパティを設定するには ウィザードの右側のドロップダウンリストを使用する ( 図 10.8 を参照 ) 図 10.8 InsertCommand のパラメータに SourceColumn プロパティを設定する -31-

32 データアダプタ構成ウィザードでは Parameter オブジェクトの SourceVersion プロパティを設定出来ない 其のプロパティの既定値は Current なので パラメータを変更列の元の値にバインドする場合は [ プロパティ ] ウィンドウで パラメータの SourceVersion プロパティの値を変更する必要が有る Visual Studio.NET の Enterprise Edition の場合は DataAdapter オブジェクトの SelectCommand UpdateCommand InsertCommand DeleteCommand の新しい SQL Server ストアドプロシージャを生成する為に SQL クエリを指定する事も出来る データアダプタ構成ウィザードの [ クエリの種類の選択 ] 画面で [ 新しいストアドプロシージャの作成 ] オプションを選択すると データベースのデータを返す SQL クエリを入力する為の画面が表示される ( 図 10.9 を参照 ) 図 10.9 新しいストアドプロシージャの SQL クエリを指定する 次の画面では ウィザードから生成されるストアドプロシージャの名前を指定する 亦 此の画面の [SQL スクリプトのプレビュー ] ボタンをクリックすると ストアドプロシージャの作成用と仕て生成された SQL スクリプトを書き出したダイアログボックスが表示される ( 図 を参照 ) サンプルデータベースを対象と仕たアプリケーションを作成して居た場合は 此のダイアログボックスから SQL スクリプトをファイルに保存し 後から実稼働データベースに対して其のスクリプトを実行すると謂う使い方が出来る 図 新しいストアドプロシージャを作成する為の SQL スクリプトを表示した画面 -32-

33 此のウィザードが完了すると データベース内に新しいストアドプロシージャが作成され 新しい DataAdapter が其のストアドプロシージャを使用する様に設定される ウィザードを使用する利点と欠点 既に触れた通り データアダプタ構成ウィザードを使用する目的の 1 つは 効率的な更新コードを簡単に作成する為の更新ロジックを生成する事で有る 此のウィザードでは CommandBuilder オブジェクトの場合よりも多くのオプションを指定出来る 亦 開発者が先ず書かない様な長いコードも生成して下れる 此のウィザードも CommandBuilder オブジェクトと同じ様に 更新ロジックを生成する為にスキーマ情報をデータベースから取得するが 此のウィザードの場合は スキーマ情報をデザイン時に一度丈取得した後 其の情報に基づいて新しく生成したロジックをコードの中に保存する 従って CommandBuilder オブジェクトの様に実行時にパフォーマンスが下がると謂う事は無い 併し データアダプタ構成ウィザードも完全では無い Visual Studio.NET の最初のリリースでは OLE DB/SQL Server.NET データプロバイダの DataAdapter オブジェクト丈が 此のウィザードの対象に成って居る 亦 同時実行制御のオプションも限られて居る 此のウィザードが生成した更新用の Command オブジェクトに手を加える事は可能だが 其の様に仕て加えた変更は DataAdapter を再構成した時に失われて仕舞う 併し 此の様な制限が有るとは謂え 此のウィザードは矢張り非常に便利で強力なツールで有る事に違いは無い 更新に関する其の他の問題 此れで DataSet 内に格納されて居る変更内容に基づいてデータベースを更新する基本的な方法が解った 併し 自分で更新ロジックを用意する場合は (INSERT UPDATE DELETE の各クエリと仕て用意すると仕ても ストアドプロシージャ呼出の形で用意すると仕ても ) 基本的な事に加えて更に幾つかの点を理解して置く必要が有る 例えば 別のユーザーが加えた変更を間違って上書きして仕舞わない様にする為の同時実行制御は 何の様に設定したら良いだろうか 同時実行制御のチェックで NULL 値は何う処理するのだろうか トランザクションの中で更新を実行するには 何うしたら良いのだろうか データベースに更新を適用する時に DataAdapter の TableMappings コレクションは何んな役割を果たすのだろうか 其れでは 斯うした点を 1 つ宛観て行く事にする オプティミスティックな同時実行オプション マルチユーザーのデータベースアプリケーションで データベースの更新時にオプティミスティックな同時実行制御を活用する場合は 更新クエリの中でオプティミスティックな同時実行制御のチェックを正しく実行する事が重要に成る 例えば 其の様なアプリケーションで 2 人のユーザーが同じデータ行を要求し 同じデータ行を更新しようと仕たとする 何う謂う事に成るだろうか 其の結果は 更新クエリの作成方法に依って決まる SQL 更新クエリのオプティミスティックな同時実行制御には 4 つの基本的なオプションが有る 主キー列丈を含めるオプション -33-

34 此れは 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 = ' ' WHERE CustomerID = 'ABCDE' AND CompanyName = 'Original Company Name' AND ContactName = 'Original Contact' AND Phone = ' ' 処が 其の間に ユーザー A も顧客データの其の同じ行を取り込み CompanyName 列の値を変更する ユーザー A が其の行を取り出したのは ユーザー B が ContactName 列への変更内容をデータベースに適用する前だったので ユーザー A の UPDATE クエリは次の様に成る -34-

35 UPDATE Customers SET CustomerID = 'ABCDE', CompanyName = 'New Company Name', ContactName = 'Original Contact', Phone = ' ' WHERE CustomerID = 'ABCDE' AND CompanyName = 'Original Company Name' AND ContactName = 'Original Contact' AND Phone = ' ' 此の場合 ユーザー A がデータベースを更新しようとした時点では データベース内の此のデータ行の ContactName 列の値が既にユーザー B に依って変更されて居る為 此のクエリの WHERE 句の基準に該当する行はテーブルの中にもう存在しない 従って データベース内の其の顧客データ行の更新は出来ないと謂う事に成る DataAdapter は 其のクエリに依って更新された行の数を確認する為に更に別のクエリを実行し 其の結果から目的の行が実際には更新されなかった事を判別し 其の結果に合わせて DataRow の状態を設定する と謂う流れに成る 此れは 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 = ' ' WHERE CustomerID = 'ABCDE' AND TimestampColumn = 0x CC 行が更新される度に timestamp 列の値が新しく生成されるので 更新クエリの WHERE 句に主キー列と timestamp 列を組み合わせて指定すれば 他のユーザーに依る変更の上書きを防止出来る 殆どのデータベースでは 此れと似た様なデータ型がサポートされて居る 一意のバイナリ値を使用するデータベースも有れば 日付 / 時刻の値を使用するデータベースも有る バックエンドデータベースのデータ型の詳細や 行の内容が変更される度に其の種の値が更新される様にデータベースを設定する方法に付いては 夫々れのデータベースシステムの資料を参照され度い オプティミスティック同時実行制御の此のオプションを使って更新ロジックを生成する機能は 今の処 CommandBuilder でもデータアダプタ構成ウィザードでもサポートされて居ない -35-

36 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 -36-

37 此の様に 孰れの更新操作も成功し ユーザー B に依る変更はユーザー A に依る変更に依って上書きされない 処が ADO.NET の DataAdapter の構造は 此のオプションに依る更新に適して居ない 此のオプションでは 保留状態の変更内容が含まれる行の変更列に基づいてクエリの構造を修正する必要が有るが DataAdapter は 更新クエリのパラメータ値を行単位で渡す丈で パラメータクエリの実際の構造は変更しないからで有る 理論的には 該当する Command オブジェクトの構造を動的に変更するコードを作成し DataAdapter オブジェクトの RowUpdating イベントを処理する時に其のコードを使用すると謂う方法も可能で有る 確かに 此の更新オプションにも其れなりの利点が有るが 矢張り欠点の方が大きいと思う 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 = AND ProductID = 1 AND Quantity = Null AND UnitPrice =

38 此の場合は 何の行も変更されない 問題は 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) 既に確認したが データアダプタ構成ウィザードが生成する更新ロジックは非常に良く出来て居る 開発者が自分で更新ロジックを用意する場合も 此のウィザードが生成するコードを参考にすれば コードのダブルチェックが出来る トランザクション内での更新の実行 総ての更新操作を 1 つの作業単位の中で実行し 総てが成功するか 総てが失敗するかの孰れかに仕度い場合は 何うすれば良いだろうか 答えは簡単で有る 総ての更新を 1 つのトランザクションの中に組み込むと謂う事で有る 併し DataAdapter には Transaction プロパティが用意されて居ない DataAdapter は 実際に更新を実行する訳では無い 寧ろ 実際の更新操作は Command オブジェクトに任せて仕舞う ( 其れ故に DataAdapter には UpdateCommand InsertCommand DeleteCommand の各プロパティが用意されて居る ) 其の Command オブジェクトには Transaction プロパティが用意されて居るので DataAdapter を使ってデータベースに対する更新をトランザクションと仕て実行するには DataAdapter が使用する Command オブジェクトの Transaction プロパティを設定すれば良い訳で有る -38-

39 次に 其の様なコードの一例を示す 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 = " & _ "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( ) C# 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 = " + "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; -39-

40 // 変更をデータベースに適用する da.update(tbl); // 変更をコミットし 接続を閉じる txn.commit( ); cn.close( ); トランザクション内でデータベースを更新する処理は CommandBuilder オブジェクトを使って更新ロジックを生成する時には少し難しく成る 更新ロジックが生成されるのは CommandBuilder のインスタンスを作成した時点ではなく DataAdapter オブジェクトの Update メソッドを呼び出した時点で有る CommandBuilder を使ってトランザクション内でデータベースを更新する場合は 此の動作が問題に成る 次の様なコードを使って保留状態の変更内容をデータベースに反映させようとすると ADO.NET から例外が出される Visual Basic 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( ) C# 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 -40-

41 処が 此れでは CommandBuilder がデータベースからスキーマ情報を取り込む処理がトランザクション内に組み込まれる事に成って仕舞う 基本的には トランザクション内でのデータベース処理は最小限に押さえた方が良いので トランザクションの開始前に CommandBuilder から更新ロジックを生成すると謂う流れが適切だと謂う事に成る 其の為には CommandBuilder オブジェクトの GetUpdateCommand( 又は GetInsertCommand や GetDeleteCommand) メソッドを呼び出す 其の後で CommandBuilder が生成した Command オブジェクトを新しい Transaction オブジェクトに関連付ければ トランザクション内で DataAdapter に依るデータベース更新丈が実行される事に成る 其の為のコードは 次の通りで有る Visual Basic 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( ) C# 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( ); TableMappings コレクションの使用 DataAdapter オブジェクトの Fill メソッドに依って DataSet にデータを取り込む場合は DataAdapter オブジェクトの TableMappings コレクションに依って其の時の処理を制御出来る 次に示すのは DataAdapter の Fill メソッドを呼び出して 新しい DataTable を作成し 其の TableName プロパティを Table に設定するコードで有る -41-

42 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 = " & _ "ORDER BY ProductID" Dim da As New OleDbDataAdapter(strSQL, strconn) Dim ds As New DataSet( ) da.fill(ds) C# 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 = " + "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... 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... Dim da As New OleDbDataAdapter(strSQL, strconn) da.tablemappings.add("table", "Order Details") Dim ds As New DataSet( ) da.fill(ds) C#... 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 を調べたら良いのかを判別する -42-

43 Visual Basic... Dim da As New OleDbDataAdapter(strSQL, strconn) da.tablemappings.add("table", "Order Details") ' 更新ロジックを定義する Dim ds As New DataSet( ) da.fill(ds) ' 一連の行を変更する da.update(ds) C#... 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... 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) C#... 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); -43-

44 此の様に DataTable を指定するロジックは DataAdapter.Fill の場合と DataAdapter.Update の場合とで統一して置くのが原則で有る 最善の更新方法 ADO.NET では データベースを更新する方法が色々用意されて居る 先ず CommandBuilder オブジェクトを使って実行時に更新ロジックを生成すると謂う方法が有る 亦 コードの中に自分で更新ロジックを用意して INSERT UPDATE DELETE の各クエリ 又は ストアドプロシージャに依ってデータベースに変更を適用する事も出来る 更に データアダプタ構成ウィザードを使用すれば デザイン時に其の様なコードを簡単に生成出来る 其れでは 何の方法を使うのが一番良いのだろうか 其の答えは アプリケーションの様々な要素に依って決まる 例えば パフォーマンスが一番高いのは DataAdapter オブジェクトからストアドプロシージャの呼出に依って更新を実行する方法だが ストアドプロシージャをサポートして居ないデータベース (Microsoft Access 等 ) が対象の場合は INSERT UPDATE DELETE の各クエリを使う事に成る 夫々れのアプリケーションで何の方法を採用するかを決めるには 其等の要素を考慮する必要が有る 一般論と仕ては 出来る限りストアドプロシージャに依って変更を実行する事を強く勧める 但し 複数のバックエンドデータベースへの対応を優先する場合は クエリに基づく更新 (INSERT UPDATE DELETE) を使用する 孰れに仕ても 開発者が自分で更新ロジックを用意すると謂う事で有る データアダプタ構成ウィザード等のコード生成ツールを使えば 確かに開発時間を短縮出来る 併し 実行時に更新ロジックを生成すると謂う方法は 最後の手段で有る 本稿で 1 つ丈覚えて置くとすれば 其れは次の点で有る 詰まり 絶対に必要な場合以外は アプリケーションで CommandBuilder オブジェクトを使わないと謂う事で有る 更に高度な更新シナリオも沢山有る 例えば 新しく生成された自動インクリメント値を取り込むには 何うしたら良いのだろうか DataSet から 複数の関連テーブルの行の新規追加 / 削除をデータベースに適用するには 何うすれば良いのだろうか 何うすれば 更新の失敗を検出して処理出来るだろうか 何うしたら ADO.NET で分散トランザクションを処理出来るだろうか OleDbCommandBuilder オブジェクトリファレンス 此の項では ADO.NET の CommandBuilder オブジェクトを取り上げたので 此処に OleDbCommandBuilder オブジェクトのプロパティとメソッドを纏めて置く CommandBuilder オブジェクトに付いては 先ず押さえて置く可き点が有る 詰まり OleDbCommandBuilder オブジェクトと SqlCommandBuilder オブジェクトは 同じ基本クラスから派生した物ではないと謂う点で有る 現に 最初のリリースの ADO.NET には CommandBuilder と謂う基本クラスが抑も存在しない バックエンドデータベースから必要なメタデータを取り込み 其のデータを更新ロジックに変換するコードを作成する事は 其れ程簡単では無い 其れが簡単で有れば 最初から CommandBuilder オブジェクト等は必要なかった筈で有る.NET データプロバイダの CommandBuilder クラスを作成するコードは決して単純では無いし 実行時に CommandBuilder オブジェクトを使えばパフォーマンスが落ちるのは解り切った事なので 幾つかのサードパーティ製の.NET データプロバイダに CommandBuilder クラスが含まれて居ないのも別段驚く可き事では無い -44-

45 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 オブジェクトがクエリの中でテーブル名と列名を区切る為に使用する文字列で有る 孰れの場合も 既定値は空文字列で有る OleDbCommonBuilder オブジェクトのメソッド 表 10.2 は OleDbCommandBuilder オブジェクトのメソッドを纏めた物で有る 表 10.2 OleDbCommandBuilder オブジェクトのメソッドメソッド説明 DeriveParameters GetDeleteCommand GetInsertCommand GetUpdateCommand RefreshSchema DeriveParameters ストアドプロシージャを呼び出す Command のパラメータ情報を取り込む DataAdapter オブジェクトの DeleteCommand のロジックを含んだ Command を返す DataAdapter オブジェクトの InsertCommand のロジックを含んだ Command を返す DataAdapter オブジェクトの UpdateCommand のロジックを含んだ Command を返す CommandBuilder に対し 其の更新ロジックを生成し直す必要が有る事を通知する CommandBuilder オブジェクトの機能は DataAdapter オブジェクトの更新ロジックを生成する丈では無い CommandBuilder に依って ストアドプロシージャの為のパラメータ情報を取り込む事も出来る 次に示すのは CommandBuilder オブジェクトの DeriveParameters メソッドを使って ストアドプロシージャ呼出の為のパラメータ情報を取り込み 其のデータを書き出すコードで有る Visual Basic 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) -45-

46 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 C# 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 メソッドを呼び出す -46-

47 RefreshSchema メソッドを呼び出しても CommandBuilder が其の時点で直ぐに更新ロジックを生成する訳では無い 寧ろ CommandBuilder の中に 現在のロジックが正確では無いと謂う事を示すフラグがセットされる丈で有る DataAdapter オブジェクトの Update メソッドか CommandBuilder オブジェクトの Get<Update/Insert/Delete>Command メソッドの孰れかを呼び出した時点で初めて CommandBuilder から更新ロジックが生成される事に成る FAQ( 良く尋ねられる質問 ) 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: 先ず リレーショナルデータの処理 の 結合クエリ に付いて考えて観る必要が有る 孰れのコンポーネントでも其の種の更新ロジックを生成出来ないのは 結合クエリから返されるデータの変更操作が実際に何を意味するのかが ( 其等のコンポーネントに取って ) 明確ではない為で有る 例えば 注文の詳細データを抽出する為に此れ迄使って来たクエリを次の様に変更して 品目の製品名も取り込むとする -47-

48 SELECT D.OrderID, P.ProductName, D.ProductID, D.Quantity, D.UnitPrice FROM [Order Details] D, Products P WHERE D.OrderID = AND D.ProductID = P.ProductID ORDER BY P.ProductID 此のクエリの結果を 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 列の操作をクエリに追加し直すと謂う方法が有る ( 将に裏技と謂った感じでは有るが ) 処で 此の答えの冒頭で リレーショナルデータの処理 の事に触れたが 複数の DataTable オブジェクトと 1 つの DataRelation を使って結合クエリと同じ様な結果を抽出する事も出来る 此の方法には 更新ロジックが非常に簡単に成ると謂う大きなメリットも有る 各 DataTable のデータは データベース内の各テーブルに対応するので CommandBuilder でもデータアダプタ構成ウィザードでも 正しい更新ロジックを生成出来る Q: オプティミスティックな同時実行制御に付いての説明は有ったが ペシミスティックな同時実行制御の事は出て来なかった様で有る ADO.NET でペシミスティックな同時実行制御を設定するには 何うしたら良いだろうか A: ペシミスティックな同時実行制御とは 行を変更する前に其の行をロックする事を意味する DataSet の内容はデータベースから切断されて居る為 DataSet 内の行を変更する前にデータベース内のデータをロックする簡単な方法は無い 併し 其れと似た様な機能をトランザクションに依って実現出来る 例えば 画面に表示されるデータにユーザーが変更を加える前に データベース内のデータをロッ -48-

49 クすれば 其のユーザーの加える変更が正しくデータベースに反映される様に成る 其の為には トランザクションを開き 其のトランザクション内で次のクエリを実行して データベース内の対象行をロックし 他のユーザーからの変更を禁止する訳で有る SELECT * FROM [Order Details] HOLDLOCK WHERE OrderID = 此のクエリは SQL Server 2000 の構文で記述して有る 此の構文をサポートして居ないデータベースも有るので SQL Server 2000 以外のデータベースを使って居る場合は 夫々れのデータベースの資料で クエリに依るデータロックの方法を確認して欲しい 此の方法には 大きな欠点も有る 例えば ユーザーが或るデータベースアプリケーションの [ 変更の送信 ] ボタンをクリックするのを忘れた儘 台所に行ってドーナツを摘んでコーヒーを飲むとしたら 何う成るだろうか データベース内の対象行は ロックされた儘に成る ロックするデータが多くなれば成る程 亦 ロックの時間が長く成れば成る程 アプリケーションの処理能力は落ちて仕舞う Q:DataSet に BLOB 列が含まれて居る場合 其の DataSet を使ってデータベースを更新しても大丈夫だろうか A: 何のクエリで其れ程問題に成らないし 特にストアドプロシージャを使ってデータベースを変更する場合には 必要悪 で有るとも謂えるが BLOB 列が含まれて居る場合には 此れが非常に厄介で有る 何故だろうか 例えば 社員情報のデータベースの Employees テーブルに 社員の名前 ID 番号 役職 写真の各列が含まれて居るとする 問題は其の写真の列で有る 其の列には 大量のバイナリ情報 (JPEG ファイルの内容 ) が入って居るとする 此の様な状況で 総ての列を DataTable に取り込んでから 1 つの行の役職の列丈を変更したと仕ても DataAdapter は データベース内の対応する行を更新する為のクエリに総ての列の現在の値を書き出す事に成る 詰まり 文字列を値とする小さな列を修正した丈でも 其の社員の写真のバイナリデータの内容全体がデータベースに送信される事に成る訳で有る 亦 此の他にも データを複数のテーブルに分割すると謂う方法も有る ( 図 を参照 ) 此の図の様に 2 つの DataTable オブジェクトに分割し 其の 2 つの繋がりを示す DataRelation を用意する訳で有る 親 DataTable には Employees テーブルの主な列 (EmployeeID LastName FirstName) を組み込み 子 DataTable には BLOB の Photo( 写真データ ) 列と 親 DataTable との繋がりを確保する為の EmployeeID 列を組み込む 図 BLOB 列に基づいて DataTable を分割したようす -49-

50 此の様な構造の 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 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 = " & _ "ORDER BY ProductID" Dim cn As New OleDbConnection(strConn) Dim da As New OleDbDataAdapter(strSQL, cn) cn.open( ) Dim cb As New OleDbCommandBuilder(da) Dim tblschema As DataTable tblschema = cn.getoledbschematable(oledbschemaguid.dbinfoliterals, _ New Object( ) ) cn.close( ) tblschema.primarykey = New DataColumn( ) _ tblschema.columns("literalname") Dim row As DataRow row = tblschema.rows.find("quote_prefix") If Not row Is Nothing Then cb.quoteprefix = row("literalvalue") End If -50-

51 row = tblschema.rows.find("quote_suffix") If Not row Is Nothing Then cb.quotesuffix = row("literalvalue") End If C# 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 = " + "ORDER BY ProductID"; OleDbConnection cn = new OleDbConnection(strConn); OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn); cn.open( ); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); DataTable tblschema; tblschema = cn.getoledbschematable(oledbschemaguid.dbinfoliterals, new object[] ); cn.close( ); tblschema.primarykey = new DataColumn[] tblschema.columns["literalname"]; DataRow row; row = tblschema.rows.find("quote_prefix"); if (row!= null) cb.quoteprefix = row["literalvalue"]; row = tblschema.rows.find("quote_suffix"); if (row!= null) cb.quotesuffix = row["literalvalue"]; 勿論 最初から区切り文字を必要としない様なテーブル名や列名を使って居れば 此の様な問題は発生しない Q:ADO.NET の DataAdapter を使って新しい行をデータベースに送信した時の事で有る 此のデータベースには 既定値を定義した列が有るが 新しい行の其の列には 其の既定値ではなく NULL 値が入って仕舞う ADO の場合は既定値が入って居たのだが 此れは何う謂う事だろうか A:SQL Server 等のデータベースでは データベースの列に対して既定値を定義出来る ADO.NET の DataColumn オブジェクトの DefaultValue プロパティは 其のデータベース機能に厳密に対応する訳では無い 其の為 ADO.NET では データベースの既定値が自動的に生成されない様に成って居る 亦 更に別の要素も関係して居る INSERT クエリで列を省略して居たり 列値の代わりに DEFAULT キーワードを指定した場合は 新しい行の其の列に既定値が生成されるが ADO.NET の更新ロジックでは 列が省略される事も DEFAULT キーワードが使用される事も無い ADO.NET の前身の ADO では 行単位で動的に更新が生成される データベースに更新を適用する時点で生成される INSERT ステートメントでは 変更されて居ない列は省略されて居た 其の為 ADO に依ってデータベースに新しい行を追加すると 自動的に既定値が設定されたが ADO.NET で追加する行の場合は其の様に成らない ADO.NET の場合 一番簡単な解決策は 新しい行を追加した時点で目的の列に自動的に既定値を設定するコードをアプリケーションに追加する事で有る -51-