数週間に渡り、LINQ to SQLをカバーしたブログ投稿シリーズを書いてきました。LINQ to SQLはビルドインのO/RM（オブジェクトリレーショナルマッパー）のことで、.NET Framework 3.5リリースで出荷されます。これにより.NETクラスを使用してリレーショナルデータベースをモデル化することができます。LINQ文を使用してデータベースを検索し、データの更新、挿入、削除を行うことができます。
以下はこのシリーズの最初の6つのパートです。
パート6では、どのようにデータベースのストアドプロシージャ（SPROC）およびユーザ定義関数（UDF）をオプションとして使用してLINQ to SQLデータモデルを使用したデータの検索と取得を行うことができるかについてご紹介しました。本日のブログ投稿では、データベースからデータを更新、挿入、削除するために、オプションとしてどのようにストアドプロシージャを使用することができるかについてお話したいと思います。
これを上手くご説明するために、ゼロから始めて、Northwindのサンプルデータベースに対してデータアクセス層を形成します。
ステップ1:データアクセス層を作成（ストアドプロシージャはまだ使用しないで）
パート2: データモデルクラスを定義のチュートリアルで、以下のようなLINQ to SQLクラスモデルを作成するためにVS 2008にビルドインされたLINQ to SQL ORMデザイナの使用方法についてお話ししました。
データモデルクラスに検証ルールを追加
データモデルクラスやリレーションシップを定義した後に、データモデルにいくつかのビジネスロジック検証を追加します。パーシャルクラスをプロジェクトに追加することでこれを行うことができます。（パート4: データベースの更新のLINQ to SQLチュートリアルで方法については深くカバーしています。）
例えば、顧客の電話番号は有効なパターンに従い、顧客の要求日時が実際の発注日時より以前だった場合注文を追加しないように検証ルールを追加することができます。一度パーシャルクラスで以下の様に定義されると、アプリケーションでデータモデルオブジェクトを更新するコードを書く度にこれらの検証メソッドは自動的に実行されるようになります。
VB:
C#:
データコンテキストにGetCustomer() ヘルパーメソッドを追加
作成されたデータモデルクラスと、それに適用された検証ルールがあるので、これでデータを検索および操作することができます。データモデルクラスに対してLINQ文を書くことで、データベースを検索してそれらを格納することができます。（パート3: データベースの検索 LINQ to SQLチュートリアルでこの方法をカバーしています。）もしくは、データコンテキストにストアドプロシージャをマップし、それを使用してデータモデルクラスを格納することができます。（パート6: ストアドプロシージャを使用してデータを取得 LINQ to SQLチュートリアルでこれをカバーしています。）
LINQ to SQLデータ層を構築する時、通常はよく使用するLINQクエリ（またはストアドプロシージャの呼び出し）をデータコンテキストクラスに追加するヘルパーメソッドの中にカプセル化しておきたいと思われると思います。これは、パーシャルクラスをプロジェクトに追加することでできるようになります。たとえば、"GetCustomer()"というヘルパーメソッドを追加します。このメソッドにより、CustomerID値に基づいてデータベースからCustomerオブジェクトを検索し取得することができます。
VB:
C#:
ステップ2: データ層を使用（ストアドプロシージャをまだ使用しないで）
この時点でデータモデルをカプセル化し、ビジネス検証ルールを統合し、データの検索、更新、挿入、削除を可能にするデータアクセス層ができました。
それを使用した簡単なシナリオ、例えば、既存の顧客オブジェクトを取得、顧客のContactNameとPhone Numberを更新、新しいOrderオブジェクトを作成してそれらを関連付けるようなシナリオを見てみましょう。1つのトランザクション内でこれを全て行うコードを以下のように書くことができます。LINQ to SQLはデータベースに何かが保存される前に必ずビジネスロジック検証ルールがクリーンになるようにします。
VB:
C#:
LINQ to SQLは、データコンテキストから取得したオブジェクトに加えられた修正をモニターし、そこへ追加されたすべてのオブジェクトをトラッキングしています。最後にDataContext.SubmitChanges()を呼び出す時、LINQ to SQLはビジネスロジックルールが有効であるかチェックし、もし有効であれば自動的に適切な動的SQLを生成し上記のCustomerレコードを更新し、Orderテーブルに新しいレコードを挿入します。
ちょっと待って - これってストアドプロシージャを使うという投稿だと思ったんですけど???
これをまだ読まれているとしたら、この投稿でストアドプロシージャはどこに当てはまるのだろうと困惑されているかもしれません。何で上記のように、データモデルオブジェクトと動作するコードの書き方や、その後に起動させる動的SQLの発生方法を紹介しているのだろう？ 何で、挿入、更新、削除を行うストアドプロシージャの呼び出し方法を、その代わりに紹介しないのだろう？と思われているかもしれません。
その理由は、ストアドプロシージャがバックアップするデータモデルオブジェクトと動作するLINQ to SQLのプログラミングモデルは、動的SQLを通じて更新されるものと同じだからです。データモデル検証ロジックを追加した方法は全く同じものです。（上記のデータモデルクラス上の検証ルールは全てストアドプロシージャを使用するときに適用されます。）顧客を取得、更新し、その後新しい注文の関連付けを追加するためにデータアクセス層を使用している上記のコードスニペットもまた、更新に対して動的SQLを使用したのか、もしくはその代わりにストアドプロシージャを使用してデータモデルクラスを構成したのかに関わらず、全く同じになります。
プログラミングモデルの調和は、方法を2通り習得しなくてもいいという点、プロジェクトの始めでストアドプロシージャを使用するのかどうかを決定しなくてもいいという点のどちらにおいても強力なことです。すべての検索、挿入、更新、削除に対して、まずLINQ to SQL ORMが提供する動的SQLのサポートを使用するところから始めます。その後ビジネスおよび検証ルールをモデルに追加します。そして、その後オプションとしてデータマッピングモデルをストアドプロシージャを使用して（必要なければ使用しないで）更新します。データモデルクラスに対して書くコードやテストは、動的SQLまたはストアドプロシージャのどちらを使ったとしても、全く同じになります。
この投稿の残りについては、（同じ検証ルールを使用し、上記にある同じコードスニペットで動作する状態で）更新、挿入、削除を行うストアドプロシージャを使用するために構築したデータモデルをどのように更新することができるかをご紹介します。
挿入、更新、削除のシナリオにストアドプロシージャを使用する方法
動的SQLの代わりに更新処理を行うストアドプロシージャを使用するために構築したデータアクセス層を、1～2通りの方法で修正することができます。
1) LINQ to SQLデザイナを使用して、データモデルクラス上での挿入、更新、削除操作に応じて実行させるストアドプロシージャをグラフィカルに構成します。
または、
2) プロジェクトにNorthwindDataContextのパーシャルクラスを追加して、その上に提供される（データモデルオブジェクトの挿入、更新、削除を行った時に呼び出される）適切な挿入、更新、削除のパーシャルメソッドを実装します。（例えば、InsertOrder、UpdateOrder、DeleteOrder）これらのパーシャルメソッドには更新したいデータモデルインスタンスが渡されるので、その時にそれをデータベース内に保存するためにストアドプロシージャもしくはSQLコードのどちらでも好きな方を実行することができます。
1番目の方法（LINQ to SQLデザイナ）を使用して、呼び出そうとするストアドプロシージャをグラフィカルに構成する場合、（作成されたパーシャルクラスで）生成されたコード（2番目の方法を使用して書くコードと同じ）は背後に隠されます。通常の場合、90％のケースに対してLINQ to SQLデザイナを使用してストアドプロシージャを構成し、そしてより上級なシナリオにおいては、必要に応じて生成されるストアドプロシージャの呼び出しコードをひとひねりするようにカスタマイズすることをお薦めします。
ステップ3: ストアドプロシージャでOrderを挿入
まずOrderオブジェクトから始めて、データモデルでストアドプロシージャを使用するように変更します。
最初にVisual Studioの"サーバーエクスプローラ"ウィンドウに行って、展開されたノードからデータベースの"ストアドプロシージャ"を右クリックし、"新しいストアドプロシージャ..."を選択します。
新しいストアドプロシージャを作成し、"InsertOrder"と名前を付け、新しい注文レコードをOrdersテーブルに挿入します。:
上記で、"OrderID"引数を出力引数としてストアドプロシージャがどのように定義しているかをご確認いただけると思います。これはデータベースのOrderIDカラムが新しいレコードが追加される度に自動インクリメントされるように設定されたIDENTITYカラムだからです。ストアドプロシージャの呼び出し元は呼び出している時にNULLを値として引き渡し、（ストアドプロシージャの最後でSCOPE_IDENTITY()関数を呼び出すことで）出力値として新しく作成されたOrderID値を戻します。
ストアドプロシージャを作成した後、データアクセス層に対してLINQ to SQL ORMデザイナを開きます。このシリーズの1つ前の投稿（パート6: ストアドプロシージャを使用してデータを取得）でお話したように、データコンテキストデザイナのメソッドペイン上にサーバーエクスプローラからストアドプロシージャをドラッグアンドドロップすることができます。新しく作成したInsertOrderストアドプロシージャでこれを行いたいと思います。:
最後のステップは、新しいOrderオブジェクトをデータベースに挿入する時に、データアクセス層にInsertOrderストアドプロシージャを使用することを告げます。これは、LINQ to SQL ORMデザイナの"Order"クラスを選択し、プロパティグリッドで"..."ボタンをクリックし、挿入の実行方法をオーバライドして実現します。
"..."ボタンをクリックすると、ダイアログがポップアップするので、挿入の実行方法をカスタマイズすることができます。
上記をご覧頂くと、デフォルトモード（"Use Runtime"）では、挿入操作を処理する動的SQLをLINQ to SQLに計算、実行させているところをご確認頂けると思います。"Customize"ラジオボタンを選択し、利用可能なストアドプロシージャリストからInsertOrderストアドプロシージャを選択して変更することができます。
LINQ to SQLデザイナは選択したストアドプロシージャに対して引数リストを格納し、Orderクラス上のプロパティをInsertOrderストアドプロシージャの引数にマップすることができます。デフォルトでも賢く名前に基づいてそれらが"最適な一致"されるようにします。必要であればそれらをオーバライドすることができます。
ダイアログ上で"OK"をクリックすれば終わりです。これで新しいOrderがデータコンテキストに追加され、SubmitChanges()メソッドが呼び出される度に、動的SQLが実行される代わりにInsertOrderストアドプロシージャが使用されます。
重要: 今保存のためにストアドプロシージャを使用していますが、先に（このブログ投稿のステップ１で）作成したOrder検証ルールをカプセル化するためのカスタム"OnValidate()"パーシャルメソッドは、全ての変更が保存される前またはストアドプロシージャが発生する前に実行されます。つまり、動的SQLまたはストアドプロシージャのどちらが使われているのかに関わらず、データモデルにおいてビジネスおよび検証ルールをカプセル化するクリーンな方法があり、それらを再利用できるということです。
ステップ4: ストアドプロシージャでCustomerを更新
Customerオブジェクトをストアドプロシージャを使用して更新処理できるように修正してみましょう。
以下のように、新しい"UpdateCustomer"ストアドプロシージャをまず作成します。
上記をご覧頂ければ、@CustomerID引数を渡すことに加え、@Original_CustomerID引数も渡している様子をご確認頂けると思います。CustomersテーブルのCustomerIDカラムは自動インクリメントIDENTITYカラムではありませんので、Customerオブジェクトの更新の一部として修正することができます。従って、レコードを更新するために元のCustomerIDと新しいCustomerIDをストアドプロシージャに提供できるようにしておく必要があります。LINQ to SQLデザイナを使用してこれをマップする方法をこの後見てみたいと思います。
上記ではまたどのようにストアドプロシージャに@Version引数（timestamp型）を引き渡しているかもご覧頂けると思います。これはNorthwindのCustomersテーブルに追加した新しいカラムでオプティミスティック同時実行制御の処理をサポートします。LINQ to SQLシリーズのブログ投稿で後日もっと深くオプティミスティック同時実行制御についてカバーしますが、簡単にまとめると、LINQ to SQLは完全にオプティミスティック同時実行制御をサポートし、そして最後にデータオブジェクトをリフレッシュしてから他のユーザが変更していないかどうかを判断するために、バージョンのタイムスタンプを使用するか、もしくは新旧両方の値をストアドプロシージャへ提供するか、このどちらでも行うことができるようにしています。このサンプルではコードをクリーンにするためにタイムスタンプを使用しています。
ストアドプロシージャを一度作成すると、それをLINQ to SQLデザイナ上にドラッグアンドドロップし、データコンテキスト上のメソッドとして追加することができます。そうすると、CustomerクラスをORMデザイナで選択し、"..."ボタンをクリックすればCustomerオブジェクトの更新動作をプロパティグリッドでオーバーライドすることができます。
"Customize"のラジオボタンを選択して、使用するUpdateCustomerストアドプロシージャを選択します。
Customerオブジェクトのプロパティをストアドプロシージャの引数にマッピングする時、データオブジェクト上の"現在"のプロパティ値を提供するのか、オブジェクトが最初に取得された時のデータベースにあった元の値を提供するのかについて検討しないといけないことに気付かれると思います。例えば、必ずCustomer.CustomerIDプロパティの"現在"の値は@CustomerIDストアドプロシージャ引数にマップされ、元の値は@original_customerIDストアドプロシージャ引数にマップされるようにしておきたいと思います。
ダイアログ上で"OK"をクリックすれば終わりです。これで新しいCustomerが更新され、SubmitChanges()メソッドが呼び出される度に、動的SQLが実行される代わりにUpdateCustomerストアドプロシージャが使用されます。
重要: たとえストアドプロシージャを保存するために使用しているとはいえ、先に（このブログ投稿のステップ１で）作成したCustomerの"OnPhoneChanging()"パーシャルメソッド（Phone Number検証ルールをカプセル化するためのメソッド）が全ての変更が保存される前、またストアドプロシージャが呼び出される前に実行されます。動的なSQLもしくはストアドプロシージャのどちらが使用されているかに関わらず、データモデル上のビジネスおよび検証ルールをカプセル化するクリーンな方法があり、それらを再利用することができます。
ステップ5: データアクセス層を再度使用（今度はストアドプロシージャで）
保存するのに動的SQLの代わりにストアドプロシージャを使用するようデータ層を一度更新すると、データモデルクラスに対して前のステップ2で書いた全く同じコードを再度起動させることができます。
これでCustomerオブジェクトの更新、およびOrderオブジェクトの挿入は動的SQLの代わりにストアドプロシージャを通じて実行されます。定義した検証ロジックは前と同じように実行しますが、データモデルクラスを使用するために書いたデータアクセスコードは全く同じです。
ストアドプロシージャを使用する時のいくつかの上級用注釈
LINQ to SQLとより上級なストアドプロシージャのシナリオで有用だと思われるかもしれないいくつかの注釈です:
ストアドプロシージャ出力引数の利用法:
上記の挿入シナリオ（ステップ３）で、ストアドプロシージャの出力引数を使用して新しいOrderID値（Ordersテーブルの自動インクリメントIDENTITYカラム）をどのように戻すかについてご紹介しました。LINQ to SQLとストアドプロシージャを一緒に使用する時にIDENTITYカラムを戻すということに限定されてはいません。実際、ストアドプロシージャのどの引数に対しても更新し出力値を戻すことができます。この方法は挿入および更新のシナリオの両方に対して使用することができます。そしてLINQ to SQLは返り値を受け取って、それをデータモデルオブジェクトのプロパティ値を更新するために使用します。その際にデータベースに対し2番目のクエリを発行してそれらをリフレッシュおよび格納する必要はありません。
もしストアドプロシージャがエラーを投げたらどうなりますか？
ストアドプロシージャが挿入、更新、削除を行う場合にエラーを発生させた場合、LINQ to SQLは自動的にデータコンテキスト上の現在のSubmitChanges()の呼び出しに関連付いている全ての変更トランザクションをキャンセルし、ロールバックします。これによりデータは常にクリーンで統一された状態が保たれていることが保障されます。
ストアドプロシージャの呼び出しにORMデザイナを使用する代わりにコードを書くことはできますか？
この投稿の前の方でお話したように、LINQ to SQL ORMデザイナを使用して挿入、更新、削除の操作をストアドプロシージャにマップすることも、パーシャルメソッドをデータコンテキストクラス上に追加してプログラム上で発生させるようにすることもどちらでも可能です。ストアドプロシージャを呼び出すUpdateCustomerの動作をオーバライドするために、NorthwindDataContextに対するパーシャルクラスで書くことができるクリアなコード例をお見せします。
実は上記のコードは、デザイナを使用してストアドプロシージャをマップし、Customerオブジェクトの更新動作をそれに関連付けた時にLINQ to SQL ORMデザイナが生成したものです。それを開始ポイントとして使用しより上級仕様にするために追加のロジックを追加することができます。（たとえば、ストアドプロシージャの返り値を使用してカスタムの例外をエラー条件やオプティミスティック同時実行制御などに対して発生させる）
まとめ
LINQ to SQLは非常に柔軟なORMです。これにより、データを取得、更新、挿入するためにクリーンなオブジェクト志向のコードを書くことができるようになります。
一番いいところは、データベースへの保存またはロード方法とは関係なく、データモデルクラスをクリーンに設計することができるところです。動的SQLを使用してデータベースでデータを効率的に取得し更新するためにビルドインのORMエンジンを使用することができます。もしくは、ストアドプロシージャを使用してデータ層を構成することもできます。いいところは、どの保存方法を使用するのかに関わらず、データ層を消費するコード、そしてそれと共に注釈するビジネスロジック検証ルール全てが同じになることです。
このシリーズの今後のブログ投稿では、次のものを含む残りのLINQ to SQL概念をカバーしたいと思います。: シングルテーブルの継承、遅延/Eager（最初から持ってくる）ローディング、オプティミスティック同時実行制御、複数層シナリオの処理。次週はお休みですので、その時にそのうちのいくつかを書く時間があればと思っています。
Hope this helps,
Scott