May 25, 2011

SQL Server CLR User Defined Function using Visual Studio 2010

In this post, we will see how to create a User Defined Function using Visual Studio 2010. In my previous article ‘SQL CLR Stored Procedure using Visual Studio 2010’, we have seen how to create a Stored Procedure using C# language with Visual studio 2010. Initial few steps are common for this article like –

1) Enabling CLR using SQL Server Management Studio.
2) Setting connection properties after creating Visual Studio project.
3) Setting security if you are implementing the functionalities like File IO operations or calling Win 32 APIs.

Please follow the steps for enabling CLR under SQL Server as specified in my previous article.

Now let’s create a ‘Visual C# SQL CLR Database Project’ with the name ‘SampleCLRUDF’ as shown below –

SQL Server CLR

Once you create the project, it will ask you for a connection. If you already have created a connection, use the same or create a new connection to the database of your choice. I already have an existing connection to the database ‘SALCLRSampleDB’ database. I will choose the same.

Now let’s add a ‘User Defined Function’ to our project with the name ‘CheckEMailUDF’ as shown below –

SQL Server UDF

Write some code which will take ‘Email’ address as parameter and will return true or false value depending upon the validation. To do so, first import a namespace ‘using System.Text.RegularExpressions;’ in our function. Now write code to validate the email address as shown below –

[Microsoft.SqlServer.Server.SqlFunction]
public static bool CheckEMailUDF(string EMailID)
{
   return Regex.IsMatch(EMailID, @"\w+([-+.']\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*");
}


Now let’s deploy the project. Right click the project and click on ‘Deploy’ menu. Once the deployment is completed, test whether the deployment is successful or not in the ‘Object Explorer’ of SQL Server Management Studio as shown below –

SSMS Object Explorer

Let’s execute the function and observe the result as shown below –

USE SQLCLRSampleDB
GO

SELECT dbo.CheckEMailUDF('dabade.pravin')
SELECT dbo.CheckEMailUDF(‘youremailaddresshere@gmail.com')

The result of both select statements is shown below  –

clip_image001


2 comments:

RDWilson2 said...

I tried to access the previous (CLR Stored Procedure) entry but it doesn't appear to ezist any more. :-/ Any chance there is a better/working link to it?

Suprotim Agarwal said...

Sorry the link was broken. It's fixed now. Thanks RDWilson2!