方法 : CLR の SQL Server ユーザー定義型を作成および実行する

  • [アーティクル]

更新 : 2007 年 11 月

SQL Server プロジェクトに [ユーザー定義型] を追加して、SQL ユーザー定義型を作成します。正常に配置されると、システム型を使用できるあらゆるコンテキストで、それを使用できます。たとえば、列定義、変数、パラメータ、関数の結果、カーソル、トリガ、およびレプリケーションなどです。ユーザー定義型を使用すると、SQL Server のデータ型システムの機能を拡張したり、複雑な構造型を定義したりできます。

a8s4s5dz.alert_note(ja-jp,VS.90).gifメモ :

既定では、Microsoft SQL Server では共通言語ランタイム (CLR: Common Language Runtime) 統合機能が無効になっており、SQL Server プロジェクト項目を使用するにはこの機能を有効にする必要があります。CLR 統合を有効にするには、sp_configure ストアド プロシージャの clr enabled オプションを使用します。詳細については、「CLR 統合の有効化」を参照してください。
a8s4s5dz.alert_note(ja-jp,VS.90).gifメモ :

お使いのマシンで、Visual Studio ユーザー インターフェイスの一部の要素の名前や場所が、次の手順とは異なる場合があります。これらの要素は、使用している Visual Studio のエディションや独自の設定によって決まります。詳細については、「Visual Studio の設定」を参照してください。

ユーザー定義型の作成

SQL ユーザー定義型を作成するには

  1. 既存の SQL Server プロジェクトを開くか、または新しい SQL Server プロジェクトを作成します。詳細については、「方法 : SQL Server プロジェクトを作成する」を参照してください。

  2. [プロジェクト] メニューの [新しい項目の追加] をクリックします。

  3. [新しい項目の追加] ダイアログ ボックスの [ユーザー定義型] を選択します。

  4. 新しいユーザー定義型の名前を [ファイル名] に入力します。

  5. ユーザー定義型を定義および作成するコードを追加します。下記の最初の例を参照してください。

    a8s4s5dz.alert_note(ja-jp,VS.90).gifメモ :

    C++ の例は、/clr:safe コンパイラ オプションを使ってコンパイルする必要があります。

  6. Visual Basic および Visual C# の場合は、ソリューション エクスプローラで TestScripts フォルダを開き、Test.sql ファイルをダブルクリックします。

    Visual C++ の場合は、ソリューション エクスプローラで debug.sql ファイルをダブルクリックします。

  7. ユーザー定義型を実行するコードを Test.sql (Visual C++ の場合は debug.sql) ファイルに追加します。この手順に続く 2 番目の例を参照してください。

  8. F5 キーを押して、ユーザー定義型をビルド、配置、およびデバッグします。デバッグせずに配置する方法については、「方法 : SQL Server のプロジェクト項目を SQL Server に配置する」を参照してください。

  9. [出力] ウィンドウに表示される結果を確認して、[出力元の表示: データベース出力] を選択します。

使用例

この例では、他の単純型と同じように使用できる Point 型を作成します。Serializable 属性と SqlUserDefinedTypeAttribute 属性の両方を使用して、クラス宣言を装飾します。SqlUserDefinedTypeAttribute の Format プロパティでユーザー定義型の格納形式が決まります。Parse メソッドと ToString メソッドを実装すると、この型に文字列の変換が実装されます。また、この型には、このクラスで表される点の X と Y の値を取得して設定する、2 つのプロパティ プロシージャも実装されます。

Imports System
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

<Serializable()> _
<SqlUserDefinedType(Format.Native)> _
Public Structure Point
    Implements INullable

    Private m_x As Int32
    Private m_y As Int32
    Private is_Null As Boolean


    Public Property X() As Int32
        Get
            Return (Me.m_x)
        End Get
        Set(ByVal Value As Int32)
            m_x = Value
        End Set
    End Property


    Public Property Y() As Int32
        Get
            Return (Me.m_y)
        End Get
        Set(ByVal Value As Int32)
            m_y = Value
        End Set
    End Property


    Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull
        Get
            Return is_Null
        End Get
    End Property


    Public Shared ReadOnly Property Null() As Point
        Get
            Dim pt As Point = New Point
            pt.is_Null = True
            Return pt
        End Get
    End Property


    Public Overrides Function ToString() As String
        If Me.IsNull() Then
            Return Nothing
        Else
            Return Me.m_x & ":" & Me.m_y
        End If
    End Function


    Public Shared Function Parse(ByVal s As SqlString) As Point
        If s = SqlString.Null Then
            Return Null
        End If

        If s.ToString() = SqlString.Null.ToString() Then
            Return Null
        End If

        If s.IsNull Then
            Return Null
        End If

        'Parse input string here to separate out coordinates
        Dim str As String = Convert.ToString(s)
        Dim xy() As String = str.Split(":"c)

        Dim pt As New Point()
        pt.X = CType(xy(0), Int32)
        pt.Y = CType(xy(1), Int32)
        Return (pt)
    End Function


    Public Function Quadrant() As SqlString

        If m_x = 0 And m_y = 0 Then
            Return "centered"
        End If

        Dim stringResult As String = ""

        Select Case m_x
            Case 0
                stringResult = "center"
            Case Is > 0
                stringResult = "right"
            Case Is < 0
                stringResult = "left"
        End Select

        Select Case m_y
            Case 0
                stringResult = stringResult & " center"
            Case Is > 0
                stringResult = stringResult & " top"
            Case Is < 0
                stringResult = stringResult & " bottom"
        End Select

        Return stringResult
    End Function
End Structure

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable()]
[SqlUserDefinedType(Format.Native)]
public struct Point : INullable
{
    private Int32 m_x;
    private Int32 m_y;
    private bool is_Null;


    public Int32 X
    {
        get
        {
            return (this.m_x);
        }
        set
        {
            m_x = value;
        }
    }


    public Int32 Y
    {
        get
        {
            return (this.m_y);
        }
        set
        {
            m_y = value;
        }
    }


    public bool IsNull
    {
        get
        {
            return is_Null;
        }
    }


    public static Point Null
    {
        get
        {
            Point pt = new Point();
            pt.is_Null = true;
            return (pt);
        }
    }


    public override string ToString()
    {
        if (this.IsNull)
        {
            return "NULL";
        }
        else
        {
            return this.m_x + ":" + this.m_y;
        }
    }


    public static Point Parse(SqlString s)
    {
        if (s.IsNull)
        {
            return Null;
        }

        // Parse input string here to separate out coordinates
        string str = Convert.ToString(s);
        string[] xy = str.Split(':');

        Point pt = new Point();
        pt.X = Convert.ToInt32(xy[0]);
        pt.Y = Convert.ToInt32(xy[1]);
        return (pt);
    }


    public SqlString Quadrant()
    {
        if (m_x == 0 && m_y == 0)
        {
            return "centered";
        } 

        SqlString stringReturn = "";

        if (m_x == 0)
        {
            stringReturn = "center";
        }
        else if (m_x > 0)
        {
            stringReturn = "right";
        } 
        else if (m_x < 0)
        {
            stringReturn = "left";
        }

        if (m_y == 0) 
        {
            stringReturn = stringReturn + " center";
        }
        else if (m_y > 0)
        {
            stringReturn = stringReturn + " top";
        }
        else if (m_y < 0)
        {
            stringReturn = stringReturn + " bottom";
        }

        return stringReturn;
    }
}

#include "stdafx.h"

#using <System.dll>
#using <System.Data.dll>
#using <System.Xml.dll>

using namespace System;
using namespace System::Data;
using namespace System::Data::Sql;
using namespace System::Data::SqlTypes;
using namespace Microsoft::SqlServer::Server;

