为强类型 DataSet 的 TableAdapter 创建新的存储过程66

简介

这些教程的数据访问层 (DAL) 使用的是强类型 DataSet 。在创建数据访问层教程中讲过，强类型 DataSet 由强类型 DataTable 和 TableAdapter 构成。 DataTable 代表系统中的逻辑实体，而 TableAdapter 与底层数据库接口来进行数据访问。这包括为 DataTable 填充数据、执行返回标量数据的查询以及在数据库中插入、更新和删除记录。

TableAdapter 执行的 SQL 命令要么是 ad-hoc SQL 语句，比如SELECT columnList FROM TableName ，要么是存储过程。我们架构中的 TableAdapter 使用的是 ad-hoc SQL 语句。然而，很多开发人员和数据库管理员基于安全性、可维护性和可修改性方面的考虑，更喜欢使用存储过程而不是 ad-hoc SQL 语句。不过也有人出于灵活性的考虑更喜欢使用 ad-hoc SQL 语句。就我而言，与 ad-hoc SQL 语句相比，我更偏爱存储过程，而在前面的教程中选用 ad-hoc SQL 语句是出于简化教程的目的。

定义一个 TableAdapter 或添加新方法时，通过 TableAdapter 的向导，我们可以轻松地创建新的存储过程或使用现有的存储过程，这与通过该向导来使用 ad-hoc SQL 语句的情形是一样的。在本教程中，我们将探讨怎样使用 TableAdapter 的向导自动地生成存储过程。在下一篇教程中，我们将探讨怎样配置 TableAdapter 的方法，使其使用现有的或手动创建的存储过程。

注意： 请参阅 Rob Howard 的博客文章目前不要使用存储过程？和 Frans Bouma 的博客文章存储过程不好，这对吗？，其中热议了使用存储过程还是使用 ad-hoc SQL 语句的问题。

存储过程基础

函数是所有编程语言通用的结构。函数是由一系列语句组成的，调用该函数时就执行这些语句。函数可接受输入参数，可能返回一个值。 存储过程 是数据库中的结构，与编程语言中的函数有很多类似之处。一个存储过程由一组 T-SQL 语句组成，调用该存储过程时执行这些语句。存储过程可以接受 0 到多个输入参数，可返回标量值、输出参数、或者，也是最常见的， SELECT 查询的结果集。

注意： 存储过程常常被称作 “sprocs” 或 “SPs” 。

可以使用 CREATE PROCEDURE 这个 T-SQL 语句来创建存储过程。例如，下面的 T-SQL 脚本创建一个名为GetProductsByCategoryID 的存储过程，它有一个名为@CategoryID 的输入参数，返回 Products 表中其 CategoryID 值等于输入参数值的那些记录的ProductID 、ProductName 、UnitPrice 和 Discontinued 字段。

CREATE PROCEDURE GetProductsByCategoryID 
( 
    @CategoryID int 
) 
AS 
 
SELECT ProductID, ProductName, UnitPrice, Discontinued 
FROM Products 
WHERE CategoryID = @CategoryID

创建了该存储过程后，可以使用下面的语句来调用它：

EXEC GetProductsByCategory categoryID

注意： 在下一篇教程中，我们将探讨通过 Visual Studio IDE 创建存储过程。不过在本教程中，我们将通过 TableAdapter 向导自动地生成存储过程。

除了简单地返回数据外，还可以使用存储过程执行一个事务范围内的多条数据库命令。例如，一个名为 DeleteCategory 的存储过程，接收一个 @CategoryID 参数，执行两条 DELETE 语句：第一条语句删除相关的产品，第二条语句删除指定的分类。存储过程中的多条语句不是自动封装在一个事务中的。我们需要添加额外的 T-SQL 命令以确保系统将存储过程的多个命令当作一个原子操作来处理。我们将在后面的教程中探讨如何将存储过程的命令封装在一个事务中。

在架构中使用存储过程时，数据访问层的方法调用特定的存储过程而不是发出 ad-hoc SQL 语句。这使要执行的 SQL 语句集中在同一位置（在数据库上），而不是在应用程序的架构中进行定义。这样一来，我们可容易地找到、分析和调整查询，并可以更清楚地看到以何种方式使用了数据库的哪个部分。

有关存储过程基本原理的更多信息，请参考本教程结束部分的更多阅读中的资料。

步骤1 ：创建高级数据访问层场景 Web 页面

后面我们将讲述怎样创建一个使用存储过程的DAL ，在此之前，我们花点时间在我们的网站项目中创建一些ASP.NET 页面，本教程及后面几个教程将会用到这些页面。首先，添加一个新的名为 AdvancedDAL 的文件夹。接下来，将以下 ASP.NET 页面添加到该文件夹，并确保每个页面都与 Site.master 母版页相关联：

