SQL CLRコーディング、最初の一歩
前回「SQL CLRの仕組みとメリットを理解しよう 」で紹介したSQL CLRの基本情報に続き、今回はSQL CLRの基本的なコーディング手法を紹介したいと思います。
SQL CLRはVB.NETやC#など慣れ親しんだ言語での開発が可能なため、言語を新しく覚える苦労はありません。今回紹介するいくつかのサンプルに目を通せば、基本的な動きがすぐに理解できるはずです。
SQL CLRを有効にする
SQL Server 2005では「デフォルトセキュア」の思想が掲げられ、デフォルト（既定）ではセキュリティが最も厳しい状態となっています。このためSQL CLRは既定で無効となっており、これを有効にする必要があります。
スタートメニューから「Microsoft SQL Server 2005」-「構成ツール」より「SQL Serverセキュリティ構成」を実行します（図1）。
「SQL Serverセキュリティ構成」は、SQL Server本体やIntegration Servicesなど各サービスの開始や停止、SQL CLRなど各種機能の有効化／無効化を行うことができます。図1の「機能のセキュリティ構成」をクリックし、「機能のセキュリティ構成」の画面でCLR統合を有効としてください（図2）。
SQL CLRは直接コマンドを発行して有効にすることも可能です。コマンドで有効にする場合は、Management Studioからリスト1のSQLコマンドを発行します。
sp_configure 'clr enabled', 1 GO RECONFIGURE GO
Visual Studio 2005によるSQL CLRの作成
SQL CLRが有効となったところで、早速Visual Studio 2005でSQL CLRを作成してみましょう。Visual Studio 2005はSQL Server 2005と密接な統合が図られているとともに、SQL CLRを完全にサポートしています。コードの作成からSQL Serverへの登録、テストまで一通りの作業をVisual Studio 2005で行うことができます。
Visual Studio 2005では、SQL CLRの作成用に専用のプロジェクトテンプレートが用意されています。「プロジェクトの種類」から「データベース」を選択し「SQL Serverプロジェクト」テンプレートを指定します。今回はC#でコーディングを行うので、言語はVisual C#を選択しています（図3）。
図3 Visual Studio 2005でSQL CLR用のプロジェクトを作成「プロジェクトの種類」で「データベース」を選び、「Visual Studioにインストールされたテンプレート」から「SQL Serverプロジェクト」を選択する。（画像をクリックすると拡大します）
プロジェクトを作成すると、接続先となるSQL Serverを指定するために「新しいデータベース参照」ダイアログが表示されます。SQL Server 2005をインストールしたマシンの名前と認証方法、利用データベースを指定してください。ここでは、SQL Server 2005のサンプルデータベースである「AdventureWorks」を利用します（図4）。
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); } } }
このコードでは、おなじみの「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
SQL CLRで作成したストアドプロシージャであっても、実行方法は一般的なストアドプロシージャと同様です。あとはF5を押すか、「デバッグ」メニュー内の「デバッグ開始」を選択すると、上記コードが実行され、結果が「出力」ウィンドウに表示されます（リスト4）。
Hello world! It's now 2006/06/03 12:08:04 ～中略～ (16 行が返されました)
SQL CLRで作成したストアドプロシージャから無事に結果が返ってきました。C#を利用してストアドプロシージャの作成を行いましたが、非常に容易に開発から配置まで行えることを実感できたはずです。（
SQL CLRのデバッグ
SQL CLRのデバッグに当たっては、通常のコードと変わらないインターフェイスが提供されています。ブレイクポイントによる実行の中断やステップ実行、変数の監視などを行うことができます（図5）。
図5ではブレイクポイントから1行進んだ所で止まっています。ステップ実行や変数の監視を利用することにより、プログラムの実行状況を随時把握できます。
SQL CLRによるユーザー定義関数の作成
ストアドプロシージャに続いて、ユーザー定義関数を作成してみましょう。ユーザー定義関数は、パラメータを受け取り、計算や各種操作を実行し、その結果を返すためのプログラムです。SQL CLRでは1つの値を返すスカラ値関数（SVF）と行セットを返すテーブル値関数（TVF）を扱うことができます。ここではサンプルとして、スカラ値関数を作成します。
まず、先ほどまで利用したプロジェクトに対し、ユーザー定義関数のファイルを追加します。「プロジェクト」メニュー内の「ユーザー定義関数の追加」を選択してファイルの追加を行います。
続いて、コーディングを行います。コードとしては、渡された日付文字列に対し、曜日を返す、という単純なものです（リスト5）。
using System; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public class SampleUDF { [Microsoft.SqlServer.Server.SqlFunction] public static SqlString GetWeek(string myDay) { DateTime MyDateTime = DateTime.Parse(myDay); return new SqlString(MyDateTime.ToString("dddd")); } };
リスト5では戻り値としてSqlString型を指定していますが、これはSystem.Data.SqlTypes名前空間で定義されたSQL Serverプログラミング向けのネイティブなデータ型（以下SQL Server CLRデータ型）です。SqlString型は.NETでのデータ型（以下CLRデータ型）としてString型に対応しています。
データベースへ格納する、またはデータベースからデータを取得する際のデータ型はパフォーマンスと安全性の観点から、SQL Server CLRデータ型でのプログラム作成が推奨されています。SQL Server CLRデータ型とCLRデータ型の対応については、MSDN2のドキュメント「SQL Serverデータ型と.NET Frameworkデータ型の対応 」にて確認できます。
コーディング後、ビルドと配置を行い、Test.sqlより実行テストを行います。ユーザー定義関数は、SELECT文より呼び出すことが可能なので、リスト6のように記述します。
SELECT dbo.GetWeek('2006/06/04') AS DayOfWeek
実行結果はストアドプロシージャと同じく「出力」ウィンドウに表示されます（リスト7）。
DayOfWeek ----------- 日曜日
以上のように、ユーザー定義関数も簡単に作成できます。ユーザー定義関数はSELECT文などから直接呼び出せるため、クエリとの親和性が非常に高く、SQL CLRでの実行結果をクエリ内で利用したい場合に大きな助けとなるでしょう。
まとめ
ここまで、ストアドプロシージャとユーザー定義関数について簡単なサンプル作成を行いました。Visual Studio 2005を利用することによりSQL CLRの作成からSQL Serverへの配置、デバッグまで必要な作業をすべて行うことができます。その生産性は非常に高く、言語としてもC#やVB.NETを知っていれば新たに言語を覚える必要はありません。また、Visual Studioを利用することによってプロジェクトごとのコードの管理や履歴管理なども一元化されるため、プロジェクトの運用管理という面でも利点は大きいでしょう。
しかしながら、今回のサンプルに示したような単純なデータ処理であれば、Transact-SQLの優位性は揺らぎません。データを取り扱うための専用言語であるTransact-SQLの方が、より単純にパフォーマンスの高いコードを記述できます。開発者には、それぞれ利点欠点を見極めた使い分けが求められます。
次回は、テーブル値関数の解説に加えて、SQL CLRを利用する動機ともいえる正規表現やほかのデータベース製品との接続などの確認を行う予定です。お楽しみに！
浙公网安备 33010602011771号