新建适用于类型化数据集的 TableAdapter 的存储过程 (VB)
在前面的教程中,我们在代码中创建了 SQL 语句,并将语句传递给要执行的数据库。 另一种方法是使用存储过程,其中 SQL 语句在数据库中预定义。 本教程介绍如何让 TableAdapter 向导为我们生成新的存储过程。
简介
这些教程的数据访问层 (DAL) 使用类型化数据集。 如 创建数据访问层 教程中所述,类型化数据集由强类型数据表和 TableAdapter 组成。 DataTable 表示系统中的逻辑实体,而 TableAdapters 与基础数据库进行交互以执行数据访问工作。 这包括使用数据填充 DataTable、执行返回标量数据的查询,以及从数据库插入、更新和删除记录。
TableAdapters 执行的 SQL 命令可以是临时 SQL 语句(如 SELECT columnList FROM TableName
),也可以是存储过程。 体系结构中的 TableAdapter 使用即席 SQL 语句。 但是,出于安全性、可维护性和可更新性的原因,许多开发人员和数据库管理员更喜欢存储过程而不是临时 SQL 语句。 另一些则出于灵活性而热衷使用临时 SQL 语句。 在我自己的工作中,我倾向于使用存储过程,而非临时 SQL 语句,但选择使用即席 SQL 语句来简化前面的教程。
定义 TableAdapter 或添加新方法时,TableAdapter 向导可以像使用临时 SQL 语句一样轻松地创建新的存储过程或使用现有存储过程。 在本教程中,我们将了解如何让 TableAdapter 向导自动生成存储过程。 在下一教程中,我们将了解如何将 TableAdapter 方法配置为使用现有或手动创建的存储过程。
注意
有关存储过程和临时 SQL 的利弊的激烈辩论,请参阅 Rob Howard 的博客文章 Don t use Stored Procedures yet? 和 Frans Bouma 的博客文章 Stored Procedures are Bad, M Kay?
存储过程基本知识
函数是所有编程语言通用的构造。 函数是调用函数时执行的语句的集合。 函数可以接受输入参数,并且可以选择性地返回值。 存储过程 是数据库构造,它们与编程语言中的函数有许多相似之处。 存储过程由调用存储过程时执行的一组 T-SQL 语句组成。 存储过程可以接受零到多个输入参数,并且可以返回标量值、输出参数,或者最常见的是查询 SELECT
的结果集。
注意
存储过程通常称为 sprocs 或 SP。
存储过程是使用 CREATE PROCEDURE
T-SQL 语句创建的。 例如,以下 T-SQL 脚本创建一个名为 的GetProductsByCategoryID
存储过程,该过程接受名为 @CategoryID
的单个参数,并返回ProductID
表中具有匹配CategoryID
值的列的 Products
、UnitPrice
ProductName
、 和 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
存储过程可能采用 参数并执行两DELETE
个@CategoryID
语句:第一个语句用于删除相关产品,另一个用于删除指定类别。 存储过程中的多个语句 不会 自动包装在事务中。 需要发出其他 T-SQL 命令,以确保存储过程的多个命令被视为原子操作。 在后续教程中,我们将了解如何在事务范围内包装存储过程的命令。
在体系结构中使用存储过程时,数据访问层 的 方法调用特定的存储过程,而不是发出即席 SQL 语句。 这会集中 (数据库) 上执行的 SQL 语句的位置,而不是在应用程序的体系结构中定义它。 这种集中化可以说可以更轻松地查找、分析和优化查询,并更清楚地了解数据库的使用位置和方式。
有关存储过程基础知识的详细信息,请参阅本教程末尾的“进一步阅读”部分中的资源。
步骤 1:创建高级数据访问层方案网页
在开始讨论如何使用存储过程创建 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
会列出其部分中的教程。 回想一下, SectionLevelTutorialListing.ascx
用户控件提供了此功能。 因此,通过将用户控件从解决方案资源管理器拖动到Default.aspx
页面设计视图中,将此用户控件添加到 。
图 2:将 SectionLevelTutorialListing.ascx
用户控件添加到 Default.aspx
(单击以查看全尺寸图像)
最后,将这些页作为条目添加到文件中 Web.sitemap
。 具体而言,在“使用批处理数据 <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 以创建新的存储过程
为了演示如何创建使用存储过程而不是临时 SQL 语句的数据访问层,让我们在名为 NorthwindWithSprocs.xsd
的文件夹中创建~/App_Code/DAL
一个新的类型化数据集。 由于我们在前面的教程中详细介绍了此过程,因此我们将快速完成此处的步骤。 如果在创建和配置类型化数据集时遇到困难或需要进一步的分步说明,请参阅 创建数据访问层 教程。
右键单击 DAL
文件夹,选择“添加新项”,然后选择“数据集”模板,将新的数据集添加到项目,如图 4 所示。
图 4:将新的类型化数据集添加到名为 NorthwindWithSprocs.xsd
的项目 (单击以查看全尺寸图像)
这将创建新的类型化数据集,打开其Designer,创建新的 TableAdapter,并启动 TableAdapter 配置向导。 TableAdapter 配置向导的第一步要求我们选择要使用的数据库。 Northwind 数据库的连接字符串应列在下拉列表中。 选择此项并单击“下一步”。
在下一个屏幕中,我们可以选择 TableAdapter 应如何访问数据库。 在前面的教程中,我们选择了第一个选项“使用 SQL 语句”。 对于本教程,请选择第二个选项“创建新存储过程”,然后单击“下一步”。
图 5:指示 TableAdapter 创建新的存储过程 (单击以查看全尺寸图像)
与使用即席 SQL 语句一样,在以下步骤中,我们被要求为 TableAdapter main 查询提供 SELECT
语句。 但是,TableAdapter 向导将创建包含此SELECT
查询的存储过程,而不是使用SELECT
此处输入的 语句直接执行即席查询。
SELECT
对此 TableAdapter 使用以下查询:
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
图 6:输入查询 SELECT
(单击以查看全尺寸图像)
注意
上述查询与类型化数据集中 Northwind
的ProductsTableAdapter
main查询略有不同。 回想一下, ProductsTableAdapter
类型化数据集中的 Northwind
包含两个相关子查询,用于恢复每个产品类别和供应商的类别名称和公司名称。 在即将发布的 更新 TableAdapter 以使用 JOINs 教程中,我们将了解如何将此相关数据添加到此 TableAdapter。
花点时间单击“高级选项”按钮。 在这里,我们可以指定向导是否还应为 TableAdapter 生成插入、更新和删除语句,是否使用乐观并发,以及是否应在插入和更新后刷新数据表。 默认情况下,选中“生成插入、更新和删除语句”选项。 保持选中。 在本教程中,将“使用乐观并发选项”保留为未选中状态。
当由 TableAdapter 向导自动创建的存储过程时,似乎忽略了“刷新数据表”选项。 无论是否选中此复选框,生成的插入和更新存储过程都会检索刚刚插入或刚刚更新的记录,如步骤 3 中所示。
图 7:选中“生成插入、更新和删除”语句选项
注意
如果选中了“使用乐观并发”选项,向导将向 子句添加其他条件 WHERE
,以防止在其他字段中发生更改时更新数据。 有关使用 TableAdapter 内置的 乐观并发 控制功能的详细信息,请参阅实现乐观并发教程。
输入 SELECT
查询并确认选中“生成插入”、“更新”和“删除语句”选项后,单击“下一步”。 如图 8 所示的下一个屏幕提示输入向导为选择、插入、更新和删除数据而创建的存储过程的名称。 将这些存储过程名称更改为 Products_Select
、 Products_Insert
、 Products_Update
和 Products_Delete
。
图 8:重命名存储过程 (单击以查看全尺寸图像)
若要查看 TableAdapter 向导将用于创建四个存储过程的 T-SQL,请单击“预览 SQL 脚本”按钮。 在“预览 SQL 脚本”对话框中,可以将脚本保存到文件,或将其复制到剪贴板。
图 9:预览用于生成存储过程的 SQL 脚本
命名存储过程后,单击“下一步”将 TableAdapter 命名为相应的方法。 就像使用即席 SQL 语句一样,我们可以创建填充现有 DataTable 或返回新 DataTable 的方法。 还可以指定 TableAdapter 是否应包含用于插入、更新和删除记录的 DB-Direct 模式。 选中所有三个复选框,但将“返回 DataTable”方法重命名为 GetProducts
(,如图 10) 所示。
图 10:命名方法 Fill
并 GetProducts
(单击以查看全尺寸图像)
单击“下一步”查看向导将执行的步骤的摘要。 单击“完成”按钮完成向导。 向导完成后,将返回到 DataSet Designer,现在应包括 ProductsDataTable
。
图 11:数据集Designer显示“新添加 ProductsDataTable
(单击以查看全尺寸图像)
步骤 3:检查新建的存储过程
步骤 2 中使用的 TableAdapter 向导自动创建了用于选择、插入、更新和删除数据的存储过程。 可以通过 Visual Studio 查看或修改这些存储过程,方法是转到服务器资源管理器并向下钻取到数据库的“存储过程”文件夹。 如图 12 所示,Northwind 数据库包含四个新的存储过程: Products_Delete
、 Products_Insert
、 Products_Select
和 Products_Update
。
图 12:可在数据库存储过程文件夹中找到步骤 2 中创建的四个存储过程
注意
如果看不到服务器资源管理器,请转到“视图”菜单并选择“服务器资源管理器”选项。 如果未看到从步骤 2 中添加的产品相关存储过程,请尝试右键单击“存储过程”文件夹,然后选择“刷新”。
若要查看或修改存储过程,请在服务器资源管理器中双击其名称,或者右键单击该存储过程并选择“打开”。 图 13 显示了 Products_Delete
打开时的存储过程。
图 13:可以从 Visual Studio 中打开和修改存储过程 (单击以查看全尺寸图像)
和Products_Select
存储过程的内容Products_Delete
都非常简单。 另一方面,和Products_Insert
Products_Update
存储过程应进行更密切的检查,因为它们在 和 UPDATE
语句之后INSERT
执行SELECT
语句。 例如,以下 SQL 构成 Products_Insert
存储过程:
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
属性。
以下代码演示了此功能。 它包含 ProductsTableAdapter
为NorthwindWithSprocs
类型化数据集创建的 和 ProductsDataTable
。 通过创建 ProductsRow
实例、提供其值并调用 TableAdapter 方法 Update
(传入 ProductsDataTable
)将新产品添加到数据库中。 在内部,TableAdapter 方法 Update
枚举 ProductsRow
传入的 DataTable (在此示例中只有一个实例 - 我们刚刚) 添加的实例,并执行相应的插入、更新或删除命令。 在这种情况下, Products_Insert
将执行存储过程,这将向表添加新记录 Products
,并返回新添加的记录的详细信息。 ProductsRow
然后更新实例的 ProductID
s 值。 Update
方法完成后,可以通过 s ProductID
属性访问新添加的ProductID
记录值ProductsRow
。
' Create the ProductsTableAdapter and ProductsDataTable
Dim productsAPI As New NorthwindWithSprocsTableAdapters.ProductsTableAdapter
Dim products As New NorthwindWithSprocs.ProductsDataTable
' Create a new ProductsRow instance and set its properties
Dim product As NorthwindWithSprocs.ProductsRow = 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
Dim newlyAddedProductIDValue as Integer = 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
列,永远不应更改。 事实上, UPDATE
存储过程中的 Products_Update
语句在其列列表中不包含列 ProductID
。 因此,虽然 @Original_ProductID
在 语句 s WHERE
子句中使用UPDATE
,但它对于表来说是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)
若要将这些更改保存到数据库,请单击工具栏中的“保存”图标或按 Ctrl+S。 此时, Products_Update
存储过程不需要 @Original_ProductID
输入参数,但 TableAdapter 配置为传递此类参数。 可以通过在 DataSet Designer中选择 TableAdapter、转到属性窗口并单击集合Parameters
中的省略号来查看 TableAdapter 将发送到Products_Update
存储过程的参数UpdateCommand
。 此时会显示“参数集合编辑器”对话框,如图 14 所示。
图 14:Parameters 集合编辑器 Lists传递给Products_Update
存储过程所用的参数
只需从成员列表中选择参数并单击“删除”按钮, @Original_ProductID
即可从此处删除此参数。
或者,可以通过右键单击Designer中的 TableAdapter 并选择“配置”来刷新用于所有方法的参数。 这将打开 TableAdapter 配置向导,其中列出了用于选择、插入、更新和删除的存储过程,以及存储过程预期接收的参数。 如果单击“更新”下拉列表,可以看到预期输入参数的 Products_Update
存储过程,现在不再包含 @Original_ProductID
(请参阅图 15) 。 只需单击“完成”即可自动更新 TableAdapter 使用的参数集合。
图 15:也可以使用 TableAdapter 配置向导刷新其方法参数集合 (单击以查看全尺寸图像)
步骤 5:添加其他 TableAdapter 方法
如步骤 2 所示,在创建新的 TableAdapter 时,很容易自动生成相应的存储过程。 向 TableAdapter 添加其他方法时也是如此。 为了说明这一点,让我们将方法 GetProductByProductID(productID)
添加到 ProductsTableAdapter
步骤 2 中创建的 。 此方法将采用值作为输入 ProductID
,并返回有关指定产品的详细信息。
首先,右键单击“TableAdapter”,然后从上下文菜单中选择“添加查询”。
图 16:向 TableAdapter 添加新查询
这将启动 TableAdapter 查询配置向导,该向导首先提示 TableAdapter 应如何访问数据库。 若要创建新的存储过程,请选择“创建新的存储过程”选项,然后单击“下一步”。
图 17:选择“创建新存储过程”选项 (单击以查看全尺寸图像)
下一个屏幕要求我们确定要执行的查询类型,是返回一组行或单个标量值,还是执行 UPDATE
、 INSERT
或 DELETE
语句。 GetProductByProductID(productID)
由于 该方法将返回行,因此请保留选择“返回行的 SELECT”选项,然后单击“下一步”。
图 18:选择返回行的 SELECT 选项 (单击以查看全尺寸图像)
下一个屏幕显示 TableAdapter main 查询,该查询仅列出存储过程的名称 (dbo.Products_Select
) 。 将存储过程名称替换为以下 SELECT
语句,该语句返回指定产品的所有 product 字段:
SELECT ProductID, ProductName, SupplierID, CategoryID,
QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID
图 19:将存储过程名称替换为 SELECT
查询 (单击以查看全尺寸图像)
后续屏幕要求命名将要创建的存储过程。 输入名称 Products_SelectByProductID
并单击“下一步”。
图 20:将新存储过程 Products_SelectByProductID
命名为 (单击以查看全尺寸图像)
向导的最后一步允许我们更改生成的方法名称,并指示是使用填充 DataTable 模式还是返回 DataTable 模式,或同时使用这两者。 对于此方法,请同时选中这两个选项,但将方法重命名为 FillByProductID
和 GetProductByProductID
。 单击“下一步”查看向导将执行的步骤的摘要,然后单击“完成”以完成向导。
图 21:将 TableAdapter 方法重命名为 FillByProductID
, GetProductByProductID
(单击 以查看全尺寸图像)
完成向导后,TableAdapter 具有可用的新方法, GetProductByProductID(productID)
该方法在调用时将执行 Products_SelectByProductID
刚刚创建的存储过程。 请花点时间从服务器资源管理器查看此新的存储过程,方法是钻取到“存储过程”文件夹,如果看不到它,请打开 Products_SelectByProductID
(,右键单击“存储过程”文件夹,然后选择“刷新) ”。
请注意, 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) 。 为了遵循此设计决策,我们首先需要为新的类型化数据集创建 BLL 类,然后才能从表示层访问产品数据。
在 ~/App_Code/BLL
文件夹中创建名为 ProductsBLLWithSprocs.vb
的新类文件,并向其添加以下代码:
Imports NorthwindWithSprocsTableAdapters
<System.ComponentModel.DataObject()> _
Public Class ProductsBLLWithSprocs
Private _productsAdapter As ProductsTableAdapter = Nothing
Protected ReadOnly Property Adapter() As ProductsTableAdapter
Get
If _productsAdapter Is Nothing Then
_productsAdapter = New ProductsTableAdapter()
End If
Return _productsAdapter
End Get
End Property
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Select, True)> _
Public Function GetProducts() As NorthwindWithSprocs.ProductsDataTable
Return Adapter.GetProducts()
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Select, False)> _
Public Function GetProductByProductID(ByVal productID As Integer) _
As NorthwindWithSprocs.ProductsDataTable
Return Adapter.GetProductByProductID(productID)
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Insert, True)> _
Public Function AddProduct _
(ByVal productName As String, ByVal supplierID As Nullable(Of Integer), _
ByVal categoryID As Nullable(Of Integer), ByVal quantityPerUnit As String, _
ByVal unitPrice As Nullable(Of Decimal), _
ByVal unitsInStock As Nullable(Of Short), _
ByVal unitsOnOrder As Nullable(Of Short), _
ByVal reorderLevel As Nullable(Of Short), _
ByVal discontinued As Boolean) _
As Boolean
' Create a new ProductRow instance
Dim products As New NorthwindWithSprocs.ProductsDataTable()
Dim product As NorthwindWithSprocs.ProductsRow = products.NewProductsRow()
product.ProductName = productName
If Not supplierID.HasValue Then
product.SetSupplierIDNull()
Else
product.SupplierID = supplierID.Value
End If
If Not categoryID.HasValue Then
product.SetCategoryIDNull()
Else
product.CategoryID = categoryID.Value
End If
If quantityPerUnit Is Nothing Then
product.SetQuantityPerUnitNull()
Else
product.QuantityPerUnit = quantityPerUnit
End If
If Not unitPrice.HasValue Then
product.SetUnitPriceNull()
Else
product.UnitPrice = unitPrice.Value
End If
If Not unitsInStock.HasValue Then
product.SetUnitsInStockNull()
Else
product.UnitsInStock = unitsInStock.Value
End If
If Not unitsOnOrder.HasValue Then
product.SetUnitsOnOrderNull()
Else
product.UnitsOnOrder = unitsOnOrder.Value
End If
If Not reorderLevel.HasValue Then
product.SetReorderLevelNull()
Else
product.ReorderLevel = reorderLevel.Value
End If
product.Discontinued = discontinued
' Add the new product
products.AddProductsRow(product)
Dim rowsAffected As Integer = Adapter.Update(products)
' Return true if precisely one row was inserted, otherwise false
Return rowsAffected = 1
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Update, True)> _
Public Function UpdateProduct
(ByVal productName As String, ByVal supplierID As Nullable(Of Integer), _
ByVal categoryID As Nullable(Of Integer), ByVal quantityPerUnit As String, _
ByVal unitPrice As Nullable(Of Decimal), _
ByVal unitsInStock As Nullable(Of Short), _
ByVal unitsOnOrder As Nullable(Of Short), _
ByVal reorderLevel As Nullable(Of Short), _
ByVal discontinued As Boolean, ByVal productID As Integer) _
As Boolean
Dim products As NorthwindWithSprocs.ProductsDataTable = _
Adapter.GetProductByProductID(productID)
If products.Count = 0 Then
' no matching record found, return false
Return False
End If
Dim product As NorthwindWithSprocs.ProductsRow = products(0)
product.ProductName = productName
If Not supplierID.HasValue Then
product.SetSupplierIDNull()
Else
product.SupplierID = supplierID.Value
End If
If Not categoryID.HasValue Then
product.SetCategoryIDNull()
Else
product.CategoryID = categoryID.Value
End If
If quantityPerUnit Is Nothing Then
product.SetQuantityPerUnitNull()
Else
product.QuantityPerUnit = quantityPerUnit
End If
If Not unitPrice.HasValue Then
product.SetUnitPriceNull()
Else
product.UnitPrice = unitPrice.Value
End If
If Not unitsInStock.HasValue Then
product.SetUnitsInStockNull()
Else
product.UnitsInStock = unitsInStock.Value
End If
If Not unitsOnOrder.HasValue Then
product.SetUnitsOnOrderNull()
Else
product.UnitsOnOrder = unitsOnOrder.Value
End If
If Not reorderLevel.HasValue Then
product.SetReorderLevelNull()
Else
product.ReorderLevel = reorderLevel.Value
End If
product.Discontinued = discontinued
' Update the product record
Dim rowsAffected As Integer = Adapter.Update(product)
' Return true if precisely one row was updated, otherwise false
Return rowsAffected = 1
End Function
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Delete, True)> _
Public Function DeleteProduct(ByVal productID As Integer) As Boolean
Dim rowsAffected As Integer = Adapter.Delete(productID)
' Return true if precisely one row was deleted, otherwise false
Return rowsAffected = 1
End Function
End Class
此类模拟前面教程中的ProductsBLL
类语义,但使用 ProductsTableAdapter
DataSet 中的 NorthwindWithSprocs
和 ProductsDataTable
对象。 例如,类使用 Imports NorthwindWithSprocsTableAdapters
,而不是像 那样ProductsBLLWithSprocs
ProductsBLL
在类文件的开头有语句Imports NorthwindTableAdapters
。 同样, ProductsDataTable
此类中使用的 和 ProductsRow
对象也以 NorthwindWithSprocs
命名空间为前缀。 类 ProductsBLLWithSprocs
提供两种数据访问方法和 GetProducts
GetProductByProductID
、 和 方法,用于添加、更新和删除单个产品实例。
步骤 7:使用NorthwindWithSprocs
表示层中的数据集
此时,我们创建了一个 DAL,该 DAL 使用存储过程来访问和修改基础数据库数据。 我们还构建了一个基本的 BLL,其中包含用于检索所有产品或特定产品的方法以及用于添加、更新和删除产品的方法。 为了结束本教程,让我们创建一个 ASP.NET 页面,该页面使用 BLL 类 ProductsBLLWithSprocs
显示、更新和删除记录。
NewSprocs.aspx
打开 文件夹中的页面,将“工具箱”中的 AdvancedDAL
GridView 拖到Designer,将其Products
命名为 。 从 GridView 智能标记中选择将其绑定到名为 ProductsDataSource
的新 ObjectDataSource。 将 ObjectDataSource 配置为使用 ProductsBLLWithSprocs
类,如图 22 所示。
图 22:将 ObjectDataSource 配置为使用 ProductsBLLWithSprocs
类 (单击以查看全尺寸图像)
SELECT 选项卡中的下拉列表有两个选项, GetProducts
即 GetProductByProductID
。 由于我们想要在 GridView 中显示所有产品,因此请选择 GetProducts
方法。 UPDATE、INSERT 和 DELETE 选项卡中的下拉列表各只有一种方法。 确保其中每个下拉列表都选择了其适当的方法,然后单击“完成”。
ObjectDataSource 向导完成后,Visual Studio 会将 BoundFields 和 CheckBoxField 添加到 GridView 的产品数据字段。 通过选中智能标记中存在的“启用编辑”和“启用删除”选项,打开 GridView 的内置编辑和删除功能。
图 23:页面包含已启用编辑和删除支持的 GridView (单击以查看全尺寸图像)
正如我们在前面的教程中讨论的那样,在完成 ObjectDataSource 向导时,Visual Studio 会将 OldValuesParameterFormatString
属性设置为 original_{0}。 这需要还原到其默认值, {0} 以便数据修改功能在给定 BLL 中方法所需的参数时正常工作。 因此,请务必将 属性设置为 OldValuesParameterFormatString
{0} 或完全从声明性语法中删除属性。
完成“配置数据源”向导、在 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>
此时,可以通过自定义编辑界面以包含验证、将 CategoryID
和 SupplierID
列呈现为 DropDownLists 等来整理 GridView。 我们还可以将客户端确认添加到“删除”按钮,我鼓励你花点时间实现这些增强功能。 由于前面的教程中已介绍这些主题,因此我们不会在此处再次介绍这些主题。
无论是否增强 GridView,在浏览器中测试页面的核心功能。 如图 24 所示,该页面列出了 GridView 中提供每行编辑和删除功能的产品。
图 24:可以从 GridView 中查看、编辑和删除产品 (单击以查看全尺寸图像)
总结
Typed DataSet 中的 TableAdapter 可以使用即席 SQL 语句或通过存储过程访问数据库中的数据。 使用存储过程时,可以使用现有的存储过程,也可以指示 TableAdapter 向导基于 SELECT
查询创建新的存储过程。 在本教程中,我们探讨了如何为我们自动创建存储过程。
虽然自动生成存储过程有助于节省时间,但在某些情况下,向导创建的存储过程与我们自己创建的过程不一致。 一个示例是Products_Update
存储过程,即使@Original_ProductID
参数是多余的,它仍@Original_ProductID
需要 和 @ProductID
输入参数。
在许多情况下,存储过程可能已创建,或者我们可能需要手动生成它们,以便对存储过程的命令进行精细的控制。 在任一情况下,我们都希望指示 TableAdapter 对其方法使用现有的存储过程。 我们将在下一教程中了解如何完成此操作。
编程快乐!
深入阅读
有关本教程中讨论的主题的详细信息,请参阅以下资源:
关于作者
斯科特·米切尔是七本 ASP/ASP.NET 书籍的作者和 4GuysFromRolla.com 的创始人,自 1998 年以来一直在使用 Microsoft Web 技术。 Scott 担任独立顾问、培训师和作家。 他的最新一本书是 山姆斯在 24 小时内 ASP.NET 2.0。 可以在 上mitchell@4GuysFromRolla.com联系他,也可以通过他的博客(可在 中找到http://ScottOnWriting.NET)。
特别感谢
本教程系列由许多有用的审阅者审阅。 本教程的首席审阅者是希尔顿·吉塞诺。 有兴趣查看我即将发布的 MSDN 文章? 如果是,请在 处mitchell@4GuysFromRolla.com放置一行。
反馈
https://aka.ms/ContentUserFeedback。
即将发布:在整个 2024 年,我们将逐步淘汰作为内容反馈机制的“GitHub 问题”,并将其取代为新的反馈系统。 有关详细信息,请参阅:提交和查看相关反馈