Share via


How to: Create and Run a CLR SQL Server User-Defined Type

Create SQL user-defined type by adding a User-Defined Type to a SQL Server project. After successful deployment, you can use it in all the contexts that you can use a system type. This includes column definitions, variables, parameters, function results, cursors, triggers, and replication. User-defined types provide user extensibility of the SQL Server data type system, and also the ability to define complex structured types.

Note

By default, the common language runtime (CLR) integration feature is off in Microsoft SQL Server and must be enabled in order to use SQL Server project items. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure. For more information, see Enabling CLR Integration.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.

Creating a User-Defined Type

To create a SQL User-defined Type

  1. Open an existing SQL Server Project, or create a new one. For more information, see How to: Create a SQL Server Project.

  2. From the Project menu, select Add New Item.

  3. Select User-Defined Type in the Add New Item Dialog Box.

  4. Type a Name for the new user-defined type.

  5. Add code to define and create the user-defined type. See the first example that follows this procedure.

    Note

    C++ examples must be compiled with the /clr:safe compiler option.

  6. For Visual Basic and Visual C#, in Solution Explorer, open the TestScripts folder and double-click the Test.sql file.

    For Visual C++, in Solution Explorer, double-click the debug.sql file.

  7. Add code to the Test.sql (debug.sql in Visual C+) file to execute the user-defined type. See the second example that follows this procedure.

  8. Press F5 to build, deploy, and debug the user-defined type. For information about deploying without debugging, see How to: Deploy SQL Server Project Items to a SQL Server.

  9. View the results that are shown in the Output Window and select Show output from: Database Output.

Example

This example creates a type Point that you can use as you would with other simple types. The class declaration is decorated with both the Serializable and the SqlUserDefinedTypeAttribute attributes. The Format property of SqlUserDefinedTypeAttribute determines the storage format of the user-defined type. The type implements string conversion by implementing the Parse and ToString methods. The type also implements two property procedures to get and set the values of X and Y for the point represented by this class.

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;
    }
};

Add code to execute and test your user-defined type (Point) to the Test.sql (debug.sql in Visual C+) file in the TestScripts folder in your project. For example, to check the new type, create a table that uses this type. The following example demonstrates how to use the Point type in table creation.

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

See Also

Tasks

How to: Create a SQL Server Project

How to: Create and Run a CLR SQL Server Stored Procedure

How to: Create and Run a CLR SQL Server Trigger

How to: Create and Run a CLR SQL Server Aggregate

How to: Create and Run a CLR SQL Server User-Defined Function

How to: Create and Run a CLR SQL Server User-Defined Type

Walkthrough: Creating a Stored Procedure in Managed Code

How to: Debug a SQL CLR Stored Procedure

Concepts

Introduction to SQL Server CLR Integration (ADO.NET)

Advantages of Using Managed Code to Create Database Objects

Item Templates for SQL Server Projects

Reference

Attributes for SQL Server Projects and Database Objects

Other Resources

SQL CLR Database Debugging