CLRSQL を使う方法

SQL Server 2005 以降では CLRSQL を使って、C# など .NET Framework 上でストアドプロシージャを記述すること可能です。

一般に次のように使い分けます。

SQL CLR を使ったほうが良い場面

• 正規表現を利用したい時
• 文字列操作
• 外部リソースの利用

T-SQL を使ったほうが良い場面

• データ操作

環境設定

SQL CLR 実行の許可を設定する。

----- ここから -----
EXEC sys.sp_configure 'clr enabled', '1'
RECONFIGURE
----- ここまで -----

※必要に応じて
1. [SQL Server Surface Area Configuration] を開く
2. [Surface Area Configuration for Services and Connections] を選択
    [MSSQLSERVER]
     - [Database Engine]
      - Remote Connections を選択
3. [Local and remote connections] – [Using both TCP/IP and named pipes] を選択
   接続数の制限をデフォルトの 3 から 100 に変更
4. [SQL Server Management Studio] から次のクエリを実行

    ----- ここから -----
    exec sp_configure 'show advanced option', '1'
    reconfigure
    exec sp_configure 'user connections', '100'
    reconfigure
    ----- ここまで -----

Visual Studio 2005 からのストアドプロシージャの作成

1. プロジェクトは [SQL Server Project] を選択
2. プロジェクトに [Add New Item...] で [Stored Procedure] を選択

3. コードの作成
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures {
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ClrSplit ( SqlString s ) {
        string[] arr = s.Value.Split ( new char[] { ' ' } );
        SqlDataRecord record = new SqlDataRecord(
            new SqlMetaData ( "word" , SqlDbType.NVarChar, 128 )
        );
        SqlContext.Pipe.SendResultsStart ( record );
        foreach ( string w in arr ) {
            record.SetValue ( 0 , w );
            SqlContext.Pipe.SendResultsRow ( record );
        }
        SqlContext.Pipe.SendResultsEnd ();
    }
};
4. [Build Solution]
5. [Deploy Solution]

Visual Studio を用いない方法

1. コード作成

以下を test.cs として保存する。
--------------------------- ここから ----------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures {
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ClrSplit ( SqlString s ) {
        string[] arr = s.Value.Split ( new char[] { ' ' } );
        SqlDataRecord record = new SqlDataRecord(
            new SqlMetaData ( "word" , SqlDbType.NVarChar, 128 )
        );
        SqlContext.Pipe.SendResultsStart ( record );
        foreach ( string w in arr ) {
            record.SetValue ( 0 , w );
            SqlContext.Pipe.SendResultsRow ( record );
        }
        SqlContext.Pipe.SendResultsEnd ();
    }
};
------------------- ここまで -----------------------
2. .NET Framework 2.0 SDK コマンドプロンプトから次を実行

    > csc /t:library test.cs

3. 2 で作成した DLL を SQL サーバー上に配置

4. アセンブリの登録

    > CREATE ASSEMBLY ClrTest2 FROM 'C:\temp\public\test.dll' WITH PERMISSION_SET=SAFE

5. プロシージャの作成

    CREATE PROCEDURE ClrSplit (
        @s nvarchar(1000) )
    AS
    EXTERNAL NAME ClrTest2.StoredProcedures.ClrSplit

関連書籍

このトピックに関するおすすめの参考資料は次です。