在ASP.NET 2.0中操作数据之七十三：用Managed Code创建存储过程和用户自定义函数(上部分)

导言：

　　数据库，比如Microsoft's SQL Server 2005使用Transact-Structured Query Language (T-SQL)来插入、修改、检索数据.绝大多数数据库系统都包含constructs来对一系列的SQL statements进行分组，这些statements可以作为单独的单元来执行.存储过程就是一个例子，另一个例子是用户自定义函数(UDFs), 我们将在第9步进行详细的探讨.

　　SQL是设计来处理一系列数据的. SELECT,UPDATE,和DELETE statements适用于相应表的所有记录，且通过WHERE字句来进行筛选.也有很多的特性被设计来一次处理一条记录，或操作标量数据(scalar data).比如CURSORs允许一次遍历所有的记录.字符串操作功能，比如LEFT, CHARINDEX, 以及PATINDEX用来处理标量数据.SQL也包含了控制流声明，比如IF和WHILE.

　　在Microsoft SQL Server 2005之前，存储过程和用户自定义函数UDFs只能当做一个T-SQL statements集来创建，而SQL Server 2005设计时包含Common Language Runtime (CLR)。因此，对一个SQL Server 2005数据库里的存储过程和用户定义方法，我们可以用managed code来进行创建。那就也说你可以在一个C#类里创建一个存储过程或用户定义函数.这样一来我们就可以在.NET Framework或你自己定义的类里面使用这些存储过程或方法.

在本文我们将考察如何创建存储过程和用户定义函数，以及如何将它们整合进数据库Northwind.让我们开始吧。

