More than 5 years have passed since last update.

SQL CLR を使ってみた

Posted at 2014-10-28

SQL CLRとは

C#などの.NET言語を使って、ストアドプロシージャや、ユーザー定義関数を使えるようにしたもの
SQL Server 2005 から使えるようになった機能で、CLR統合機能と呼ばれています

詳細は、以下参照
SQL Server の共通言語ランタイム統合

準備

SQL Server側

CLR統合機能はデフォルトではOFFとなっているので、使う場合にはONにする必要があります
以下の処理で有効にすることが可能です

t-sql

EXEC sp_configure 'clr enabled';        --現在の設定値表示
EXEC sp_configure 'clr enabled' , '1';  --設定の変更
RECONFIGURE;
EXEC sp_configure 'clr enabled';        --変更後の設定値表示

サーバーに対する設定となります
DB単位には設定できないようです
また、場合によっては、再起動しないと反映されないようです

clr enabled サーバー構成オプション

WOW64 サーバーの場合、この設定に対する変更を有効にするには再起動が必要です。他の種類のサーバーでは、再起動は不要です。

プログラム側

SQL Serverプロジェクトを作成し、そこに、ユーザ定義関数などを追加していくようになります
1. 新しいソリューションを作成
2. ソリューションを右クリックし、「追加」→「新規のプロジェクト」から「SQL Server データベースプロジェクト」を選択
3. 追加したプロジェクトを右クリックし、「追加」→「新しい項目」から「SQL CLR C#」を選択
4. SQL CLR C#ユーザ定義関数を選択し、作成

以下のようなコードが生成されます
ここを編集することで、C#による処理の記述が可能となります

//------------------------------------------------------------------------------
// <copyright file="CSSqlFunction.cs" company="Microsoft">
//     Copyright (c) Microsoft Corporation.  All rights reserved.
// </copyright>
//------------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString SqlFunction2()
    {
        // コードをここに記述してください
        return new SqlString (string.Empty);
    }
}

プロジェクトのプロパティ

プロジェクトに対して、接続先のDBなど各種設定を行います
プロジェクトを右クリックし、プロパティ表示

プロジェクトの設定

ターゲットプラットフォーム

対象とするSQL Serverのバージョンを指定してください

全般の「データベースの設定」

照合順序などDBに関する設定箇所があるので必要に応じて設定してください
照合順序が一致していない場合、配置時に警告（照合順序がずれているけど大丈夫ですか？）が出ることがあるので注意

SQLCLR

ターゲットフレームワーク

CLR オブジェクトを開発するときに、.NET Framework のバージョンと SQL Server のバージョンの組み合わせに注意が必要です

SQL Server2008 R2を使用する場合は、.NET Framework 3.5 を選択してください
SQL Server2008 R2に.NET Framework 4.0でビルドしたものは展開できないので注意が必要です

CLR オブジェクト開発時の注意点

権限のレベル

アセンブリの権限を設定します（SAFEがデフォルトです）

SAFE：内部コンピューター処理とローカルデータのアクセスだけが許可。最も制限が厳しい権限セット
EXTERNAL_ACCESS：SAFE アセンブリの権限に、ファイル、ネットワーク、環境変数、レジストリなどの外部システムリソースにアクセスできる機能を加えたもの
UNSAFE：アセンブリから SQL Server の内外のリソースにも無制限にアクセス

CLR 統合のコードアクセスセキュリティ

デバッグ（プロジェクトのプロパティ）

ターゲット接続文字列

ここに接続するDBへの接続文字列を設定する

配置オプションの「詳細設定」

配置に関する各種設定が可能

ユーザー定義関数の作成

スカラー値関数

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString SqlFunction2()
{
    return new SqlString ("test");
}

SqlFunction属性をつけたメソッドを作成する
戻り値を、1つにする(System.Data.SqlTypes 名前空間の型とか）

引数が必要な場合は、メソッドに引数を追加するだけでよいです

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString SqlFunction2(SqlInt32 id)
{
    // コードをここに記述してください
    if(id == 10)
        return new SqlString ("10dayo");
    else
        return new SqlString ("10jyanai");
}

この例だと、SQL CLRにするメリット何もないですが、.NET側の処理を使うことで、T-SQLだけでは難しかったことができるようになります
ただ、別途記述しますが、SQL CLRとしてデフォルトで使用可能なライブラリ以外は自分でライブラリを登録する必要があります

テーブル値関数

戻り値をIEnumerableにします

[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "GetUser_FillRow"
    , TableDefinition = "UserId nvarchar(20), Password nvarchar(20), AddTime datetime"
    , DataAccess = DataAccessKind.Read)]
