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;这个程序集中的代码创建了数据库连接，然后执行T-SQL语句，结果返回Product表中具有所提供类别ID的ProductID和Name列的所有记录。注意，示例1所示的上下文连接允许在调用CLR代码的同一上下文中执行SQL语句。为了启用上下文连接，可将连接字符串设置为“context connection=True”。
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);
}
};
为了使用在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#" %>正如这个示例所示，调用CLR存储过程与调用T-SQL存储过程没有任何不同。二者都按照相同的步骤，而没有任何改变。结果应该显示Production.Product表中Name和ProductNumber列的所有记录。在这个示例中，返回了Name和ProductNumber列的所有行，因为查询没有为返回单个行而设置特定ProductID。因此，返回所有行。
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Retrieving Data from a CLR Stored Procedure</title>
</head>
<body>
<form id="form1" runat="server">
<div>
Enter Category ID:
<asp:TextBox runat="server" ID="txtCategoryID" />
<asp:Button runat="server" ID="btnRetrieve" Text="Retrieve Products" /><br />
<asp:GridView ID="productsView" runat="server"
AutoGenerateColumns="false" DataSourceID="productSource">
<Columns>
<asp:BoundField HeaderText="Product ID" DataField="ProductID" />
<asp:BoundField HeaderText="Name" DataField="Name" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="productSource" runat="server"
SelectCommand="GetProducts" SelectCommandType="StoredProcedure"
ConnectionString="<%$ConnectionStrings:AdventureWorks%>">
<SelectParameters>
<asp:ControlParameter DefaultValue="1" Name="categoryID"
ControlID="txtCategoryID"
PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
