使用 SQL Server 2005 的公共语言运行时 (CLR) 集成构建托管代码

引言

在本文中，我将介绍 SQL Server 2005 的一项强大新功能“CLR 集成”，它的优点，以及如何使用 Visual Studio 2005 Beta 2 中的一个简单项目，通过托管代码编写数据库对象。

什么是 CLR 集成？

公共语言运行时（Common Language Runtime），它是 .NET Framework 的核心组件，现已集成到代号为 Yukon 的 SQL Server 2005 中，并称为 CLR 集成。这意味着，对于开发人员而言，用户定义类型 (UDT)、用户定义函数 (UDF)、表、存储过程和触发器等数据库对象，都可以使用 C#、VB.NET 或任何支持 .NET 的语言进行构建。开发人员可以利用托管代码丰富的特性，如跨语言集成、对象生命周期管理、代码访问安全等，来编写数据库对象，还可以利用支持 .NET 的语言的面向对象能力。TSQL 在数据访问和管理方面表现出色，但它并非一个功能完善的编程语言。

当 CLR 在 SQL Server 中托管时，SQL Server 本质上充当 CLR 的操作系统。CLR 调用 SQL Server 实现的低级例程，用于线程、调度、同步和内存管理。这些是其余 SQL Server 引擎使用的相同基元。

CLR 集成的优势

SQL Server 2005 中的 CLR 集成提供了许多优势。

1. 在创建存储过程和触发器时，我们可以使用 .NET Framework 基类库 (BCL) 来处理复杂的执行逻辑、字符串操作、加密和文件管理。
2. 我们可以利用 C#、VB.NET 提供的面向对象能力（如封装、多态和继承）来编写更优化的代码，使代码更具组织性和可管理性。
3. 托管代码确保类型安全。在代码执行之前，CLR 会验证代码是否安全。
4. 提供更好的内存管理。CLR 调用 SQL Server 的基元来分配和释放其内存。由于 CLR 使用的内存计入系统的总内存使用量，因此 SQL Server 可以保持在配置的内存限制内，并确保 CLR 和 SQL Server 不会相互竞争内存。
5. 通常情况下，托管代码能提供更好的性能，但 CLR 集成存在一些性能上的考虑。性能因托管代码的上下文和使用情况而异。例如，所有不访问数据的内存密集型函数都可以用托管代码编写以获得最佳性能。然而，TSQL 函数在数据访问方面比 CLR 集成效率更高。

TSQL 与 CLR 集成

您需要根据具体情况来决定使用 TSQL 还是托管代码。我已列出一些情况，以简化您的决策。

在以下情况下使用 CLR 集成：

1. 当程序需要复杂的逻辑，可以通过面向对象、异常处理和复杂的条件结构来实现时。
2. 当程序需要使用 .NET 基类库 (BCL) 来进行加密、处理文件系统、调用 Web 服务或执行 TSQL 无法实现的任何其他任务时。
3. 当程序是 CPU 密集型的。由于托管代码总是经过编译，因此运行效率更高。
4. 在使用任何扩展存储过程之前，请检查是否可以通过使用托管代码实现相同的功能。如果是，则选择托管代码以获得类型安全。

您应该使用 TSQL 来创建和管理数据库对象及其过程化语言特性，因为它对此进行了高度优化。当只需要访问数据而无其他操作时，请勿使用托管代码。

使用 CLR 集成构建存储过程

我们已经对这项强大的新功能有了相当多的了解，现在我将演示一个简单的示例。以下所有代码均在 Visual Studio 2005 Beta 2 中构建。因此，最终产品可能会略有不同。

1. 打开 Visual Studio 2005 IDE 并创建一个新项目。

   文件 >> 新建项目 >> Visual C# >> SQL Server Project >> 为项目命名。在本例中，我将其命名为 EMP_SqlServerProject。

2. 下一步是创建数据库引用。单击“添加新引用”按钮，然后指定服务器名称、凭据和数据库名称。您可以单击“测试连接”按钮来验证这些信息。
3. 现在我们需要选择要创建的数据库对象。要创建新的存储过程，请转到 项目 >> 添加存储过程。将存储过程指定为 EMP_GetEmployee。
4. 默认情况下，所有必需的命名空间都已添加到项目中。它们是：System; System.Data; System.Data.Sql; System.Data.SqlTypes; Microsoft.SqlServer.Server;
5. 这是创建存储过程“EMP_SqlServerProject”的 C# 代码，该过程从数据库中的 EMPLOYEE 表检索记录。

   using System;
   using System.Data;
   using System.Data.Sql;
   using System.Data.SqlTypes;
   using System.Data.SqlClient;
   using Microsoft.SqlServer.Server;
   
   public partial class StoredProcedures
   {
       [Microsoft.SqlServer.Server.SqlProcedure]
       public static void sp_GetEmployeeInfo()
       {
           SqlConnection connection = 
                 new SqlConnection("context connection=true");
           try
           {
               connection.Open();
               SqlCommand sqlCommand = 
                   new SqlCommand("SELECT EMPID,EMAIL FROM EMPLOYEE",
                                                           connection);
               SqlDataReader sqlReader = sqlCommand.ExecuteReader();
               SqlContext.Pipe.Send(sqlReader);
           }
           catch(Exception ex)
           {
               throw ex;
           }
           finally
           {
               if (connection != null)
               connection.Close();
           }
           
       }
   };

此代码中使用的新类是 SqlContext，它是 Microsoft.SqlServer.Server 命名空间的一部分。为了从该存储过程返回结果集和消息，我使用了另一个对象 SqlPipe，该对象通过 SqlContext 类的 Pipe 属性公开。此对象的 Send 方法用于将消息或结果集输出到客户端。因此，在此示例中，代码使用来自 EMPLOYEE 表的数据创建一个 SqlDataReader，并将结果集发送回客户端。

您也可以像在 TSQL 中一样，将参数传递给存储过程。所有与 SQL Server 数据类型等效的 CLR 数据类型都存在于 System.Data.SqlTypes 命名空间中。例如，SqlChars 是 CLR 中 NVARCHAR 数据类型的等效项。

生成 - 生成 >> 生成 EMP_SqlServerProject

部署 - 要将此程序集部署到目标 SQL Server，请转到 生成 >> 部署 EMP_SqlServerProject。要验证此部署，请打开 SQL Server Management Studio。导航到 服务器名称 >> 数据库 >> 数据库名称 >> 可编程性 >> 程序集 >> 您将看到已部署的程序集 “EMP_SqlServerProject”。

运行 - EXEC sp_GetEmployeeInfo

结论

在此示例中，我解释了如何为 CLR 集成创建托管代码，但您还可以执行比执行简单查询更多的操作。有关创建 UDF、UDT 和其他数据库对象的更多信息，请参阅 MSDN。