During my Pocket PC project development, I needed to convert Gregorian date to Persian date. It’s easy to do such a thing in Windows or Web applications using System.Globalization namespace (PersianCalendar class) but this feature is not available when developing a Pocket PC application. I’ve tried a lot of things such as web services, windows services, etc but none of them is as good as converting it in your database and fortunately my database is Microsoft SQL Server 2005. So I decided to use CLR integration and create an UDF (User-Defined Function) in order to do so. In this post I wanted to show you how you can use CLR to create a function in .NET environment and execute it in SQL Server 2005-2008 environments.
The first thing you need is to create a SQL Server project which is available in Database node in new Project window:
Note that I used .NET Framework 2 because I wanted to create a CLR function in SQL Server 2005. If you’re using SQL Server 2008, there’s no problem to use .NET Framework 3.5 (SP1).
After you create a SQL Server project you can add any object that is common in SQL Server to your project by right click on the project and point to add; so you add a UDF, SP, etc:
As I mentioned before, we want to create an application which converts DateTime to another culture so I need to create a UDF. I select User-Defined Function and name it “PersianDateConvertor”. As you can see, Visual Studio will create a class named UserDefinedFunctions and add a method with the name you just specified:
using System;using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString PersianDateConvertor()
{
}
};
Now you need to convert a DateTime to a Persian DateTime like before using System.Globalization.PersianCalendar class. So our class will be something like this:
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString PersianDateConvertor(DateTime dt)
{
PersianCalendar p = new PersianCalendar();
return String.Format("{0}/{1}/{2}",
p.GetYear(dt).ToString(),
p.GetMonth(dt).ToString(),
p.GetDayOfMonth(dt).ToString());
}
By the way, As you can see our method is a static method and it returns a SqlString. You can change the return type if you want.
You’ve created you CLR library! Now it’s time to use it in Microsoft SQL Server. By default, you cannot use CLR libraries in SQL Server unless you enable it. In order to enable CLR integration, you need to make use of sp_configure system stored-procedure:
EXECUTE sp_configure 'clr enabled' , '1'
Note: In order to enable CLR, you need administrative privileges.
In order to make use of an external assembly in SQL Server, you have to create an assembly like following:
CREATE ASSEMBLY DateConvertorFROM 'D:\MMR.CRL.DateConvertor.dll'WITH PERMISSION_SET = SAFE;GO
After you’ve created your assembly, you need to use it in an UDF, SP, etc:
CREATE FUNCTION dbo.GetPersianDate(@date datetime)
RETURNS nvarchar(max)AS EXTERNAL NAME DateConvertor.UserDefinedFunctions.PersianDateConvertor;
GO
You’re DONE! now you can use your UDF and the result will be something like this:
SELECT dbo.GetPersianDate(GetDate())
Result:
-------------------------------------------------------------
1387/12/2
(1 row(s) affected)