사용자 정의 형식 만들기

  • 아티클

SQL Server에 설치할 수 있는 UDT(사용자 정의 형식)를 만들려면 먼저 Visual C# 또는 Visual Basic과 같은 지원되는 .NET Framework 프로그래밍 언어 중 하나에서 UDT를 만들기 위한 사양에 맞는 클래스를 만들어야 합니다. 그런 다음 클래스를 DLL(동적 연결 라이브러리)로 컴파일하여 SQL Server에 로드할 수 있습니다. 또한 Visual Studio를 사용하여 UDT를 만들고 배포할 수도 있습니다.

SQL Server에서는 CLR(공용 언어 런타임) 코드를 실행하는 기능이 기본적으로 OFF로 설정되어 있습니다. 다음 Transact-SQL 문과 같이 sp_configure 시스템 저장 프로시저를 사용하여 CLR을 사용하도록 설정할 수 있습니다.

sp_configure 'clr enabled', 1
Reconfigure

섹션 내용

예

다음 코드 목록에서는 사용자 정의 형식 코딩에 자세히 설명된 Point UDT를 정의합니다.

이 섹션에서 설명한 다른 예제의 전체 코드 목록은 CLR 예제를 설치하면 확인할 수 있습니다. 이러한 예제를 설치하는 방법은 SQL Server 데이터베이스 엔진 예제를 참조하십시오.

C#

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

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,
     IsByteOrdered=true, ValidationMethodName = "ValidatePoint")]
public struct Point : INullable
{
    private bool is_Null;
    private Int32 _x;
    private Int32 _y;

    public bool IsNull
    {
        get
        {
            return (is_Null);
        }
    }

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

    // Use StringBuilder to provide string representation of UDT.
    public override string ToString()
    {
        // Since InvokeIfReceiverIsNull defaults to 'true'
        // this test is unneccesary if Point is only being called
        // from SQL.
        if (this.IsNull)
            return "NULL";
        else
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(_x);
            builder.Append(",");
            builder.Append(_y);
            return builder.ToString();
        }
    }

    [SqlMethod(OnNullCall = false)]
    public static Point Parse(SqlString s)
    {
        // With OnNullCall=false, this check is unnecessary if 
        // Point only called from SQL.
        if (s.IsNull)
            return Null;

        // Parse input string to separate out points.
        Point pt = new Point();
        string[] xy = s.Value.Split(",".ToCharArray());
        pt.X = Int32.Parse(xy[0]);
        pt.Y = Int32.Parse(xy[1]);

        // Call ValidatePoint to enforce validation
        // for string conversions.
        if (!pt.ValidatePoint()) 
            throw new ArgumentException("Invalid XY coordinate values.");
        return pt;
    }

    // X and Y coordinates exposed as properties.
    public Int32 X
    {
        get
        {
            return this._x;
        }
        // Call ValidatePoint to ensure valid range of Point values.
        set 
        {
            Int32 temp = _x;
            _x = value;
            if (!ValidatePoint())
            {
                _x = temp;
                throw new ArgumentException("Invalid X coordinate value.");
            }
        }
    }

    public Int32 Y
    {
        get
        {
            return this._y;
        }
        set
        {
            Int32 temp = _y;
            _y = value;
            if (!ValidatePoint())
            {
                _y = temp;
                throw new ArgumentException("Invalid Y coordinate value.");
            }
        }
    }

    // Validation method to enforce valid X and Y values.
    private bool ValidatePoint()
    {
        // Allow only zero or positive integers for X and Y coordinates.
        if ((_x >= 0) && (_y >= 0))
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    // Distance from 0 to Point method.
    [SqlMethod(OnNullCall = false)]
    public Double Distance()
    {
        return DistanceFromXY(0, 0);
    }

    // Distance from Point to the specified point method.
    [SqlMethod(OnNullCall = false)]
    public Double DistanceFrom(Point pFrom)
    {
        return DistanceFromXY(pFrom.X, pFrom.Y);
    }

    // Distance from Point to the specified x and y values method.
    [SqlMethod(OnNullCall = false)]
    public Double DistanceFromXY(Int32 iX, Int32 iY)
    {
        return Math.Sqrt(Math.Pow(iX - _x, 2.0) + Math.Pow(iY - _y, 2.0));
    }
}