Default.aspx
NewSprocs.aspx
ExistingSprocs.aspx
JOINs.aspx
AddingColumns.aspx
ComputedColumns.aspx
EncryptingConfigSections.aspx
ManagedFunctionsAndSprocs.aspx

图1 ：添加高级数据访问层场景教程所需的 ASP.NET 页面

和其它文件夹中的 Default.aspx 一样，AdvancedDAL 文件夹中的Default.aspx 将列出本部分中的所有教程。回想一下， SectionLevelTutorialListing.ascx 用户控件将提供本功能。因此，我们需要将此用户控件从 Solution Explorer 拖放到该页面的 Design 视图，从而将其添加到 Default.aspx 。

图2 ：将SectionLevelTutorialListing.ascx 用户控件添加至 Default.aspx

最后，将这些页面作为条目添加到 Web.sitemap 文件中。具体地，将下面的标记代码添加到 “Working with Batched Data” <siteMapNode> 的后面：

<siteMapNode url="~/AdvancedDAL/Default.aspx"  
    title="Advanced DAL Scenarios"  
    description="Explore a number of advanced Data Access Layer scenarios."> 
     
    <siteMapNode url="~/AdvancedDAL/NewSprocs.aspx"  
        title="Creating New Stored Procedures for TableAdapters"  
        description="Learn how to have the TableAdapter wizard automatically  
            create and use stored procedures." /> 
    <siteMapNode url="~/AdvancedDAL/ExistingSprocs.aspx"  
        title="Using Existing Stored Procedures for TableAdapters"  
        description="See how to plug existing stored procedures into a  
            TableAdapter." /> 
    <siteMapNode url="~/AdvancedDAL/JOINs.aspx"  
        title="Returning Data Using JOINs"  
        description="Learn how to augment your DataTables to work with data  
            returned from multiple tables via a JOIN query." /> 
    <siteMapNode url="~/AdvancedDAL/AddingColumns.aspx"  
        title="Adding DataColumns to a DataTable"  
        description="Master adding new columns to an existing DataTable." /> 
    <siteMapNode url="~/AdvancedDAL/ComputedColumns.aspx"  
        title="Working with Computed Columns"  
        description="Explore how to work with computed columns when using  
            Typed DataSets." /> 
    <siteMapNode url="~/AdvancedDAL/EncryptingConfigSections.aspx"  
        title="Protected Connection Strings in Web.config"  
        description="Protect your connection string information in  
            Web.config using encryption." /> 
    <siteMapNode url="~/AdvancedDAL/ManagedFunctionsAndSprocs.aspx"  
        title="Creating Managed SQL Functions and Stored Procedures"  
        description="See how to create SQL functions and stored procedures  
            using managed code." /> 
</siteMapNode>

更新Web.sitemap 后，花点时间通过浏览器查看教程网站。左边的菜单现在包含了高级 DAL 场景教程的条目。

图3 ：站点地图现在包含高级 DAL 场景教程的条目。

骤2 ：配置TableAdapter 创建新的存储过程

为了演示怎样创建使用存储过程而不是ad-hoc SQL 语句的数据访问层，我们在 ~/App_Code/DAL 文件夹中创建一个新的名为 NorthwindWithSprocs.xsd 的强类型 DataSet 。由于我们在前面的教程中逐步详细讨论了这个过程，在此我们一笔带过。如果您有疑问或想知道创建和配置强类型 DataSet 的逐步详细过程，请参阅创建数据访问层教程。

在项目中添加一个新的 DataSet ：右键单击 DAL 文件夹，选择 Add New Item ，选择图 4 所示的 DataSet 模板。

图4 ：将一个新的名为 NorthwindWithSprocs.xsd 的强类型 DataSet 添加到项目中

这样会创建一个新的强类型 DataSet 。打开它的设计器，创建一个新的 TableAdapter ，启动 TableAdapter Configuration Wizard 。 TableAdapter Configuration Wizard 的第一步是让我们选择要使用的数据库。在下拉列表中会列出一个到 Northwind 数据库的连接字符串。选中它后单击 Next 。

在下一屏幕中，我们可以选择该 TableAdapter 访问数据库的方式。在前面的教程中，我们选择了第一个选项：“Use SQL statements” 。对于本教程，选择第二个选项， “Create new stored procedures” ，单击 Next 。

图5 ：指示 TableAdpater 创建新的存储过程

和使用ad-hoc SQL 语句一样，在下一步骤中，我们要提供TableAdapter 主查询的 SELECT 语句。但是， TableAdapter 的向导在这里不是使用输入的 SELECT 语句来直接完成一个 ad-hoc 查询，而是会创建一个存储过程来包含该 SELECT 查询。

该 TableAdapter 使用如下的 SELECT 查询：

SELECT ProductID, ProductName, SupplierID, CategoryID,  
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,  
       ReorderLevel, Discontinued 
FROM Products

图6 ：输入 SELECT 查询

