Microsoft SQL Server 的波斯日期转换 CLR 函数

Ali-DN

4.82/5 (30投票s)

2008年7月26日

CPOL

3分钟阅读

89166

1437

Microsoft SQL Server 的波斯日期转换 CLR 函数

下载源代码 - 6.45 KB

引言

Microsoft SQL Server 2005 最令人兴奋的新功能之一是它能够托管 .NET 公共语言运行时 (CLR)。此功能的设计不仅仅是为了提供 Transact SQL (TSQL) 的替代方案。在任何开发项目中，重要的是使用正确的工具来完成正确的工作。如果您想创建一个存储过程来对关系数据执行标准操作，那么毫无疑问，TSQL 是您的首选平台。由于 TSQL 专门为操作关系数据而设计，因此它在这方面非常出色。但是，有很多任务超出了关系数据的范围。对于这些任务，CLR 代码可能是一个明智的选择。

这样的任务可能包括编写一个日期转换函数，以支持 Microsoft SQL Server 中的 Persian Date（波斯日期）。由于缺少对波斯语排序规则的支持，SQL Server 本身不支持波斯日期。值得庆幸的是，.NET framework 2.0 及更高版本支持 System.Globalization 命名空间中的 PersianCalendar。现在，借助在 Microsoft SQL Server 中嵌入 CLR 函数的功能，我们可以编写一个函数将任何 DateTime 格式转换为波斯日期格式。在本文中，我将展示如何在 C# 中创建一个波斯日期转换器，然后如何将其嵌入到 Microsoft SQL Server 中，最后是如何在 SQL Server 环境中将其用作函数。

步骤

首先，我们需要在 Visual Studio 中创建一个 SQL Server 项目。

然后，右键单击 Visual Studio 中创建的项目 PersianSQLFunctions，将用户定义的函数添加到项目中。

接下来，我们将看到创建了一个名为 UserDefinedFunctions 的分部类，其中包含一个 Hello SQL 函数。我们将在该类中创建的函数的模式与这个简单的函数相同，当我们在 SQL Server 中调用它时，该函数返回一个“Hello”字符串。

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()
    {
        // Put your code here
        return new SqlString("Hello");
    }
};

我们的项目需要两个函数，它们传递一个 DateTime 对象作为参数，并以 SqlString 对象的形式返回波斯日期和波斯日期时间。

[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 类有许多方法可以从 DateTIme 对象中提取日期部分，例如 GetYear、GetMonth 等。

[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); 
}

这是完整的代码

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); 
    } 
};

最后，我们需要构建这个类来创建 *PersianSQLFunctions.dll* 程序集。这就是我们在 Visual Studio 中需要做的全部工作。然后，我们应该将这个程序集引入 SQL Sever。但在这样做之前，我们应该通过执行以下命令在 SQL Server 中启用 CLR

EXEC sp_configure 'clr enabled' , '1' 
go 
reconfigure;

因为，在启用 CLR 之前，CLR 在 SQL Server 中处于禁用状态。此过程执行启用 CLR 的过程，然后使用 reconfigure; 命令重新配置 SQL Server。之后，我们应该在 SQL Server 中运行此命令

CREATE ASSEMBLY PersianSQLFunctions 
FROM 'F:\My Projects\PersianSQLFunctions\PersianSQLFunctions
\bin\Debug\ PersianSQLFunctions.dll'

安装 CLR 代码的最后一步是告诉 SQL Server 如何将 Transact SQL 请求与 CLR 函数匹配起来。我们使用 CREATE FUNCTION 语句来执行此操作。但是，与通常的 CREATE FUNCTION 语句不同，没有 TSQL 代码。只有对该函数的 EXTERNAL NAME 引用。请注意，函数名称是完全限定的，即 _assemblyName.ClassName.FunctionName_。同样重要的是要注意 EXTERNAL NAME 规范是区分大小写的！

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

请注意，CREATE FUNCTION 语句中的程序集名称是您将程序集加载到 SQL Server 时给它的名称，而不是 DLL 文件的名称，SQL Server 不再关心 DLL 文件的名称。TSQL 函数名称不必与 CLR 函数相同，但如果它们相同，则不易混淆。我对 NVARCHAR 声明的字符大小的选择是任意的；您可能认为其他大小更合适。

现在是测试我们的创建的时候了。

SELECT dbo.ToPersianDate(GETDATE()) 
‘1386/05/05’ 
SELECT dbo.ToPersianDateTime(GETDATE()) 
‘1386/05/05 18:03:24’