对强类型 DataSet 的 TableAdapter 使用现有存储过程67

简介

在前一篇教程中，我们了解了如何配置强类型 DataSet 的 TableAdapter ，使其使用存储过程（而非 ad-hoc SQL 语句）来访问数据。我们还特别研究了如何使 TableAdapter 向导自动创建这些存储过程。在将一个原有应用程序移植到 ASP.NET 2.0 时，或在围绕当前数据模型构建 ASP.NET 2.0 网站时，数据库中可能已经包含了我们所需的存储过程。或者，你也许更愿意以手动方式或通过其他工具（而非自动生成存储过程的 TableAdapter 向导）来创建存储过程。

在本教程中，我们将了解如何将 TableAdapter 配置为使用现有的存储过程。由于 Northwind 数据库中包含的内置存储过程很少，因此我们也将学习如何在 Visual Studio 环境中以手动方式向数据库添加新存储过程。让我们开始吧！

注意： 在教程在事务中封装数据库修改中，我们向TableAdapter 添加了一些支持事务（如 BeginTransaction 、CommitTransaction 等）的方法。我们也可以在存储过程中管理事务，不需要修改任何数据访问层代码。此外，在本教程中，我们还将研究 T-SQL 命令，这些命令用于在事务范围内执行存储过程的语句。

步骤1 ：向Northwind 数据库添加存储过程

我们很容易在Visual Studio 中向数据库添加新存储过程。下面，让我们向 Northwind 数据库添加一个新存储过程，使该存储过程返回 Products 表中含有特定 CategoryID 值的所有列。在 Server Explorer 窗口中展开 Northwind 数据库，使屏幕显示该数据库的 Database Diagrams 、 Tables 和 Views 等文件夹。正如我们在前面教程中看到的那样， Stored Procedures 文件夹中含有数据库的现有存储过程。要添加新存储过程，只需以右键单击 Stored Procedures 文件夹，然后从上下文菜单中选择 Add New Stored Procedure 选项。

图1 ：右键单击 Stored Procedures 文件夹，添加一个新存储过程

如图1 所示，选择 Add New Stored Procedure 选项后，Visual Studio 打开一个脚本窗口，其中含有创建存储过程所需的 SQL 脚本的轮廓。在充实并执行该脚本后，我们就为数据库添加了一个新的存储过程。

输入以下脚本：

CREATE PROCEDURE dbo.Products_SelectByCategoryID  
( 
    @CategoryID int 
) 
AS 
 
SELECT ProductID, ProductName, SupplierID, CategoryID,  
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,  
       ReorderLevel, Discontinued 
FROM Products 
WHERE CategoryID = @CategoryID

执行此脚本会为 Northwind 数据库添加一个名为 Products_SelectByCategoryID 的存储过程。该存储过程只接受一个输入参数（@CategoryID ，类型为int ），返回的结果是含有与 CategoryID 相匹配的值的所有产品字段。

要执行CREATE PROCEDURE 脚本并向数据库添加存储过程，单击工具栏上的 Save 图标或按下 Ctrl+S 。之后， Visual Studio 刷新 Stored Procedures 文件夹，显示新创建的存储过程。同时，窗口中的脚本也从 “CREATE PROCEDURE dbo.Products_SelectProductByCategoryID” 更改为 “ALTER PROCEDURE dbo.Products_SelectProductByCategoryID” 。 CREATE PROCEDURE 向数据库添加新存储过程，而 ALTER PROCEDURE 用于更新现有存储过程。由于脚本的开头部分已更改为 ALTER PROCEDURE ，通过更改存储过程输入参数（或 SQL 语句）并单击 Save 图标，我们即可完成对存储过程的更新。

图 2 显示了保存 Products_SelectByCategoryID 存储过程之后， Visual Studio 的界面。

图2 ：已将存储过程 Products_SelectByCategoryID 添加到数据库

步骤2 ：配置TableAdapter 使用现有存储过程

由于我们已将Products_SelectByCategoryID 存储过程添加到数据库，我们可以配置数据访问层，使数据访问层在它的方法被调用时使用该存储过程。我们特别为 NorthwindWithSprocs 强类型 DataSet 中的 ProductsTableAdapter 添加了一个 GetProducstByCategoryID(categoryID) 方法，该方法调用我们刚刚创建的 Products_SelectByCategoryID 存储过程。

