連載：VS 2005でいってみようDBプログラミング 第9回　Let's Master ストアド・プロシージャ！（後編） 山田 祥寛（http://www.wings.msn.to/） 2006/10/28
Page1 Page2 Page3 Page4

［3］新規のストアド・プロシージャを追加する

　新規にSQL CLRのストアド・プロシージャを追加するには、ソリューション・エクスプローラからプロジェクト名を右クリック、表示されたコンテキスト・メニューから［追加］−［ストアドプロシージャ］を選択してください。

　［新しい項目の追加］ダイアログが表示されますので、［テンプレート］に「ストアド プロシージャ」が選択されていることを確認し、ファイル名には（ここでは）「SelectCLR.vb」と入力したうえで、［追加］ボタンをクリックします。

図6　［新しい項目の追加］ダイアログ

ソリューション・エクスプローラからプロジェクト名を右クリック、表示されたコンテキスト・メニューから［追加］−［ストアドプロシージャ］を選択して開く。ここでは［テンプレート］に「ストアド プロシージャ」を選択し、ファイル名を入力して［追加］ボタンをクリックする。

　コード・エディタが開き、ストアド・プロシージャの骨格（PublicなSharedメソッド）が自動生成されていますので、リスト3の要領でコードを追加してみましょう（白字が追記部分）。

Imports System Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes ' （2） Imports System.Runtime.InteropServices Imports Microsoft.SqlServer.Server Partial Public Class StoredProcedures   <Microsoft.SqlServer.Server.SqlProcedure()> _   Public Shared Sub SelectCLR(ByVal publish_id As SqlInt32, _     <Out()> ByRef num As SqlInt32) ' （4）     Using db As New SqlConnection("context connection=true") ' （1）       Dim comm As SqlCommand        ' 引数publish_idが0以外の場合のみpublish_id列で絞り込み       If publish_id = 0 Then         comm = New SqlCommand("SELECT * FROM Book AS b INNER JOIN Publish AS p ON b.publish_id=p.publish_id", db)       Else         comm = New SqlCommand("SELECT * FROM Book AS b INNER JOIN Publish AS p ON b.publish_id=p.publish_id WHERE b.publish_id=@publish_id", db)         comm.Parameters.AddWithValue("@publish_id", publish_id)       End If       db.Open()       Dim reader As SqlDataReader = comm.ExecuteReader()        ' 取得した結果セットをクライアントに送信       SqlContext.Pipe.Send(reader) ' （3）       reader.Close()        ' 結果セットのレコード行数を取得し、出力パラメータにセット       Dim comm2 As New SqlCommand("SELECT @@ROWCOUNT", db)       Dim reader2 As SqlDataReader = comm2.ExecuteReader()       If reader2.Read Then         num = reader2.GetSqlInt32(0)       Else         num = 0       End If     End Using   End Sub End Class

リスト3　SQL CLRで記述したストアド・プロシージャ（SelectCLR.vb）

グレー部分は自動作成されたコード。コメント（1）〜（4）は以下の解説に対応している。

　SQL CLR固有の構文のほとんどはVS 2005が自動生成してくれますので、アプリケーション開発が殊更にSQL CLRを意識して記述するところはほとんどありません。第6回で登場したコードなどと比較しても分かるように、データベース・アクセスの構文自体も、一般的なADO.NETのコードとほとんど同様であることがお分かりになるはずです。

　ここで注目していただきたいのは、以下の4点です。

（1）データベース・アクセスには「コンテキスト接続」を使用

　コンテキスト接続とは、現在、SQL CLRを実行しているユーザーが使用しているSQL Server接続を、そのまま引き継いで行う接続のことをいいます。コンテキスト接続を利用することで、インプロセスでデータベースとの接続を行うことができますので、通常のデータベース・アクセスに比べても高いパフォーマンスが期待できます。SQL CLRで内部的なデータベース・アクセスを行う場合には、コンテキスト接続を利用するべきです。

　コンテキスト接続を利用するには、従来の、

