通过

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

  • 项目

作者 ：斯科特·米切尔

下载 PDF

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

介绍

在 前面的教程 中，我们了解了如何将 Typed DataSet s TableAdapters 配置为使用存储过程来访问数据，而不是即席 SQL 语句。 具体而言，我们检查了如何让 TableAdapter 向导自动创建这些存储过程。 将旧应用程序移植到 ASP.NET 2.0 或围绕现有数据模型生成 ASP.NET 2.0 网站时，数据库可能已包含所需的存储过程。 或者，你可能希望手动或通过一些自动生成存储过程的 TableAdapter 向导以外的工具创建存储过程。

本教程介绍如何将 TableAdapter 配置为使用现有存储过程。 由于 Northwind 数据库只有一小部分内置存储过程，因此我们还将查看通过 Visual Studio 环境手动向数据库添加新存储过程所需的步骤。 让我们开始吧！

注意

在 Transaction 教程中的包装数据库修改中，我们向 TableAdapter 添加了支持事务的方法（BeginTransactionCommitTransaction等等）。 或者，可以在存储过程中完全管理事务，这无需修改数据访问层代码。 在本教程中，我们将探讨用于在事务范围内执行存储过程的语句的 T-SQL 命令。

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

Visual Studio 可以轻松地向数据库添加新存储过程。 让我们向 Northwind 数据库添加新存储过程，该存储过程返回 Products 表中具有特定 CategoryID 值的所有列。 在“服务器资源管理器”窗口中，展开 Northwind 数据库，以便显示其文件夹（数据库关系图、表、视图等）。 如上一教程所示，存储过程文件夹包含数据库的现有存储过程。 若要添加新存储过程，只需右键单击“存储过程”文件夹，并从上下文菜单中选择“添加新存储过程”选项。

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

图 1：右键单击存储过程文件夹并添加新存储过程（单击以查看全尺寸图像）

如图 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 数据库的新存储过程添加一个新存储过程。 此存储过程接受单个输入参数（@CategoryID类型）， int并返回具有匹配 CategoryID 值的这些产品的所有字段。

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

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

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

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

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

将存储过程添加到数据库后 Products_SelectByCategoryID ，我们可以配置数据访问层，以在调用其中一种方法时使用此存储过程。 具体而言，我们将向类型化数据集中添加一个GetProductsByCategoryID(categoryID)调用刚创建的存储过程的方法ProductsTableAdapterNorthwindWithSprocs。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单击以查看全尺寸图像）

注意

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

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

完成 DAL 方法后 GetProductsByCategoryID ，下一步是在业务逻辑层中提供对此方法的访问权限。 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 进行大纲/详细信息筛选教程。

打开文件夹中的页面AdvancedDAL，ExistingSprocs.aspx并将一个 DropDownList 从工具箱拖到设计器上。 将 DropDownList s ID 属性设置为Categories及其AutoPostBack属性。true 接下来，从智能标记将 DropDownList 绑定到名为 CategoriesDataSource的新 ObjectDataSource。 配置 ObjectDataSource，以便从 CategoriesBLL 类方法 GetCategories 检索其数据。 将 UPDATE、INSERT 和 DELETE 选项卡中的下拉列表设置为“无”。

从 CategoriesBLL 类获取数据 GetCategories 方法

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

将 UPDATE、INSERT 和 DELETE 选项卡中的下拉列表设置为 （无）

图 8：将 UPDATE、INSERT 和 DELETE 选项卡中的下拉列表设置为 （无） （单击可查看全尺寸图像）

完成 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 拖到设计器上，将其放在 DropDownList 下面。 将 GridView 设置为IDProductsByCategory其智能标记并将其绑定到名为 ProductsByCategoryDataSource 的新 ObjectDataSource。 将 ProductsByCategoryDataSource ObjectDataSource 配置为使用 ProductsBLLWithSprocs 类，使其使用 GetProductsByCategoryID(categoryID) 该方法检索其数据。 由于此 GridView 仅用于显示数据，因此请将 UPDATE、INSERT 和 DELETE 选项卡中的下拉列表设置为“无”，然后单击“下一步”。

将 ObjectDataSource 配置为使用 ProductsBLLWithSprocs 类

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

从 GetProductsByCategoryID（categoryID） 方法检索数据

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

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

使用 Categories DropDownList 作为 categoryID 参数的源

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

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

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

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

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

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

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

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

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

用于手动启动、提交和回滚事务的三个关键 SQL 命令分别为BEGIN TRANSACTIONCOMMIT 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，则上述模板将不起作用。

让我们看看一个具体示例。 表之间存在CategoriesProducts外键约束，这意味着表中的每个CategoryID字段Products都必须映射到CategoryID表中的值Categories。 任何违反此约束的操作（例如尝试删除具有关联产品的类别）都会导致外键约束冲突。 若要验证这一点，请重新访问“使用二进制数据”部分（~/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 值。 问题是，在这种情况下，在尝试删除类别之前，将删除所有产品。 净结果是，对于此类类别，此存储过程将删除其所有产品，而该类别仍保留，因为它仍具有其他表中的相关记录。

但是，如果在事务范围内包装了存储过程，则会在删除失败时回滚对 Products 表的删除 Categories。 以下存储过程脚本使用事务来确保两 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。 但是，DataSet 只有一个实体， ProductsDataTable我们需要处理类别。 因此，对于本教程的其余部分，当我谈到我指的是 Northwind DataSet 的数据访问层时，我们首先在“创建数据访问层”教程中创建 的数据访问层 。

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

在设计器中选择 CategoriesTableAdapter，在“属性”窗口中查看其属性

图 15：在 CategoriesTableAdapter 设计器中选择可在“属性”窗口中查看其属性

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

确保参数设置正确

图 16：确保参数设置正确

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

注意

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

总结

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

快乐编程！

关于作者

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

特别感谢

本教程系列由许多有用的审阅者审阅。 本教程的主要审阅者是希尔顿·吉塞诺、S 任雅各布·劳里森和特蕾莎·墨菲。 有兴趣查看即将发布的 MSDN 文章？ 如果是这样，请把我扔一条线。mitchell@4GuysFromRolla.com

上一页下一页