SQL Server Yukon Beta 1 での .NET プログラミング機能の概要

SQL Server Yukon Beta 1 での .NET プ...

Microsoft Corporation

October 2003

日本語版最終更新日 2003 年 12 月 9 日

概要 : 本書では、Microsoft® SQL Server™ の .NET 共通言語ランタイムの機能を説明しています。本書では、SQL Server コンテキスト内での .NET Framework オブジェクトの使用方法だけでなく、データベース指向オブジェクトを実装するための基本的な手法や事例についても説明します。

対象 :

SQL Server "Yukon" Beta 1

はじめに

Microsoft SQL Server "Yukon" Beta 1 では、Microsoft Windows .NET Framework の共通言語ランタイム (CLR) がデータベースに統合されました。開発者は、Microsoft Visual Basic® .NET や C# など、任意の .NET 言語を使用して、ストアドプロシージャ、トリガ、およびユーザー定義関数を記述できるようになりました。また、マネージコードを使用して、2 つの新しいオブジェクトであるユーザー定義型および集計を作成することもできます。

この SQL Server と .NET の統合により、データベース開発者には、次のような大きな利点がいくつかもたらされます。

"プログラミングモデルの強化" : .NET 言語は多くの点において、Transact-SQL (T-SQL) よりも機能が豊富で、以前は SQL 開発者が使用できなかった構造や機能が用意されています。
"安全性とセキュリティの強化" : マネージコードは共通言語ランタイム環境で動作し、データベースエンジンによりホストされます。これにより、.NET データベースオブジェクトは、以前のバージョンの SQL Server で使用できた拡張ストアドプロシージャよりも安全性が高く、セキュリティで保護できるようになります。
"ユーザー定義型と集計" : ユーザー定義型とユーザー定義集計は、2 つの新しいマネージデータベースオブジェクトで、SQL Server のストレージ機能とクエリ機能を拡張します。
"共通の開発環境" : データベース開発は、Microsoft Visual Studio .NET 開発環境の今後のリリースに統合されます。開発者は、データベースオブジェクトやスクリプトの開発とデバッグを行う際に、中間層またはクライアント層の .NET コンポーネントやサービスの記述に使用しているのと同じツールを使用できます。
"パフォーマンスとスケーラビリティ" : .NET 言語のコンパイルモデルや実行モデルは、多くの状況で、Transact-SQL を超える優れたパフォーマンスを提供します。

ページのトップへ

CLR の統合

Microsoft SQL Server "Yukon" Beta 1 でホストされる CLR を使用することにより、ストアドプロシージャ、トリガ、ユーザー定義関数、およびその他のデータベースオブジェクトをマネージコードで作成できます。マネージコードは実行前にネイティブコードにコンパイルされるので、シナリオによってはパフォーマンスが大幅に向上します。マネージコードでは、コードアクセスセキュリティ (CAS) を使用して、アセンブリによる特定の操作の実行が防止されます。 SQL Server "Yukon" では CAS を使用してマネージコードを管理し、オペレーティングシステムやデータベースサーバーが危険にさらされないようにします。

マネージコードの定義

CLR は Microsoft .NET Framework の中心であり、すべての .NET コード用の実行環境を提供します。そのため、CLR 内で実行されるコードを "マネージコード" と呼びます。 CLR では、ジャストインタイム (JIT) コンパイル、メモリの割り当てと管理、タイプセーフティの実行、例外処理、スレッドの管理とセキュリティなど、プログラムの実行に必要なさまざまな機能およびサービスが提供されます。 CLR は、.NET ルーチンの最初の呼び出しで、SQL Server により読み込まれます。

Transact-SQL の制限事項

Transact-SQL は、国際標準化機構 (ISO) および米国規格協会 (ANSI) によって定義された構造化照会言語 (SQL) を拡張した言語です。データベース開発者は、Transact-SQL を使用して、データベースに格納されているデータの挿入、取得、変更、および削除だけでなく、データベースおよびテーブルの作成、変更、および削除を行うことができます。 Transact-SQL は特に直接的なデータアクセスおよび操作用にデザインされています。 Transact-SQL は、データアクセスおよびデータの管理には優れていますが、Visual Basic .NET や C# のような本格的なプログラミング言語ではありません。たとえば、Transact-SQL では、配列、コレクション、for each ループ、ビットシフト、またはクラスはサポートされません。これらの構造のいくつかは Transact-SQL でもシミュレートできますが、マネージコードではこれらの構造に対する最も優れたサポートが提供されます。シナリオによっては、このような機能はマネージコードで特定のデータベース機能を実装する大きな理由になります。

CLR の統合の利点

以前のバージョンの SQL Server では、データベースプログラマがサーバー側でコードを記述する際に使用する言語は、Transact-SQL に制限されていました。 CLR の統合により、データベース開発者は Transact-SQL のみでは不可能または困難であった作業を実行できるようになりました。 Visual Basic .NET や C# は、配列、構造化例外処理、およびコレクションを完全にサポートする最新のプログラミング言語です。開発者は、CLR の統合を利用し、Visual Basic .NET や C# などの言語を使用して、より複雑なロジックやより計算タスクに適したコードを記述できます。

Visual Basic .NET や C# では、カプセル化、継承、ポリモーフィズムなど、オブジェクト指向の機能が用意されています。関連するコードにより、クラスや名前空間を容易に体系化できるようになりました。大量のサーバーコードを使って作業する場合、この機能により、コード資産をより簡単に体系化および管理できます。論理的、物理的にコードをアセンブリまたは名前空間に体系化する機能は非常に役立ち、大規模なデータベースの実装では、さまざまなコードをより容易に検索して、関連付けることが可能になります。

マネージコードは、大量の計算や複雑な実行ロジックでは、T-SQL よりもはるかに優れており、文字列処理や正規表現などの多くの複雑な作業へのさまざまなサポートも備えています。データベース開発者は、.NET Framework 基本クラスライブラリ (BCL) の機能を使用して、多数のビルド済みクラスおよびルーチンにアクセスできるようになりました。これらのクラスやルーチンには、任意のストアドプロシージャ、トリガ、およびユーザー定義関数から容易にアクセスできます。 BCL には、強化された文字列機能、高度な数学演算、ファイルアクセス、暗号化などの機能を提供するクラスが含まれています。これらのクラスの多くは SQL CLR コード内から使用できますが、サーバー側での使用に適さないクラス (たとえば、ウィンドウクラス) では使用できません。

マネージコードの利点の 1 つに、タイプセーフティがあります。マネージコードが実行される前に、CLR によりコードが安全かどうか確認されます。このプロセスを "検証" といいます。検証中、コードを安全に実行できることを保証するために、CLR によりいくつかの確認処理が行われます。たとえば、コードが調査され、書き込まれていないメモリが読み取られないことが確認されます。また、CLR により、バッファオーバーフローも防止されます。

既定では、Visual Basic .NET および C# では常に safe コードが作成されます。ただし C# プログラマには、unsafe コードの作成に unsafe キーワードを使用する選択肢があります。unsafe コードを作成するのは、たとえば、メモリに直接アクセスするコードを作成する場合です。

Transact-SQL とマネージコード間の選択

ストアドプロシージャ、トリガ、およびユーザー定義関数を記述する場合、プログラマは従来の Transact-SQL、または Visual Basic .NET または C# などの .NET 言語のどちらを使用するかを決定する必要があります。関連する状況によって、適する言語は異なります。 T-SQL の使用に適した状況もあり、マネージコードの使用に適した状況もあります。

Transact-SQL は、手続き型のロジックがほとんど、またはまったくない、データアクセスを主に実行するコードでの使用に最も適しています。マネージコードは、CPU を集中的に使用する関数や複雑なロジックを備えたプロシージャ、または .NET Framework の基本クラスライブラリを利用する場合に適しています。

また、コードの配置も重要になります。 Transact-SQL およびインプロセスマネージコードは、両方ともサーバーで実行できます。そのため、機能とデータが近くに配置されることになり、サーバーコンピュータの処理能力を活用できるようになります。一方、プロセッサを集中的に使用するタスクをデータベースサーバーに配置するのを避けることもできます。現在の大部分のクライアントコンピュータは非常に強力なので、クライアントに可能な限り多くのコードを配置して、その処理能力を活用できます。 Transact-SQL のコードはクライアントコンピュータでは実行できませんが、SQL Server のインプロセスプロバイダはクライアント側のマネージ ADO.NET に可能な限り同じ働きをするようにデザインされており、サーバーとクライアント間のコードの移植性が拡張されています。インプロセスプロバイダの詳細については、この資料の後半で説明します。

ページのトップへ

アセンブリの使用

マネージコードを記述する場合の配置単位を "アセンブリ" といいます。アセンブリは、DLL または実行可能 (EXE) ファイルとしてパッケージされます。実行可能ファイルは単独で実行できますが、DLL は既存のアプリケーションでホストされる必要があります。マネージ DLL アセンブリは、Microsoft SQL Server "Yukon" Beta 1 に読み込まれ、ホストされます。SQL Server では、実行可能ファイルはホストできません。

アセンブリのロードとアンロード

CREATE ASSEMBLY ステートメントは、サーバーへのアセンブリの登録に使用されます。次に例を示します。

CREATE ASSEMBLY YukonCLR
FROM 'C:\MyDBApp\YukonCLR.dll'

FROM 句では、ロードするアセンブリのパス名を指定します。このパスは、UNC パスまたはローカルコンピュータの物理ファイルパスにできます。

アセンブリの登録を解除するには、次の構文で DROP ASSEMBLY ステートメントを使用します。

DROP ASSEMBLY MyDotNETAssembly

Microsoft SQL Server "Yukon" Beta 1 へのアセンブリのロードは、アセンブリが提供する機能を公開するための最初の手順です。

アクセス許可セットとロードされたアセンブリの理解

アセンブリのセキュリティは、コード自体の ID ではなく、ロードしたユーザーのユーザー ID によってスコープが設定されます。信頼は、データベースのユーザー ID またはロールごとに許可され、設定されます。特定のユーザーまたはロールによって所有されているデータとコード (アセンブリ) は、明確にアクセス権が与えられていない場合、別のユーザーまたはユーザーロールによって所有されているデータとコードから分離されます。

