SQL CLRコーディング、最初の一歩：SQL Server 2005を使いこなそう（5）（2/3 ページ）

[石橋潤一，株式会社システムインテグレータ]

SQL CLRによるストアドプロシージャ作成

　これでプロジェクトが1つ用意できたので、続いてコーディングを行うためにファイルの追加を行います。「プロジェクト」メニュー内の「ストアドプロシージャの追加」を選択してファイルを追加してください。ファイルの追加を行うと、選択したテンプレートに従ったcsファイルが作成されます。

　それでは簡単なストアドプロシージャを作成してみましょう（リスト2）。

using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public class SampleSQLCLR
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void HelloWorld()
  {
    SqlContext.Pipe.Send("Hello world! It's now " +
        System.DateTime.Now.ToString() + "\n");
    using (SqlConnection conn =
        new SqlConnection("context connection=true"))
    {
      conn.Open();
      SqlCommand cmd = new SqlCommand(
          "SELECT Name FROM HumanResources.Department", conn);
      SqlDataReader dr = cmd.ExecuteReader();
      SqlContext.Pipe.Send(dr);
    }
  }
}

リスト2　簡単なストアドプロシージャ

　このコードでは、おなじみの「Hello world!」メッセージとともに、AdventureWorks内に用意されたテーブルからデータを取得しています。クライアントへのテキストメッセージの送信にはSqlContextクラスのPipeオブジェクトを利用しています。PipeオブジェクトのSendメソッドはオーバーロードされており、Stringを渡した場合は文字列メッセージを、SqlDataReaderを渡した場合は複数行の結果セットを返します。

データベースへの接続

　データベースへの接続は、インプロセスでの接続を行うためにコンテキスト接続を利用しています。ストアドプロシージャや関数を実行しているサーバに接続する場合は、コンテキスト接続を利用します。コンテキスト接続には、同一のトランザクション領域で実行され再認証が不要であるなどの利点があり、通常の接続に比べパフォーマンスの向上とリソースの使用を抑えるメリットがあります。

カスタム属性

　SQL CLRには「カスタム属性」という説明用のキーワードを加えることができます。カスタム属性はオブジェクトのメタデータとともにアセンブリに記録され、SQL Server内部での動作に影響することを説明するために使用します。上記コードではカスタム属性として「Microsoft.SqlServer.Server.SqlProcedure」と記述することにより、コードがストアドプロシージャとして作成されることを説明しています。

　カスタム属性については、ストアドプロシージャやユーザー定義関数の説明だけでなく、データの読み取りや内部動作についてさまざまな属性情報を設定することが可能です。詳細については、MSDN2のドキュメント「CLRルーチンのカスタム属性」を確認してください。

SQL CLRの配置と実行

　それでは、このコードのビルドとSQL Serverへの配置を行いましょう。「ビルド」メニュー内の「SQL CLRのビルド」を選択し、ビルドを実行します。コードに不正があれば、この時点で検知されます。続いて、同じメニュー内から「SQL CLRの配置」を選択し、ビルドによって作成されたアセンブリ（DLL）をSQL Serverへ配置します。配置先はプロジェクト作成の際に指定した接続先となります。

　配置結果の確認は「サーバーエクスプローラ」より行います。接続対象のツリーを展開し、ストアドプロシージャ内に「HelloWorld」が存在すれば、正しく配置が完了しています。このように、コーディングだけでなくSQL Serverへの配置もVisual Studioより行うことができます。

　配置を完了すると、通常のストアドプロシージャと同じようにSQL CLRで作成したストアドプロシージャを呼び出すことができます。Visual Studioからストアドプロシージャを実行するためには、「ソリューションエクスプローラ」内の「Test Scripts」の下にあるTest.sqlの書き換えを行います。デフォルトで用意されたSQLはすべてコメントアウトし、リスト3のコードを追加してください。

EXEC HelloWorld

リスト3　Test.sqlの書き換え

　SQL CLRで作成したストアドプロシージャであっても、実行方法は一般的なストアドプロシージャと同様です。あとはF5を押すか、「デバッグ」メニュー内の「デバッグ開始」を選択すると、上記コードが実行され、結果が「出力」ウィンドウに表示されます（リスト4）。

Hello world! It's now 2006/06/03 12:08:04
〜中略〜
(16 行が返されました)

リスト4　ストアドプロシージャの実行結果

　SQL CLRで作成したストアドプロシージャから無事に結果が返ってきました。C#を利用してストアドプロシージャの作成を行いましたが、非常に容易に開発から配置まで行えることを実感できたはずです。（次ページへ続く）