SQL User Defined Types CLR
Using SQL CLR User Defined Types
This article describes using C# to build a User Defined Type for use on SQL Server. It will also demonstrate how the User Defined Type can be enhanced by using CLR.
Getting Started with SQL User Defined CLR Type
This data type will be used to hold Fahrenheit temperatures of earths weather between 140 and -140 degrees.
To begin, open Visual Studio, select Visual C# and select Database Project and Save.
The application will want to set a reference to the database. This can be set now or click Cancel to set it later.
Right click in the Solution Explorer on the project and select Add, User Defined Type.
Code Behind the CLR User Defined Type
Visual Studio will autogenerate code to modify for the UDT. Note the implementation of the INullable interface (implement the IsNull property for the struct) and Format.Native for the SqlUserDefinedType. This means using only blittable types (such as Byte, Int16, Int32, Int64, single, double, IntPtr) - no conversion necessary for these types.
Start by adding private members to the UDT.
private double _myex;
private bool m_Null;
Create 2 constructors: one for a Null condition and one to accept the value.
public MyNum(bool isnull)
{
m_Null = isnull;
_myex = 0;
}
public MyNum(double s)
{
_myex = s;
m_Null = false;
}
Modify the overriden ToString function to return the private variable member as a string, in this case _myex.
public override string ToString()
{
return m_Null ? null : _myex.ToString();
}
Modify the code for the IsNull and Null properties.
public bool IsNull
//Returns the value of m_Null, the null state of the UDT.
{
get
{
return m_Null;
}
}
public static MyNum Null
{
//Returns a null instance of the UDT
get
{
MyNum h = new MyNum();
h.m_Null = true;
return h;
}
}
Add code to the Parse function which takes a SqlString argument
public static MyNum Parse(SqlString s)
{
string value = s.Value.ToString();
//Check for null value
if (s.IsNull || s == "")
return Null;
//check to see if non-numeric data is entered
double Num;
bool isNum = double.TryParse(value, out Num);
if (!isNum)
{
throw new InvalidOperationException("Non numeric characters are not permitted!");
}
//verify the temp is within the range.
double d = double.Parse(value);
if ( d > 140 || d < -140)
throw new InvalidOperationException("Temperature outside of range.");
MyNum u = new MyNum(double.Parse(value));
return u;
}
Build the solution. Now the assembly and type can be referenced in SQL Server.
Creating and Using the SQL CLR User Defined Type in SSMS
Create the assembly by opening up SSMS and picking a database.
CREATE ASSEMBLY MyNum
FROM
'C:\Dev\SqlUDT\SqlUDT\bin\Debug\SqlUDT.dll'
GO
Create the UDT in the database
CREATE TYPE myTemp
EXTERNAL NAME MyNum.MyNum
GO
Test the code.
declare @s dbo.myTemp
set @s = '99.9'
print @s.ToString()
Test with invalid data
declare @s dbo.myTemp
set @s = '99.9^y@u'
print @s.ToString()
Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "myTemp":
System.InvalidOperationException: Non numeric characters are not permitted!
System.InvalidOperationException:
at MyNum.Parse(SqlString s)
Test with a null value. Nothing happens; a null value is returned.
declare @s dbo.myTemp
set @s = null
print @s.ToString()
Test with values outside of the range of 140 and -140.
declare @s dbo.myTemp
set @s = '188'
print @s.ToString()
Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "myTemp":
System.InvalidOperationException: Temperature outside of range.
System.InvalidOperationException:
at MyNum.Parse(SqlString s)
Methods can also be added to the C# code to return something like a conversion of the temperature provided into Celsius.
public double convertToCelsius
{
get
{
if (this._myex > 140 || this._myex < -140)
throw new InvalidOperationException("Temperature outside of range.");
double _myF= (_myex - 32) * 5 / 9;
return _myF;
}
}
Test the results in SSMS.
declare @s dbo.myTemp
set @s = convert(varchar(4),88)
print @s.ToString()
print @s.convertToCelsius
Results
88
31.1111
This article used a variable of type Double for the UDT. A non-blittable type of String is used in the next article, Using SQL CLR User Defined Types w/String.