Download source - 6.45 KB
Introduction
One of the most exciting new features of Microsoft SQL Server 2005 is its ability to host .NET Common Language Runtime (CLR). This feature was not, designed merely to provide an alternative to Transact SQL (TSQL). In any development project, it is important to use the right tool for the right job. If you want to create a Stored Procedure that performs standard operations on relational data, then, without doubt, TSQL is the platform to choose. Since TSQL is designed solely for the purpose of manipulating relational data, it is superb at that job. However, there are many tasks which fall outside of the realm of relational data. It is for these tasks that CLR code might be a wise choice.
Such a task might include writing a date converter function to support the Persian Date inside Microsoft SQL Server. Due to the lack of support of the Persian language collation; SQL Server does not support Persian Date natively. Thankfully .NET framework 2.0 and later support PersianCalendar in the System.Globalization namespace. Now, with the facility to embed CLR functions in Microsoft SQL Server, we can write a function to convert any DateTime format into the Persian one. In this article, I will show how easy it is to create a Persian date converter in C# and then how to embed it into the Microsoft SQL Server, and finally how to use it as a function inside the SQL Server environment.
Steps
First of all, we need to create a SQL Server project inside Visual studio.
Then, right-click on the created project PersianSQLFunctions in Visual Studio to add a user-defined function to the project.
Next, we will see a partial class with the name UserDefinedFunctions has been created which includes a Hello SQL function. The schema of the functions which we will create inside this class is the same as this simple function which returns a “Hello” string when we call it in SQL Server.
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 Function1()
{
return new SqlString("Hello");
}
};
Our project needs two functions, which passing a DateTime object as an argument, they returning the Persian Date, and Persian DateTime in the form of SqlString object
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ToPersianDateTime(DateTime dt)
{
return new SqlString("");
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ToPersianDate(DateTime dt)
{
return new SqlString("");
}
PersianCalendar class has numerous methods to extract the date parts form a DateTIme object, such as GetYear, GetMonth, and so on.
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ToPersianDateTime(DateTime dt)
{
string result = "";
if (dt != null)
{
PersianCalendar objPersianCalendar = new PersianCalendar();
int year = objPersianCalendar.GetYear(dt);
int month = objPersianCalendar.GetMonth(dt);
int day = objPersianCalendar.GetDayOfMonth(dt);
int hour = objPersianCalendar.GetHour(dt);
int min = objPersianCalendar.GetMinute(dt);
int sec = objPersianCalendar.GetSecond(dt);
result = year.ToString().PadLeft(4, '0') + "/" +
month.ToString().PadLeft(2, '0') + "/" +
day.ToString().PadLeft(2, '0') + " " +
hour.ToString().PadLeft(2, '0') + ":" +
min.ToString().PadLeft(2, '0') + ":" + sec.ToString().PadLeft(2, '0');
}
return new SqlString(result);
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ToPersianDate(DateTime dt)
{
string result = "";
if (dt != null)
{
PersianCalendar objPersianCalendar = new PersianCalendar();
int year = objPersianCalendar.GetYear(dt);
int month = objPersianCalendar.GetMonth(dt);
int day = objPersianCalendar.GetDayOfMonth(dt);
result = year.ToString().PadLeft(4, '0') + "/" +
month.ToString().PadLeft(2, '0') + "/" +
day.ToString().PadLeft(2, '0');
}
return new SqlString(result);
}
Here is the complete code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ToPersianDateTime(DateTime dt)
{
string result = "";
if (dt != null)
{
PersianCalendar objPersianCalendar = new PersianCalendar();
int year = objPersianCalendar.GetYear(dt);
int month = objPersianCalendar.GetMonth(dt);
int day = objPersianCalendar.GetDayOfMonth(dt);
int hour = objPersianCalendar.GetHour(dt);
int min = objPersianCalendar.GetMinute(dt);
int sec = objPersianCalendar.GetSecond(dt);
result = year.ToString().PadLeft(4, '0') + "/" +
month.ToString().PadLeft(2, '0') + "/" +
day.ToString().PadLeft(2, '0') + " " +
hour.ToString().PadLeft(2, '0') + ":" +
min.ToString().PadLeft(2, '0') + ":" +
sec.ToString().PadLeft(2, '0');
}
return new SqlString(result);
}
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString ToPersianDate(DateTime dt)
{
string result = "";
if (dt != null)
{
PersianCalendar objPersianCalendar = new PersianCalendar();
int year = objPersianCalendar.GetYear(dt);
int month = objPersianCalendar.GetMonth(dt);
int day = objPersianCalendar.GetDayOfMonth(dt);
result = year.ToString().PadLeft(4, '0') + "/" +
month.ToString().PadLeft(2, '0') + "/" +
day.ToString().PadLeft(2, '0');
}
return new SqlString(result);
}
};
Finally, we need to build this class to create the PersianSQLFunctions.dll assembly. That is all that we need to do inside Visual Studio. Then, we should introduce this assembly to SQL Sever. But before doing that, we should enable CLR in SQL Server, by executing the following command:
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
Because, CLR is disabled in SQL Server until we enable it. This procedure does the process of enabling CLR and then reconfigure the SQL Server by using the reconfigure; command. After that, we should run this command inside SQL Server:
CREATE ASSEMBLY PersianSQLFunctions
FROM 'F:\My Projects\PersianSQLFunctions\PersianSQLFunctions
\bin\Debug\ PersianSQLFunctions.dll'
The final step for the installation of our CLR code is telling SQL Server how to match up a Transact SQL request with a CLR function. We do this with a CREATE FUNCTION statement. However, unlike the usual CREATE FUNCTION statement, there is no TSQL code. There is only the EXTERNAL NAME reference to the function. Note that the function name is fully qualified, that is, assemblyName.ClassName.FunctionName. It is also important to be aware that the EXTERNAL NAME specification is case-sensitive!
CREATE FUNCTION ToPersianDateTime
(
@dt DateTime
)
RETURNS NVARCHAR(19)
AS EXTERNAL NAME PersianSQLFunctions.UserDefinedFunctions.ToPersianDateTime
CREATE FUNCTION ToPersianDate
(
@dt DateTime
)
RETURNS NVARCHAR(10)
AS EXTERNAL NAME PersianSQLFunctions.UserDefinedFunctions.ToPersianDate
Note that the assembly name in the CREATE FUNCTION statement is the name you gave it when you load the assembly into SQL Server, not the name of the DLL file, which is no longer of any concern to SQL Server. The TSQL function name need not be the same as the CLR function, but it is less confusing if they are the same. My choice of character size for the NVARCHAR declarations is arbitrary; you might feel some other size is more appropriate.
The time has come to test our creation.
SELECT dbo.ToPersianDate(GETDATE())
‘1386/05/05’
SELECT dbo.ToPersianDateTime(GETDATE())
‘1386/05/05 18:03:24’