注意： 上面的查询与Northwind 强类型 DataSet 中 ProductsTableAdapter 的主查询稍有不同。我们知道，Northwind 强类型 DataSet 的 ProductsTableAdapter 有两个相关的子查询，返回每个产品的分类和供应商的名称。在稍后的更新 TableAdapter 来使用 JOIN 教程中，我们会将该相关数据添加给此 TableAdapter 。

我们花点时间来看看 “Advanced Options” ，单击此按钮。在这里，我们可以指定是否让向导生成 TableAdapter 的插入、更新和删除语句，是否使用开放式并发，以及在插入和更新后是否应刷新数据表。默认情况下， “Generate Insert, Update and Delete statements” 选项是选中的。保持该选中状态。 "Use optimistic concurrency” 选项是不选中的，对于本教程，保持该不选中状态。

由 TableAdapter 向导自动创建存储过程时， “Refresh the data table” 选项将被忽略。不管是否选中该复选框，最终的插入和更新存储过程都会提取刚插入或刚更新的记录，我们将在步骤 3 中看到这点。

图7 ：保持 “Generate Insert, Update and Delete statements” 选项为选中状态

注意： 如果选中了 “Use optimistic concurrency” 选项，向导会在WHERE 子句中添加额外的条件，从而在其它字段有改动时阻止数据更新。有关使用 TableAdapter 内置的开放式并发控制功能的详细信息，请参阅实现并发优化教程。

输入SELECT 查询、确认选中了 “Generate Insert, Update and Delete statements” 选项后，单击 Next 。下一屏幕中，如图 8 所示，向导提示我们输入即将创建的数据选择、插入、更新和删除存储过程的名称。将这些存储过程的名称更改为 Products_Select 、 Products_Insert 、 Products_Update 和 Products_Delete 。

图8 ：重命名存储过程

要查看TableAdapter 向导创建这四个存储过程所使用的 T-SQL ，单击“Preview SQL Script” 按钮。在 Preview SQL Script 对话框中，您可以将脚本保存到一个文件或复制到剪贴板。

图9 ：预览生成存储过程所使用的 SQL 脚本

命名这些存储过程后，单击Next ，对该 TableAdapter 的相应方法进行命名。和使用ad-hoc SQL 语句时一样，我们可创建一些方法来填充一个现有的DataTable 或返回一个新的 DataTable 。我们也可指定该 TableAdapter 是否采用 DB-Direct 模式来插入、更新和删除记录。保持所有三个复选框为选中状态，不过要将 Return a DataTable 方法重命名为 GetProducts （如图 10 所示）。

图10 ：将方法命名为 Fill 和 GetProducts

单击Next ，查看向导将执行的一系列步骤的汇总。单击 Finish 按钮完成向导。一旦完成向导后，将返回到 DataSet 的设计器，此时它会包含 ProductsDataTable 。

图11 ：DataSet 的设计器显示新添加的 ProductsDataTable

步骤3 ：探讨刚创建的存储过程

步骤2 中使用 TableAdapter 向导自动创建了选择、插入、更新和删除数据的存储过程。我们可通过 Visual Studio 来查看或修改这些存储过程：进入 Server Explorer ，向下展开到数据库的 Stored Procedures 文件夹。如图 12 所示， Northwind 数据库有四个新的存储过程： Products_Delete 、 Products_Insert 、 Products_Select 和 Products_Update 。

图12 ：在数据库的 Stored Procedures 文件夹中可看到步骤 2 中创建的四个存储过程

注意： 如果看不到 Server Explorer ，进入 View 菜单并选择 Server Explorer 选项。如果看不到步骤 2 中创建的与产品有关的存储过程，右键单击 Stored Procedures 文件夹，选择 Refresh 。

要查看或修改某个存储过程，在Server Explorer 中双击其名称，或右键单击该存储过程并选择 Open 。图 13 显示的是打开 Products_Delete 存储过程的画面。

图13 ：可在Visual Studio 中打开并修改存储过程

Products_Delete 和 Products_Select 存储过程的内容很好理解。而Products_Insert 和 Products_Update 存储过程则值得细看，因为这两个过程在 INSERT 和 UPDATE 语句后都要执行 SELECT 语句。例如， Products_Insert 存储过程由以下 SQL 代码构成：

ALTER PROCEDURE dbo.Products_Insert 
( 
    @ProductName nvarchar(40), 
    @SupplierID int, 
    @CategoryID int, 
    @QuantityPerUnit nvarchar(20), 
    @UnitPrice money, 
    @UnitsInStock smallint, 
    @UnitsOnOrder smallint, 
    @ReorderLevel smallint, 
    @Discontinued bit 
) 
AS 
    SET NOCOUNT OFF; 
INSERT INTO [Products] ([ProductName], [SupplierID], [CategoryID], [QuantityPerUnit],  
    [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued])  
VALUES (@ProductName, @SupplierID, @CategoryID, @QuantityPerUnit, @UnitPrice,  
    @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued); 
     
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,  
    UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued  