　　注意：管理数据库对象(Managed database objects)与SQL数据库里包含的相对应的数据库对象比较起来有一些优势，主要体现在：使用的语言更丰富、熟悉；可以使用现有的代码和逻辑.但是在处理那些并不包含很多逻辑的一系列数据时，其效率可能要低一些.关与managed code相较T-SQL而言的优势，请参阅文章《Advantages of Using Managed Code to Create Database Objects》(http://msdn2.microsoft.com/en-us/library/k2e1fb36(VS.80).aspx)

第一步：将Northwind数据库移出App_Data文件夹

　　本教程到目前为止使用的是放在App_Data文件夹里的Microsoft SQL Server 2005 Express版本的数据库.

　　然而在本教程，我们将Northwind数据库移出App_Data文件夹，再使用一个被注册为SQL Server 2005 Express版本数据库的实例。虽然我们不移出的话也可以执行本文的这些步骤，不过将其注册为一个SQL Server 2005 Express版本数据库的实例的话，这些步骤就要简单的多了。

　　本文下载代码里包含2个数据库文件:NORTHWND.MDF 和 NORTHWND_log.LDF，将它们放在一个叫DataFiles的文件夹里，先关闭Visual Studio，再将NORTHWND.MDF 和 NORTHWND_log.LDF文件从根目录的App_Data文件夹里移出到根目录以外的其它文件夹里。完成后，我们需要将Northwind数据库注册为SQL Server 2005 Express版本数据库的实例。为此我们要用到SQL Server Management Studio.如果你没有安装的话，可以在此下载并安装：(http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796)。

　　打开SQL Server Management Studio，如图1所示，Management Studio首先问我们连接什么服务器，在server name里键入“localhost/SQLExpress” ，在Authentication下拉列表里选“Windows Authentication” 。点Connect.

图1：连接恰当的数据库实例

　　一旦连接后，Object Explorer窗口将会把SQL Server 2005 Express版本数据库实例的信息显示出来，比如databases, security information, management options等.

　　我们需要将DataFiles文件夹里的Northwind数据库作为SQL Server 2005 Express版本数据库实例。在Databases文件夹里右键单击，选“Attach”项。这将打开Attach Databases对话框.点 Add 按钮，找到NORTHWND.MDF文件，再点OK。这样，你的屏幕看起来和图2差不多：

图2：连接到合适的数据库实例

　　注意：当通过Management Studio连接到SQL Server 2005 Express版本的数据库实例时，Attach Databases对话框不允许你浏览用户私人文件目录(user profile directories),比如My Documents.因此，一定要将NORTHWND.MDF 和 NORTHWND_log.LDF文件放在一个非用户私人文件目录里.

　　点OK完成后，Attach Databases对话框将会关闭，Object Explorer将会将新添加的数据库列出来。问题来了，它的名字可能是这样的：9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES/DATATUTORIALS/VOLUME 3/CSHARP/73/ASPNET_DATA_TUTORIAL_75_CS/APP_DATA/NORTHWND.MDF，我们将其重命名为“Northwind”，方法是在该数据库右键单击，选“Rename”.

图3：将数据库重命名为“Northwind”

第二步：在Visual Studio里创建一个新的解决方案和SQL Server Project

　　要在SQL Server 2005里创建管理存储过程或用户定义函数，我们要在一个类里用C# 代码写这些存储过程和用户定义函数。一旦写完后，我们需要将该类编译为一个 .dll文件，将该编译文件注册到一个SQL Server 数据库，然后在数据库里创建一个存储过程或用户定义函数以指向编译文件里对应的方法。这些步骤要手工完成。我们可以在文本编辑器里写代码，在命令行里用C#编译器 (csc.exe)对其进行编译；用CREATE ASSEMBLY 命令或从Management Studio里将其注册到数据库.添加存储过程或用户定义函数的方法类似.幸运的是 Visual Studio的Professional 和 Team Systems 版本包含一个SQL Server Project类型，它可以自动的完成这些工作.在本文，我们将使用SQL Server Project类型来创建一个管理存储过程和用户定义函数。

　　注意：如果你使用的是Visual Studio的Visual Web Developer 或 Standard版本，你就要手动完成了。在Step 13，我们将详细介绍手动完成的细节.我们鼓励你从Steps 2 看到Steps 12 ，再看Step 13.因为Steps 2 到Steps 12包含了很重要的SQL Server 配置说明，不管你用的是什么版本.

　　打开Visual Studio. 从 File 菜单，选New Project以打开New Project对话框(见图4).点到Database工程类型，在右边的Templates列表，选择创建一个新的SQL Server Project.我将其命名为ManagedDatabaseConstructs并放在一个名为Tutorial75的解决方案里.

图4：创建一个新的SQL Server Project

　　在New Project对话框里点OK按钮，创建该解决方案和SQL Server Project.

　　一个SQL Server Project依赖于一个具体的数据库.因此，接下来我们要指定该信息。如图5所示，New Database Reference对话框里指向了Northwind数据库，也就是我们在第一步里注册的SQL Server 2005 Express版本数据库实例.

图5：将SQL Server Project与Northwind数据库联系起来

　　为了对我们将要在本工程创建的管理存储过程和用户自定义函数进行调试，我们需要激活SQL/CLR调试支持.无论什么时候将一个SQL Server Project与新的数据库联系起来时(就像我们在图5做的那样)，Visual Studio会询问我们是否激活SQL/CLR调试(如图6)，选Yes.

图6：激活SQL/CLR调试

　　此时，这个新的SQL Server Project已经添加到解决方案里了。其包含一个Test Scripts文件夹，文件夹里是一个Test.sql文件。它用来对本工程添加的管理数据库对象进行调试，我们将在第12步考察调试.

　　我们现在可以对该工程添加新的管理存储过程和用户自定义函数.不过在此之前，我们要将现有的web应用程序包含进解决方案。在 File 菜单里选Add项，再选Existing Web Site.浏览到相应的文件夹，点OK.如图7所示，这将更新解决方案以包含2个工程：即website和名为ManagedDatabaseConstructs的SQL Server Project.

图7：该解决方案现在包含2个工程

　　Web.config文件里的NORTHWNDConnectionString值当前引用的是App_Data文件夹里的NORTHWND.MDF。由于我们已经将其从App_Data文件夹移出了，其注册为SQL Server 2005 Express版本数据库实例，因此我们需要相应的更新NORTHWNDConnectionString值。打开Web.config文件，改动NORTHWNDConnectionString值，像这样：“Data Source=localhost/SQLExpress;Initial Catalog=Northwind;Integrated Security=True”.

　　完成后，你的Web.config文件的<connectionStrings>节点看起来和下面的差不多：

<connectionStrings>
 <add name="NORTHWNDConnectionString" connectionString=
  "Data Source=localhost/SQLExpress;Initial Catalog=Northwind;
   Integrated Security=True;Pooling=false"
  providerName="System.Data.SqlClient" />
</connectionStrings>

　　注意：就像在上一章探讨的一样，当从一个客户端程序——比如一个ASP.NET website，调试一个SQL Server对象时，我们需要关闭连接池。上面的连接字符串里我们关闭了连接池(“Pooling=false”). 如果你不打算从ASP.NET website调试管理存储过程和用户自定义函数的话，激活连接池.

第三步：创建一个Managed Stored Procedure

　　要向Northwind数据库添加一个管理存储过程的话，我们首先要创建一个存储过程作为该SQL Server Project里的一个方法。从解决资源管理器里，右键单击 ManagedDatabaseConstructs工程，选“添加新项”，这将展示Add New Item 对话框，其列出了可以添加到该工程的各种管理数据库对象的类型，如图8所示，包括stored procedures 、User-Defined Functions等.我们来创建一个存储过程，用来简单的将那些处于discontinued状态的产品返回，将该存储过程文件命名为GetDiscontinuedProducts.cs.

图8：添加一个新的存储过程，名为GetDiscontinuedProducts.cs

这将创建一个新的C# class类文件，如下：

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 GetDiscontinuedProducts()
 {
  // Put your code here
 }
}

