Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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.

UDT CLR C#

The application will want to set a reference to the database. This can be set now or click Cancel to set it later.

Database Reference

Right click in the Solution Explorer on the project and select Add, User Defined Type.

Add New UDT

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.

UDT Code

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.