Visual Basic

Option Explicit On
Option Strict On

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

<Serializable(), SqlUserDefinedTypeAttribute(Format.Native, _
  IsByteOrdered:=True, _
  ValidationMethodName:="ValidatePoint")> _
  Public Structure Point
    Implements INullable

    Private is_Null As Boolean
    Private _x As Int32
    Private _y As Int32

    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 New Point
            pt.is_Null = True
            Return (pt)
        End Get
    End Property

    ' Use StringBuilder to provide string representation of UDT.
    Public Overrides Function ToString() As String
        ' Since InvokeIfReceiverIsNull defaults to 'true'
        ' this test is unneccesary if Point is only being called
        ' from SQL.
        If Me.IsNull Then
            Return "NULL"
        Else
            Dim builder As StringBuilder = New StringBuilder
            builder.Append(_x)
            builder.Append(",")
            builder.Append(_y)
            Return builder.ToString
        End If
    End Function

    <SqlMethod(OnNullCall:=False)> _
    Public Shared Function Parse(ByVal s As SqlString) As Point
        ' With OnNullCall=False, this check is unnecessary if
        ' Point only being called from SQL.
        If s.IsNull Then
            Return Null
        End If

        ' Parse input string here to separate out points.
        Dim pt As New Point()
        Dim xy() As String = s.Value.Split(",".ToCharArray())
        pt.X = Int32.Parse(xy(0))
        pt.Y = Int32.Parse(xy(1))

        ' Call ValidatePoint to enforce validation
        ' for string conversions.
        If Not pt.ValidatePoint() Then
            Throw New ArgumentException("Invalid XY coordinate values.")
        End If
        Return pt
    End Function

    ' X and Y coordinates are exposed as properties.
    Public Property X() As Int32
        Get
            Return (Me._x)
        End Get

        Set(ByVal Value As Int32)
            Dim temp As Int32 = _x
            _x = Value
            If Not ValidatePoint() Then
                _x = temp
                Throw New ArgumentException("Invalid X coordinate value.")
            End If
        End Set
    End Property

    Public Property Y() As Int32
        Get
            Return (Me._y)
        End Get

        Set(ByVal Value As Int32)
            Dim temp As Int32 = _y
            _y = Value
            If Not ValidatePoint() Then
                _y = temp
                Throw New ArgumentException("Invalid Y coordinate value.")
            End If
        End Set
    End Property

    ' Validation method to enforce valid X and Y values.
    Private Function ValidatePoint() As Boolean
        ' Allow only zero or positive integers for X and Y coordinates.
        If (_x >= 0) And (_y >= 0) Then
            Return True
        Else
            Return False
        End If
    End Function

    ' Distance from 0 to Point method.
    <SqlMethod(OnNullCall:=False)> _
  Public Function Distance() As Double
        Return DistanceFromXY(0, 0)
    End Function

    ' Distance from Point to the specified point method.
    <SqlMethod(OnNullCall:=False)> _
    Public Function DistanceFrom(ByVal pFrom As Point) As Double
        Return DistanceFromXY(pFrom.X, pFrom.Y)
    End Function

    ' Distance from Point to the specified x and y values method.
    <SqlMethod(OnNullCall:=False)> _
    Public Function DistanceFromXY(ByVal ix As Int32, ByVal iy As Int32) _
        As Double
        Return Math.Sqrt(Math.Pow(ix - _x, 2.0) + Math.Pow(iy - _y, 2.0))
    End Function
End Structure

참고 항목

개념

CLR 사용자 정의 형식