データベースユーザーまたはユーザーロールは、データベースにアセンブリを作成する、つまり、所有する権限が許可されます。アセンブリの所有者は、他のデータベースユーザーまたはロールにて、アセンブリを参照する権限を順次許可することができます。アセンブリは、次の場合においてのみ、正常に他のアセンブリを参照または呼び出すことができます。(a) 呼び出し先または参照先のアセンブリが同じユーザーまたはロールによって所有されている場合（b）呼び出し先または参照先のアセンブリを所有しているユーザーまたはロールが、呼び出し元または参照元のアセンブリを所有しているユーザーまたはロールにアクセス権を与えている場合 (c) 参照先または呼び出し先のアセンブリが同じデータベースで作成された場合。

SQL Server へのアセンブリのロード時に、コードを実行できる以下の 3 つの異なるセキュリティレベルのいずれかを指定する機能があります。

SAFE
EXTERNAL_ACCESS
UNSAFE

SAFE は既定のアクセス許可のセットで、大部分のシナリオで機能します。特定のセキュリティレベルを指定するには、CREATE ASSEMBLY ステートメントの構文を次のように変更します。

CREATE ASSEMBLY YukonCLR
FROM 'C:\MyDBApp\YukonCLR.dll'
WITH PERMISSION_SET = SAFE

単に上記のコードの 3 行目を省略して、次のように SAFE アクセス許可セットと一緒にアセンブリを登録することもできます。

CREATE ASSEMBLY YukonCLR
FROM 'C:\MyDBApp\YukonCLR.dll'

アセンブリのコードが SAFE アクセス許可セットで実行されるときは、インプロセスマネージプロバイダ経由でのサーバー内での計算およびデータアクセスのみを実行できます。

EXTERNAL_ACCESS は、コードでファイル、ネットワーク、レジストリおよび環境変数などのサーバー外のリソースにアクセスする必要があるシナリオで使用する、コードアクセス許可セットです。サーバーは外部リソースにアクセスする場合、常に、マネージコードを呼び出す側のユーザーのセキュリティコンテキストを借用します。

EXTERNAL ACCESS アセンブリを作成するには、作成者が EXTERNAL ACCESS アクセス許可を持っている必要があります。アセンブリが EXTERNAL_ACCESS アクセス許可で実行されるよう指定するには、アセンブリのロード時に指定されるアクセス許可セットを単純に変更します。

CREATE ASSEMBLY YukonCLR
FROM 'C:\MyDBApp\YukonCLR.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

UNSAFE は、アセンブリが安全であることを検証できない状況、または Win32 API などの制限付きのリソースへの追加のアクセスを必要とする状況に適したコードアクセス許可です。

アセンブリが UNSAFE アクセス許可でロードされるように指定するには、サーバーへのアセンブリのロード時に UNSAFE アクセス許可セットを指定します。

CREATE ASSEMBLY YukonCLR
FROM 'C:\MyDBApp\YukonCLR.dll'
WITH PERMISSION_SET = UNSAFE

特定のアクセス許可レベルで許可される項目のいくつかの例を以下に示します。

アクセス許可セット	許可される権限
SAFE	非常に限定されたアクセス許可。アセンブリはタイプセーフであることを検証できる必要があります。ファイル、ネットワーク、環境変数およびレジストリなどの外部リソースへはアクセスできません。
EXTERNAL_ACCESS	SAFE に許可されたアクセス許可に加えて、ファイルおよびディレクトリの読み取りおよび書き込み、ネットワーク、イベントログおよびレジストリなどにアクセスできます。
UNSAFE	アセンブリはタイプセーフであることを検証できる必要はありません。アセンブリからのアンマネージコード内への呼び出しが可能であり、タイプセーフであるかどうかは検証されません。

SAFE が大部分の状況で推奨されるアクセス許可設定です。

実行コンテキストの理解

Microsoft SQL Server "Yukon" Beta 1 は、ユーザーベースである SQL Server のセキュリティモデル、およびコードアクセスベースである CLR のセキュリティモデルが統合されています。 SQL Server "Yukon" Beta 1 には、両方のモデルの役立つ機能が組み込まれています。システムの安定性を保証するには、マネージコードからのデータベース内のデータやオブジェクト、ファイルシステムやネットワークなどの SQL Server 外のシステムリソースへの未承認のアクセスを許可しない必要があります。この操作を行うために、SQL Server では実行コンテキストの原則を監視します。実行コンテキストは、現在実行中のクエリ固有のデータを含むデータ構造です。呼び出し側と呼び出し先が同じユーザーによって所有されており、呼び出しが静的 SQL 形式である場合は、アクセス許可を調べる必要はありません。ただし、含まれているオブジェクトに呼び出し側が存在しない場合、現在の実行コンテキストは現在のデータベースユーザーの実行コンテキストになります。プロシージャ、関数、またはトリガなどのコンテナオブジェクトの内部に呼び出し側が存在する場合、実行コンテキストは以下の規則によって変化します。コンテナオブジェクトが execution_context=<owner_name> でマークされている場合、現在の実行コンテキストはコンテナオブジェクトの所有者になります。それ以外の場合、実行コンテキストはコンテナオブジェクトの呼び出し側の実行コンテキストになります。

他のアセンブリの参照

アセンブリは、他のアセンブリを参照できます。これは、1 つ以上の他のアセンブリによって使用される共通機能が含まれたアセンブリをロードできることを意味します。

ページのトップへ

.NET ルーチンの作成

Microsoft SQL Server "Yukon" Beta 1 内で実行されるマネージコードは ".NET ルーチン" と呼ばれます。 .NET ルーチンには以下の 4 種類があります。

スカラ値ユーザー定義関数 (スカラ UDF)
テーブル値ユーザー定義関数 (TVF)
ユーザー定義プロシージャ (UDP)
ユーザー定義トリガ

これらの各ルーチンの詳細について、以下に説明します。各種 .NET ルーチンには Transact-SQL と等価な機能が含まれており、SQL Server 内で Transact-SQL の等価な機能を使用できる場所であればどこでも使用できます。たとえば、スカラ UDF は任意のスカラ式で使用できます。 TVF は任意の FROM 句で使用できます。プロシージャは、EXEC ステートメントでの呼び出し、またはクライアントアプリケーションからの呼び出しが可能です。

4 つのルーチンは、すべてマネージコードの同じ構造を持っています。各ルーチンは、クラスの public static (Visual Basic .NET では Shared) メソッドである必要があります。各種 .NET ルーチンの追加要件については、以下で説明します。

インプロセスマネージプロバイダの理解

.NET ルーチンを使用すると、実行している SQL Server のインスタンスに格納されたデータに容易にアクセスできます。ルーチンがアクセスできる特定のデータは、コードを実行しているユーザーコンテキストによって決まります。データへのアクセスに使用されるメカニズムは、"inproc プロバイダ" とも呼ばれるインプロセスマネージプロバイダです。

inproc プロバイダは、SQL Server プロセス内のデータを使って作業するために最適化されます。インプロセスマネージプロバイダのクラスとメソッドを使用することにより、容易にデータベースにクエリを送信し、DML および DDL ステートメントを実行して、クライアントアプリケーションに結果セットやメッセージを返すことができます。

System.Data.SqlServer 名前空間は、inproc プロバイダを構成する型をグループ化します。この名前空間は、ADO.NET の SqlClient 名前空間と多くの類似点があり、インターフェイスを共有しており、開発者によってマネージクライアントおよび中間層アプリケーションからの SQL Server データへのアクセスに使用されます。この類似点によって、クライアントアプリケーションからサーバーライブラリへのコードの移植、またはその逆の移植を容易に行うことができます。

inproc プロバイダ固有の ADO.NET に対する機能拡張が 3 つあります。

SqlContext: このクラスは、他の拡張機能をカプセル化します。また、ルーチンが実行される環境の一部であるトランザクションおよびデータベース接続を提供します。
SqlPipe: このクラスを使用すると、ルーチンでクライアントに表形式の結果やメッセージを送信できます。この SqlPipe クラスは、概念的には ASP.NET の Response クラスに似ています。
SqlTriggerContext: このクラスでは、T-SQL トリガからアクセス可能な挿入済みおよび削除済みのテーブルなど、トリガが実行されているコンテキストの情報を提供します。詳細については、以下のトリガに関するセクションを参照してください。

スカラ値関数 (スカラ UDF)

スカラ値関数は、文字列、整数またはビット値などの単一値を返す関数です。整数値を返す単純なスカラ値関数を次に示します。

using System.Data.SqlServer;
using System.Data.Sql;
public class T {
  [SqlFunc]
  [SqlFunction(DataAccess = DataAccessKind.Read)]
   public static int ReturnOrderCount()
   {
      SqlCommand sqlComm = SqlContext.GetCommand();
      sqlComm.CommandText = "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader";
      return (int) sqlComm.ExecuteScalar();
   }
}

上記のコード例を詳しく検証しましょう。

最初のコード行の using System.Data.SqlServer は、inproc プロバイダの型にアクセスするために必要になります。

次に、関数は、System.Data.Sql 名前空間の [SqlFunction] カスタム属性で修飾されています。このカスタム属性は、UDF で inproc プロバイダを使用するかどうか、および UDF でデータの読み取りのみを行うのか、またはデータの更新、挿入または削除も行うのかを示します。 SQL では、DataAccessKind を DataAccessKind.None に設定することにより、inproc プロバイダを使用しない UDF の実行を最適化できます。対象のメソッドは、次の行の public static (Visual Basic .NET では Shared) です。

System.Data.SqlServer 名前空間の SqlContext クラスは、セットアップ済みの SQL Server インスタンスへの接続を使用した SqlCommand オブジェクトへのアクセスに使用されます。ここでは使用されていませんが、現在のトランザクションコンテキストを SqlContext クラスから使用することもできます。

最後の 2 つのコード行は、System.Data.SqlClient 名前空間の型を使用するクライアントアプリケーションを作成したことのある開発者にとってはなじみのあるものです。

sqlComm.CommandText = "SELECT COUNT(*) AS 'Order Count' FROM SalesOrderHeader";
return (int) sqlComm.ExecuteScalar();

適切なコマンドテキストは、SqlContext.GetCommand への呼び出しによって返される SqlCommand オブジェクトの CommandText プロパティを設定することによって指定されます。次に、sqlComm オブジェクトの ExecuteScalar() メソッドが呼び出されます。これにより、クエリに基づいた int 型の値が返されます。最後に、注文数が呼び出し側に返されます。

上記のコードを FirstUdf.cs というファイルに保存した場合、次のようにアセンブリとしてコンパイルできます。

