SQLCLR - Create CLR User-Defined Function ( UDF ) - Check Constraint on EmailAddress Column Using RegEx
by David Hayden ( Florida .NET Developer )
CLR Integration is an awesome new feature in SQL Server 2005. In those instances where you have heavy computational, procedural, or recurive functionality that is either difficult, impossible, or not very performant in T-SQL, one has the opportunity to create CLR Database Objects in SQL Server 2005 that can leverage some of the libraries in the .NET Framework.
Regular Expressions
One of the better examples I have found of leveraging the .NET Framework in SQL Server 2005 is for validating data using regular expressions. Using RegEx to validate that a string is a valid email address is child's play, but it is no small task in T-SQL.
Storing customer, subscriber, and user email addresses in a table is a very common task. In most cases, one wants to make sure that the email address inserted into the table is valid. If it is invalid, an error should occur not allowing the email address to be entered into the table.
Ideally, we want a check constraint on the table that says an email address must be valid. The check constraint can be a user defined function that checks the validity of the email address and returns true if it is valid and false if it is not valid. If the user defined function returns false, the invalid email address should not be entered into the table.
Enable CLR in SQL Server 2005
Before you can use the CLR in SQL Server 2005, you need to enable it. Here are the commands:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
Create Database Project
Visual Studio 2005 has a new Database Project, which we will use to create the CLR User Defined Function. Once you create the project you will need to associate it with a database and then add a new item, User-Defined Function.
Create CLR User-Defined Function
Add the RegEx Code to the User-Defined Function and deploy it to your database. Note that your UDF needs to be within a Public Class, have a Public Method, and the method must be Static. Notice my emailRegex variable is also Static and ReadOnly. These are the rules :)
public partial class UserDefinedFunctions
{
static readonly string emailRegex =
@"^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]"
+ @"{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))"
+ @"([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$";
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlBoolean
ValidateEmailAddress(SqlString emailAddress)
{
Regex regex = new Regex(emailRegex);
return regex.IsMatch(emailAddress.Value);
}
};
Create Table and Check Constraint
Let's add the subscribers table and add a check constraint to the table that calls the CLR UDF to validate the email address of the subscriber being added to the table.
Verify CLR UDF and Table
Everything should be fine, but you can use SQL Server Management Studio to verify both your table and CLR User-Defined Function, ValidateEmailAddress, are indeed in your database.
Conclusion
If used correctly, CLR Integration in SQL Server 2005 is a wonderful new feature for developers. You can leverage the best of the .NET Framework when T-SQL needs a little helping hand.
Just to reiterate, the general rule is that you ownly use SQLCLR when T-SQL cannot do what you need, provides extremely poor performance, or the T-SQL is extremely complex and a nightmare to maintain. CLR Integration is a perfect solution for replacing your extended stored procedures, for example.
Enjoy!
Source: David Hayden ( Florida .NET Developer )
SQL Server Tutorials