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

导言：

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

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

第一步：向northwind数据库添加存储过程

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

图1：右击stored procedures文件夹选“add a new stored procedure”

如图1所示，选“add a new stored procedure”项后，将在visual studio里打开一个脚本窗口.输入如下的脚本：

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存储过程后的画面.

图2：products_selectbycategoryid存储过程已经添加到数据库

第二步：设置tableadapter使用现有的存储过程

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

　　打开northwindwithsprocs数据集，在productstableadapter上右键单击，选“添加查询”以启用tableadapter query configuration wizard.我们将使用刚刚创建的存储过程products_selectbycategoryid，因此选“use existing stored procedure”项，然后点next.

图3：选“use existing stored procedure”项

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

图4：选products_selectbycategoryid存储过程.

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

图5：指定存储过程返回tabular data

　　然后需要指定采用的方法模式以及方法的名称.同时选中fill a datatable” 和 “return a datatable”项.将这2个方法重命名为fillbycategoryid和 getproductsbycategoryid. 点next，确认无误的话，再点finish完成设置。

图6：将方法命名为fillbycategoryid 和 getproductsbycategoryid

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

第三步：在bll层添加一个getproductsbycategoryid(categoryid)方法

设置完dal层的getproductsbycategoryid方法后，下一步我们将在业务逻辑层添加方法以调用该方法.打开productsbllwithsprocs class类的文件，添加如下方法：

[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)”.

图7：调用categoriesbll class类的getcategories方法

图8：在update, insert,和delete标签里选“(none)”

完成objectdatasource向导后，我们设置dropdownlist控件显示的是categoryname列，而传递的value值为categoryid列.此时，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>

　　接下来，在dropdownlist控件下面放一个gridview控件，社其id为productsbycategory ，并将其绑定到一个名为productsbycategorydatasource的objectdatasource控件，该控件调用productsbllwithsprocs class类的getproductsbycategoryid(categoryid)方法。由于该gridview控件仅仅用来展示数据，因此，在update, insert, 和delete标签里选“(none)”并点next.

图9：设置objectdatasource控件productsbllwithsprocs class类

图10：调用getproductsbycategoryid(categoryid)方法

接下来要选择参数来源，我们在parameter source下拉列表里选“control”；在controlid下拉列表里选“categories” 。点finish完成设置.

图11：设参数categoryid来源于id为categories的dropdownlist控件

完成objectdatasource向导后，visual studio会自动的添加boundfields列和一个 checkboxfield列。你可以对其外观尽情定制.

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

图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来执行该模式，如下：

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所示).

图13：每条category记录都包含edit 和 delete按钮

图14：你无法删除有对应产品的category

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

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

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的原子操作：

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查询.

图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).

图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技术。大家可以点击查看全部教程《[翻译]scott mitchell 的asp.net 2.0数据教程》，希望对大家的学习asp.net有所帮助。