csc.exe /t:library /r:sqlaccess.dll /o:FirstUdf.dll FirstUdf.cs

注意事項 :

inproc プロバイダを使用するすべてのアセンブリは、sqlaccess.dll を参照する必要があります。 Sqlaccess.dll (および付属の clrcppmodule.dll) は、SQL Server のインストールディレクトリの BINN サブディレクトリにあります。 sqlaccess.dll を参照するには、/r を使用する必要があります。 sqlaccess.dll およびその clrcppmodule.dll との依存関係は、将来の SQL Server "Yukon" のベータ版で削除される可能性があります。
/t:library は、実行可能ファイルではなくライブラリを作成する必要があることを示します。実行可能ファイルは、SQL Server には登録できません。

SQL でアセンブリと UDF を登録する Transact-SQL、および呼び出し例を次に示します。
```
CREATE ASSEMBLY FirstUdf FROM Ã‚Â‘FirstUdf.dll'
CREATE FUNCTION CountSalesOrderHeader() RETURNS INT 
AS EXTERNAL NAME FirstUdf:T::ReturnOrderCount
SELECT dbo.CountSalesOrderHeader()
```
注意事項 :
Transact-SQL で公開するアセンブリ名は、マネージアセンブリ名と一致する必要があります。
Transact-SQL で公開する関数名は、対象の public static メソッドの名前と一致させる必要はありません。

テーブル値関数 (TVF)

テーブル値関数 (TVF) は単純に言えば、テーブルを返すユーザー定義関数です。 Transact-SQL の TVF は、SQL Server 2000 の機能として初めて導入されました。

SQL Server "Yukon" Beta 1 では、任意のマネージ言語での TVF の定義が可能になったことにより、TVF の機能が拡張されます。データは、ISqlReader オブジェクト経由で TVF から返されます。

CLR TVF を定義する構文は、Transact-SQL TVF を定義する構文に、次のように EXTERNAL NAME 句を加えたものと同じです。

CREATE FUNCTION GetEmpFirstLastNames
RETURNS @EmpTab TABLE (FirstName NVARCHAR(4000), LastName NVARCHAR(4000))
EXTERNAL NAME MyDotNETAssembly:[MyNamespace.CLRCode]:: GetEmpFirstLastNames

TVF は、クエリでの次のような追加処理に対するリレーショナル形式でのデータの表示に使用されます。

select * from function()
select * from tbl join function() onÃ‚Â...
select * from table t cross apply function(t.column)Ã‚Â...

TVF によるテーブルの返し方には以下の 2 つがあります。

スカラ入力引数からテーブルを作成する場合。たとえば、カンマ区切りの数値文字列を取得し、それらをテーブルに変換する TVF があります。
外部データからテーブルを生成する場合。たとえば、イベントログを読み取り、それをテーブルとして表示する TVF があります。

TVF のいくつかの例については、「シナリオ」を参照してください。

ユーザー定義ストアドプロシージャ (UDP)

ストアドプロシージャは、スカラ式で使用できない .NET ルーチンです。関数とは異なり、ストアドプロシージャでは、クライアントに表形式の結果やメッセージを返し、DDL および DML ステートメントを呼び出し、出力パラメータを返すことができます。

表形式の結果とメッセージは、SqlPipe オブジェクト経由でクライアントに返されます。パイプを取得するには、SqlContext クラスの GetPipe() メソッドを使用します。 SqlPipe オブジェクトには、Send() メソッドが含まれています。 Send() メソッドのさまざまなオーバーロードを呼び出すことにより、パイプ経由で呼び出し側のアプリケーションにデータを転送できます。

Send() メソッドのオーバーロードを次に示します。

Send(ISqlDataReader)
Send(ISqlDataRecord)
Send(ISqlError)
Send(msg As String)

Sql.Pipe 経由で表形式の結果やメッセージを送信する例を次に示します。

Using System.Data.Sql;
Using System.Data.SqlServer; 
Class T {
[SqlProcedure]
public static void HelloWorld()
{
  SqlPipe sp = SqlContext.GetPipe();
  sp.Send("Hello world! It's now " + System.DateTime.Now.ToString()+"\n");
  SqlCommand cmd = SqlContext.GetCommand()
  cmd.CommandText = "SELECT ProductNumber FROM ProductMaster"
  SqlDataReader rdr = cmd.ExecuteReader();
  Sp.Send(rdr);
}
}

プロシージャが、System.Data.Sql でも定義されているカスタム属性 SqlProcedure でマークされていることに注意してください。

最初の sp.Send ではクライアントにメッセージを送信します。また、2 番目の sp.Send では ISqlReader を使用して表形式の結果を送信します。

ほぼ等しい操作を行う Transact-SQL のストアドプロシージャを次に示します。