　　我们注意到该存储过程作为一个static方法来执行，且位于一个名为StoredProcedures的部分类(partial class)文件之内. 此外，该 GetDiscontinuedProducts方法有一个SqlProcedure特性, 这就标明了该方法是一个存储过程.

　　下面的代码创建了一个SqlCommand对象，设其CommandText为一个SELECT查询，以返回Products table表里所有Discontinued列为1的记录.它然后执行该命令并将结果返回给客户端程序.添加这些代码到GetDiscontinuedProducts方法.

// Create the command
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText =
  @"SELECT ProductID, ProductName, SupplierID, CategoryID,
    QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
    ReorderLevel, Discontinued
  FROM Products
  WHERE Discontinued = 1";

// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);

　　所有的管理数据库对象都可以使用SqlContext对象，该对象展示“调用者”(caller)的内容；而SqlContext又可以通过其Pipe属性来访问一个SqlPipe对象，该对象用来在SQL Server数据库和调用程序之间传递信息；而ExecuteAndSend方法，就像其名字暗示的那样，执行传入的SqlCommand对象，并将结果返回给客户端程序.

　　注意：管理数据库对象最适合做这种存储过程和用户定义函数——使用procedural logic逻辑而不是set-based logic逻辑.所谓Procedural logic逻辑包括处理一系列一行行(on a row-by-row basis)的数据或者处理标量数据(scalar data).然而，我们刚刚创建的GetDiscontinuedProducts方法，并未使用Procedural logic逻辑。其实该方法最理想的是作为一个T-SQL存储过程来执行.之所以作为一个管理存储过程来执行，是为了示范创建和配置管理存储过程所必要的步骤.

步骤4：配置Managed Stored Procedure

　　代码完成后我们准备将其配置给Northwind数据库.“Deploy”项执行的具体步骤我们将在第13步讲明白。进入解决资源管理器，在ManagedDatabaseConstructs工程名上右键单击，选“Deploy”项，然而，可能会出现如下的错误消息：“Incorrect syntax near 'EXTERNAL'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.”

　　该出错信息发生试图将编译文件注册到Northwind数据库时.为了将一个编译文件注册到一个SQL Server 2005数据库，该数据库的compatibility level必须设置为90.默认下，一个新的SQL Server 2005数据库的compatibility level为90.而Microsoft SQL Server 2000使用的数据库的默认compatibility level为80.由于使用的Northwind数据库最初是一个Microsoft SQL Server 2000数据库，其compatibility level被设置为80，因此需要设置为90以便于进行注册.要更新数据库的compatibility level，在Management Studio里打开一个New Query窗口，输入：exec sp_dbcmptlevel 'Northwind', 90点击工具栏上的执行图标以运行上述查询.

图9：更新Northwind数据库的Compatibility Level

　　更新后重新部署该SQL Server Project，这次应该不会出错了.返回SQL Server Management Studio，在Object Explorer里的Northwind 数据库上右键单击，选“刷新”.接下来，找到Programmability文件夹，再展开Assemblies文件夹。如图10所示，Northwind数据库现在包含了一个由ManagedDatabaseConstructs工程生成的编译文件.