首先，打开NorthwindWithSprocs DataSet 。右键单击ProductsTableAdapter ，选择 Add Query 来启动TableAdapter Query Configuration 向导。在前一篇教程中，我们让 TableAdapter 为我们创建了一个新存储过程。然而，在本教程中，我们希望将新的 TableAdapter 方法与现有的 Products_SelectByCategoryID 存储过程关联起来。因此，我们在向导的第一步中选择 “Use existing stored procedure” 选项，然后单击 Next 。

图3 ：选择 “Use existing stored procedure” 选项

以下屏幕中显示了一个下拉列表，该列表可提供数据库现有的存储过程。选中一个存储过程后，屏幕左侧列出其输入参数，右侧列出返回的数据字段（如果有）。从列表中选择 Products_SelectByCategoryID 存储过程，然后单击 Next 。

图4 ：选择 Products_SelectByCategoryID 存储过程

接下来的屏幕询问我们存储过程返回哪种类型的数据，我们的答案将确定由 TableAdapter 的方法返回的数据类型。例如，如果我们指定返回表格数据， TableAdapter 方法将返回由存储过程返回的记录构成的 ProductsDataTable 实例。而如果我们指定存储过程返回一个值， TableAdapter 将返回一个对象，该对象的值是由存储过程返回的第一条记录的第一列中的值。

由于Products_SelectByCategoryID 存储过程返回属于特定类别的所有产品，因此我们选择第一项 “Tabular data” ，然后单击Next 。

图5 ：指定存储过程返回表格数据

余下的任务是指定方法名称和方法模式。选中 “Fill a DataTable” 和 “Return a DataTable” 选项，将这两种方法重新命名为 FillByCategoryID 和 GetProductsByCategoryID 。然后单击 Next ，查看向导执行的任务汇总。如果确认无误，单击 Finish 。

图6 ：将方法命名为 FillByCategoryID 和 GetProductsByCategoryID

注意： 我们刚创建的 TableAdapter 方法 FillByCategoryID 和 GetProductsByCategoryID 需要整数类型的输入参数。输入参数值通过@CategoryID 参数传递到存储过程。如果要修改 Products_SelectByCategory 存储过程的参数，你也需要更新这些 TableAdapter 方法的参数。正如在前一篇教程中讨论的那样，更新参数的方法有两种：手动添加或删除参数集中的参数或重新运行 TableAdapter 向导。

步骤3 ：向 BLL 添加GetProductsByCategoryID(categoryID) 方法

配置完数据访问层的 GetProductsByCategoryID 方法后，下一步我们将在业务逻辑层提供对该方法的访问。打开 ProductsBLLWithSprocs 类文件，添加以下方法：

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

该 BLL 方法仅返回由 ProductsTableAdapter 的 GetProductsByCategoryID 方法返回的 ProductsDataTable 。DataObjectMethodAttribute 属性提供由 ObjectDataSource 的 Configure Data Source 向导使用的元数据。此外，该方法将显示在 SELECT 选项卡的下拉列表中。

步骤4 ：根据类别显示产品

为验证新添加的 Products_SelectByCategoryID 存储过程和相应的 DAL 和 BLL 方法，让我们创建一个含有 DropDownList 和 GridView 的 ASP.NET 页面。 DropDownList 将显示数据库中的所有类别，而 GridView 将显示属于选定类别的产品。

注意： 在前面的教程中，我们已经使用DropDownLists 创建了主/ 明细界面。有关实现主 / 明细报表的详细信息，参见使用 DropDownList 的主/明细筛选教程。

打开AdvancedDAL 文件夹中的 ExistingSprocs.aspx 页面，将DropDownList 从工具箱拖放到 Designer 。将 DropDownList 的 ID 属性设置为 Categories ，将 AutoPostBack 属性设置为 True 。然后，从智能标记中，将DropDownList 绑定到一个名为 CategoriesDataSource 的新ObjectDataSource 。配置 ObjectDataSource ，使其从 CategoriesBLL 类的 GetCategories 方法获取数据。将 UPDATE 、 INSERT 和 DELETE 选项卡中的下拉列表设置为 “(None)” 。

图7 ：从 CategoriesBLL 类的 GetCategories 方法获取数据