CREATE PROCEDURE HelloWorld() AS
BEGIN
PRINT(Ã‚Â‘Hello world!')
SELECT ProductNumber FROM ProductMaster
END

メッセージと結果セットは、クライアントアプリケーションによって別々に取得されます。たとえば、SQL "Workbench" 結果セットは [結果] ビューに表示され、メッセージはメッセージペインに表示されます。

上記のコードを MyFirstUdp.cs ファイルに保存し、次のコードでコンパイルする場合を考えます。

csc /t:library /r:sqlaccess.dll MyFirstUdp.cs /o: MyFirstUdp.dll

生成されたアセンブリは登録でき、次の DDL でそのエントリポイントを呼び出すことができます。

CREATE ASSEMBLY MyFirstUdp FROM Ã‚Â‘MyFirstUdp.dll'
CREATE PROCEDURE HelloWorld
AS EXTERNAL NAME MyFirstUdp:T::HelloWorld
Exec MyFirstUdp

.NET ルーチン : サンプルシナリオと実行に関するヒント集

ここでは、.NET ルーチンで使用できる機能の一部を示すサンプルを紹介します。

また、ここでは、スカラ UDF、TVF、および UDP について、それぞれが最も適した状況、およびパフォーマンスを最大にする方法に関する推奨事項を含む追加情報も提供します。

注説明のために、汎用のパス名を使用しています。シナリオのサンプルコードをコンパイルおよび実行する前に、パス名を適切なものに変更する必要があります。

注以下のシナリオでは、YukonCLR という名前のアセンブリが、サーバーの AdventureWorks データベースにセーフモードで読み込まれることを想定しています。特定のシナリオに特別な記述を加えた場合は除きます。アセンブリをロードするコードは次のとおりです (パスは適切なものに変更してください)。

CREATE ASSEMBLY YukonCLR
FROM 'C:\MyDBApp\YukonCLR.dll'
WITH PERMISSION_SET = SAFE

シナリオ #1 - 正規表現

.NET Framework には、豊富な正規表現機能が用意されています。次のメソッドを例として示します。

Public Shared Function IsValidZipCode(ByVal zipCode As String) As Boolean
   Return Regex.IsMatch(zipCode, "^\s*(\d{5}|(\d{5}-\d{4}))\s*$") 
End Function

IsMatch() メソッドでは、文字列を正規表現と比較します。正規表現は、非常に複雑になる可能性があります。 ^\s*(\d{5}|(\d{5}-\d{4}))\s*$ は、5 桁または 9 桁の郵便番号に一致させる表現です。

上記の関数をユーザー定義関数として登録するには、次の Transact-SQL の DDL を実行する必要があります。

CREATE FUNCTION IsValidZipCode(@ZipCode NVARCHAR(4000))
RETURNS BIT
EXTERNAL NAME YukonCLR:[MyNamespace.CLRCode]::IsValidZipCode

最後に、Transact-SQL から上記の関数を呼び出すには、次のステートメントを実行する必要があります。

SELECT DBO.IsValidZipCode('97124')

また、正規表現を使用して、データベースに入力された電子メールアドレスを次のように容易に検証できます。

Public Shared Function IsValidEMailAddress(ByVal email As String) _
   As Boolean
   Return Regex.IsMatch(email, _
     "^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$") 
End Function

上記の関数をユーザー定義関数として登録するには、次の Transact-SQL の DDL を実行する必要があります。

CREATE FUNCTION IsValidEMailAddress(@email NVARCHAR(4000))
RETURNS BIT
EXTERNAL NAME YukonCLR:[MyNamespace.CLRCode]:: IsValidEMailAddress

最後に、Transact-SQL から上記の関数を呼び出すには、次のステートメントを実行する必要があります。

SELECT DBO.IsValidEMailAddress('someone@microsoft.com')

これらの例は、Transact-SQL では非常に困難な操作が、CLR を使用すると非常に容易に行えることを示しています。

シナリオ #2 - Path クラスおよびスカラ UDF と TVF の比較

.NET Framework には、豊富な文字列処理機能が用意されています。この例の 1 つとして、System.IO 名前空間の Path クラスがあります。このクラスを使用すると、指定した文字列の拡張子なしのファイル名を返す "GetFileNameWithoutExtension" というメソッドのように、ファイルパスの容易な操作が可能になります。

完全なファイルパスが含まれた写真のテーブルがあるとすると、Path クラスを使用して拡張子なしのファイル名の一覧を取得するには 2 つの方法があります。

String を渡すスカラ UDF を記述して、Path.GetFileNameWithoutExtension を呼び出します。この関数は、Photos テーブルへの Transact-SQL クエリで使用します。
Photos テーブルにクエリして、各列に対して GetFileNameWithoutExtension を呼び出し、ファイル名の一覧を返す TVF を記述します。

2 番目のメソッドは、最初のメソッドに比べて効果的ではありません。テーブル値関数から値が返される前に、すべての SqlCommand オブジェクトを閉じる必要があります。これにより 2 番目のメソッドで使用される TVF では、ArrayList などに行のコピーを作成し、その手順を、返される ISqlReader の ArrayList に対して繰り返す必要があります。さらにコードの大部分はデータアクセスなので、列に対する計算よりも、マネージメモリへの列の移動およびその逆の操作に多くの時間が費やされます。

一般的に、以下の方法で SQL Server に格納されたデータを操作します。

各ソース列を個別に操作する場合、Transact-SQL クエリからスカラ関数を使用します。
単一の出力列の作成時に複数のソース列を操作する必要がある場合、TVF を使用します。
SQL Server 外に格納されたデータを操作するには以下の方法を使用します。
1 つの値のみが返される場合、スカラ関数を使用します。

複数の値が返される場合、テーブル値関数を使用します。この場合の例については、以下のシナリオ #5 を参照してください。

C# の GetFileNameWithoutExtension スカラ関数の例を次に示します。

using System;
using System.IO;
public class Wrapper 
{
public static String ExtractFileNameWithoutExtension(String str)
   {
   return System.IO.Path.GetFileNameWithoutExtension(str);
   }

};このコードを次のコマンドラインでコンパイルします。

csc /t:library /r:sqlaccess.dll scenario2.cs /o: scenario2.dll

SQL でアセンブリおよびプロシージャを登録し、呼び出す DDL を次に示します。

use tempdb
go
drop table PHOToS
drop function GetFileName
drop assembly scenario2
go
CREATE TABLE PHOTOS
(
  ID INT NOT NULL,
  PATH NVARCHAR(400) NOT NULL
)
INSERT PHOTOS VALUES(1, 'C:\IMAGES\PRODUCTS\PHOTOS\COLOR\KETCHUP.JPG')
INSERT PHOTOS VALUES(2, 'C:\IMAGES\PRODUCTS\PHOTOS\COLOR\MUSTARD.JPG')
INSERT PHOTOS VALUES(3, 'C:\IMAGES\PRODUCTS\PHOTOS\COLOR\RELISH.JPG')
INSERT PHOTOS VALUES(4, 'C:\IMAGES\PRODUCTS\PHOTOS\COLOR\PICKLES.JPG')
go
CREATE ASSEMBLY scenario2 FROM 'c:\yourpathhere\scenario2.dll'
go
CREATE FUNCTION GetFileName(@FullFileName NVARCHAR(400))
RETURNS NVARCHAR(30)
EXTERNAL NAME scenario2:Wrapper::ExtractFileNameWithoutExtension
go
select dbo.GetFileName(path) from photos
go

シナリオ #3 - オークションの入札とパフォーマンスの高いストアド プロシージャ

データベースのデータのみを操作し、外部のデータにアクセスしないストアドプロシージャの大部分は、以下のいずれかとして記述できます。

CLR UDP

多くの場合、スカラ CLR UDF を呼び出して計算を実行する Transact-SQL プロシージャ

計算へのデータアクセスの割合によって、どちらを使用するかが異なります。計算が多いほど、CLR UDP のパフォーマンスは Transact-SQL プロシージャに比べて向上します。極端な例を挙げると、コードで計算のみを実行する場合、.NET ルーチンでは Transact-SQL プロシージャより数倍速く実行できます。逆にコードでデータアクセスのみを実行する場合、CLR での実行速度は Transact-SQL プロシージャよりも約 20 パーセント "遅く" なります。

ストアドプロシージャの記述方法を決める際の他の重要な要素として、コードの容易な記述および管理、および中間層またはクライアント層への相対的な移植性があります。一般的に、計算ロジックを記述、管理、および移植する際は、CLR 言語を使用すると、Transact-SQL より容易に行うことができます。一般的に、データアクセスを記述および管理する際は、Transact-SQL を使用すると、より容易に行うことができます。

このシナリオでは、オークションの入札を受け付けるストアドプロシージャを実装します。使用するのは簡単な入札のセマンティクスですが、CLR UDP の記述を魅力的にするのに十分な計算処理があります。

.NET ルーチンからのデータアクセスの最大限のスケーラビリティを実現するために、SQL Server "Yukon" Beta 1 では ADO.NET のプログラミングモデルへの拡張が提供されています。 SqlDefinition と SqlExecutionContext という 2 つのクラスが提供されます。両方のクラスに、SqlCommand オブジェクトの一部が含まれています。 SqlDefinition には、それぞれの実行によって変化しないコマンド部分、つまりコマンドテキスト、パラメータ型、および方向は静的なままとします。 SqlExecutionContext には、パラメータの値など、各実行によって変化する要素が含まれます。この分離により、SqlDefinition オブジェクトは多数のユーザーによる同時および順次参照を可能にし、各 SqlExecutionContext は 1 人のユーザー固有の参照を可能にします。

SqlDefinition と SqlExecutionContext を使用すると、ユーザー 1 人あたりの実行時間は約 1 パーセント遅くなります。ただし、複数のユーザーによるサーバーベースのシナリオにおける実質的なスループットは、場合によっては 50 パーセントも高くなります。マイクロソフトの SQL Server 製品チームは、CLR プロシージャのすべてのデータアクセスで、SqlDefinition と SqlExecutionContext を使用することを推奨しています。

以下のシナリオでは、SqlDefinition と SqlExecutionContext の使用方法を例示します。

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlServer;
public class T {
   static readonly SqlDefinition sm_InsertNewBid;
   static readonly SqlDefinition sm_GetBidOfUserItem; 
   static readonly SqlDefinition sm_UpdateBidOfUserItem;
   static readonly SqlDefinition sm_GetCurrentWinnerOfItem;
   static readonly SqlDefinition sm_GetTop5;
   static T() {
      SqlCommand cmd = new SqlCommand();
      SqlParameter param;
      cmd.CommandText = "insert bids values(@uid,@itemid,@price,@maxprice,@incr,getdate())";
      param = cmd.Parameters.Add("@uid",SqlDbType.Int);   param.Direction = ParameterDirection.Input;
      param = cmd.Parameters.Add("@itemid",SqlDbType.Int);   param.Direction = ParameterDirection.Input;
      param = cmd.Parameters.Add("@price",SqlDbType.Float);   param.Direction = ParameterDirection.Input;
      param = cmd.Parameters.Add("@maxprice",SqlDbType.Float);param.Direction = ParameterDirection.Input;
      param = cmd.Parameters.Add("@incr",SqlDbType.Float);   param.Direction = ParameterDirection.Input;
      sm_InsertNewBid = new SqlDefinition(cmd);
      cmd.Parameters.Clear();
      cmd.CommandText = "select @price=price,@maxprice=maxprice,@incr=incr 
from bids where (uid=@uid and itemid=@itemid)";
      param = cmd.Parameters.Add("@price",SqlDbType.Float);   param.Direction = ParameterDirection.Output;
      param = cmd.Parameters.Add("@maxprice",SqlDbType.Float);param.Direction = ParameterDirection.Output;
      param = cmd.Parameters.Add("@incr",SqlDbType.Float);   param.Direction = ParameterDirection.Output;
      param = cmd.Parameters.Add("@uid",SqlDbType.Int);   param.Direction = ParameterDirection.Input;
      param = cmd.Parameters.Add("@itemid",SqlDbType.Int);   param.Direction = ParameterDirection.Input;
      sm_GetBidOfUserItem = new SqlDefinition(cmd);
      cmd.Parameters.Clear();
      cmd.CommandText = "update bids set price=@price,maxprice=@maxprice,incr=@incr,bid_time=getdate() 
      from bids where (uid=@uid and itemid=@itemid)";
      param = cmd.Parameters.Add("@price",SqlDbType.Float);   param.Direction = ParameterDirection.Input;
      param = cmd.Parameters.Add("@maxprice",SqlDbType.Float);param.Direction = ParameterDirection.Input;
      param = cmd.Parameters.Add("@incr",SqlDbType.Float);   param.Direction = ParameterDirection.Input;
      param = cmd.Parameters.Add("@uid",SqlDbType.Int);   param.Direction = ParameterDirection.Input;
      param = cmd.Parameters.Add("@itemid",SqlDbType.Int);   param.Direction = ParameterDirection.Input;
      sm_UpdateBidOfUserItem = new SqlDefinition(cmd);
      cmd.Parameters.Clear();
      cmd.CommandText = "select @uid=uid,@price=price,@maxprice=maxprice,@incr=incr 
      from bids where price=(select max(price) 
      from bids where itemid=@iid) and itemid=@iid order by maxprice";
      param = cmd.Parameters.Add("@iid",SqlDbType.Int);   param.Direction = 
ParameterDirection.Input;
      param = cmd.Parameters.Add("@uid",SqlDbType.Int);   param.Direction = ParameterDirection.Output;
      param = cmd.Parameters.Add("@price",SqlDbType.Float);   param.Direction = ParameterDirection.Output;
      param = cmd.Parameters.Add("@maxprice",SqlDbType.Float);param.Direction = ParameterDirection.Output;
      param = cmd.Parameters.Add("@incr",SqlDbType.Float);   param.Direction = ParameterDirection.Output;
      sm_GetCurrentWinnerOfItem = new SqlDefinition(cmd);
      cmd.Parameters.Clear();
      cmd.CommandText = "select top 5 uid,price,maxprice,bid_time from bids where itemid=@itemid order by price";
      param = cmd.Parameters.Add("@itemid",SqlDbType.Int);   param.Direction = ParameterDirection.Input;
      sm_GetTop5 = new SqlDefinition(cmd);
      }
   public static void Bid(Int32 new_uid,Int32 iid,Double new_price,Double new_maxprice,Double new_incr)
      {
      Int32 cur_uid=0;
      Double exist_price=0,cur_price=0, cur_maxprice=0, cur_incr=0;
      SqlPipe sp = SqlContext.GetPipe();
      // Validate bid: 
      if (    (new_maxprice <= new_price) || 
         ((new_maxprice > new_price) && (new_incr < 0)) ||
         ((new_maxprice == new_price) && (new_incr != 0)) )
         {sp.Send("Invalid Bid"); return;}
      // Begin transaction
      SqlConnection cn = SqlContext.GetConnection();
      SqlTransaction tran = cn.BeginTransaction();
      // determine current high bid. 
      SqlExecutionContext reqGetCurrentWinnerOfItem = cn.CreateExecutionContext(sm_GetCurrentWinnerOfItem);
      reqGetCurrentWinnerOfItem.Transaction = tran;
      reqGetCurrentWinnerOfItem.SetInt32(0,iid);
      reqGetCurrentWinnerOfItem.ExecuteNonQuery();
      if (!reqGetCurrentWinnerOfItem.GetSqlInt32(1).IsNull)
         {   
         cur_uid = reqGetCurrentWinnerOfItem.GetInt32(1);
         cur_price = reqGetCurrentWinnerOfItem.GetDouble(2);
         cur_maxprice = reqGetCurrentWinnerOfItem.GetDouble(3);
         cur_incr = reqGetCurrentWinnerOfItem.GetDouble(4);
         }
      // determine if this user has already bid  
      SqlExecutionContext reqGetBidOfUserItem = cn.CreateExecutionContext(sm_GetBidOfUserItem);
      reqGetBidOfUserItem.Transaction = tran;
      reqGetBidOfUserItem.SetInt32(3,new_uid);
      reqGetBidOfUserItem.SetInt32(4,iid);
      reqGetBidOfUserItem.ExecuteNonQuery();
      if (reqGetBidOfUserItem.GetSqlDouble(0).IsNull)
         {   //No existing bid by this user, insert new one
         SqlExecutionContext reqInsertNewBid = cn.CreateExecutionContext(sm_InsertNewBid);
         reqInsertNewBid.Transaction = tran;
         reqInsertNewBid.SetInt32(0,new_uid);         
         reqInsertNewBid.SetInt32(1,iid);         
         reqInsertNewBid.SetDouble(2,new_price);         
         reqInsertNewBid.SetDouble(3,new_maxprice);         
         reqInsertNewBid.SetDouble(4,new_incr);         
         reqInsertNewBid.ExecuteNonQuery();
         }         
      else
         {   // replace existing bid with new bid
         exist_price = reqGetBidOfUserItem.GetDouble(0);
         if (exist_price > new_price)   // new bid lower, error
            {sp.Send("Invalid Bid"); tran.Rollback(); return;}
         SqlExecutionContext reqUpdateBidOfUserItem = cn.CreateExecutionContext(sm_UpdateBidOfUserItem);
         reqUpdateBidOfUserItem.Transaction = tran;
         reqUpdateBidOfUserItem.SetDouble(0,new_price);         
         reqUpdateBidOfUserItem.SetDouble(1,new_maxprice);         
         reqUpdateBidOfUserItem.SetDouble(2,new_incr);         
         reqUpdateBidOfUserItem.SetInt32(3,new_uid);         
         reqUpdateBidOfUserItem.SetInt32(4,iid);         
         reqUpdateBidOfUserItem.ExecuteNonQuery();
         }         
      // Determine winner between new bid and cur winner; adjust as necessary
      if ((cur_uid == 0) || cur_uid == new_uid) 
         {goto send_top5;} //first bid or raise of existing winning bid
      if ( (!(cur_maxprice <= new_price))   && (!(new_maxprice <= cur_price)) )
         {
         if ((cur_price <= new_price) && (new_price <= cur_maxprice) && (cur_maxprice <= new_maxprice))
            {
            cur_price = cur_maxprice; new_price = System.Math.Min(new_maxprice,cur_maxprice + new_incr);
            }
         else if ((cur_price <= new_price) && (new_maxprice <= cur_maxprice))
            {
            cur_price = System.Math.Min(cur_maxprice,new_maxprice + cur_incr); new_price = new_maxprice;
            }
         else if ((new_price <= cur_price) && (cur_maxprice <= new_maxprice))
            {
            cur_price = cur_maxprice; new_price = System.Math.Min(new_maxprice,cur_maxprice + new_incr);
            }         
         else if ((new_price <= cur_price) && (cur_price <= new_maxprice) && (new_maxprice <= cur_maxprice))
            {
            cur_price = System.Math.Min(cur_maxprice,new_maxprice + cur_incr); new_price = new_maxprice;
            }            
         SqlExecutionContext reqUpdateBidOfUserItem = cn.CreateExecutionContext(sm_UpdateBidOfUserItem);
         reqUpdateBidOfUserItem.Transaction = tran;
         // update cur_bid
         reqUpdateBidOfUserItem.SetDouble(0,new_price);         
         reqUpdateBidOfUserItem.SetDouble(1,new_maxprice);         
         reqUpdateBidOfUserItem.SetDouble(2,new_incr);         
         reqUpdateBidOfUserItem.SetInt32(3,new_uid);         
         reqUpdateBidOfUserItem.SetInt32(4,iid);         
         reqUpdateBidOfUserItem.ExecuteNonQuery();
         // update new_bid
         reqUpdateBidOfUserItem.SetDouble(0,cur_price);         
         reqUpdateBidOfUserItem.SetDouble(1,cur_maxprice);         
         reqUpdateBidOfUserItem.SetDouble(2,cur_incr);         
         reqUpdateBidOfUserItem.SetInt32(3,cur_uid);         
         reqUpdateBidOfUserItem.SetInt32(4,iid);         
         reqUpdateBidOfUserItem.ExecuteNonQuery();
         }
send_top5:
      tran.Commit();      
      sp.Send("Bid Accepted");      
      //Send result of top5 current bids
      SqlExecutionContext reqGetTop5 = cn.CreateExecutionContext(sm_GetTop5);
      reqGetTop5.SetInt32(0,iid);          
      sp.Execute(reqGetTop5);
      }
}
inproc プロバイダを使用してアセンブリをコンパイルするには、次のように sqlaccess.dll を参照する必要があります。
csc /t:library /r:sqlaccess.dll scenario3.cs /o: scenario3.dll
SQL でアセンブリおよびプロシージャ、および呼び出し例を登録する DDL を次に示します。
use tempdb
go
DROP PROCEDURE Bid
DROP ASSEMBLY scenario3
DROP TABLE bids
go
CREATE TABLE bids (uid int, itemid int, price float, maxprice float, incr float,bid_time datetime)
create assembly scenario3 from 'c:\yourpathhere\scenario3.dll'
go
CREATE PROCEDURE Bid (@uid int,@iid int,@price float,@maxprice float,@incr float)
AS EXTERNAL NAME scenario3:T::Bid
go
exec Bid 2,1,50,100,1
go
exec Bid 1,1,30,110,1
go
exec Bid 2,1,200,300,1
go

inproc プロバイダを使用してアセンブリをコンパイルするには、次のように sqlaccess.dll を参照する必要があります。

csc /t:library /r:sqlaccess.dll scenario3.cs /o: scenario3.dll

SQL でアセンブリおよびプロシージャ、および呼び出し例を登録する DDL を次に示します。

use tempdb
go
DROP PROCEDURE Bid
DROP ASSEMBLY scenario3
DROP TABLE bids
go
CREATE TABLE bids (uid int, itemid int, price float, maxprice float, incr float,bid_time datetime)
create assembly scenario3 from 'c:\yourpathhere\scenario3.dll'
go
CREATE PROCEDURE Bid (@uid int,@iid int,@price float,@maxprice float,@incr float)
AS EXTERNAL NAME scenario3:T::Bid
go
exec Bid 2,1,50,100,1
go
exec Bid 1,1,30,110,1
go
exec Bid 2,1,200,300,1
go

シナリオ #4 - イベント ログへの書き込み

管理者は、SQL Server の以前のリリースでは RAISERROR を使用して、適切な組み合わせの重大度、エラーコード、およびメッセージの文字列を渡すことによってイベントログへの記録を行うことができました。

ただし、特にさまざまな異なるメッセージの種類およびログターゲットを使用する場合、ログへのデータの記録という点において、RAISERROR では常に十分な柔軟性は得られませんでした。

CLR の統合を利用することにより、容易にローカルおよびリモートイベントログへの記録を実行できるストアドプロシージャおよび関数を記述できます。また、記録されるメッセージの形式を細かく選択できます。

System.Diagnostics を使用してイベントログへの記録を行うには、EventLogPermission が必要になります。 Yukon Beta 1 では、このアクセス許可は EXTERNAL_ACCESS を持つアセンブリには許可されません。これは Yukon Beta 1 の不具合であり、将来の Yukon のベータ版リリースでは、EventLogPermission は EXTERNAL_ACCESS に含める予定です。したがって、この例では次のようにアセンブリを UNSAFE として登録します。

CREATE ASSEMBLY YukonCLRFROM 'C:\MyDBApp\YukonCLR.dll'
WITH PERMISSION_SET = UNSAFE

Visual Basic .NET でイベントログへの記録を行うルーチンを次に示します。

Imports System
Imports System.Diagnostics
Public Shared Sub WriteToEventLog(ByVal Msg As String, ByVal EntryType As String, ByVal LogName As String)
   Dim entry As EventLogEntryType
   Select Case EntryType
       Case "Information"
           entry = EventLogEntryType.Information
       Case "Warning"
           entry = EventLogEntryType.Warning
       Case "Error"
           entry = EventLogEntryType.Error
   End Select
   Dim ev As New EventLog(LogName, Environment.MachineName,  & _
      "CLR    Integration")
   ev.WriteEntry(Msg, entry)
   ev.Dispose()
End Sub

強調表示されたコードの 1 行目では、コンストラクタに LogName パラメータを渡して EventLog クラスの新しいインスタンスを作成しています。 LogName は、標準アプリケーションログ、システムログ、またはカスタムログにすることができます。コンストラクタに渡される次のパラメータには、Environment クラスの MachineName プロパティによって取得されるローカルコンピュータ名を指定します。最後に、イベントのソースは "CLR Integration" として登録されます。

次のコード行では、渡された重大度およびメッセージを使用してメッセージをログに記録します。

ev.WriteEntry(Msg, entry)

このサブルーチンをストアドプロシージャとしてラップするには、次のコードを使用します。

CREATE PROCEDURE WriteToEventLog(@MSG NVARCHAR(4000), 
@EntryType NVARCHAR(11), @LogName NVARCHAR(4000))
AS 
EXTERNAL NAME YukonCLR:[MyNamespace.CLRCode]::WriteToEventLog

シナリオ #5 - イベント ログからの読み取りとテーブル値関数の記述方法

テーブル値関数へのイベントログの公開は、イベントログの保存および分析に役立ちます。テーブル値関数では、ISqlReader を返します。これは非常に大きなインターフェイスですが、幸運にも SQL Server で使用されるメソッドで、実装する必要があるものはごく少数です。

メンバ FieldCount は、返されたテーブルの列の数を示します。
返されたテーブルの列の順序を渡すメソッド GetSqlMetaData は、型を返します。
メソッド Read は、追加で返される列があるかどうかを判断するために呼び出されます。 Read では、返される列がある場合は true を、返されるテーブルが完了している場合は false を返します。

返されるテーブルのそれぞれの一意列の型の場合は、メソッド GetSql<Typename> が呼び出され、返されるテーブルの列の順序が引数として渡されます。その結果、現在の行の列の値を返します。

イベントログを読み取り、テーブルとして返す C# コードを次に示します。

using System;
using System.Data;
using System.Data.Sql; 
using System.Data.SqlServer;
using System.Data.SqlTypes;
using System.Diagnostics;
public class T
{
   public static ISqlReader ReadEventLog(String logname)
   {
      return (ISqlReader) new MySqlReader(logname);
   }
   public class MySqlReader : ISqlReader
   {
      private EventLog m_ev; // EventLog we are reading
      private   EventLogEntryCollection m_evc;
      private int m_iRow = 0; // # rows read
      //Initialize eventlog reader
      public MySqlReader (String logname)
      {
         m_ev = new EventLog(logname,Environment.MachineName);
         m_evc = m_ev.Entries;
      }
   // SECTION: Metadata related: Provide #, names, types of result columns
      public int FieldCount {get{return 4;}}
      public SqlMetaData GetSqlMetaData(int FieldNo)
      {
         if (FieldNo == 0)
            return new SqlMetaData("Time",SqlDbType.DateTime);
         if (FieldNo == 1)
            return new SqlMetaData("Message",SqlDbType.NVarChar,400);
         if (FieldNo == 2)
            return new SqlMetaData("Category",SqlDbType.NVarChar,50);
         return new SqlMetaData("EventId",SqlDbType.Int);
      }
// SECTION: Row Handling. Read is called until it returns false.  
   // After each Read call, Get<TypeName> for each field is called.
      public bool Read()
      {
         if (m_iRow == m_evc.Count)
            return false;
         m_iRow++;
         return true;
      }
      public SqlChars GetSqlCharsRef(int i)
      {
      if (i==0) 
         return new SqlChars(m_evc[m_iRow].Message);
      return new SqlChars(m_evc[m_iRow].Category);
      }
      public SqlDateTime GetSqlDateTime(int i){return new SqlDateTime(m_evc[m_iRow].TimeWritten);}
      public SqlInt32 GetSqlInt32(int i) {return m_evc[m_iRow].EventID;}
   // SqlType accessors - no columns of these types so these are not called.
      public SqlBinary GetSqlBinary(int i) {throw new NotImplementedException();}
      public SqlBoolean GetSqlBoolean(int i) {throw new NotImplementedException();}
      public SqlByte GetSqlByte(int i) {throw new NotImplementedException();}
      public SqlInt16 GetSqlInt16(int i) {throw new NotImplementedException();}
      public SqlInt64 GetSqlInt64(int i){throw new NotImplementedException();}
      public SqlSingle GetSqlSingle(int i){throw new NotImplementedException();}
      public SqlDouble GetSqlDouble(int i){throw new 
NotImplementedException();}
      public SqlMoney GetSqlMoney(int i){throw new 
NotImplementedException();}
      public SqlDecimal GetSqlDecimal(int i){throw new NotImplementedException();}
      public SqlString GetSqlString(int i){throw new NotImplementedException();}
      public SqlChars GetSqlChars(int i){throw new NotImplementedException();}
      public SqlGuid GetSqlGuid(int i){throw new NotImplementedException();}
      public SqlBytes GetSqlBytes(int i){throw new NotImplementedException();}
      public SqlBytes GetSqlBytesRef(int i){throw new NotImplementedException();}
      public object GetSqlValue(int i){throw new NotImplementedException();}
      public int GetSqlValues(object[] values){throw new NotImplementedException();}
      public System.Data.SqlTypes.SqlUtcDateTime GetSqlUtcDateTime(int i){throw new NotImplementedException();}
      public System.Data.SqlTypes.SqlDate GetSqlDate(int i){throw new NotImplementedException();}
      public System.Data.SqlTypes.SqlTime GetSqlTime(int i){throw new NotImplementedException();}
      public System.Data.SqlTypes.SqlXmlReader GetSqlXmlReader(Int32 i){throw new NotImplementedException();}
   // SECTION: Non-SqlType accessors - never used by SQL 
      public Object GetValue(int FieldNo) {throw new NotImplementedException();}
      public Int32 GetInt32(int FieldNo) {   throw new NotImplementedException();}
      public bool GetBoolean(int i) {   throw new NotImplementedException();}
      public byte GetByte(int i){   throw new NotImplementedException();}
      public long GetBytes(int i, long fieldOffset, byte[] buffer, int 
bufferoffset, int length)
      {throw new NotImplementedException();}
      public String GetString(int i)    {throw new NotImplementedException();}
      public char GetChar(int i){throw new NotImplementedException();}
      public long GetChars(int i, long fieldoffset, char[] buffer, int bufferoffset, int length)
      {throw new NotImplementedException();}
      public Guid GetGuid(int i) {throw new NotImplementedException();}
      public Int16 GetInt16(int i) {throw new NotImplementedException();}
      public Int64 GetInt64(int FieldNo) {throw new NotImplementedException();}
      public float GetFloat(int FieldNo) {throw new NotImplementedException();}
      public double GetDouble(int i) {throw new NotImplementedException();}
      public Decimal GetDecimal(int i) {throw new NotImplementedException();}
      public DateTime GetDateTime(int i) {throw new NotImplementedException();}
      public IDataReader GetData(int i) {throw new NotImplementedException();}
   // SECTION: These are required as part of the ISqlReader interface but are not used by SQL.      
      public bool IsClosed {get{ return false;}}
      public void Close(){}
      public bool IsDBNull(int i){return false;}
      public bool IsSetAsDefault(int i) {return false;}
      public object GetObjectRef(int i) {return null;}
      public bool HasRows{get   {throw new NotImplementedException();}}
      public void Dispose(){throw new NotImplementedException();}
      public int Depth {get{return 0;}}
      public DataTable GetSchemaTable(){return null;}
      public object this [ int i ]    { get{return null;}}
      public object this [ String name ] { get{return null;}}
      public String GetName (int FieldNo) {return null;}
      public String GetDataTypeName (int FieldNo) {return null;}
      public Type GetFieldType(int FieldNo) {return typeof(int);}
      public int RecordsAffected {get{return 0;}}
      public bool NextResult (){return false;}
      public int GetValues(object[] values){return 0;}
      public int GetOrdinal(string name){return 0;}
      public bool IsDefault(int i) {return false;}
   } // public class MySqlReader
} // class T

ISqlReader を返す関数を含むアセンブリをコンパイルするには、sqlaccess.dll を参照する必要があります。

csc /t:library /r:sqlaccess.dll scenario5.cs /o: scenario5.dll

SQL でこのアセンブリおよび関数を登録して使用し、過去 10 回、システムのログオンまたはログオフを行ったユーザーを検索するには、次のコードを実行します。

dbcc freeproccache
go
use tempdb
go
drop function ReadEventLog
drop assembly scenario5
go
create assembly scenario5 from 'c:\yourpathhere\scenario5.dll' with PERMISSION_SET = UNSAFE
go
CREATE FUNCTION ReadEventLog(@logname nvarchar(100))
RETURNS @ TABLE(Time datetime,Message nvarchar(400),Category nvarchar(50),EventId int)
AS EXTERNAL NAME   scenario5:T::ReadEventLog
go
SELECT TOP 10 T.Time 
FROM dbo.ReadEventLog(N'Security') as T
WHERE T.Category = N'Logon/Logoff'
go

シナリオ #6 - 簡単なファイルの入出力

.NET Framework は、ファイルおよびディレクトリへのアクセスにも応用されます。たとえば、次のサブルーチンでは、メッセージをテキストファイルに書き込みます。これは、サーバーからのメッセージまたは診断情報の記録に使用するという一般的な目的でデザインされたルーチンです。

Public Shared Sub WriteToTextFile(ByVal Message As String)
Const FILE As String = "C:\Windows\Temp\LogFile.txt"
   'Open the file that the message will be written to.
   Dim myFileStream As New System.IO.FileStream(FILE, _
      IO.FileMode.Append, IO.FileAccess.Write)
   'Open the stream writer to do the actual writing.
   Dim myStreamWriter As New System.IO.StreamWriter(myFileStream)
   'Write message to file.
   myStreamWriter.WriteLine(Message)
   'Close stream writer.
   myStreamWriter.Close()
   'Close file.
   myFileStream.Close()
End Sub

このルーチンではファイルシステムへのアクセスが必要なので、既定の SAFE アクセス許可では実行されません。したがって、次のように EXTERNAL_ACCESS アクセス許可セットを使用する必要があります。

CREATE ASSEMBLY YukonCLR
FROM 'C:\MyDBApp\YukonCLR.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

プロシージャを作成するには、次の DLL を使用します。

CREATE PROCEDURE WriteToTextFile
@Message NVARCHAR(4000)
AS 
EXTERNAL NAME YukonCLR:[YukonCLR.CLRCode]::WriteToTextFile

この .NET ルーチンを実行するには、EXEC キーワードを使用して次のようにメッセージを渡します。

EXEC dbo.WriteToTextFile 'Message from SQL Server'

シナリオ #7 - 財務計算

既に紹介した例に加えて、容易に Visual Basic .NET または C# などの特定の言語にのみ存在する既存の関数をラップして、Transact-SQL 関数として容易に呼び出すことができます。

One example of this is the following function:
Public Shared Function PaymentCalcMonthly _
   (ByVal InterestRate As Double, ByVal TotalPayments As Double, _
    ByVal TotalValue As Double) As Double
    Dim monthlyRate As Double = InterestRate / 12
    Return Pmt(monthlyRate, TotalPayments, TotalValue)
End Function

主要なコード行は、Microsoft.VisualBasic.Financial 名前空間の Pmt 関数を呼び出す行です。 Pmt 関数を上記のコードでラップすることにより、SQL Server のユーザー定義関数を使用して容易にこの機能にアクセスできます。この関数では、月々の金利、支払いの総数、および総元金に基づいて、月々の支払い額を計算します。

上記の .NET 関数をラップするユーザー定義関数を作成する DDL を次に示します。

CREATE FUNCTION PaymentCalcMonthly(@InterestRate float, 
@TotalPayments FLOAT, @TotalValue float)
RETURNS FLOAT
EXTERNAL NAME YukonCLR:[YukonCLR.CLRCode]:: PaymentCalcMonthly

シナリオ #8 - 数式

マネージコードは、特にループで実行される複雑または実行時間の長い数値演算にも適しています。たとえば、割賦償却を計算するアプリケーションを考えます。月々の支払いを計算するルーチンを次に示します。

Public Shared Function Amortization(ByVal Principal As Double, _
    ByVal InterestRate As Double, _
    ByVal Years As Integer) As Double
    'Principal = initial amount of the loan 
    'InterestRate = annual interest rate
    'Years = length in years of the loan
    'Calculate monthly interest rate
    Dim dblInterestPerMonth As Double
    dblInterestPerMonth = InterestRate / (12 * 100)
    'Calculate length in months of the loan
    Dim intMonths As Integer
    intMonths = Years * 12
    Return Principal * (dblInterestPerMonth / _
        (1 - (1 + dblInterestPerMonth) ^ -intMonths))
End Function

この関数は Transact-SQL で記述できますが、データベースアプリケーションでこの計算を実行した場合、データベースの行が数千または数百万にもなることが予想できます。そのような場合、マネージコードは、コンパイル済みコードを使用してパフォーマンスを強化できるため、理想的であると言えます。

このアセンブリをロードするには、次の Transact-SQL を実行します。

CREATE ASSEMBLY YukonCLR
FROM 'C:\MyDBApp\YukonCLR.dll'

この関数を作成するには、次の Transact-SQL を実行します。

CREATE FUNCTION Amortization(@Principal FLOAT, 
   @InterestRate FLOAT, @Years INT)
RETURNS FLOAT(53)
EXTERNAL NAME MyDotNETAssembly:[MyNamespace.CLRCode]::Amortization

年 5.25 パーセントの金利で、247,000 ドルを 30 年ローンで支払う場合の月々の支払い額を計算するには、次の Transact-SQL を実行します。

SELECT dbo.Amortization(247000, 5.25, 30)

返される月額は 1,363.94 ドルです。

トリガ

マネージコードを使用したストアドプロシージャおよびユーザー定義関数のビルド方法を理解したら、次はその知識を利用してトリガを実装できます。ユーザー定義関数およびプロシージャの開発時に使用するクラス、メソッド、およびプロパティの大部分は、トリガの開発にも応用できます。たとえば、ユーザーに任意の ID を選択させ、その中から電子メールアドレスを ID として入力したユーザーを検索するシナリオを考えます。次のトリガでは、その情報を検出して、監査テーブルに記録します。

Imports System
Imports System.Data
Imports System.Data.SqlServer
Imports System.Text.RegularExpressions
Public Class EmailTrigger
Public Shared Sub EmailAudit()
   Dim triggContext As SqlTriggerContext = _
      SqlContext.GetTriggerContext()
   Dim userName as String
   If triggContext.TriggerAction.Insert  Then
        Dim sqlComm As SqlCommand = SqlContext.GetCommand()
      Dim sqlP As SqlPipe = SqlContext.GetPipe()
      sqlComm.CommandText = "SELECT Username from " & _
         "INSERTED"
      Dim dataRecord as SqlDataRecord = sqlComm.ExecuteRow()
      userName = dataRecord(0)
      If IsValidEMailAddress(userName) Then
         sqlComm.CommandText = "INSERT Emails " & _ 
         VALUES('"userName & "')"
         sqlP.Send(sqlComm.CommandText)
         sqlP.Execute(sqlComm)
      End If
   End If
End Sub
Public Shared Function IsValidEMailAddress(ByVal email As String) _ 
   As Boolean
   Return Regex.IsMatch(email, _
     "^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$") 
End Function
End Class.

上記の EmailAudit 関数の最初の手順では、TriggerContext を取得します。この手順は、SqlContext クラスの GetTriggerContext メソッドを呼び出すことによって実行されます。

Dim triggContext As SqlTriggerContext = _
   SqlContext.GetTriggerContext()

この手順後の、次のコードがマネージトリガの主要部分です。

If triggContext.TriggerAction.Insert  Then
     Dim sqlComm As SqlCommand = SqlContext.GetCommand()
    Dim sqlP As SqlPipe = SqlContext.GetPipe()
   sqlComm.CommandText = "SELECT Username from INSERTED"
   Dim dataRecord as SqlDataRecord = sqlComm.ExecuteRow()
   userName = dataRecord(0)
   If IsValidEMailAddress(userName) Then
      sqlComm.CommandText = "INSERT Emails VALUES('" & _
         userName & "')"
      sqlP.Send(sqlComm.CommandText)
      sqlP.Execute(sqlComm)
   End If
End If

このコードでは、まず TriggerAction を調べることによって、トリガが INSERT 操作の結果として実行されているかどうかを判断します (TriggerAction の他の値は Update および Delete です)。次に、インプロセスマネージプロバイダを構成するクラスおよびメソッドを使用して、username テーブルから最近挿入されたユーザー名を取得します。このユーザー名は、IsValidEmailAddress 関数にパラメータとして渡されます。ユーザー名が電子メールアドレスである場合、データベースの Emails テーブルに書き込まれます。

以下の定義を持つ 2 つのテーブルがあると仮定します。

CREATE TABLE Users
(
   UserName   NVARCHAR(200)    NOT NULL,
   Pass      NVARCHAR(200)    NOT  NULL
)
CREATE TABLE Emails
(
   UserName    NVARCHAR(200)    NOT NULL
)

トリガを作成する DDL を次に示します。

CREATE TRIGGER EmailAudit
ON Users
FOR INSERT
AS
EXTERNAL NAME YukonCLR:EmailTrigger::EmailAudit

CREATE TRIGGER ステートメントの EXTERNAL NAME 要素が、CREATE FUNCTION ステートメントと CREATE PROCEDURE ステートメントで使用された場合と似たパターンで記述されていることに注意してください。

上記のトリガは、Framework の型および Visual Basic .NET または C# で使用できるより強力な言語構造を応用したより強力なトリガの記述方法の一例です。

ユーザー定義データ型 (UDT)

Yukon では、データベースのスカラ型システムの拡張を可能にする新しいメカニズムである UDT へのサポートが追加されます。アプリケーションのアーキテクチャという観点から見た場合、UDT には 2 つの重要な利点があります。 UDT では、内部状態と外部動作間の強力なカプセル化 (クライアントとサーバーの両方)、および他の関連するサーバー機能との高レベルな統合が提供されます。独自の UDT を定義すると、列定義、変数、パラメータ、関数の結果、カーソル、トリガ、およびレプリケーションなど、Yukon のシステム型を使用できるすべてのコンテキストでその UDT を使用できます。

型システムを拡張するプロセスは、以下の手順を完了することによって実現します。

UDT 作成の規則に従って、マネージコードでクラスを作成します。
CREATE ASSEMBLY ステートメントを使用して、UDT を含むアセンブリをサーバーのデータベースに読み込みます。
CREATE TYPE ステートメントを使用して、マネージコード UDT を公開する型をデータベースに作成します。

プロセスの完了後、テーブルの定義にその型を使用できます。

マネージコードで UDT の定義を作成した場合、その型は以下の要件を満たしている必要があります。

Serializable でマークされる必要があります。
SqlUserDefinedTypeAttribute で修飾される必要があります。
INullable インターフェイスを実装して、型での NULL の使用を許可する必要があります。
以下のメソッドを実装して、型での文字列への変換および文字列からの変換をサポートする必要があります。
- Public String ToString()
- Public Shared <type> Parse (SqlString s)
型に、引数を受け取らないパブリックコンストラクタが含まれている必要があります。

以上の特徴を持ったクラスを実装すれば、開発者が既にマネージコードアプリケーションを記述している場合でも問題は発生しません。 SqlUserDefinedTypeAttribute は新しい属性であり、以下のプロパティが含まれています。

名前	説明
MaxByteSize	型のインスタンスの最大サイズです。
IsFixedLength	型のすべてのインスタンスが固定長であるかどうかを示すブール値です。
IsByteOrdered	比較に 2 進表現が使用されるかどうかを示すブール値です。
Format	UDT のストレージ形式です。Native、UserDefined、または SerializedDataWithMetadata のいずれかにできます。

Visual Basic .NET の UDT の例を次に示します。

Imports System
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports System.Runtime.Serialization
<Serializable(), SqlUserDefinedTypeAttribute(Format.Native)> _
Public Structure Point
    Implements INullable
    Private is_Null As Boolean 
    Private m_x As Double
    Private m_y As Double
    Public ReadOnly Property IsNull() As Boolean _
       Implements INullable.IsNull
        Get
            Return (is_Null)
        End Get
    End Property
    Public Overrides Function ToString() As String
        If Me.IsNull Then
            Return "NULL"
        Else
            Return Me.m_x & ":" & Me.m_y
        End If
    End Function
    Public Shared Function Parse(ByVal s As SqlString) _
    As Point
        If s.IsNull Then
            Return Nothing
        Else
        'Parse input string here to separate out points
       Dim pt as new Point()
       Dim str as String = Convert.ToString(s)
       Dim xy() as String = str.Split(":")
       pt.x = xy(0)
       pt.y = xy(1)
            Return (pt)
        End If
    End Function
    Public Shared ReadOnly Property Null() As Point
        Get
            Dim pt As New Point
            pt.is_Null = True
            Return (pt)
        End Get
    End Property
    Public Property X() As Double
        Get
            Return (Me.m_x)
        End Get
        Set(ByVal Value As Double)
            m_x = Value
        End Set
    End Property
    Public Property Y() As Double
        Get
            Return (Me.m_y)
        End Get
        Set(ByVal Value As Double)
                m_y = Value
        End Set
    End Property
End Structure

上記の例で最初に興味を引くコード行は、クラスの宣言です。クラスの宣言は、Serializable 属性と SqlUserDefinedTypeAttribute 属性の両方で修飾されています。 SqlUserDefinedTypeAttribute の Format プロパティによって、UDT のストレージ形式が決まります。

Yukon では、3 つのシリアル化形式がサポートされています。各シリアル化形式は、特定のエンドユーザーのシナリオを対象としています。次の表では、シナリオ間の比較検討を一覧します。

シナリオ	推奨形式	利点	欠点
単純 blittable 型 (structs と同様)	Native	コンパクト、高速	参照型に対するサポートなし
単純参照型 (たとえば、文字列フィールド)	UserDefined	シリアル化形式に対するフルコントロール、高速、宣言型プログラミングモデル	複雑 (独自のシリアライザの役割)
複雑参照型 (たとえば配列、文字列など)	SerializedDataWithMetadata	複雑な型定義、宣言型プログラミングモデルに対するサポート	最初の 2 つの形式よりはるかに大きな領域とパフォーマンスのオーバーヘッド

MaxByteSize プロパティにより、UDT の最大サイズが制御されます。妥当な UDT の最大サイズを指定することにより、Microsoft SQL Server "Yukon" では、UDT のストレージを適切に判断できます。 SQL Server "Yukon" Beta 1 での UDT の最大サイズは、8K に制限されています。

サンプルクラスでは INullable インターフェイスを実装して、型での NULL の使用を可能にします。また、型では Parse および ToString() メソッドの実装により、文字列変換を実装します。最後に、型では 2 つのプロパティプロシージャを実装して、このクラスによって表される地点の X および Y の値を取得および設定します。

このクラスをアセンブリにコンパイルしたら、次の手順は Microsoft SQL Server "Yukon" Beta 1 へのアセンブリの登録です。

create assembly point from 'c:\yourpathhere\point.dll'
go

アセンブリがロードされたら、CREATE TYPE ステートメントを使用して、データベースの使用可能な型の一覧にその型を追加します。

CREATE Type Point
external name [point]:Point

次に、型をデータベースの使用可能な型の 1 つとして登録した後、テーブルなどのオブジェクトの作成にその型を使用できます。

CREATE TABLE Points
(
   PointID int not null,
   Pnt Point not null
)

上記のように、テーブル定義で UDT を使用する場合、特別なコーディングは必要ありません。 nvarchar または nchar などの組み込みデータ型のいずれかを使用していた場合と同様の方法で、テーブルを定義します。 Transact-SQL を使用してテーブルにデータを設定するには、次のスクリプトを実行できます。

DECLARE @startPoint Point
DECLARE @endPoint Point
SET @startPoint = CONVERT(Point, '5:5')
SET @endPoint = CONVERT(Point, '10:10')
-- SET @startPoint::X = 1
-- SET @startPoint::Y = 2
INSERT Points
VALUES(1, @startPoint)
INSERT Points
VALUES(2, @endPoint)

上記のコードの大部分は、以前のバージョンの SQL Server からの Transact-SQL 変数、DML ステートメント、および SELECT ステートメントを使用した経験があるユーザーにとってはなじみのあるコードです。ただし、上記の Transact-SQL のコードには、いくつかの重要な要素があります。 Point 型の 2 つの変数が作成された後、CONVERT ステートメントの使用によってそれらの変数に値が設定されます。この方法で CONVERT ステートメントを使用すると、UDT で Parse メソッドが呼び出され、データが UDT インスタンスに設定されます。また、コメントになっているコードが示すように、UDT のプロパティに明示的に値を渡すことができます。 Points テーブルへの UDT 型のインスタンスの挿入は、標準の INSERT ステートメントの使用によって実現されます。テーブルに格納された UDT からの適切な値の選択は、次の構文 ColumnName::Property または Method Name を使用して呼び出すプロパティまたはメソッドを指定するプロセスで行われます。

SELECT Pnt::X, Pnt::Y FROM Points

マネージクライアントプロバイダ (ADO.NET) を使用して UDT の値が取得されると、型のインスタンスが返される、つまり状態と動作のカプセル化がクライアントに保存されます。オブジェクトを取得すると、クライアントの動作のコンシューマから同じ一連の動作が使用できます。

ユーザー定義集計

"製品版" に既に存在する集計関数よりも優れた追加の集計関数を作成する機能は、SQL Server の以前のリリースを使用している開発者は使用できませんでした。 Microsoft SQL Server "Yukon" Beta 1 リリースにより、開発者は、マネージコードでカスタム集計関数を作成し、この関数から T-SQL または他のマネージコードにアクセスできるようにすることができます。ユーザー定義集計を作成するには、最初に、特定のメソッドを実装する、Serializable 属性および SqlUserDefinedAggregate 属性で修飾するクラスを作成する必要があります。集計のシェルを次に示します。

Imports System
Imports System.Data
Imports System.Data.SqlServer
Imports System.Data.SqlTypes
Imports System.Runtime.Serialization
<Serializable()> _
<SqlUserDefinedAggregateAttribute _
   Format.Native)> _
