How to: Create and Run a SQL Server User-Defined Type by using Common Language Run-time Integration
Create a SQL user-defined type by adding a User-Defined Type to a SQL Server Common Language Run-time (SQL CLR) database 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
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
Open an existing SQL CLR Database Project, or create a new one. For more information, see How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration.
On the Project menu, select Add New Item.
In the Add New Item dialog box, select User-Defined Type.
Type a Name for the new user-defined type.
Add code to define and create the user-defined type. See the first example that follows this procedure.
In Solution Explorer, open the TestScripts folder and double-click the Test.sql file.
Note
You can specify other scripts as your default debug script. For more information, see How to: Edit the Test.sql Script to Run Objects that use SQL Server Common Language Run-time Integration.
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.
Press F5 to build, deploy, and debug the user-defined type. For information about how to deploy without debugging, see How to: Deploy SQL Server CLR Integration Database Project Items to a SQL Server.
Important
SQL Server 2005 and SQL Server 2008 only support SQL Server projects that were built with the 2.0, 3.0, or 3.5 version of the .NET Framework. If you try to deploy a SQL Server project to SQL Server 2005 or SQL Server 2008, an error appears: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database (where AssemblyName is the name of the assembly that you are deploying). For more information, see How to: Create a Project for Database Objects that Use SQL Server Common Language Run-time Integration.
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;
}
}
Add code to execute and test your user-defined type (Point) to the Test.sql 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 and Run a SQL Server Stored Procedure by using Common Language Run-time Integration
How to: Create and Run a SQL Server Trigger by using Common Language Run-time Integration
How to: Create and Run a SQL Server Aggregate by using Common Language Run-time Integration
Walkthrough: Creating a Stored Procedure in Managed Code
How to: Debug a SQL Server CLR Integration Stored Procedure
Reference
Attributes for SQL Server CLR Integration Database Projects and Database Objects
Concepts
Introduction to SQL Server CLR Integration (ADO.NET)
Advantages of Using Managed Code to Create Database Objects
Starting Team Development of Databases that Reference SQLCLR Objects
Creating SQL Server Objects in Managed Code