User Defined Data Types using SQLCLR

There can be times when we might want to create our own data types. For instance, we want SQL Server to check the email format during its update and insert, or for any other custom validations at Database level UDTs are highly useful. UDTs are structs that are built in .NET framework. When we create structs(value types) in .NET framework we can only assign certain number of bytes and that's what makes it highly efficient. The limit is no more than 8KB.

If you are new to UDTs then I would recommend you to also go over my article on SQLCLR. UDTs can be deployed and configured in different ways; I am covering one way in this article.

In order to create your first UDT, you need to open your Visual Studio project

  1.  

  2. Under Database there is an option SQL Server Project. Give it a name for the sake of simplicity I am just naming it to default SQLServerProject2

     

     

  3. Click Ok, it will ask for Database Reference as shown below. If you want to use the existing reference then select one from the list and click OK. If you want to add new references then click on Add Reference and give the details, Test the connection and Click Ok

     

     

     

  4. Once the reference is added, right click on your project, go to Add and select User Defined Types

     

    .

     

  5. Give a name to User-Defined Type. We are naming it as EmailFormat.cs. Some auto generated code will be present. Let's go over the basics and then we will add and tweak in this code.

                 [Serializable]

[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]

 

Above Format can be Native or UserDefined. In our example below we will use Format.UserDefined. Native would mean that you are asking SQL Server to handle the Serialization without us having to worry about it and UserDefined would mean that we would do our own Serialization. If we choose to specify UserDefined then we will have to inherit IBinarySerialize interface in order to receive the values from constructor (it can be any datatype like string, int, etc…) to binary.

public static EmailFormat Parse(SqlString s)

{

if (s.IsNull)

return Null;

EmailFormat u = new EmailFormat();

// Put your code here

return u;

}

SQL Server would need to convert from string to struct, thereby a static method Parse is essential as shown above.

public bool IsNull

{

get

{

// Put your code here

return m_Null;

}

}

 

public static EmailFormat Null

{

get

{

EmailFormat h = new EmailFormat();

h.m_Null = true;

return h;

}

}

Since implementing INullable instance is required, we need IsNull method to determine if the instance is null from SQL server, and thereby, it needs to have a static property Null to return a instance of type struct if the value is null

Below will be our complete code

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

 

[Serializable]

[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined

, IsFixedLength = false

, IsByteOrdered = true

, MaxByteSize = 250

, ValidationMethodName = "EmailPatternValidator")]

public struct EmailFormat : INullable,IBinarySerialize

{

private Regex _Em;

private bool _null;

 

public EmailFormat(String s)

{

_Em =new Regex (s);

_null =(s==String.Empty);

}

 

public override string ToString()

{

return _Em.ToString();

}

 

public bool IsNull

{

get

{

if (_Em == null || _Em.ToString() == string.Empty)

{

return true;

}

else

return false;

}

}

 

public static EmailFormat Null

{

get

{

EmailFormat h = new EmailFormat();

h._null = true;

return h;

}

}

 

public static EmailFormat Parse(SqlString s)

{

if (s.IsNull)

return Null;

else

{

EmailFormat u = new EmailFormat((String) s);

return u;

}

}

 

public bool EmailPatternValidator()

{

return (_Em.ToString() != string.Empty);

}

 

public String FindMatch()

{

Match m = Regex.Match(_Em.ToString(), @"^([0-9a-zA-Z]+[-._+&//])*[0-9a-zA-Z]+@([-0-9a-zA-Z]+[.])+[a-zA-Z]{2,6}$");

if (m != null)

{

if (m.Success)

{

return "Matches Email Format"; }

else

{

return "Does Not Match Email Format";

}

}

else

return "Does Not Match Email Format";

}

 

 

#region IBinarySerialize Members

public void Read(System.IO.BinaryReader r)

{

_Em = new Regex(r.ReadString());

}

public void Write(System.IO.BinaryWriter w)

{

w.Write(_Em.ToString());

}

#endregion

}

 

 

Once the code is ready we need to create the .dll file. In my earlier article of SQLCLR I showed how this can be done from command prompt once the environment variables are set. Here I will be showing how we can do this from Visual 2008 comand prompt.

Start your Visual Studio Command Prompt as shown below:

 

When command prompt opens up, change the directory to location where your project is stored. Since, mine is in the location shown below so I am navigating to that path.

 After going to the location where your file EmailFormat.cs is located run below command and this will create the .dll file in the same location

     csc /target:library EmailFormat.cs

 

  After creation of your .dll file. Open your SQL Server Management Studio and create assembly

create assembly _AssmEmail

from 'C:\Documents\Visual Studio 2008\Projects\SqlServerProject2\SqlServerProject2\EmailFormat.dll' ß This will be the path where your .dll file is located

CREATE TYPE EmailFormat EXTERNAL NAME _AssmEmail.EmailFormat

 

DECLARE @format EmailFormat

SET @format = 'abc'

SELECT

@format.ToString() AS ToString,

@format.FindMatch() AS Match

go

 

Result:

ToString    Match

abc          Does Not Match Email Format

 

and when

DECLARE @format EmailFormat

SET @format = 'abc@gmail.com'

SELECT

@format.ToString() AS ToString,

@format.FindMatch() AS Match

Go

Result:

ToString               Match

abc@gmail.com    Matches Email Format

DECLARE @format EmailFormat

SET @format = '(abc@gmail.com'

SELECT

@format.ToString() AS ToString,

@format.FindMatch() AS Match

Go

Result:

This will return a .NET framework error describing that parsing is not right. This is because when we are passing string from SQL server, the constructor in our CLR is validating if it is a correct Regular Expression. If right regex is not found error will be returned.

 

Things to Note:

1. User Defined Types cannot use inheritance . They can only have read-only static field.

2. They can have constructors but cannot have any other overloaded methods

3. If any modifications are made to the CLR code, then dll has to be recreated and so is the Type and assembly. Assembly can however be altered but Type can only be dropped so you can execute Drop Type EmailFormat but not Alter Type EmailFormat

4. Once the type is created, .dll file is no more used.

 

 

Tags: ,

SQL Server

Comments (1) -

Harto Indonesia, on 3/3/2011 12:46:57 PM Said:

Harto

Well Thanks!But most of it has gone beyond my general understanding..
But i am hanging on to your article and hope full i will get it....
I really liked that you provided whole codes... Thankyou!!!!