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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter



 
  Feedback:

comments

2 Responses to "SQL Server CLR User Defined Function using Visual Studio 2010"
  1. RDWilson2 said...
    July 19, 2011 at 11:24 AM

    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?

  2. Suprotim Agarwal said...
    July 19, 2011 at 6:04 PM

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

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions