在 SQL Server 2005 中执行 .NET 代码

• 下载源代码 - 7.95 KB

引言

首先，我想祝贺微软开发团队提供了如此出色和创新的技术，使我们能够使用用任何 .NET 支持的语言编写的托管代码在 Microsoft SQL Server 2005 存储过程中执行。简单来说，“现在您可以在 SQL Server 存储过程中调用 .NET 类库中编写的任何函数”。这项技术将使我们能够利用 .NET 语言的特性以及 SQL Server 的可扩展性。

描述

我在 CodeProject 上看到了一篇由一位先生写的关于在 SQL Server 中使用 MSMQ 的文章，其中从 SQL SP 使用了 MSMQ 和消息传递。我试图展示使用此类程序集（assemblies）的限制和约束，以及 SQL Server 2005 中的必需配置，以便开发人员可以开始。我在这里分享我在实际实现过程中遇到的实际问题。

在开始之前，您必须启用 SQL Server 的托管代码执行功能，该功能默认是禁用的。要启用它，请在 SQL Server 查询编辑器中执行以下代码作为查询。

sp_configure 'clr enable', 1
GO
RECONFIGURE
GO

现在您的服务器已准备好在其运行时下运行托管代码。但我要告诉您，这不是魔术，而是 .NET 框架所使用的相同技术。唯一重要的一点是，现在 SQL Server 也能够执行 CLR 上的代码。这难道不棒吗？在写这篇文章之前，我对此进行了大量研究。我找到了几篇解释相同内容的文章。但由于信息分散，我遇到了很多问题。现在我正试图在这篇文章中模拟整个过程。我之所以进行这项研究，是因为我有特定的需求。我当时在 C# .NET 中使用 Microsoft 的消息队列服务。但过了一段时间，我遇到了一个需求，需要在存储过程中向 MSMQ 队列发送消息。而那时对我来说，只能通过 .NET 运行时中的 System.Messaging 命名空间来实现。我知道大多数开发人员对 MSMQ 感到好奇。别担心，我现在不会深入探讨其中的细节。我们将从一个简单的应用程序开始，该应用程序将使用 C# .NET 类库中一个简单的返回 string 的函数。让我们从类库开始。

• 启动 .NET 2005 Studio。
• 打开一个新的类库。
• 选择 C# 作为语言。
• 将项目命名为 ManagedCodeAndSQLServer。
• 默认情况下，您会发现一个名为 Class1.cs 的类已为您创建。
• 将其重命名为 BaseFunctionClass.cs。

在类中创建一个简单的函数，如下所示：

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;

namespace ManagedCodeAndSQLServer
{
    public class BaseFunctionClass
    {
        #region "Default Constructor"
        public BaseFunctionClass()
        { 
                   
        }
        #endregion

        #region "Welcome Function"
        /// <summary>
        /// This function will be called from the SQL Stored Procedure.
        /// </summary>
        /// <param name=""strName"">Name</param>
        /// <returns>Welcome Message</returns>
        [SqlProcedure]
        public static void GetMessage(SqlString strName, out SqlString  
        strMessge)
        {
          strMessge = "Welcome," + strName + ", " + "your code is getting
          executed under CLR !";
        }

        #endregion
    }
}

重要提示

我希望您能够注意到上面代码中的一些新内容。首先，GetMessage 函数上的 [SqlProcedure] 属性会告知 CLR 该函数可以从 SQL Server 存储过程中调用。我告诉您的是我自己的经验，您可能在互联网上发布的文章中找不到这些内容。在使用 CLR 函数时，您必须记住 SQL Server 的 nvarchar 等同于 System.String。但最重要的是，“启用 CLR 的过程只能返回 Int32、Int 和 void 数据类型”。这就是为什么我在 GetMessage 函数中使用输出参数。我也使用了 SqlString 而不是 System.String。请牢记以上所有事项。

现在构建项目，然后暂时搁置，因为您在 .NET 类库方面的所有工作都已完成。现在回到您的 SQL Server。我希望您仍然记得我们在文章开头启用了服务器的 CLR 集成。如果忘记了，那就不要等着我来帮您启用。

我们必须将 ManagedCodeAndSQLServer.dll 注册为数据库中的一个程序集。要注册程序集，您应该拥有数据库的所有者权限，或者您应该是本地系统管理员或服务器管理员。我们将要注册的程序集应该以 UnSafe 模式注册。否则，它们将无法访问 SQL Server 2005 外部的资源。要注册一个不安全（unsafe）的程序集，您应该在您的用户名或角色中启用“unsafe”权限。以上所有事项都非常重要，所以请注意它们，否则您将无法注册您的程序集。因此，创建一个名为 'TestingCLR' 的数据库。在将 .NET 程序集注册到数据库之前，您必须使用以下代码将数据库的 trustworthy 选项设置为 ON。

ALTER DATABASE TestingCLR SET TRUSTWORTHY ON
GO

现在使用以下代码在该数据库下注册 ManagedCodeAndSQLServer.dll。

CREATE ASSEMBLY ManagedCodeAndSQLServer
AUTHORIZATION dbo
FROM 'E:\Important\SQL\ManagedCodeAndSQLServer\ManagedCodeAndSQLServer\bin\
Debug\ManagedCodeAndSQLServer.dll'
WITH PERMISSION_SET = UNSAFE
GO

我再次强调，如果 dbo 没有“不安全程序集”的权限，它将无法工作。同样，如果我在 DLL 中使用 .NET 的特定类，我必须以相同的方式在 DLL 注册之前先注册它。例如，如果我在我的程序集中使用 System.Messaging，那么我必须使用以下代码进行注册。

CREATE ASSEMBLY Messaging
AUTHORIZATION dbo
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Messaging.dll'
WITH PERMISSION_SET = UNSAFE
GO

要使用普通功能，我们不需要像本例那样注册 .NET 运行时中的 DLL。现在，在您的服务器资源管理器中，转到您的 TestingCLR 数据库，选择“可编程性”（Programmability），然后在“程序集”（Assemblies）下，您会找到一个以您命名的程序集，即“HelloDotNet”。现在您几乎完成了。您应该知道您将要使用的程序集中的函数的完整路径（命名空间约定）。在本例中，它是：ManagedCodeAndSQLServer.BaseFunctionClass，而 GetMessage 是我们要使用的函数。现在我正在创建一个简单的存储过程来使用这个程序集，如下所示：

CREATE PROCEDURE usp_UseHelloDotNetAssembly
@name nvarchar(200),
@msg nvarchar(MAX)OUTPUT
AS EXTERNAL NAME ManagedCodeAndSQLServer.[ManagedCodeAndSQLServer.
BaseFunctionClass].GetMessage
GO

要执行该过程：

DECLARE @msg varchar(MAX)
EXEC usp_UseHelloDotNetAssembly 'Kittu And Tannu',@msg output
PRINT @msg

您将得到以下输出： Welcome, Kittu And Tannu, your code is getting executed under CLR !

试试看，尽情享受吧！