SQL Server 2005中的CLR集成(2)

4.1 创建复杂CLR存储过程

在这个示例中，将创建从AdventureWorks数据库的Production.Product表返回数据的存储过程。首先，选择“文件”→“新建项目”，然后在弹出窗口的左边导航菜单中选择“Visual C#”→“数据库”，接着将项目模板选择为“SQL Server项目”，这样可在Visual Studio 2005中创建新项目SqlServerDataAccess。如果单击新建项目对话框中的OK按钮，那么会要求添加数据库引用。如果AdventureWorks引用还不可用，那么可使用“添加新引用”选项来添加指向AdventureWorks数据库的引用。

当选择启用CLR存储过程调试和项目建立后，在菜单中选择“项目”→“添加存储过程”项，接着设置类名称为GetProducts.cs。一旦创建了类，可根据示例1修改代码。

示例1：由CLR存储过程返回表格式结果集

using System; 
using System.Data; 
using System.Data.SqlClient; 
using System.Data.SqlTypes; 
using Microsoft.SqlServer.Server; 
public partial class StoredProcedures 
{ 
[Microsoft.SqlServer.Server.SqlProcedure] 
public static void GetProducts(int categoryID) 
{ 
SqlConnection connection = new SqlConnection("context connection = True"); 
connection.Open(); 
SqlCommand command = new SqlCommand("SELECT ProductID, Name FROM " + 
"Production.Product WHERE ProductSubcategoryID = " + 
categoryID.ToString(), connection); 
SqlDataReader reader = command.ExecuteReader(); 
SqlContext.Pipe.Send(reader); 
} 
}; 

这个程序集中的代码创建了数据库连接，然后执行T-SQL语句，结果返回Product表中具有所提供类别ID的ProductID和Name列的所有记录。注意，示例1所示的上下文连接允许在调用CLR代码的同一上下文中执行SQL语句。为了启用上下文连接，可将连接字符串设置为“context connection=True”。

为了使用在SQL Server中处理的数据，内部过程的提供程序进行了优化。使用内部过程受管的提供程序的类和方法，能够很简单的将查询提交给数据库，执行DML和DDL语句，以及将结果集和消息返回给客户端应用程序。

Microsoft.SqlServer.Server命名空间对组成内部过程提供程序的类型进行分组。这个命名空间与ADO.NET的SqlClient命名空间共享很多相似性和接口，开发人员使用SqlClient命名空间从受管客户端和中间层应用程序访问SQL Server数据。由于具有相似性，所以能够很简单的将代码从客户端应用程序迁移到服务器和后台使用。

Microsoft.SqlServer.Server命名空间中有两个重要类，它们用于设置内部过程提供程序：

SqlContext：这个类封装了其他一些扩展。另外，该类提供了事务和数据库连接，它们是例程执行环境的一部分。
SqlPipe：这个类可使例程向客户端发送表格式结果和消息。这个类在概念上与ASP.NET中的Response类很相似，Response类也能用于向调用者发送消息。

既然已经创建了存储过程，那么就可以使用“生成”→“生成SqlServerDataAccess”菜单项生成项目。一旦项目生成，下一步是将存储过程部署到SQL Server。与前一示例不同，前一示例必须通过很多步骤才能部署存储过程，而Visual Studio 2005只要单击按钮就能够部署存储过程。为此，可选择“生成”→“部署SqlServerDataAccess”菜单项。以上就是使用Visual Studio 2005部署CLR存储过程的全部过程。

此时，可以通过执行来测试这个存储过程。为了达成本示例目的，创建一个测试该存储过程的简单ASP.NET页面，如示例2所示。

<%@ Page Language="C#" %> 
 
 
 
 
Enter Category ID: 
 

AutoGenerateColumns="false" DataSourceID="productSource"> 
 
 
 
 
 
SelectCommand="GetProducts" SelectCommandType="StoredProcedure" 
ConnectionString="<%$ConnectionStrings:AdventureWorks%>"> 
 
ControlID="txtCategoryID" 
PropertyName="Text" /> 
 
 
 
 