Data Source=.\SQLEXPRESS;Initial Catalog=;AttachDbFilename=|DataDirectory|\MyDB.mdf;Initial Catalog=;Integrated Security=True;MultipleActiveResultSets=True;User Instance=True

通常の接続文字列の例

のような接続文字列の代わりに、

context connection=true

コンテキスト接続を利用する接続文字列

を指定するだけです*2。もちろん、ここで外部のデータベース・サーバにアクセスする場合には、従来と同様の形式で接続文字列を指定することも可能です。

*2 コンテキスト接続を使用した場合、MARS（複数の結果セットを有効にする機能）が利用できない、「context connection」以外の接続キーワードを指定できない、など、通常の接続にはない制限もいくつかありますので注意してください。

（2）データ記述にはSystem.Data.SqlTypes名前空間を利用

　System.Data.SqlTypes名前空間は、SQL Server 2005でネイティブに利用可能なデータ型のためのクラスを提供します。Int32やString、DoubleのようなCLR標準のデータ型を利用することも可能ですが、System.Data.SqlTypes名前空間で定義されたクラス（データ型）を利用することで、データ変換時に精度が失われるなどの不具合を未然に防止することができます。

　また、CLR標準のデータ型を使った場合には内部的にSystem.Data.SqlTypesデータ型への変換が発生します。最初からSystem.Data.SqlTypesデータ型を使用することで、処理自体を高速化できるというメリットもあります。

　特別な理由がない限りは、SQL CLRプログラミングでは、System.Data.SqlTypesデータ型を優先して利用することをお勧めします。System.Data.SqlTypesデータ型とCLR標準のデータ型、そして、SQL Server 2005のネイティブ・データ型の対応関係に関する詳細は、MSDNドキュメントから「System.Data.SqlTypes 名前空間」を参照してください。

（3）クライアントにデータを返すのはSqlPipeオブジェクトの役割

　SQL CLRで作成したストアド・プロシージャからデータを返すには、SqlPipeクラス（Microsoft.SqlServer.Server名前空間）を使用します。SqlPipeクラスで利用可能な主なメソッドは以下のとおりです。

構文 概要 ExecuteAndSend(comm As SqlCommand) コマンドを実行した結果を送信 Send(reader As SqlDataReader) 複数行の結果セットを送信 Send(str As String) 文字列メッセージを送信

表2　SqlPipeクラスで利用可能な主なメソッド

　本サンプルで使用しているのは、この表の2番目のSendメソッドですが、ExecuteAndSendメソッドを利用することで指定したコマンドを実行し、その結果を直接クライアントに送信することも可能です。単純な文字列メッセージを出力するには、3番目のSendメソッドを使用します。

（4）出力パラメータにはOut属性を付与する

　SQL CLRで出力パラメータを定義するには、パラメータを参照渡し（ByRefキーワード）で指定するとともに、Out属性を指定する必要があります。

　ちなみに、ストアド・プロシージャから戻り値を返すには、以下のようにSubプロシージャをFunctionプロシージャに置き換えたうえで、戻り値型として整数型を指定するだけです。試しに、リスト3のコードが戻り値を返すように、メソッドの先頭および末尾部分を書き換えてみましょう（リスト4）。

<Microsoft.SqlServer.Server.SqlProcedure()> _ Public Shared Function SelectCLR2(ByVal publish_id As SqlInt32) As SqlInt32     ……中略……     If reader2.Read Then         Return reader2.GetSqlInt32(0)     Else         Return 0     End If   End Using End Function

リスト4　戻り値を返すように書き換えたストアド・プロシージャ（SelectCLR2.vb）

INDEX
	Visual Studio 2005でいってみようDBプログラミング
	第9回　Let's Master ストアド・プロシージャ！（後編）
	1．ストアド・プロシージャでトランザクションを利用
	2．SQL CLR機能によるストアド・プロシージャ
	3．新規SQL CLRストアド・プロシージャの追加
	4．アクセス許可レベルを設定／アセンブリの配置

　

「Visual Studio 2005でいってみようDBプログラミング」

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）