使用适用于类型化数据集的 TableAdapter 的现有存储过程 (C#)

  • 项目

作者 ：Scott Mitchell

下载 PDF

在上一教程中，我们学习了如何使用 TableAdapter 向导生成新的存储过程。 在本教程中，我们将了解同一 TableAdapter 向导如何处理现有存储过程。 我们还了解如何将新的存储过程手动添加到数据库。

简介

在 前面的教程中 ，我们了解了如何将 Typed DataSet s TableAdapters 配置为使用存储过程来访问数据，而不是临时 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 值的列的所有列。 在“服务器资源管理器”窗口中，展开 Northwind 数据库，以显示其文件夹（数据库关系图、表、视图等）。 正如我们在前面的教程中看到的，“存储过程”文件夹包含数据库的现有存储过程。 若要添加新存储过程，只需右键单击“存储过程”文件夹，然后从上下文菜单中选择“添加新存储过程”选项。

右键单击“存储过程”文件夹并添加新存储过程

图 1：Right-Click“存储过程文件夹”和“添加新存储过程” (单击以查看全尺寸图像)

如图 1 所示，选择“添加新存储过程”选项会在 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

执行此脚本时，会将新的存储过程添加到名为 的 Products_SelectByCategoryIDNorthwind 数据库。 此存储过程接受类型为) 的单个输入参数 (@CategoryIDint ，并返回具有匹配CategoryID值的产品的所有字段。

若要执行此 CREATE PROCEDURE 脚本并将存储过程添加到数据库，请单击工具栏中的“保存”图标或按 Ctrl+S。 执行此操作后，“存储过程”文件夹将刷新，显示新建的存储过程。 此外，窗口中的脚本会将细微之处从 CREATE PROCEDURE dbo.Products_SelectProductByCategoryID 更改为 ALTER PROCEDUREdbo.Products_SelectProductByCategoryID。 CREATE PROCEDURE 向数据库添加新存储过程，同时 ALTER PROCEDURE 更新现有存储过程。 由于脚本的开头已更改为 ALTER PROCEDURE，因此更改存储过程输入参数或 SQL 语句并单击“保存”图标将使用这些更改更新存储过程。

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

存储过程Products_SelectByCategoryID已添加到数据库

图 2： Products_SelectByCategoryID 存储过程已添加到数据库 (单击以查看全尺寸图像)

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

Products_SelectByCategoryID现在，存储过程已添加到数据库，我们可以将数据访问层配置为在调用其中一个存储过程的方法时使用此存储过程。 具体而言，我们将向类型化数据集中的 NorthwindWithSprocs 添加一个GetProductsByCategoryID(categoryID)方法ProductsTableAdapter，用于调用Products_SelectByCategoryID我们刚刚创建的存储过程。

首先打开 NorthwindWithSprocs 数据集。 右键单击 ProductsTableAdapter 并选择“添加查询”以启动 TableAdapter 查询配置向导。 在 前面的教程中 ，我们选择让 TableAdapter 为我们创建新的存储过程。 但是，在本教程中，我们希望将新的 TableAdapter 方法连接到现有 Products_SelectByCategoryID 存储过程。 因此，从向导的第一个步骤中选择“使用现有存储过程”选项，然后单击“下一步”。

选择“使用现有存储过程”选项

图 3：选择“使用现有存储过程”选项 (单击以查看全尺寸图像)

以下屏幕提供了一个使用数据库存储过程填充的下拉列表。 选择存储过程将在左侧列出其输入参数，如果右侧有任何) ，则返回的数据字段 (。 Products_SelectByCategoryID从列表中选择存储过程，然后单击“下一步”。

选取Products_SelectByCategoryID存储过程

图 4：选择 Products_SelectByCategoryID “存储过程” (单击以查看全尺寸图像)

下一个屏幕询问存储过程返回的数据类型，此处的答案确定 TableAdapter 方法返回的类型。 例如，如果指示返回表格数据，该方法将返回一个 ProductsDataTable 实例，该实例填充了存储过程返回的记录。 相反，如果我们指示此存储过程返回单个值，则 TableAdapter 将返回一个 object ，该值在存储过程返回的第一条记录的第一列中分配了值。

Products_SelectByCategoryID由于存储过程返回属于特定类别的所有产品，请选择第一个答案 -“表格数据”，然后单击“下一步”。

指示存储过程返回表格数据

图 5：指示存储过程返回表格数据 (单击以查看全尺寸图像)

剩下的只是指示要使用的方法模式，后跟这些方法的名称。 选中“填充数据表”和“返回数据表”选项，但将方法重命名为 FillByCategoryID 和 GetProductsByCategoryID。 然后单击“下一步”查看向导将执行的任务摘要。 如果一切正常，请单击“完成”。

将方法命名为 FillByCategoryID 和 GetProductsByCategoryID

图 6：命名方法 FillByCategoryID 并 GetProductsByCategoryID (单击以查看全尺寸图像)

注意

刚刚创建的 FillByCategoryID TableAdapter 方法和 GetProductsByCategoryID需要类型的 int输入参数。 此输入参数值通过其 @CategoryID 参数传递到存储过程中。 如果修改 Products_SelectByCategory 存储过程的参数，则还需要更新这些 TableAdapter 方法的参数。 如 上一教程中所述，可通过以下两种方式之一完成此操作：手动添加或删除参数集合中的参数，或重新运行 TableAdapter 向导。

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

GetProductsByCategoryID DAL 方法完成后，下一步是在业务逻辑层中提供对此方法的访问权限。 ProductsBLLWithSprocs打开 类文件并添加以下方法：

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

此 BLL 方法只返回ProductsDataTable从 s GetProductsByCategoryID 方法返回的 ProductsTableAdapter 。 属性 DataObjectMethodAttribute 提供 ObjectDataSource 的“配置数据源”向导使用的元数据。 具体而言，此方法将显示在 SELECT 选项卡下拉列表中。

步骤 4：按类别显示产品

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

注意

在前面的教程中，我们已使用 DropDownLists 创建了主接口/详细信息接口。 若要更深入地了解如何实现此类大纲/详细信息报表，请参阅 使用 DropDownList 筛选母版/详细信息 教程。

ExistingSprocs.aspx打开 文件夹中的页面AdvancedDAL，并将“工具箱”中的 DropDownList 拖到Designer。 将 DropDownList 的 ID 属性设置为 Categories ，将其 AutoPostBack 属性设置为 true。 接下来，在其智能标记中，将 DropDownList 绑定到名为 CategoriesDataSource的新 ObjectDataSource。 配置 ObjectDataSource，以便它从 CategoriesBLL 类方法 GetCategories 检索其数据。 将“更新”、“插入”和“删除”选项卡中的下拉列表设置为 (“无”) 。

从 CategoriesBLL 类 GetCategories 方法检索数据

图 7：从 CategoriesBLL 类中 GetCategories 检索数据 方法 (单击以查看全尺寸图像)

将“更新”、“插入”和“删除”选项卡中的 Drop-Down Lists 设置为“ (None”)

图 8：将“更新”、“插入”和“删除”选项卡中的 Drop-Down Lists 设置为“ (无”) (单击以查看全尺寸图像)

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

此时，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 ，并从其智能标记将其绑定到名为 ProductsByCategoryDataSource的新 ObjectDataSource。 将 ProductsByCategoryDataSource ObjectDataSource 配置为使用 ProductsBLLWithSprocs 类，使其使用 GetProductsByCategoryID(categoryID) 方法检索其数据。 由于此 GridView 仅用于显示数据，因此请将“更新”、“插入”和“删除”选项卡中的下拉列表设置为 (“无”) 并单击“下一步”。

将 ObjectDataSource 配置为使用 ProductsBLLWithSprocs 类

图 9：将 ObjectDataSource 配置为使用 ProductsBLLWithSprocs 类 (单击以查看全尺寸图像)

从 GetProductsByCategoryID (categoryID) 方法检索数据

图 10：从 GetProductsByCategoryID(categoryID) 方法中检索数据 (单击以查看全尺寸图像)

在 SELECT 选项卡中选择的方法需要参数，因此向导的最后一步会提示我们输入参数源。 将“参数源”下拉列表设置为“控件”， Categories 然后从“ControlID”下拉列表中选择控件。 单击“完成”以完成向导。

使用 Categories DropDownList 作为 categoryID 参数的源

图 11：使用 Categories DropDownList 作为参数的源 categoryID (单击以查看全尺寸图像)

完成 ObjectDataSource 向导后，Visual Studio 将为每个产品数据字段添加 BoundFields 和 CheckBoxField。 根据需要随意自定义这些字段。

通过浏览器访问页面。 访问页面时，已选择“饮料”类别，并在网格中列出相应的产品。 如图 12 所示，将下拉列表更改为备用类别会导致回发，并使用新选择类别的积重新加载网格。

显示“产品类别”中的“产品”

图 12：“产品类别中的产品”显示 (单击以查看全尺寸图像)

步骤 5：在事务范围内包装存储过程 语句

在 事务中包装数据库修改 教程中，我们讨论了在事务范围内执行一系列数据库修改语句的技术。 回想一下，在事务的保护下执行的修改要么全部成功，要么全部失败，这保证了原子性。 使用事务的技术包括：

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

在 事务中包装数据库修改 教程使用 DAL 中的 ADO.NET 类。 本教程的其余部分介绍如何在存储过程中使用 T-SQL 命令管理事务。

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

  1. 指示事务的开始。
  2. 执行构成事务的 SQL 语句。
  3. 如果步骤 2 中的任何一个语句中存在错误，请回滚事务
  4. 如果步骤 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 年新增的构造。 与 try...catch C# 中的块一样，SQL TRY...CATCH 块执行 块中的 TRY 语句。 如果任何语句引发错误，则会立即将控制权转移到 CATCH 块。

如果执行构成事务的 SQL 语句时没有错误，该 COMMIT TRANSACTION 语句将提交更改并完成事务。 但是，如果其中一个语句导致错误， ROLLBACK TRANSACTION 块中的 CATCH 会将数据库返回到事务开始前的状态。 存储过程还会使用 RAISERROR 命令引发错误，这会导致 SqlException 在应用程序中引发 。

注意

由于 块TRY...CATCH是 SQL Server 2005 中的新增功能，因此，如果使用较旧版本的 Microsoft SQL Server，上述模板将不起作用。

让我们看一个具体示例。 和 表之间存在Categories外键约束，这意味着表中的每个ProductsCategoryID字段都必须映射到CategoryID表中的值Categories。Products 任何违反此约束的操作（例如尝试删除具有关联产品的类别）都会导致外键约束冲突。 若要验证这一点，请重新访问使用二进制数据部分中的更新和删除现有二进制数据示例 (~/BinaryData/UpdatingAndDeleting.aspx) 。 此页面列出了系统中的每个类别以及“编辑”和“删除”按钮， (见图 13) ，但如果尝试删除具有关联产品的类别（如饮料），则删除会因违反外键约束而失败 (请参阅图 14) 。

每个类别都显示在带有“编辑”和“删除”按钮的 GridView 中

图 13：每个类别显示在 GridView 中，其中包含“编辑”和“删除”按钮 (单击以查看全尺寸图像)

不能删除包含现有产品的类别

图 14：不能删除具有现有产品的类别 (单击以查看全尺寸图像)

但试想一下，无论类别是否具有关联的产品，我们都希望允许删除类别。 如果删除包含产品的类别，假设我们还希望删除其现有产品 (但另一个选项是将其产品 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

虽然这肯定会删除关联的产品和类别，但它不会在事务的保护下这样做。 假设上存在一些其他外键约束，该约束 Categories 将禁止删除特定 @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

花点时间将 Categories_Delete 存储过程添加到 Northwind 数据库。 有关将存储过程添加到数据库的说明，请参阅步骤 1。

步骤 6：更新CategoriesTableAdapter

虽然我们已将 Categories_Delete 存储过程添加到数据库，但 DAL 当前配置为使用即席 SQL 语句执行删除。 我们需要更新 并 CategoriesTableAdapter 指示它改用 Categories_Delete 存储过程。

注意

在本教程的前面部分， NorthwindWithSprocs 我们使用的是 DataSet。 但是，该数据集只有一个实体 ProductsDataTable，我们需要使用类别。 因此，在本教程的其余部分，当我谈到数据访问层时，我指的是 Northwind 数据集，即我们在 创建数据访问层 教程中首次创建的数据集。

打开 Northwind DataSet，选择 CategoriesTableAdapter，然后转到属性窗口。 属性窗口列出了 InsertCommandTableAdapter 使用的 、UpdateCommand、 DeleteCommand和 SelectCommand ，以及其名称和连接信息。 DeleteCommand展开 属性以查看其详细信息。 如图 15 所示， DeleteCommand s CommandType 属性设置为 Text，指示它以即席 SQL 查询的形式发送属性中的 CommandText 文本。

在Designer中选择 CategoriesTableAdapter，在“属性”窗口中查看其属性

图 15：在CategoriesTableAdapterDesigner中选择 ，在“属性”窗口中查看其属性

若要更改这些设置，请在属性窗口中选择 (DeleteCommand) 文本，然后从下拉列表中选择“ (新建) ”。 这将清除 、 CommandType和 Parameters 属性的设置CommandText。 接下来，将 CommandType 属性设置为 StoredProcedure ，然后键入 (dbo.Categories_Delete) 存储过程CommandText的名称。 如果确保按此顺序输入属性，则首先 CommandType 输入 ，然后 CommandText 是 - Visual Studio 将自动填充 Parameters 集合。 如果不按此顺序输入这些属性，则必须通过 Parameters 集合编辑器手动添加参数。 无论哪种情况，都需谨慎单击 Parameters 属性中的省略号来调出 Parameters 集合编辑器，以验证是否进行了正确的参数设置更改， (请参阅图 16) 。 如果在对话框中看不到任何参数，请手动添加 @CategoryID 参数， (无需) 添加 @RETURN_VALUE 参数。

确保参数设置正确

图 16：确保参数设置正确

更新 DAL 后，删除类别将自动删除其所有关联的产品，并在事务保护下执行此操作。 若要验证这一点，请返回到“更新和删除现有二进制数据”页，然后单击其中一个类别的“删除”按钮。 单击鼠标一下，将删除类别及其所有关联的产品。

注意

在 Categories_Delete 测试存储过程（将删除大量产品以及所选类别）之前，最好是创建数据库的备份副本。 如果在 中使用 NORTHWND.MDFApp_Data数据库，只需关闭 Visual Studio 并将 MDF App_Data 和 LDF 文件复制到其他文件夹中。 测试功能后，可以通过关闭 Visual Studio 并将 中的 App_Data 当前 MDF 和 LDF 文件替换为备份副本来还原数据库。

总结

虽然 TableAdapter 向导将为我们自动生成存储过程，但有时我们可能已经创建了此类存储过程，或者希望手动或使用其他工具创建它们。 为了适应此类方案，还可以将 TableAdapter 配置为指向现有存储过程。 在本教程中，我们了解了如何通过 Visual Studio 环境手动将存储过程添加到数据库，以及如何将 TableAdapter 方法连接到这些存储过程。 我们还检查了用于从存储过程内启动、提交和回滚事务的 T-SQL 命令和脚本模式。

编程愉快！

关于作者

斯科特·米切尔是七本 ASP/ASP.NET 书籍的作者和 4GuysFromRolla.com 的创始人，自 1998 年以来一直在使用 Microsoft Web 技术。 Scott 担任独立顾问、培训师和作家。 他的最新一本书是 山姆斯在 24 小时内 ASP.NET 2.0。 可以在 上mitchell@4GuysFromRolla.com联系他，也可以通过他的博客（可在 中找到http://ScottOnWriting.NET）。

特别感谢

本教程系列由许多有用的审阅者审阅。 本教程的主要审阅者是希尔顿·吉塞诺、S ren Jacob Lauritsen 和 Teresa Murphy。 有兴趣查看我即将发布的 MSDN 文章？ 如果是，请在 处mitchell@4GuysFromRolla.com放置一行。

上一页下一页