正如这个示例所示，调用CLR存储过程与调用T-SQL存储过程没有任何不同。二者都按照相同的步骤，而没有任何改变。结果应该显示Production.Product表中Name和ProductNumber列的所有记录。在这个示例中，返回了Name和ProductNumber列的所有行，因为查询没有为返回单个行而设置特定ProductID。因此，返回所有行。

4.2 SQL Server中的T-SQL和受管代码

一种很吸引人的想法是认为，开发人员能够避免编写业务层，而在SQL Server中使用受管代码编写所有代码。然而，重要的是记住SQLCLR不是用于代替业务层。因此，正由于能够在SQL Server中编写C#或者VB.NET代码，所以不希望将SQL Server作为托管所有.NET代码的应用程序服务器。SQLCLR是专用于在SQL Server中实现特定目的，而此时T-SQL可能不是正确的选择。

注意，开发人员应该将SQLCLR作为一种无法使用T-SQL显式表达逻辑的备选解决方案——也不能作为实现业务层逻辑的替代品。那么根据这个规则，开发人员应该首先使用T-SQL解决问题。

T-SQL比较适合基于集合的操作，例如表格式数据，SQLCLR比较适合实现过程性代码和递归操作。然而，真正是否更加适合取决于多种因素。通过以下三条规则可很好的比较总结T-SQL和SQLCLR：（1）基于集合的操作最好使用T-SQL。（2）使用程序性和递归代码最好使用SQLCLR。（3）存在多个有关因素可能影响以上两条规则，例如编译CLR代码和解释性的T-SQL代码，在SQL Server中加载时的开销，过程中所需的数据访问，辅助函数库等等。这种情况由存在多种原因造成，其中多数由于数据库内部的CLR与Windows计算机中的CLR受到了不同的约束限制。Windows计算机中的CLR由操作系统来运行，其与SQL Server中运行的同一CLR不同。主要的不同点是SQL Server所担负的责任，管理线程调用，同步，锁定和内存位置等方面。

Windows计算机中的CLR和SQL Server中的CLR之间的另一个重要不同是，用于加载CLR的引导机制。SQL Server 2005除非在需要的情况下，否则不会加载CLR。这是因为SQL Server遵循了保护内存，尽量加载任何所需资源的规则。根据除非需要，否则不加载CLR的原则，可节约CLR本来要占据的数兆字节内存空间。因此，如果有少量使用CLR的代码片段，同时加载CLR，那么由此可能会影响特定计算机的其他操作。

将这样的责任都委托给宿主（SQL Server）反而会带来有趣的挑战。就本质而言，这意味着运行在SQL Server内部的个别操作此时可能被认为具有一定欺诈性，是一个潜在的安全线程，或者可能降低服务器安全性。因此，当自由使用CLR及其各种操作时，则变得有些危险，那么SQL Server应用程序则以一定方式正确的自己实现，即个别应用程序不应该以任何方式忽略或者暗中损害服务器。

通过对SQL Server内部能够执行的.NET代码操作集合的粒度控制能够强制SQL Server实现以上内容。这种机制构建于代码访问安全（Code Access Security，缩写为CAS）之上，它是CLR的一部分。微软开发人员要花费长时间艰难的查看.NET Framework中的每个类，将它们分为三种类别（SAFE，EXTERNAL_ACCESS和UNSAFE），这些类别在上面的内容中已经介绍了。换言之，开发人员需要告诉SQL Server，根据所设置操作，代码将按照三种类别之一执行。如果代码试图进行一些初始化设置之外的事情，那么SQL Server会阻止其执行。

小结

本文重点对SQL Server 2005中的CLR集成功能进行了讲解。这的确是一种非常吸引开发人员的功能，能够大幅提高开发效率，减低应用程序的维护修改强度。希望开发人员能够熟练的使用这项功能。

【相关文章】

• 使用 SQL Server 2005中的 CLR 集成(1)

• SQL Server 2005上的CLR和ADO.NET 2.0(1)

• 使用SQL Server 2005中的CLR集成