Public Class MyAgg
   Public Sub Init()
   End Sub
   Public Sub Accumulate(i as Integer)
   End Sub
   Public Sub Merge(otherAggregate as MyAgg)
   End Sub
   Public Function Terminate() as Integer
      Return([type])
   End Function
End Class

標準の CREATE ASSEMBLY ステートメントを使用してアセンブリを読み込み、次のように集計を作成します。

CREATE AGGEGATE MyAgg(@p1 INT)
RETURNS INT
EXTERNAL NAME Agg:MyAgg

SqlUserDefinedAggregate 属性は、いくつかの点で SqlUserDefinedType 属性に似ています。この属性は型のシリアル化形式を受け取り、型の最大サイズを指定します。この属性には、次の表に要約されているいくつかのプロパティが含まれています。

IsInvariantToDuplicates	省略可能なプロパティです。集計が複製に対して不変である場合のみ true を返します。たとえば、MAX 関数や MIN 関数はこの条件を満たしますが、SUM 関数は満たしません。
IsInvariantToNulls	省略可能なプロパティです。集計が NULL に対して不変であるかどうかを指定します。たとえば、MIN 関数や SUM 関数はこの条件を満たしますが、COUNT 関数は満たしません。
IsInvariantToOrder	省略可能なプロパティです。集計が順序に対して不変であるかどうかを指定します。集計が順序に対して不変である場合、クエリオプティマイザを使用すると、集計を含むクエリの実行プランをより柔軟に選択できます。このプロパティの既定値は false です。