图8 ：将 UPDATE 、INSERT 和 DELETE 选项卡中的下拉列表设置为 “(None)”

完成ObjectDataSource 向导后，配置 DropDownList ，使其显示CategoryName 数据字段，并使用 CategoryID 字段作为每个 ListItem 的 Value 值。

此时，DropDownList 与 ObjectDataSource 的声明式标记应如下所示：

<asp:DropDownList ID="Categories" runat="server" AutoPostBack="True"  
    DataSourceID="CategoriesDataSource" DataTextField="CategoryName"  
    DataValueField="CategoryID"> 
</asp:DropDownList> 
 
<asp:ObjectDataSource ID="CategoriesDataSource" runat="server" 
    OldValuesParameterFormatString="original_{0}"  
    SelectMethod="GetCategories" TypeName="CategoriesBLL"> 
</asp:ObjectDataSource>

然后，将一个GridView 拖放到 Designer 上，使其位于DropDownList 的下方。将 GridView 的ID 设为 ProductsByCategory ，并从智能标记中将 GridView 绑定到名为 ProductsByCategoryDataSource 的新ObjectDataSource 。配置 ProductsByCategoryDataSource ObjectDataSource 使用 ProductsBLLWithSprocs 类，使其使用 GetProductsByCategoryID(categoryID) 方法获取数据。由于此 GridView 仅用于显示数据，我们将 UPDATE 、 INSERT 和 DELETE 选项卡中的下拉列表设置为 “(None)” ，然后单击 Next 。

图9 ：配置 ObjectDataSource 使用 ProductsBLLWithSprocs 类

图10 ：从 GetProductsByCategoryID(categoryID) 方法获取数据

在SELECT 选项卡中选择的方法需要一个参数，因此向导的最后一个步骤提示我们选择参数的来源。将 Parameter source 下拉列表设为 Control ，并从 ControlID 下拉列表中选择 Categories 。单击Finish 完成向导。

图11 ：使用 Categories DropDownList 作为 categoryID 参数的来源

完成ObjectDataSource 向导后，Visual Studio 会为每个产品数据字段添加 BoundField 和一个 CheckBoxField 。你可以尽情定制这些字段的外观。

在浏览器中访问该页面。在访问该页面时，选中的类别是Beverages ，屏幕中显示了相关的产品。将下拉列表中的选项更改为其它类别（如图 12 所示）将导致页面回发，页面将重新加载新选中类别的相关产品。

图12 ：页面显示 Produce 类别的产品

步骤5：使用事务封装存储过程的语句

在事务中封装数据库修改教程中，我们讨论了如何使用一个事务执行一系列数据库修改语句。通过事务执行的修改操作或者全部成功，或者全部失败，从而确保了原子性。使用事务的技巧包括：

使用 System.Transactions 命名空间中的类；
使数据访问层使用 ADO.NET 类，例如 SqlTransaction ；
直接在存储过程中添加 T-SQL 事务命令。

使用事务封装数据库修改 教程使用了DAL 层的 ADO.NET 类。在本教程的余下部分中，我们将研究如何在存储过程中使用 T-SQL 命令管理事务。

手动启动、提交和回滚事务的三个主要SQL 命令分别是 BEGIN TRANSACTION 、COMMIT TRANSACTION 和 ROLLBACK TRANSACTION 。同使用 ADO.NET 方法一样，当在存储过程中使用事务时，我们需要应用以下模式：

指出事务已经开启。
执行包含有事务的 SQL 语句。
如果第 2 步的任何一个语句出错，则回滚事务。
如果第 2 步的所有语句执行无误，则提交该事务。

此模式可以使用以下模板在 T-SQL 语法中实现：

BEGIN TRY 
  BEGIN TRANSACTION -- Start the transaction 
 
  ... Perform the SQL statements that makeup the transaction ... 
 
  -- If we reach here, success! 
  COMMIT TRANSACTION 
END TRY 
BEGIN CATCH  
  -- Whoops, there was an error 
  ROLLBACK TRANSACTION 
 
  -- Raise an error with the  
  -- details of the exception    
  DECLARE @ErrMsg nvarchar(4000), 
          @ErrSeverity int  
 
  SELECT @ErrMsg = ERROR_MESSAGE(),  
         @ErrSeverity = ERROR_SEVERITY()  
  
  RAISERROR(@ErrMsg, @ErrSeverity, 1)  
