2 out of 8 rated this helpful - Rate this topic

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

.NET Framework 2.0

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, including column definitions, variables, parameters, function results, cursors, triggers, and replication. UDTs provide user extensibility of the SQL Server data type system, and also the ability to define complex structured types.

Note
The common language runtime (CLR) integration feature is off by default 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. From more information, see Enabling CLR Integration.

Note
The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

Creating a User-Defined Type

To create a SQL User-defined Type

Open an existing SQL Server Project, or create a new one. For more information, see How to: Create a SQL Server Project.
From the Project menu, select Add New Item.
Select User-Defined Type in the Add New Item Dialog Box.
Type a Name for the new UDT.
Add code to define and create the UDT. See the first example below.

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

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.
Add code to the Test.sql (debug.sql in Visual C++) file to execute the UDT. See the second example below.
Press F5 to build, deploy, and debug the UDT. For information on deploying without debugging, see How to: Deploy SQL Server Project Items to a SQL Server.
View the results 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 UDT. 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.

C++

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

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

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

Creating a User-Defined Type

To create a SQL User-defined Type

Example

See Also

Tasks

Reference

Concepts

Other Resources

Community Additions