また、マネージコードで実装される集計には 4 つの必須のメソッドがあります。 Init メソッドは、集計の初期化に使用されます。また、このメソッドでは以前に使用した集計をクリーンアップし、計算への再利用が可能な状態にする必要があります。 Accumulate メソッドは、CREATE AGGREGATE ステートメントで使用される入力パラメータとして指定される型と等しい型の、単一の引数を受け取る必要があります。このメソッドでは、インスタンスの状態を更新し、この集計に対して適切な計算を実行します（たとえば、COUNT では内部カウンタの値を増加させます）。 Merge メソッドは、クエリプロセッサによって使用され、この集計クラスの別のインスタンスを、部分計算の一部として別のインスタンスとマージします。 Terminate メソッドは、計算処理タスクの最後で呼び出されます。このメソッドでは、CREATE AGGEGATE ステートメントの RETURNS 要素で使用される型と等しい型を返す必要があります。

次の例の集計では、テーブルの列から取得される一連の文字列値を結合します。

Imports System
Imports System.Data
Imports System.Data.SqlServer
Imports System.Data.SqlTypes
Imports System.Runtime.Serialization
Imports System.Text
<Serializable()> _
<SqlUserDefinedAggregate( _
   Format.SerializedDataWithMetadata, MaxByteSize:=8000)> _