图10：该ManagedDatabaseConstructs编译文件现在注册到Northwind数据库

　　也来打开Stored Procedures文件夹。你将会看到名为GetDiscontinuedProducts的存储过程。该存储过程是在部署的时候创建的，它指向ManagedDatabaseConstructs编译文件里的GetDiscontinuedProducts方法.当执行GetDiscontinuedProducts存储过程时，它反过来执行GetDiscontinuedProducts方法.由于它是一个管理存储过程(managed stored procedure),不能通过Management Studio来对其进行编辑(因此，在存储过程名旁边有一个锁的图标)

图11：GetDiscontinuedProducts存储过程展示在Stored Procedures文件夹

　　还有一个障碍要克服：该数据库被配置为阻止执行managed code.我们来做一个实验。打开一个new query窗口，执行GetDiscontinuedProducts存储过程.你将会收到如下的错误信息：“Execution of user code in the .NET Framework is disabled. Enable ‘clr enabled'configuration option.”

　　让我们检查Northwind数据库的配置信息，在查询窗口键入并运行命令“exec sp_configure”。其显示“clr enabled”目前设置为0.

图12：“clr enabled”目前设置为0.

　　我们注意到每条配置(如图12)都有4个值：“minimum”、“maximum”、“config”、“run” 值.要更新“clr enabled”配置的“config”值，执行如下的命令：exec sp_configure 'clr enabled', 1

　　如果你再运行“exec sp_configure”的话，你将看到上述声明将“clr enabled”配置的“config”值设为1，而“run”值仍然为0.因此，我们需要执行RECONFIGURE命令,它将会把“ run”值设为目前的“config”值.在查询窗口输入“RECONFIGURE”，再点工具栏上的执行图标.如果你运行“exec sp_configure”的话，现在你可以看到“clr enabled”配置的“config” 和 “run”值都为1.

　　完成“clr enabled”配置后，我们准备运行GetDiscontinuedProducts存储过程.在查询窗口键入并运行命令“exec GetDiscontinuedProducts”.调用该存储过程将导致执行GetDiscontinuedProducts方法里相应的managed code.代码发出一个SELECT查询并返回所有处于discontinued状况的产品，并将数据返回给调用程序——具体到本例，就是SQL Server Management Studio.Management Studio将接收到的数据展示在Results窗口.

图13：GetDiscontinuedProducts存储过程返回所有处于Discontinued状态的产品

第五步：创建接收输入参数的Managed Stored Procedures

　　我们在本教程创建的很多查询和存储过程都使用参数.比如，在第67章，我们创建了一个名为GetProductsByCategoryID的存储过程，它接收一个名为@CategoryID的输入参数.该存储过程返回那些其CategoryID值与@CategoryID吻合的产品.

　　要创建接收输入参数的managed stored procedure，仅仅在定义方法时指定这些参数即可.我们来做个演示，在ManagedDatabaseConstructs工程里添加一个名为GetProductsWithPriceLessThan的另一个managed stored procedure.该managed stored procedure接收一个指定了价格的参数，返回所有的其UnitPrice列低于参数值的产品.

　　我们来进行添加.在ManagedDatabaseConstructs工程名上右键单击，选“添加新存储过程”.将文件命名为GetProductsWithPriceLessThan.cs.就像我们在图3看到的那样，这将创建一个新的C# class类文件.

　　更新GetProductsWithPriceLessThan方法以使其接收一个名叫price的SqlMoney类型的输入参数.代码如下：

