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 –

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 –



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 –



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  –