Public Class MyAgg
   Private sb as StringBuilder
   Private firstConcat as Boolean = True
   Public Sub Init()
      sb = new StringBuilder()
   End Sub
   Public Sub Accumulate(s as String)
      If firstConcat Then
         sb.Append(s)
         firstConcat = false
      Else
         sb.Append(", ")
         sb.Append(s)
      End If
   End Sub
   Public Sub Merge(otherAggregate as MyAgg)
      Accumulate(otherAggregate)      
   End Sub
   Public Function Terminate() as String
      Return(sb.ToString())
   End Function
End Class

ほとんどの集計と同様に、主要な処理は Accumulate メソッドで行われます。 Accumulate メソッドにパラメータとして渡される文字列は、Init メソッドで初期化される StringBuilder オブジェクトに追加されます。また、Accumulate が呼ばれたのが初めてではない場合、渡された文字列を追加する前に StringBuilder にカンマが追加されます。計算タスクの最後で、StringBuilder を文字列として返す Terminate メソッドが呼び出されます。

次のスキーマで定義されたテーブルがあるとします。

CREATE TABLE .BookAuthors
(
   BookID   int       NOT NULL,
   AuthorName    nvarchar(200) NOT NULL
)

また、以下の行を追加します。

INSERT BookAuthors VALUES(1, 'Johnson')
INSERT BookAuthors VALUES(2, 'Taylor')
INSERT BookAuthors VALUES(3, 'Steven')
INSERT BookAuthors VALUES(2, 'Mayler')
INSERT BookAuthors VALUES(3, 'Roberts')
INSERT BookAuthors VALUES(3, 'Michaels')