END CATCH

该模板以定义一个 TRY...CATCH 块开始，这是 SQL Server 2005 新增的构造。与 C# 中的 try...catch 块类似，SQL TRY...CATCH 块执行 TRY 块中的语句。如果任何语句出错，则控制立即转至 CATCH 块。

如果构成事务的 SQL 语句执行无误，则 COMMIT TRANSACTION 语句提交更改并完成事务。然而，如果一条语句出错，CATCH 块中的 ROLLBACK TRANSACTION 将数据库返回到开始事务之前的状态。存储过程也会使用 RAISERROR 命令产生一条错误，导致应用程序生成 SqlException 。

注意： 由于 TRY...CATCH 块是 SQL Server 2005 的新增构造，如果使用旧版本的 Microsoft SQL Server ，以上模板无法运行。如果数据库的版本低于 SQL Server 2005 ，参考在 SQL Server 存储过程中管理事务，获得可在 SQL Server 的其他版本中使用的模板。.

让我们来看一个具体的示例。Categories 和 Products 表之间存在一个外键约束，这表示Products 表中的每个 CategoryID 字段都必须映射到 Categories 表中的 CategoryID 值。任何违背此约束的操作（试图删除拥有相关产品的某个类别）将出现外键约束违例。为了验证这一点，我们可以访问 Working with Binary Data 部分的 Updating and Deleting Existing Binary Data 示例 (~/BinaryData/UpdatingAndDeleting.aspx) 。此页面列出了系统中的所有类别，每一行都具有 Edit 和 Delete 按钮（参见图 13 ），但如果你试图删除一个拥有相关产品的类别（如 Beverages ），该操作会因外键约束违例而失败（参见图 14 ）。

图13 ：GridView 中的每个 Category 都具有Edit 和 Delete 按钮

图14 ：无法删除拥有对应产品的 Category

假设我们希望在删除类别时不考虑该类别是否拥有对应的产品，或者希望在删除类别的同时也删除该类别拥有的产品（尽管我们也可以简单地将产品的 CategoryID 值设为 NULL ）。该功能可通过外键约束的串接规则实现。或者，我们可以创建一个接受 @CategoryID 输入参数的存储过程，调用该存储过程将删除所有相关产品以及指定的类别。

我们的第一次尝试是创建如下所示的存储过程：

CREATE PROCEDURE dbo.Categories_Delete 
( 
    @CategoryID int 
) 
AS 
 
-- First, delete the associated products... 
DELETE FROM Products 
WHERE CategoryID = @CategoryID 
 
-- Now delete the category 
DELETE FROM Categories 
WHERE CategoryID = @CategoryID

尽管该存储过程会删除相关的产品和类别，但这些操作不在事务中执行。假设还存在基于类别的其他外键约束，该外键约束禁止删除特定的 @CategoryID 值。那么问题在于，上述存储过程会在删除类别之前删除该类别的所有产品。最终结果是，对于该类别，上述存储过程会删除它的所有产品，而该类别因在其它表格中存在相关记录而仍然保留在数据库中。

如果该存储过程封装在一个事务中，那么对 Categories 的删除失败将导致对 Products 表的删除操作回滚。以下存储过程脚本使用了事务来确保两个 DELETE 语句间的原子性：

CREATE PROCEDURE dbo.Categories_Delete 
( 
    @CategoryID int 
) 
AS 
 
BEGIN TRY 
  BEGIN TRANSACTION -- Start the transaction 
 
  -- First, delete the associated products... 
  DELETE FROM Products 
  WHERE CategoryID = @CategoryID 
 
 
  -- Now delete the category 
  DELETE FROM Categories 
  WHERE CategoryID = @CategoryID 
 
  -- If we reach here, success! 
  COMMIT TRANSACTION 
END TRY 
BEGIN CATCH  
  -- Whoops, there was an error 
  ROLLBACK TRANSACTION 
 
  -- Raise an error with the  
  -- details of the exception    
  DECLARE @ErrMsg nvarchar(4000), 
          @ErrSeverity int  
 
  SELECT @ErrMsg = ERROR_MESSAGE(),  
         @ErrSeverity = ERROR_SEVERITY()  
  
  RAISERROR(@ErrMsg, @ErrSeverity, 1)  