FROM Products  
WHERE (ProductID = SCOPE_IDENTITY())

在TableAdapter 的向导中指定的 SELECT 查询返回的 Products 列作为该存储过程的输入参数，在INSERT 语句中使用这些输入参数值。在 INSERT 语句的后面，使用一个 SELECT 查询来返回新添加的记录的 Products 列值（包括 ProductID ）。在使用 Batch Update （批处理更新）模式添加一条新记录时，该刷新功能是很有用的，因为它将新添加的 ProductRow 实例的 ProductID 属性更新为数据库赋予的自动递增的值。

下面的代码说明了该功能。该代码创建了基于 NorthwindWithSprocs 强类型 DataSet 的 ProductsTableAdapter 和 ProductsDataTable 。之后，代码创建一个 ProductsRow 实例，对其赋值，调用 TableAdapter 的 Update 方法，将 ProductsDataTable 传入其中，从而将一个新产品添加到数据库中。在内部， TableAdapter 的 Update 方法遍历传入的 DataTable 中的 ProductsRow 实例（本例中只有一个实例，即我们刚添加的那个实例），执行相应的插入、更新或删除命令。此例中执行的是 Products_Insert 存储过程，该存储过程向 Products 表添加一条新记录，并返回该新添加记录的详细信息。于是， ProductsRow 实例的 ProductID 值得以更新。 Update 方法完成后，我们可通过 ProductsRow 的 ProductID 属性访问新添加记录的 ProductID 值。

// Create the ProductsTableAdapter and ProductsDataTable 
NorthwindWithSprocsTableAdapters.ProductsTableAdapter productsAPI =  
    new NorthwindWithSprocsTableAdapters.ProductsTableAdapter(); 
NorthwindWithSprocs.ProductsDataTable products =  
    new NorthwindWithSprocs.ProductsDataTable(); 
 
// Create a new ProductsRow instance and set its properties 
NorthwindWithSprocs.ProductsRow product = products.NewProductsRow(); 
product.ProductName = "New Product"; 
product.CategoryID = 1;  // Beverages 
product.Discontinued = false; 
 
// Add the ProductsRow instance to the DataTable 
products.AddProductsRow(product); 
 
// Update the DataTable using the Batch Update pattern 
productsAPI.Update(products); 
 
// At this point, we can determine the value of the newly-added record's ProductID 
int newlyAddedProductIDValue = product.ProductID;

类似地，Products_Update 存储过程在其 UPDATE 语句后也有一个 SELECT 语句。

ALTER PROCEDURE dbo.Products_Update 
( 
    @ProductName nvarchar(40), 
    @SupplierID int, 
    @CategoryID int, 
    @QuantityPerUnit nvarchar(20), 
    @UnitPrice money, 
    @UnitsInStock smallint, 
    @UnitsOnOrder smallint, 
    @ReorderLevel smallint, 
    @Discontinued bit, 
    @Original_ProductID int, 
    @ProductID int 
) 
AS 
    SET NOCOUNT OFF; 
