May 25, 2011

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 –

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 –

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 –