END CATCH

花些时间向Northwind 数据库添加 Categories_Delete 存储过程。要向数据库添加存储过程，请参考步骤1 。

步骤6 ：更新CategoriesTableAdapter

尽管我们已向数据库添加了 Categories_Delete 存储过程，但当前 DAL 层的配置仍是使用 ad-hoc SQL 语句执行删除操作。因此我们需要更新 CategoriesTableAdapter ，使其使用 Categories_Delete 存储过程。

注意： 在本教程前面的部分中，我们处理的是NorthwindWithSprocs DataSet 。但该 DataSet 只有一个实体， ProductsDataTable ，并且我们需要处理类别。因此，在本教程的余下部分中，我在讨论数据访问层时指的是 Northwind DataSet ，即我们在创建数据访问层教程中创建的 DataSet 。

打开Northwind DataSet ，选择 CategoriesTableAdapter ，然后转至 Properties 窗口。Properties 窗口中列出了该 TableAdapter 使用的InsertCommand 、UpdateCommand 、DeleteCommand 和 SelectCommand ，以及它的名称和连接信息。展开 DeleteCommand 属性，查看它的详细信息。如图 15 所示， DeleteCommand 的 ComamndType 属性设置为 Text ，因此它可以将 CommandText 属性中的文本作为 ad-hoc SQL 查询发送。

图15 ：在 Designer 中选择 CategoriesTableAdapter ，在 Properties 窗口中查看其属性信息

要改变这些设置，选择Properties 窗口中的 “(DeleteCommand)” 文本，然后从下拉列表中选择 “(New)” 。这将清除对 CommandText 、 CommandType 和 Parameters 属性的设置。然后，将 CommandType 属性设置为 StoredProcedure ，并为 CommandText 输入存储过程的名称（即 dbo.Categories_Delete ）。如果你按照先设置 CommandType 再设置 CommandText 的顺序输入属性， Visual Studio 会自动将 Parameters 的属性设置为 Collection 。如果没有按照上述顺序设置这些属性，我们则需要通过 Parameters Collection Editor 手动添加参数。无论自动还是手动添加参数，我们都应该单击 Parameters 属性中的省略号按钮，打开 Parameters Collection Editor 验证参数设置更改是否正确（参见图 16 ）。如果对话框中没有参数，则手动添加 @CategoryID 参数（我们无需添加 @RETURN_VALUE 参数）。

图16 ：确保参数设置正确

DAL 更新完成后，删除一个类别的操作将自动删除该类别的所有相关产品，并且这些操作在事务中完成。要验证这一点，返回 Updating and Deleting Existing Binary Data 页面，单击一个类别的 Delete 按钮。只要单击一下鼠标，该类别和它的所有相关产品都被删除。

注意： 在测试 Categories_Delete 存储过程（该存储过程将删除所选类别和该类别的相关产品）之前，最好对数据库进行备份。如果使用的 NORTHWND.MDF 数据库位于 App_Data 中，只需关闭 Visual Studio ，并将 App_Data 中的 MDF 和 LDF 文件复制到其他文件夹中。在测试完该功能后，你可以关闭 Visual Studio ，使用备份的 MDF 和 LDF 文件替换 App_Data 中的当前文件，从而“恢复”数据库。

小结

尽管TableAdapter 的向导可以自动生成存储过程，但有时这些存储过程是现成的，或者用户希望以手动方式或通过其它工具创建存储过程。为适应这些情景，我们也可以将 TableAdapter 配置为指向现有的存储过程。在本教程中，我们研究了如何在 Visual Studio 环境中向数据库手动添加存储过程，以及如何将 TableAdapter 的方法应用到这些存储过程中。我们还研究了用于在存储过程中进行启动、提交和回滚事务的 T-SQL 命令和脚本模式。

快乐编程！

posted @ 2016-05-02 00:33 迅捷之风阅读(244) 评论(0) 收藏举报

刷新页面返回顶部

迅捷之风

对强类型 DataSet 的 TableAdapter 使用现有存储过程67

简介

步骤1 ： 向Northwind 数据库添加存储过程

步骤2 ： 配置TableAdapter 使用现有存储过程

步骤3 ： 向 BLL 添加GetProductsByCategoryID(categoryID) 方法

步骤4 ： 根据类别显示产品