在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程

时间:2022-09-23 17:56:24

导言:

  在前面的文章里我们考察了如何让TableAdapters向导自动的创建存储过程.而在本文,我们将考察如何让TableAdapter使用现有的存储过程。由于Northwind数据库现有的存储过程很少,我们也需要考察如何在Visual Studio环境里手动向数据库添加新的存储过程.

  注意:在第61章《在事务里对数据库修改进行封装》里我们向TableAdapter添加了一些方法以支持事务(比如 (BeginTransaction, CommitTransaction等)。我们可以在不修改数据访问层代码的情况下,在一个存储过程里管理整个事务.在本文,我们还将对事务里执行存储过程的T-SQL commands命令进行考察.

第一步:向Northwind数据库添加存储过程

  我们很容易通过Visual Studio向数据库添加存储过程.让我们向Northwind数据库添加一个新存储过程,它返回Products表里特定CategoryID值的产品.在服务器资源管理窗口,展开Northwind数据库,就像我们在前面的文章看到的一样,存储过程文件夹包含了现有的存储过程。要添加新的存储过程的话,只需要右键单击存储过程文件夹,选“添加新存储过程”项,

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图1:右击Stored Procedures文件夹选“Add a New Stored Procedure”

如图1所示,选“Add a New Stored Procedure”项后,将在Visual Studio里打开一个脚本窗口.输入如下的脚本:

?
1
2
3
4
5
6
7
8
9
10
11
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_SelectByCategoryID的新存储过程,该存储过程接受一个输入参数(@CategoryID, 类型为int)并将与CategoryID值匹配的所有产品返回.

  执行该CREATE PROCEDURE脚本,将向数据库添加存储过程,点工具栏的保存按钮或按Ctrl+S。如此之后,刷新存储过程文件夹以显示最近添加的存储过程,如此一ilai部分由“CREATE PROCEDURE dbo.Products_SelectProductByCategoryID” 转变为“ALTER PROCEDURE dbo.Products_SelectProductByCategoryID”. CREATE PROCEDURE用于添加新存储过程,而ALTER PROCEDURE用于更新现有的存储过程。由于脚本开头部分已经转变为ALTER PROCEDURE, 我们可以通过改动输入参数或SQL statements并点击保存按钮,即可完成对存储过程的更新.图2显示的是保存Products_SelectByCategoryID存储过程后的画面.

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图2:Products_SelectByCategoryID存储过程已经添加到数据库

第二步:设置TableAdapter使用现有的存储过程

  现在存储过程Products_SelectByCategoryID已经添加到数据库,我们将设置数据访问层使用该存储过程。具体说,我们将向ProductsTableAdapter添加GetProducstByCategoryID(categoryID)方法, 该方法将调用我们刚刚创建的存储过程Products_SelectByCategoryID.

  打开NorthwindWithSprocs数据集,在ProductsTableAdapter上右键单击,选“添加查询”以启用TableAdapter Query Configuration wizard.我们将使用刚刚创建的存储过程Products_SelectByCategoryID,因此选“Use existing stored procedure”项,然后点Next.

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图3:选“Use existing stored procedure”项

  接下来的画面为一个下拉列表框,列出了数据库现有的所有存储过程,当选择某个存储过程的话,左边将列出其输入参数,右边将列出其返回列(如果有的话).在下拉列表里选Products_SelectByCategoryID存储过程,再点Next.

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图4:选Products_SelectByCategoryID存储过程.

  接下来的画面询问我们存储过程返回的是哪种类型的数据,以及TableAdapter的方法返回的类型.比如,如果我们指定返回tabular data(表列数据)的话,该方法将返回一个ProductsDataTable instance实例;如果我们指定存储过程返回一个单一值(a single value)的话,TableAdapter将返回一个object(对象),该对象由存储过程返回的第一行的第一列来赋值.由于存储过程Products_SelectByCategoryID将返回某个category的所有产品,选第一项“Tabular data”,再点Next.

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图5:指定存储过程返回Tabular Data

  然后需要指定采用的方法模式以及方法的名称.同时选中Fill a DataTable” 和 “Return a DataTable”项.将这2个方法重命名为FillByCategoryID和 GetProductsByCategoryID. 点Next,确认无误的话,再点Finish完成设置。

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图6:将方法命名为FillByCategoryID 和 GetProductsByCategoryID

  注意:我们刚才添加FillByCategoryID 和 GetProductsByCategoryID方法,执行一个int类型的输入参数,它由@CategoryID传递进来。如果你要改动Products_SelectByCategory存储过程的参数的话,你也必须更新这些TableAdapter方法的参数.就像在前一篇文章探讨的一样,要么手动添加或删除参数集里的参数,要么再次运行TableAdapter向导.

第三步:在BLL层添加一个GetProductsByCategoryID(categoryID)方法

设置完DAL层的GetProductsByCategoryID方法后,下一步我们将在业务逻辑层添加方法以调用该方法.打开ProductsBLLWithSprocs class类的文件,添加如下方法:

?
1
2
3
4
5
6
7
[System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetProductByCategoryID(int categoryID)
{
 return Adapter.GetProductsByCategoryID(categoryID);
 
}

  该BLL层方法仅仅通过ProductsTableAdapter的 GetProductsByCategoryID()方法来返回ProductsDataTable。由于使用了DataObjectMethodAttribute属性,我们使用ObjectDataSource的设置数据源向导时,该方法会出现在SELECT标签的下拉列表中.

第四步:展示产品

  为测试新添加的Products_SelectByCategoryID存储过程,以及DAL 和 BLL层里的对应的方法, 我们将创建一个ASP.NET页面,该页面包含一个DropDownList控件以及一个 GridView控件.DropDownList控件列出数据库里所有的category,当选定某个category时,我们将在GridView里将属于该category的所有product展示出来.

  注意:我们在前面的文章里用DropDownList控件创建过主/从报表,更多细节请参考第7章《使用DropDownList过滤的主/从报表》

  打开AdvancedDAL文件夹里的ExistingSprocs.aspx页面,从工具箱里拖一个DropDownList控件到页面,设置其ID为Categories,AutoPostBack属性为true.接下来,在其智能标签里将其绑定到一个名为CategoriesDataSource的ObjectDataSource控件.设置该控件调用CategoriesBLL class类的GetCategories方法,而在UPDATE, INSERT, 以及DELETE标签里选“(None)”.

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图7:调用CategoriesBLL Class类的GetCategories方法

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图8:在UPDATE, INSERT,和DELETE标签里选“(None)”

完成ObjectDataSource向导后,我们设置DropDownList控件显示的是CategoryName列,而传递的Value值为CategoryID列.此时,DropDownList控件和ObjectDataSource控件的声明代码看起来和下面的差不多:

?
1
2
3
4
5
6
7
8
9
<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>

  接下来,在DropDownList控件下面放一个GridView控件,社其ID为ProductsByCategory ,并将其绑定到一个名为ProductsByCategoryDataSource的ObjectDataSource控件,该控件调用ProductsBLLWithSprocs class类的GetProductsByCategoryID(categoryID)方法。由于该GridView控件仅仅用来展示数据,因此,在UPDATE, INSERT, 和DELETE标签里选“(None)”并点Next.

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图9:设置ObjectDataSource控件ProductsBLLWithSprocs Class类

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图10:调用GetProductsByCategoryID(categoryID)方法

接下来要选择参数来源,我们在Parameter source下拉列表里选“Control”;在ControlID下拉列表里选“Categories” 。点Finish完成设置.

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图11:设参数categoryID来源于ID为Categories的DropDownList控件

完成ObjectDataSource向导后,Visual Studio会自动的添加BoundFields列和一个 CheckBoxField列。你可以对其外观尽情定制.

在浏览器里登录该页面,当登录时选取的是Beverages(饮料类),该类的产品将会显示出来.如果我们选择其它种类的话,对应的所有产品将显示出来.如下图: 

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图12: Produce类的所有产品都显示出来了

第五步:用事务封装存储过程命令

在第61章《在事务里对数据库修改进行封装》里我们探讨了用事务对数据库修改命令进行封装的技术,这些修改操作要么都成功要么都失败。使用事务的技术包括:

.使用System.Transactions命名空间里的类
.在Data Access Layer层调用ADO.NET classes类,比如SqlTransaction
.直接在存储过程里添加T-SQLtransaction commands事务命令

在63章我们在DAL层使用ADO.NET classe类,而在本文剩余部分,我们将在一个存储过程里运用T-SQL command命令来对一个事务进行管理.

用来手动启动、提交、回滚事务的3个主要SQL command命令分别是BEGIN TRANSACTION, COMMIT TRANSACTION, 以及ROLLBACK TRANSACTION.与使用ADO.NET方法类似,在一个存储过程里使用事务时,应采用如下的模式:

1.指出事务已经开启
2.执行事务包含的SQL statements
3.如果第二步的任何一个statement出错,则回滚事务
4.如果第二步的所有statement执行无误,则提交事务

可以用T-SQL syntax来执行该模式,如下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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 Server2005新增的结构.就像C#里的try...catch模式一样,该SQL TRY...CATCH模式在TRY区域执行statement,如果任何一个statement出错,则立即转到CATCH区域.

  如果执行无误,COMMIT TRANSACTION将提交更改并完成事务;如果执行出错,那么CATCH区域的ROLLBACK TRANSACTION将数据库返回到开始前的状态。存储过程也会通过RAISERROR command命令抛出一个SqlException异常.
注意:上面代码的的TRY...CATCH模式是SQL Server 2005里新添加的,如果你使用的是Microsoft SQL Server稍微旧点的版本的话,上面的代码不会成功执行。不过你可以参考这篇文章《Managing Transactions in SQL Server Stored Procedures》(http://www.4guysfromrolla.com/webtech/080305-1.shtml)以寻求帮助.

  让我们看一个实实在在的例子。在Categories表和Products表之间有一个外键约束,这意味着,Products表里的CategoryID列必须要与Categories表里的CategoryID值吻合.如果某个category有对应的product,而我们试图删除该category时将会导致违背外键约束.我们来进行演示,登录这个页面(~/BinaryData/UpdatingAndDeleting.aspx),该页面列出了系统里的所有category,且每行都包含Edit和Delete按钮(如图13),如果你尝试删除一个有对应product的category时,比如Beverages——删除失败,因为违背了外键约束(如图14所示).

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图13:每条Category记录都包含Edit 和 Delete按钮

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图14:你无法删除有对应产品的Category

  我们希望可以删除任何一个category,不管其是否有对应的产品.当删除category时,我们同样希望删除其对应的产品(尽管我们可以简单的将这些产品的CategoryID值设置为NULL).为此,我们可以创建一个存储过程,它接受一个输入参数@CategoryID。当调用它时明确的将所有对应的product删除,然后再将这个category删掉.

人们的第一反应是创建类似下面的存储过程:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
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

  上述代码明白无误的将相关的product以及该category删除,只是没有置身于一个事务内.假设还有其它的基于Categorie表CategoryID值的外键约束,那么在这种情况下问题就出来了:对该category来说,其相关的product都删除掉了,而这个category因与其它表还有外键约束而仍然保留在数据库.

  如果该存储过程置身于一个事务里的话,对Categories表的删除操作失败将导致对Products表的删除操作回滚.下面的存储过程脚本使用一个事务来确保对这2个DELETE statement的原子操作:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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的存储过程,具体步骤可参考第一步.

第六步:更新CategoriesTableAdapter

  一旦我们添加完Categories_Delete存储过程后,DAL层就可以使用ad-hoc SQL statements来执行删除操作了.不过我们需要更新CategoriesTableAdapter,使其使用Categories_Delete存储过程.

  注意:在前几章我们处理的是NorthwindWithSprocs数据集,该数据集只有一个实体——ProductsDataTable,但是我们将会遇到处理categories的情况。因此,在本文后面部分,当我提到数据访问层(Data Access Layer)时,我指的是Northwind数据集,也就是我们在第1章《创建一个数据访问层》里创建的那个.

  打开Northwind数据集,选中CategoriesTableAdapter并打开其属性窗口,该窗口列出了该TableAdapter用到的InsertCommand, UpdateCommand, DeleteCommand, 以及SelectCommand,以及name和数据库连接信息.展开DeleteCommand属性查看其细节.如图15所示,DeleteCommand的ComamndType属性被设置为Text, 其文本信息作为一个ad-hoc SQL查询.

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图15:在CategoriesTableAdapter的属性窗口查看其属性信息

  让我们来作一些修改.选中“(DeleteCommand)”文本,然后在下拉列表里选“(New)”,这将清除掉CommandText, CommandType,Parameters属性的设置。接着将CommandType属性设置为StoredProcedure,然后在CommandText属性里输入存储过程的名称(即dbo.Categories_Delete).如果你是按照先设置CommandType属性再设置CommandText属性的顺序的话,Visual Studio将自动生成Parameters collection(参数集).如果你没有按照这个顺序来的话,你就只能点击Parameters属性里的一个椭圆型的区域来打开Parameters Collection Editor对话框,手动添加参数.不管是自动的还是手动添加参数,我们都应该打开Parameters Collection Editor对话框以检查参数是否正确(如图16).如果你在对话框里没看到任何的参数,那么就手动添加参数@CategoryID,(你不需要添加参数@RETURN_VALUE).

在ASP.NET 2.0中操作数据之六十六:在TableAdapters中使用现有的存储过程
图16:确保参数设置正确

  当DAL完成更新后,删除一个category将自动的删除所有其对应的product,这些操作都置身于一个事务里.我们来做个验证,重返刚才那个页面,当单击某个category的Delete按钮时,该category及其所有的product都会被删除.

  注意:在测试Categories_Delete存储过程前,最好对数据库做个备份,因为该存储过程将删除选中的category及其对应的product.如果你用的是App_Data文件夹里的NORTHWND.MDF数据库的话,你只需要关闭Visual Studio并将文件夹里的MDF和LDF文件拷贝到其它文件夹.测试完毕后,关闭Visual Studio,再用备份的MDF和LDF文件覆盖掉App_Data文件夹的对应文件.

结语:

  虽然TableAdapter向导可以自动的生成存储过程,但是在某些时候我们需要使用现有的存储过程。在本文,我们考察了如何在Visual Studio环境里手动添加存储过程,并引导TableAdapter的方法使用这些存储过程。另外我们还考察了在存储过程里用来开启、提交、回滚事务的T-SQL commands 和script脚本模式.

  祝编程快乐!

作者简介

  本系列教程作者 Scott Mitchell,著有六本ASP/ASP.NET方面的书,是4GuysFromRolla.com的创始人,自1998年以来一直应用 微软Web技术。希望对大家的学习ASP.NET有所帮助。