[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProductsWithPriceLessThan(SqlMoney price)
{
 // Create the command
 SqlCommand myCommand = new SqlCommand();
 myCommand.CommandText =
   @"SELECT ProductID, ProductName, SupplierID, CategoryID,
     QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
     ReorderLevel, Discontinued
   FROM Products
   WHERE UnitPrice < @MaxPrice";

 myCommand.Parameters.AddWithValue("@MaxPrice", price);

 // Execute the command and send back the results
 SqlContext.Pipe.ExecuteAndSend(myCommand);
}

　　GetProductsWithPriceLessThan方法的生命代码和我们在第三步创建的GetDiscontinuedProducts方法的声明代码很相似.唯一的不同在于GetProductsWithPriceLessThan方法接收一个输入参数(price), 且SqlCommand的查询里也包含了一个参数(@MaxPrice).

　　完成代码添加后，重新部署SQL Server Project.接下来，返回到SQL Server Management Studio并刷新Stored Procedures文件夹.你将会看到一个新的查询——GetProductsWithPriceLessThan.从查询窗口，键入并执行命令“exec GetProductsWithPriceLessThan 25”，它会将所有价格低于25的产品显示出来，如图14所示.

图14：价格低于25的产品都显示出来

第六步：从数据访问层调用Managed Stored Procedure

　　此时，我们已经向ManagedDatabaseConstructs工程添加了GetDiscontinuedProducts 和 GetProductsWithPriceLessThan这2个managed stored procedures，并把它们注册到Northwind SQL Server数据库.我们同样从SQL Server Management调用它们(见图13和14)。为了使我们的ASP.NET应用程序能调用这些managed stored procedures,因此我们需要将它们添加到体系的数据访问层和业务逻辑层.在这一步，我们向类型化数据集NorthwindWithSprocs的ProductsTableAdapter添加2个新的方法.在第七步，我们将添加相应的方法到业务逻辑层.

　　在Visual Studio里打开类型化数据集NorthwindWithSprocs，向ProductsTableAdapter添加一个名为GetDiscontinuedProducts的方法.

　　注意：由于我们已经将Northwind数据库移出了App_Data文件夹，所以我们应该在Web.config文件里对连接字符串进行相应的更新。在第二步我们探讨了更新Web.config文件里的NORTHWNDConnectionString的值.如果你忘记了更新的话，当你试图向TableAdapter添加新方法的时候，你将看到这样的错误信息：“Failed to add query. Unable to find connection ‘NORTHWNDConnectionString' for object ‘Web.config'”.要克服这个问题， 需要更新Web.config文件的NORTHWNDConnectionString值.就像在第二步探讨的那样，再重新向TableAdapter添加新方法，这次就不会出错了.

　　添加新方法的时候，TableAdapter查询配置向导首先询问我们如何访问数据库。由于我们已经创建并注册了GetDiscontinuedProducts存储过程，因此选“Use existing stored procedure”项，点Next.

图15：选“Use existing stored procedure” 项

接下来要我们选要调用的存储过程。在左边下拉列表里选存储过程GetDiscontinuedProducts.

图16：选GetDiscontinuedProducts存储过程

由于GetDiscontinuedProducts将返回一系列的记录行，我们选第一项(“Tabular data”) ，再点Next.

图17: 选“Tabular Data” 项

最后，向导要我们为方法命名，选中这2项并分别命名为FillByDiscontinued 和 GetDiscontinuedProducts.点Finish完成向导.

图18：将方法命名为FillByDiscontinued 和 GetDiscontinuedProducts

采用同样的方法，为名为GetProductsWithPriceLessThan的managed stored procedure在ProductsTableAdapter里添加FillByPriceLessThan和 GetProductsWithPriceLessThan这2个方法.

图19显示的是为GetDiscontinuedProducts 和 GetProductsWithPriceLessThan这2个managed stored procedures在ProductsTableAdapter里添加各种方法后的界面.

图19: ProductsTableAdapter包含了新添加的方法

第七步：向业务逻辑层添加相应的方法

　　现在我们已经更新了数据访问层，我们还需要向业务逻辑层添加相应的方法.向ProductsBLLWithSprocs class类添加如下的2种方法：

[System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetDiscontinuedProducts()
{
 return Adapter.GetDiscontinuedProducts();
}

[System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable
 GetProductsWithPriceLessThan(decimal priceLessThan)
{
 return Adapter.GetProductsWithPriceLessThan(priceLessThan);
}

　　这2种方法都简单的调用数据访问层对应的方法，并返回ProductsDataTable实例.而2种方法上面附加的DataObjectMethodAttribute标记的作用是将方法包含到 ObjectDataSource控件的配置数据源向导的SELECT标签里的下拉列表项里. （上部分完）

　　祝编程快乐！

作者简介

　　本系列教程作者 Scott Mitchell，著有六本ASP/ASP.NET方面的书，是4GuysFromRolla.com的创始人，自1998年以来一直应用 微软Web技术。大家可以点击查看全部教程《[翻译]Scott Mitchell 的ASP.NET 2.0数据教程》，希望对大家的学习ASP.NET有所帮助。