UPDATE [Products]  
SET [ProductName] = @ProductName, [SupplierID] = @SupplierID,  
    [CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit,  
    [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock,  
    [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel,  
    [Discontinued] = @Discontinued  
WHERE (([ProductID] = @Original_ProductID)); 
     
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,  
    UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued  
FROM Products  
WHERE (ProductID = @ProductID)

注意该存储过程有两个关于ProductID 的输入参数：@Original_ProductID 和 @ProductID 。该功能允许对主键进行改动。例如，在一个雇员数据库中，每条雇员记录可能使用雇员的社会安全号码作为主键值。为更改某条现有雇员记录的社会安全号码，必须同时提供新的社会安全号码和原始的号码。对于 Products 表，不需要该功能，因为 ProductID 列是一个 IDENTITY 列，不应对其进行更改。实际上， Products_Update 存储过程的 UPDATE 语句在其列列表中并不包含 ProductID 列。因此，虽然在 UPDATE 语句的 WHERE 子句里使用了 @Original_ProductID ，但它对于 Products 表而言是多余的，可使用 @ProductID 参数替换之。在修改存储过程的参数时，特别要注意， TableAdapter 中所有那些使用该存储过程的方法也要更新。

步骤4 ：修改存储过程的参数并更新TableAdapter

由于 @Original_ProductID 参数是多余的，我们将它从 Products_Update 存储过程中完全删除。打开 Products_Update 存储过程，删除 @Original_ProductID 参数，并且，在 UPDATE 语句的 WHERE 子句中将所用参数名从 @Original_ProductID 改为 @ProductID 。完成这些修改后，该存储过程中的 T-SQL 代码应类似如下：

ALTER PROCEDURE dbo.Products_Update 
( 
    @ProductName nvarchar(40), 
    @SupplierID int, 
    @CategoryID int, 
    @QuantityPerUnit nvarchar(20), 
    @UnitPrice money, 
    @UnitsInStock smallint, 
    @UnitsOnOrder smallint, 
    @ReorderLevel smallint, 
    @Discontinued bit, 
    @ProductID int 
) 
AS 
    SET NOCOUNT OFF; 
UPDATE [Products] SET [ProductName] = @ProductName, [SupplierID] = @SupplierID,  
    [CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit,  
    [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock,  
    [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel,  
    [Discontinued] = @Discontinued  
WHERE (([ProductID] = @ProductID)); 
     
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit,  
    UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued  
FROM Products  
WHERE (ProductID = @ProductID)

单击工具栏中的 Save 图标或按下 Ctrl+S ，以便将这些更改保存到数据库中。此时， Products_Update 存储过程不需要 @Original_ProductID 输入参数，但 TableAdapter 的配置使其仍会传递该参数。要查看 TableAdapter 传递给 Products_Update 存储过程的参数，可以在 DataSet 设计器中选择 TableAdapter ，进入 Properties 窗口，单击 UpdateCommand 的 Parameters 集合中的省略号。这将打开如图 14 所示的 Parameters Collection Editor 对话框。

图14 ：Parameters Collection Editor 列出要传递给Products_Update 存储过程的参数

此时，从成员列表中选择 @Original_ProductID 参数并单击 Remove 按钮，就可删除该参数。

或者，在设计器中右键单击该 TableAdapter 并选择 Configure ，更新所有方法使用的参数。这将打开 TableAdapter Configuration 向导，列出用于选择、插入、更新和删除数据的存储过程以及这些存储过程预期接受的输入参数。单击 Update 的下拉列表，可以看到 Products_Update 存储过程的预期输入参数不再包含 @Original_ProductID （参见图 15 ）。单击 Finish ，将自动更新 TableAdapter 使用的参数集。

图15 ：也可使用 TableAdapter 的配置向导来更新其方法的参数集

步骤5 ：添加其它的 TableAdapter 方法

如步骤2 所述，创建一个新的 TableAdapter 时，很容易自动生成相应的存储过程。在 TableAdapter 中添加其它方法同样也很简单。为说明这点，我们为步骤2 中创建的ProductsTableAdapter 添加一个GetProductByProductID(productID) 方法。该方法接受 ProductID 值作为输入参数，并返回该指定产品的详细信息。

首先，右键单击该 TableAdapter ，从上下文菜单中选择 Add Query 。

图16 ：向 TableAdapter 添加新查询

这将启动TableAdapter Query Configuration 向导，该向导首先会询问 TableAdapter 访问数据库的方式。要创建一个新的存储过程，选择 “Create a new stored procedure” 选项并单击 Next 。

图17 ：选择 “Create a new stored procedure” 选项

下一屏幕要求我们指定要执行的查询类型，即指定该查询是返回一个记录集或一个标量值，还是执行UPDATE 、INSERT 或 DELETE 语句。由于 GetProductByProductID(productID) 方法将返回一行记录，保持 “SELECT which returns rows” 选项为选中状态并单击 Next 。

图18 ：选择 “SELECT which returns rows” 选项

下一屏幕显示 TableAdapter 的主查询，其中只列出了存储过程的名称(dbo.Products_Select) 。用下面的 SELECT 语句替换该存储过程名称，该语句返回指定产品的所有产品字段：

SELECT ProductID, ProductName, SupplierID, CategoryID,  
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,  
       ReorderLevel, Discontinued 
FROM Products 
WHERE ProductID = @ProductID

图19 ：用一个 SELECT 查询语句替换存储过程名称

随后的屏幕要求对即将创建的存储过程进行命名。输入名称 Products_SelectByProductID ，单击 Next 。

图20 ：将新存储过程命名为 Products_SelectByProductID

向导的最后一步允许我们更改生成的方法名，并指定是采用Fill a DataTable 模式还是 Return a DataTable 模式，抑或这两种模式都采用。默认情况下这两个选项都处于选中状态，对于本教程，保持两选项选中不变，将方法重命名为 FillByProductID 和 GetProductByProductID 。单击 Next ，查看向导要执行的一系列步骤的汇总，然后单击 Finish 完成向导。

图21 ：将 TableAdapter 的方法重命名为 FillByProductID 和 GetProductByProductID

完成向导后，TableAdapter 有一个可用的新方法，GetProductByProductID(productID) ，调用该方法时，将执行我们刚创建的 Products_SelectByProductID 存储过程。我们花点时间通过 Server Explorer 查看一下这个新的存储过程：进入 Stored Procedures 文件夹，打开 Products_SelectByProductID （如果没看到它，右键单击 Stored Procedures 文件夹并选择 Refresh ）。

请注意，SelectByProductID 存储过程将 @ProductID 作为输入参数，执行我们在向导中输入的SELECT 语句。

ALTER PROCEDURE dbo.Products_SelectByProductID 
( 
    @ProductID int 
) 
AS 
    SET NOCOUNT ON; 
 
SELECT ProductID, ProductName, SupplierID, CategoryID,  
       QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,  
       ReorderLevel, Discontinued 
FROM Products 
WHERE ProductID = @ProductID

步骤6 ：创建一个业务逻辑层类

在整个教程系列中，我们都努力地维护一个层次架构，该架构中的表示层所进行的所有调用都是对业务逻辑层(BLL) 的调用。为支持这种设计理念，在我们能够从表示层访问产品数据之前，首先需要为新的强类型 DataSet 创建一个 BLL 类。

在 ~/App_Code/BLL 文件夹中创建一个新的名为ProductsBLLWithSprocs.cs 的类文件，并在其中添加下面的代码：

using System; 
using System.Data; 
using System.Configuration; 
using System.Web; 
using System.Web.Security; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using System.Web.UI.WebControls.WebParts; 
using System.Web.UI.HtmlControls; 
using NorthwindWithSprocsTableAdapters; 
 
[System.ComponentModel.DataObject] 
public class ProductsBLLWithSprocs 
{ 
    private ProductsTableAdapter _productsAdapter = null; 
    protected ProductsTableAdapter Adapter 
    { 
        get 
        { 
            if (_productsAdapter == null) 
                _productsAdapter = new ProductsTableAdapter(); 
 
            return _productsAdapter; 
        } 
    } 
 
    [System.ComponentModel.DataObjectMethodAttribute 
        (System.ComponentModel.DataObjectMethodType.Select, true)] 
    public NorthwindWithSprocs.ProductsDataTable GetProducts() 
    { 
        return Adapter.GetProducts(); 
    } 
 
 
    [System.ComponentModel.DataObjectMethodAttribute 
        (System.ComponentModel.DataObjectMethodType.Select, false)] 
    public NorthwindWithSprocs.ProductsDataTable GetProductByProductID(int productID) 
    { 
        return Adapter.GetProductByProductID(productID); 
    } 
 
 
    [System.ComponentModel.DataObjectMethodAttribute 
        (System.ComponentModel.DataObjectMethodType.Insert, true)] 
    public bool AddProduct 
        (string productName, int? supplierID, int? categoryID,  
         string quantityPerUnit, decimal? unitPrice, short? unitsInStock,  
         short? unitsOnOrder, short? reorderLevel, bool discontinued) 
    { 
        // Create a new ProductRow instance 
        NorthwindWithSprocs.ProductsDataTable products =  
            new NorthwindWithSprocs.ProductsDataTable(); 
        NorthwindWithSprocs.ProductsRow product = products.NewProductsRow(); 
 
        product.ProductName = productName; 
        if (supplierID == null)  
            product.SetSupplierIDNull();  
        else  
            product.SupplierID = supplierID.Value; 
        if (categoryID == null)  
            product.SetCategoryIDNull();  
        else  
            product.CategoryID = categoryID.Value; 
        if (quantityPerUnit == null)  
            product.SetQuantityPerUnitNull();  
        else  
            product.QuantityPerUnit = quantityPerUnit; 
        if (unitPrice == null)  
            product.SetUnitPriceNull();  
        else  
            product.UnitPrice = unitPrice.Value; 
        if (unitsInStock == null)  
            product.SetUnitsInStockNull();  
        else  
            product.UnitsInStock = unitsInStock.Value; 
        if (unitsOnOrder == null)  
            product.SetUnitsOnOrderNull();  
        else  
            product.UnitsOnOrder = unitsOnOrder.Value; 
        if (reorderLevel == null) 
            product.SetReorderLevelNull();  
        else  
            product.ReorderLevel = reorderLevel.Value; 
        product.Discontinued = discontinued; 
 
        // Add the new product 
        products.AddProductsRow(product); 
        int rowsAffected = Adapter.Update(products); 
 
        // Return true if precisely one row was inserted, otherwise false 
        return rowsAffected == 1; 
    } 
 
    [System.ComponentModel.DataObjectMethodAttribute 
        (System.ComponentModel.DataObjectMethodType.Update, true)] 
    public bool UpdateProduct 
        (string productName, int? supplierID, int? categoryID, string quantityPerUnit, 
        decimal? unitPrice, short? unitsInStock, short? unitsOnOrder,  
        short? reorderLevel, bool discontinued, int productID) 
    { 
        NorthwindWithSprocs.ProductsDataTable products =  
            Adapter.GetProductByProductID(productID); 
        if (products.Count == 0) 
            // no matching record found, return false 
            return false; 
 
        NorthwindWithSprocs.ProductsRow product = products[0]; 
 
        product.ProductName = productName; 
        if (supplierID == null)  
            product.SetSupplierIDNull();  
        else  
            product.SupplierID = supplierID.Value; 
        if (categoryID == null)  
            product.SetCategoryIDNull();  
        else  
            product.CategoryID = categoryID.Value; 
        if (quantityPerUnit == null)  
            product.SetQuantityPerUnitNull();  
        else  
            product.QuantityPerUnit = quantityPerUnit; 
        if (unitPrice == null)  
            product.SetUnitPriceNull();  
        else  
            product.UnitPrice = unitPrice.Value; 
        if (unitsInStock == null)  
            product.SetUnitsInStockNull();  
        else  
            product.UnitsInStock = unitsInStock.Value; 
        if (unitsOnOrder == null)  
            product.SetUnitsOnOrderNull();  
        else  
            product.UnitsOnOrder = unitsOnOrder.Value; 
        if (reorderLevel == null)  
            product.SetReorderLevelNull();  
        else  
            product.ReorderLevel = reorderLevel.Value; 
        product.Discontinued = discontinued; 
 
        // Update the product record 
        int rowsAffected = Adapter.Update(product); 
 
        // Return true if precisely one row was updated, otherwise false 
        return rowsAffected == 1; 
    } 
 
    [System.ComponentModel.DataObjectMethodAttribute 
        (System.ComponentModel.DataObjectMethodType.Delete, true)] 
    public bool DeleteProduct(int productID) 
    { 
        int rowsAffected = Adapter.Delete(productID); 
 
        // Return true if precisely one row was deleted, otherwise false 
        return rowsAffected == 1; 
    } 
}

该类和以前教程中创建的 ProductsBLL 类在语义上非常相像，只是它使用的是NorthwindWithSprocs DataSet 的 ProductsTableAdapter 和 ProductsDataTable 对象。例如， ProductsBLL 类在类文件的开始使用 Imports NorthwindTableAdapters 语句，而 ProductsBLLWithSprocs 类在类文件的开始使用 Imports NorthwindWithSprocsTableAdapters 语句。同样，此类中使用的ProductsDataTable 和 ProductsRow 对象前面有 NorthwindWithSprocs 命名空间。ProductsBLLWithSprocs 类提供两个数据访问方法：GetProducts 和 GetProductByProductID ，以及一些用于添加、更新和删除单个产品实例的方法。

步骤7 ：在表示层使用NorthwindWithSprocsDataSet

至此，我们已创建了一个 DAL ，该 DAL 使用存储过程来访问和修改底层数据库数据。我们也创建了一个基本的 BLL ，该 BLL 包含一些方法，分别用于提取所有产品或特定产品、添加、更新和删除产品。为完成本教程，我们创建一个 ASP.NET 页面调用 BLL 的 ProductsBLLWithSprocs 类来显示、更新和删除记录。

在AdvancedDAL 文件夹下打开 NewSprocs.aspx 页面，从工具箱中将一个 GridView 控件拖放到设计器中，将其命名为 Products 。在该 GridView 控件的智能标签中，选择将其绑定到一个新的名为 ProductsDataSource 的 ObjectDataSource 。配置该 ObjectDataSource 使用 ProductsBLLWithSprocs 类，如图 22 所示。

图22 ：配置 ObjectDataSource 使用 ProductsBLLWithSprocs 类

SELECT 选项卡中的下拉列表有两个选项：GetProducts 和 GetProductByProductID 。由于我们想在 GridView 中显示所有的产品，选择 GetProducts 方法。 UPDATE 、 INSERT 和 DELETE 选项卡中的下拉列表都只有一个方法。确保这些下拉列表中都是合适的方法，然后单击 Finish 。

完成 ObjectDataSource 向导后，Visual Studio 将针对产品数据字段在 GridView 中添加一些 BoundField 控件和一个 CheckBoxField 控件。在智能标签中选中 “Enable Editing” 和 “Enable Deleting” 选项，启用 GridView 的内置编辑和删除功能。

图23 ：页面包含一个支持编辑和删除的 GridView

如前面的教程所述，完成ObjectDataSource 向导后，Visual Studio 将 OldValuesParameterFormatString 属性设置为“original_{0}” 。给定 BLL 中的方法所预期的参数，为使数据修改功能正常工作，必须将该属性还原为默认值 “{0}” 。因此，确保将 OldValuesParameterFormatString 属性设置为 "{0}” ，或从声明语句中彻底删除该属性。

完成Configure Data Source 向导、启用 GridView 中的编辑和删除功能、将 ObjectDataSource 的OldValuesParameterFormatString 属性置回默认值后，该页面的声明代码应类似如下：

<asp:GridView ID="Products" runat="server" AutoGenerateColumns="False"  
    DataKeyNames="ProductID" DataSourceID="ProductsDataSource"> 
    <Columns> 
        <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" /> 
        <asp:BoundField DataField="ProductID" HeaderText="ProductID"  
            InsertVisible="False" ReadOnly="True"  
            SortExpression="ProductID" /> 
        <asp:BoundField DataField="ProductName" HeaderText="ProductName"  
            SortExpression="ProductName" /> 
        <asp:BoundField DataField="SupplierID" HeaderText="SupplierID"  
            SortExpression="SupplierID" /> 
        <asp:BoundField DataField="CategoryID" HeaderText="CategoryID"  
            SortExpression="CategoryID" /> 
        <asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit"  
            SortExpression="QuantityPerUnit" /> 
        <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice"  
            SortExpression="UnitPrice" /> 
        <asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock"  
            SortExpression="UnitsInStock" /> 
        <asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder"  
            SortExpression="UnitsOnOrder" /> 
        <asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel"  
            SortExpression="ReorderLevel" /> 
        <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued"  
            SortExpression="Discontinued" /> 
    </Columns> 
</asp:GridView> 
 
<asp:ObjectDataSource ID="ProductsDataSource" runat="server"  
    DeleteMethod="DeleteProduct" InsertMethod="AddProduct"  
    SelectMethod="GetProducts" TypeName="ProductsBLLWithSprocs"  
    UpdateMethod="UpdateProduct"> 
    <DeleteParameters> 
        <asp:Parameter Name="productID" Type="Int32" /> 
    </DeleteParameters> 
    <UpdateParameters> 
        <asp:Parameter Name="productName" Type="String" /> 
        <asp:Parameter Name="supplierID" Type="Int32" /> 
        <asp:Parameter Name="categoryID" Type="Int32" /> 
        <asp:Parameter Name="quantityPerUnit" Type="String" /> 
        <asp:Parameter Name="unitPrice" Type="Decimal" /> 
        <asp:Parameter Name="unitsInStock" Type="Int16" /> 
        <asp:Parameter Name="unitsOnOrder" Type="Int16" /> 
        <asp:Parameter Name="reorderLevel" Type="Int16" /> 
        <asp:Parameter Name="discontinued" Type="Boolean" /> 
        <asp:Parameter Name="productID" Type="Int32" /> 
    </UpdateParameters> 
    <InsertParameters> 
        <asp:Parameter Name="productName" Type="String" /> 
        <asp:Parameter Name="supplierID" Type="Int32" /> 
        <asp:Parameter Name="categoryID" Type="Int32" /> 
        <asp:Parameter Name="quantityPerUnit" Type="String" /> 
        <asp:Parameter Name="unitPrice" Type="Decimal" /> 
        <asp:Parameter Name="unitsInStock" Type="Int16" /> 
        <asp:Parameter Name="unitsOnOrder" Type="Int16" /> 
        <asp:Parameter Name="reorderLevel" Type="Int16" /> 
        <asp:Parameter Name="discontinued" Type="Boolean" /> 
    </InsertParameters> 
</asp:ObjectDataSource>

此时，我们可对GridView 做些定制修改，使编辑界面包含验证功能，使CategoryID 和 SupplierID 列呈现为 DropDownList 等等。我们也可对 Delete 按钮添加一个客户端确认消息，我希望您花些时间来完成这些改进。由于前面的教程已包括了这些主题，此处不再涉及。

不管您是否对 GridView 作了这些改进，在浏览器中测试一下该页面的核心功能。如图 24 所示，该页面在一个 GridView 控件中列出产品，这个控件提供逐行编辑和删除功能。

图24 ：可在 GridView 中查看、编辑和删除产品

小结

强类型DataSet 中的 TableAdapter 可通过ad-hoc SQL 语句或存储过程访问数据库中的数据。使用存储过程时，可使用现有的存储过程，或通过 TableAdapter 向导创建基于 SELECT 查询的新存储过程。在本教程中，我们探讨了如何自动创建存储过程。

虽然自动生成存储过程可节约时间，但有些情况下，向导创建的存储过程与我们希望创建的还是有差距。 Products_Update 存储过程就是这样一个例子，它接受 @Original_ProductID 和 @ProductID 这两个输入参数，但 @Original_ProductID 参数对我们来说是多余的。

在许多情况下，存储过程已存在，或者我们希望手动创建它们以便更好地控制存储过程的命令。这两种情况下，我们都要指示 TableAdapter 在其方法中使用现有的存储过程。我们将在下一篇教程中探讨如何完成此工作。

快乐编程！

posted @ 2016-05-02 00:32 迅捷之风阅读(606) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

迅捷之风

为强类型 DataSet 的 TableAdapter 创建新的存储过程66

简介

存储过程基础

步骤1 ： 创建高级数据访问层场景 Web 页面

骤2 ： 配置TableAdapter 创建新的存储过程

步骤3 ： 探讨刚创建的存储过程

步骤4 ： 修改存储过程的参数并更新TableAdapter

步骤5 ： 添加其它的 TableAdapter 方法

步骤6 ： 创建一个业务逻辑层类

步骤7 ： 在表示层使用NorthwindWithSprocsDataSet

小结

公告