public static IEnumerable GetUser()
{
    var userList = new List<UserList>();

    // アセンブリが配置されているDBへの接続を取得する
    // データを読み取る場合、SqlFunctionの設定で、DataAccess = DataAccessKind.Read が必要となります
    using (SqlConnection connection = new SqlConnection("context connection=true"))
    {
        connection.Open();

        using (SqlCommand selectUsers = new SqlCommand("SELECT UserId, Password, AddTime FROM [users] WITH(NOLOCK) ", connection))
        {
            using (SqlDataReader usersReader = selectUsers.ExecuteReader())
            {
                while (usersReader.Read())
                {
                    userList.Add(new UserList
                    {
                        UserId = usersReader.GetSqlInt32(0),
                        Password = Decrypt(usersReader.GetSqlString(1)),
                        AddTime = usersReader.GetSqlDateTime(2),
                    });
                }
            }
        }
    }

    return userList;
}

/// <summary>
/// TVF コントラクトによって使用されるメソッド
/// データの読み出し時に使われている
/// SqlFunctionで指定したTableDefinitionと型の順序と引数の順序は一致するようにする（名前は見てないようですが、順番に引数をマッピングしているようです）
/// </summary>
public static void GetUser_FillRow(object row, out SqlString userId, out SqlString password, out SqlDateTime addtime)
{
    var userList = (UserList)row;
    userId = userList.UserId;
    password = userList.Password;
    addtime = userList.AddTime;
}

private static string Decrypt(SqlString encryptStr)
{
    var decryptStr = string.Empty;

    // 復号化のための処理
    // ・
    // ・
    // ・

    return decryptStr;
}

public class UserList
{
    /// <summary>
    /// ユーザーID
    /// </summary>
    public SqlString UserId;
    /// <summary>
    /// パスワード
    /// </summary>
    public SqlString Password;
    /// <summary>
    /// 登録日
    /// </summary>
    public SqlDateTime AddTime;
}

SqlFunctionAttribute クラス

ユーザー定義関数に設定する属性ですが、プロパティが複数あります
今回使ったプロパティの説明を少し記載します

DataAccess：その関数が、SQL Server のローカルインスタンスに格納されたユーザーデータにアクセスするかどうかを示します。規定ではDataAccessKind.None。DataAccessKind.Read の場合、ユーザーデータだけを読み取ります。
FillRowMethodName：TVF コントラクトによって使用されるメソッドの名前を表す String 値。ここの名前に一致する関数を作成する必要があります。関数としては、オブジェクトの値を各フィールド値にマッピングさせるような処理となります。TableDefinition で指定したフィールド情報と一致するようにする
TableDefinition：メソッドをテーブル値関数 (TVF) として使用する場合、結果のテーブル定義を表す文字列。

配置

作成したユーザー定義関数をSQL Serverに配置し使えるようにします

以下の作業を行うことで使用可能となります
1. SQL Serverプロジェクトのアセンブリを、SQL Serverに登録する
2. 「1」のアセンブリを呼び出す関数を定義する（直接アセンブリの処理を呼び出せるわけではないため）

簡単に記載しておりますが、VisualStudioを使えばこのあたりを自動的に対応可能です

SQL Serverプロジェクト、デフォルトで「構成マネージャー」の「配置」にチェックが入っています
この状態で、SQL Serverプロジェクトをスタートアッププロジェクトにして、実行(F5)にすると「デバッグ」の箇所で記載した接続先に対して上記の処理を行い配置してくれます
問題なければこれで配置が実施できます
ビルド時に配置される事が問題ある場合「配置」のチェックをOFFにしてください

ただ、スカラー値関数の箇所でも記述しましたが、
SQL ServerプロジェクトがSQL CLRとしてデフォルトで使用可能なライブラリ以外を参照している場合、そのライブラリを事前にSQL Serverへ登録が必要となる点です