次のクエリによって、その下に示す結果が返されます。

SELECT BookID, dbo.MyAgg(AuthorName) FROM BookAuthors GROUP BY BookID

BookID	Author Names
1	Johnson
2	Taylor, Mayler
3	Roberts, Michaels, Steven

ページのトップへ

まとめ

SQL Server "Yukon" Beta 1 のリリースでは、データベースプログラマは .NET の基本クラスライブラリおよび共通言語ランタイム (CLR) の豊富な機能を活用できます。データベース開発者は、CLR の統合を使用し、Visual Basic .NET や C# などの .NET 言語を使用して、独自のストアドプロシージャ、関数、トリガ、ユーザー定義型を作成できます。これらの .NET 言語は、オブジェクト指向の構造、構造化例外処理、配列、�

製品情報

リソース

企業情報

ユーザー別サイト

最新情報

目次

はじめに

CLR の統合

マネージコードの定義

Transact-SQL の制限事項

CLR の統合の利点

Transact-SQL とマネージコード間の選択

アセンブリの使用

アセンブリのロードとアンロード

アクセス許可セットとロードされたアセンブリの理解

実行コンテキストの理解

他のアセンブリの参照

.NET ルーチンの作成

インプロセスマネージプロバイダの理解

スカラ値関数 (スカラ UDF)

テーブル値関数 (TVF)

ユーザー定義ストアドプロシージャ (UDP)

.NET ルーチン : サンプルシナリオと実行に関するヒント集

トリガ

ユーザー定義データ型 (UDT)

ユーザー定義集計

まとめ

製品情報

リソース

企業情報

ユーザー別サイト

最新情報

目次

はじめに

CLR の統合

マネージ コードの定義

Transact-SQL の制限事項

CLR の統合の利点

Transact-SQL とマネージ コード間の選択

アセンブリの使用

アセンブリのロードとアンロード

アクセス許可セットとロードされたアセンブリの理解

実行コンテキストの理解

他のアセンブリの参照

.NET ルーチンの作成

インプロセス マネージ プロバイダの理解

スカラ値関数 (スカラ UDF)

テーブル値関数 (TVF)

ユーザー定義ストアド プロシージャ (UDP)

.NET ルーチン : サンプル シナリオと実行に関するヒント集

トリガ

ユーザー定義データ型 (UDT)

ユーザー定義集計

まとめ

マネージコードの定義

Transact-SQL とマネージコード間の選択

インプロセスマネージプロバイダの理解

ユーザー定義ストアドプロシージャ (UDP)

.NET ルーチン : サンプルシナリオと実行に関するヒント集