// In order to debug your User-Defined Types, add the following to your debug.sql file:
//
// CREATE TABLE test_table (column1 Point)
//
// INSERT INTO test_table (column1) VALUES ('1:2')
// INSERT INTO test_table (column1) VALUES ('-2:3')
// INSERT INTO test_table (column1) VALUES ('-3:-4')
//
// SELECT column1.Quadrant() FROM test_table
//
// DROP TABLE test_table
//
[Serializable]
[SqlUserDefinedType(Format::Native)]
public value struct Point : public INullable
{
private:
    Int32 m_x;
    Int32 m_y;
    bool is_Null;

public:
    property Int32 X
    {
        Int32 get() { return (this->m_x); }
        void set(Int32 value) { m_x = value; }
    }

    property Int32 Y
    {
        Int32 get() { return (this->m_y); }
        void set(Int32 value) { m_y = value; }
    }

    virtual property bool IsNull
    {
        bool get() { return is_Null; }
    }

    static property Point Null
    {
        Point get()
        {
            Point pt;
            pt.is_Null = true;
            return (pt);
        }
    }

    virtual String ^ToString() override
    {
        if (this->IsNull)
        {
            return "NULL";
        }
        else
        {
            return this->m_x + ":" + this->m_y;
        }
    }


    static Point Parse(SqlString s)
    {
        if (s.IsNull)
        {
            return Null;
        }

        // Parse input string here to separate out coordinates
        String ^str = Convert::ToString(s);
        array<String ^> ^xy = str->Split(':');

        Point pt;
        pt.X = Convert::ToInt32(xy[0]);
        pt.Y = Convert::ToInt32(xy[1]);
        return (pt);
    }


    SqlString Quadrant()
    {
        if (m_x == 0 && m_y == 0)
        {
            return "centered";
        }

        SqlString stringReturn = "";

        if (m_x == 0)
        {
            stringReturn = "center";
        }
        else if (m_x > 0)
        {
            stringReturn = "right";
        }
        else if (m_x < 0)
        {
            stringReturn = "left";
        }

        if (m_y == 0)
        {
            stringReturn = stringReturn + SqlString(" center");
        }
        else if (m_y > 0)
        {
            stringReturn = stringReturn + SqlString(" top");
        }
        else if (m_y < 0)
        {
            stringReturn = stringReturn + SqlString(" bottom");
        }

        return stringReturn;
    }
};

ユーザー定義型 (Point) の実行とテストを行うコードを、プロジェクトの TestScripts フォルダにある Test.sql (Visual C++ の場合は debug.sql) ファイルに追加します。たとえば、新しい型を検証するには、この型を使用するテーブルを作成します。テーブルの作成に Point 型を使用する方法を次の例に示します。

CREATE TABLE test_table (column1 Point)
go

INSERT INTO test_table (column1) VALUES ('1:2')
INSERT INTO test_table (column1) VALUES ('-2:3')
INSERT INTO test_table (column1) VALUES ('-3:-4')

select column1.Quadrant() from test_table

参照

処理手順

方法 : SQL Server プロジェクトを作成する

方法 : CLR の SQL Server ストアド プロシージャを作成および実行する

方法 : CLR の SQL Server トリガを作成および実行する

方法 : CLR の SQL Server の集計を作成および実行する

方法 : CLR の SQL Server ユーザー定義関数を作成および実行する

方法 : CLR の SQL Server ユーザー定義型を作成および実行する

チュートリアル : マネージ コードでのストアド プロシージャの作成

方法 : SQL CLR のストアド プロシージャをデバッグする

概念

SQL Server の CLR 統合の概要 (ADO.NET)

マネージ コードを使用したデータベース オブジェクトの作成の利点

SQL Server プロジェクト用の項目テンプレート

参照

SQL Server プロジェクトおよびデータベース オブジェクトの属性

その他の技術情報

SQL CLR データベースのデバッグ