サポートされている .NET Framework ライブラリ
SQL Server の CLR 統合でサポートされるライブラリは以下のものとなります
CustomMarshalers
Microsoft.VisualBasic
Microsoft.VisualC
mscorlib
System
System.Configuration
System.Data
System.Data.OracleClient
System.Data.SqlXml
System.Deployment
System.Security
System.Transactions
System.Web.Services
System.Xml
System.Core.dll
System.Xml.Linq.dll

このライブラリ以外が必要な場合（SQL Serverプロジェクトが参照した他のプロジェクトなど）、自分で登録する必要があります
SQL Serverプロジェクトに対して、参照設定は簡単に追加できたので、参照先の処理を使ってユーザ定義関数を作成していましたが、いざ配置しようとすると、参照しているアセンブリ(ライブラリ)がないという内容のエラーが出てしまいました
そのプロジェクトが参照しているものをあわせて配置はしてくれないようです（オプションとかで制御できるかもしれませんが）

アセンブリを自分で登録するための手順は以下を参照願います
方法: 参照アセンブリの SQL CLR データベースプロジェクト項目を配置する

また、配置に関しては以下の情報を参照して頂ければと思います
チュートリアル: SQL CLR オブジェクトを使用するデータベースプロジェクトを作成して配置する
 マネージコードでの SQL Server オブジェクトの作成

開発環境からアクセスできない環境への配置

VisualStudioから参照できない場所にあるSQL Serverに配置することも多くあると思います
いろんな方法があると思いますが、実際にやってみた方法を記載いたします
（ちゃんとしたやり方がわかってないだけなので無理やりな手順なので、おすすめはできません
　実際に対応される場合はMSDNも参考にして対応をお願いいたします）

SQL Serverプロジェクトのプロパティから「プロジェクトの設定」タブを開く
出力の種類の箇所で「スクリプトを生成する」にチェックを付ける
構成マネージャーを開き、SQL Serverプロジェクトの「配置」のチェックをオフ
SQL Serverプロジェクトのビルド
ビルド結果の箇所に「プロジェクト名_Create.sql」というファイルが存在するので、その中から、アセンブリ登録処理 FUNCTION登録処理のSQLのみ抜き出すこのSQLを配置したいサーバーで実行する


--アセンブリの登録
GO
PRINT N'[ProjectName] を作成しています...';

GO
CREATE ASSEMBLY [ProjectName]
    AUTHORIZATION [dbo]
    FROM 0xD5A9000300004・・・・A000FF0XXXXXX0000000000000000000;

GO
ALTER ASSEMBLY [Adn.SqlClr]
    DROP FILE ALL
    ADD FILE FROM 0x4972F7F6420・・・・A1A44530000000000XXXXXX0000 AS N'ProjectName.pdb';

--登録したアセンブリを呼び出す関数の登録
GO
PRINT N'[dbo].[GetUser] を作成しています...';

GO
CREATE FUNCTION [dbo].[GetUser]
RETURNS 
     TABLE (
        [UserId]            NVARCHAR (20)  NULL,
        [Password]          NVARCHAR (20)  NULL,
        [addtime]           DATETIME       NULL)
AS
 EXTERNAL NAME [ProjectName].[UserDefinedFunctions].[GetUser]

使ってみた感想

暗号化、正規表現など、ストアドで処理書くのが難しい部分を.NET側で処理が実装できるのは結構助かる場面もありそう
ただ、配置に関しては結構手間が多いと感じた（プロジェクトが参照しているライブラリを自分で登録しないといけないところとか）
やるにあたって、MSDNと、リリース当初の情報ぐらいしかなく、そんなに流行ってなさそうな印象を受けた
メモリ、CPUなどのリソースがどの程度消費されるかわかってないので実際の環境ガンガン使うにはもう少し使ってみないとなんとも

今回は、ユーザ定義関数にしか使わなかったので、その記述しかありませんが、他に
ストアドプロシージャ
トリガー
なども作成出来るので、興味がある方は試してみて頂ければと思います

その他の参考サイト

SQL Server の CLR 統合の概要
 SQL Server CLR Integration

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up