虽然TableAdapter向导可以自动生成存储过程，但在某些时候我们需要使用现有的存储过程。本文将介绍如何在Visual Studio环境中手动添加存储过程，并指导TableAdapter的方法使用这些存储过程。

导言：

在上一篇文章中，我们研究了如何让TableAdapters向导自动创建存储过程。在本文中，我们将研究如何让TableAdapters使用现有的存储过程。因为Northwind数据库中的存储过程很少，所以我们还需要考察在Visual Studio环境下如何手动向数据库中添加新的存储过程。

注意：在第61章《在事务里对数据库修改进行封装》中，我们给TableAdapter增加了一些支持事务的方法(比如(begin transaction，commit transaction等。).我们可以在一个存储过程中管理整个事务，而无需修改数据访问层的代码。在本文中，我们还将研究在事务中执行存储过程的T-SQL命令。

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

我们可以通过Visual Studio轻松地将存储过程添加到数据库中。让我们向Northwind数据库添加一个新的存储过程，它返回Products表中具有特定CategoryID值的产品。在“服务器资源管理”窗口中，展开Northwind数据库。正如我们在上一篇文章中看到的，存储过程文件夹包含现有的存储过程。要添加新的存储过程，只需右键单击存储过程文件夹并选择添加新存储过程。

图1：右击Stored Procedures文件夹选“Add a New Stored Procedure”

如图1所示，选择“添加新的存储过程”后，Visual Studio中将会打开一个脚本窗口。输入以下脚本：

创建过程dbo。产品_选择分类标识

（

@CategoryID int

)

如同

选择产品ID、产品名称、供应商ID、类别ID，

数量单位，单价，单位库存，单位订单，

再订购级别，已停产

来自产品

其中CategoryID=@CategoryID

执行该脚本时，名为Products_SelectByCategoryID的新存储过程将被添加到数据库中。该存储过程接受一个输入参数(@CategoryID，type int)，并返回与CategoryID的值匹配的所有产品。

执行CREATE PROCEDURE脚本会将存储过程添加到数据库中。单击工具栏中的Save按钮或按Ctrl S。之后，刷新存储过程文件夹以显示最近添加的存储过程，因此，ilai部分从“create procedure dbo。products _ selectproductbycategoryid " to "更改过程dbo。products _ selectproductbycategoryid "。Create procedure用于添加新的存储过程，而alter procedure用于更新现有的存储过程。由于脚本的开头已经更改为ALTER PROCEDURE，我们可以通过更改输入参数或SQL语句并单击Save按钮来更新存储过程。图2显示了保存Products_SelectByCategoryID存储过程后的图片。

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

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

既然存储过程Products_SelectByCategoryID已经添加到数据库中，我们将设置数据访问层来使用这个存储过程。具体来说，我们将向ProductsTableAdapter添加getProductByCategoryID(CategoryID)方法，该方法将调用我们刚刚创建的存储过程Products_SelectByCategoryID。

打开NorthwindWithSprocs数据集，右键单击ProductsTableAdapter，选择“添加查询”以启动TableAdapter查询配置向导。我们将使用刚刚创建的存储过程Products_SelectByCategoryID，因此选择“使用现有存储过程”项，然后单击Next。

图3：选“Use existing stored procedure”项

下一个屏幕是一个下拉列表框，其中列出了数据库中所有现有的存储过程。当选择一个存储过程时，它的输入参数将列在左边，它的返回列(如果有)将列在右边。在下拉列表中选择Products_SelectByCategoryID存储过程，然后单击“下一步”。

图4：选Products_SelectByCategoryID存储过程.

下一个屏幕询问我们存储过程返回哪种数据，以及TableAdapter方法返回哪种类型。比如我们指定返回表格数据(表列数据)，这个方法会返回一个ProductDataTableInstance实例；如果我们指定存储过程返回单个值，TableAdapter将返回一个对象，该对象由存储过程返回的第一行的第一列分配。由于存储过程Products_SelectByCategoryID将返回某一类别的所有产品，因此选择第一项“表格数据”,然后单击“下一步”。

图5：指定存储过程返回Tabular Data

然后，您需要指定方法模式和方法的名称。同时，勾选“填充一个数据表”和“返回一个数据表”项。将这两个方法重命名为FillByCategoryID和GetProductsByCategoryID。单击下一步，然后单击完成以完成设置。

图6：将方法命名为FillByCategoryID 和 GetProductsByCategoryID

注意：我们刚刚添加了FillByCategoryID和GetProductsByCategoryID方法来执行一个int类型的输入参数，它是由@CategoryID传入的。如果要更改Products_SelectByCategory存储过程的参数，还必须更新这些TableAdapter方法的参数。如前一篇文章所述，手动添加或删除参数集中的参数，或者再次运行TableAdapter向导。

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

在DAL层设置了GetProductsByCategoryID方法之后，接下来我们将在业务逻辑层添加一个方法来调用这个方法。打开ProductsBLLWithSprocs类的文件，并添加以下方法：

【系统。组件模型.数据对象方法属性

(系统。component model . dataobjectmethodtype . select，false)]

公共NorthwindWithSprocs。products datatable GetProductByCategoryID(int categoryID)

{

返回适配器。GetProductsByCategoryID(categoryID)；

}

BLL图层方法仅通过ProductsTableAdapter的GetProductsByCategoryID()方法返回ProductsDataTable。由于使用了DataObjectMethodAttribute属性，当我们使用ObjectDataSource的设置数据源向导时，这个方法会出现在SELECT选项卡的下拉列表中。

第四步：展示产品

为了在DAL和BLL层测试新添加的Products_SelectByCategoryID存储过程和相应的方法，我们将创建一个ASP.NET页面，其中包含一个DropDownList控件和一个GridView控件。DropDownList控件列出了数据库中的所有类别。当选择一个类别时，我们将在GridView中显示属于该类别的所有产品。

注意：在上一篇文章中，我们使用DropDownList控件来创建主/从报告。更多详情请参考第7章，《使用DropDownList过滤的主/从报表》。

在AdvancedDAL文件夹中打开ExistingSprocs.aspx页，将DropDownList控件从工具箱拖到该页，将其ID设置为Categories，并将AutoPostBack属性设置为true。接下来，将其绑定到其智能标记中名为CategoriesDataSource的ObjectDataSource控件。将控件设置为调用CategoriesBLL类的GetCategories方法，并在更新、插入和删除标记中选择“(无)”。

图7：调用CategoriesBLL Class类的GetCategories方法

图8：在UPDATE, INSERT,和DELETE标签里选“(None)”

完成ObjectDataSource向导后，我们将DropDownList控件设置为显示CategoryName列，而传递的值是CategoryID列。此时，DropDownList控件和ObjectDataSource控件的声明代码如下所示：

ASP:DropDownList ID= Categories runat= server AutoPostBack= True

data sourceid= categories data source data textfield= category name

数据值字段=CategoryID

/asp:DropDownList

ASP:objectdata source ID= categories data source runat= server

OldValuesParameterFormatString= original _ { 0 }

select method= get categories TypeName= categories bll

/asp:对象数据源

接下来，将一个GridView控件放在DropDownList控件下，其ID为ProductsByCategory，并将其绑定到一个名为ProductsByCategoryDataSource的ObjectDataSource控件，该控件调用ProductsBllWithSprocClass类的getProductsByCategoryID(CategoryID)方法。由于GridView控件仅用于显示数据，因此在更新、插入和删除选项卡中选择“(无)”，然后单击下一步。

图9：设置ObjectDataSource控件ProductsBLLWithSprocs Class类

图10：调用GetProductsByCategoryID(categoryID)方法

接下来，要选择参数源，我们在参数源下拉列表中选择“控制”；在ControlID下拉列表中选择“类别”。单击“完成”完成安装。

图11：设参数categoryID来源于ID为Categories的DropDownList控件

完成ObjectDataSource向导后，Visual Studio将自动添加BoundFields列和CheckBoxField列。你可以随心所欲地定制它的外观。

在浏览器中登录到此页面。登录时，选择饮料，将显示该类别的产品。如果我们选择其他类别，将显示所有相应的产品。下图显示了：

图12： Produce类的所有产品都显示出来了

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

在第61章《在事务里对数据库修改进行封装》中，我们讨论了用事务封装数据库修改命令的技术。这些修改操作要么成功，要么失败。用于交易的技术包括：使用系统中的类。事务命名空间。在数据访问层调用ADO.NET类，比如SqlTransaction。在存储过程中直接添加T-SQLtransaction命令。

在第63章中，我们在DAL层使用ADO.NET类，在本文的其余部分，我们将在存储过程中使用T-SQL command命令来管理事务。

用于手动启动、提交和回滚事务的三个主要SQL命令是Begin Transaction、Commit Transaction和ROLLBACK TRANSACTION。与adopted方法类似，当在存储过程中使用事务时，应该采用以下模式：

1.指示交易已打开。

2.执行事务中包含的SQL语句

3.如果第二步中的任何语句出错，回滚事务。

4.如果第二步中的所有语句都正确执行，事务将被提交。

您可以使用T-SQL语法来执行此模式，如下所示：

开始尝试

开始交易-开始交易

.执行构成事务的SQL语句.

-如果我们到达这里，成功！

提交事务

结束尝试

开始接球

-哎呀，有一个错误

回滚事务

-使用引发一个错误

-例外的详细信息

声明@ErrMsg nvarchar(4000)，

@ErrSeverity int

SELECT @ErrMsg=ERROR_MESSAGE()，

@ErrSeverity=ERROR_SEVERITY()

RAISERROR(@ErrMsg，@ErrSeverity，1)

末端捕捉

代码开始作为一种新的结构进行尝试.CATCH模式——SQL Server2005。就像尝试一样.C#中的CATCH模式，此SQL尝试.catch模式在try区域执行语句，如果有任何语句出错，它会立即转到CATCH区域。

如果执行正确，提交事务将提交更改并完成事务；如果执行出错，CATCH区域中的回滚事务会将数据库返回到启动前的状态。存储过程还将通过RAISERROR命令引发SqlException异常。

注意：试试看.上述代码的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值相匹配。如果一个类别有相应的产品，当我们试图删除该类别时，它将违反外键约束。我们来演示一下，登录这个页面(~/binary data/updatinganddeleting . aspx)，这个页面列出了系统中的所有类别，每一行都包含编辑和删除按钮(如图13所示)。如果您试图删除一个带有相应产品的类别，比如Beverages——，删除会失败，因为它违反了外键约束(如图14所示)。

图13：每条Category记录都包含Edit 和 Delete按钮

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

我们希望可以删除任何类别，无论它是否有相应的产品。在删除一个类别时，我们还希望删除其对应的产品(虽然我们可以简单地将这些产品的CategoryID值设置为NULL)。为此，我们可以创建一个接受输入参数@CategoryID的存储过程。调用它时，显式删除所有对应的产品，然后删除这个类别。

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

创建过程dbo。类别_删除

（

@CategoryID int

)

如同

-首先，删除相关产品.

从产品中删除

其中CategoryID=@CategoryID

-现在删除类别

从类别中删除

其中CategoryID=@CategoryID

上面的代码清楚地删除了相关的产品和类别，但它不在事务中。如果在Categorie表中存在基于CategoryID值的其他外键约束，那么在这种情况下会出现问题：对于该类别，所有相关的产品都被删除，但是由于其他表的外键约束，该类别仍保留在数据库中。

如果存储过程在事务中，删除Categories表失败将导致删除Products表的回滚。以下存储过程脚本使用一个事务来确保这两个deletestatements的原子操作：

创建过程dbo。类别_删除

（

@CategoryID int

)

如同

开始尝试

开始交易-开始交易

-首先，删除相关产品.

从产品中删除

其中CategoryID=@CategoryID

-现在删除类别

从类别中删除

其中CategoryID=@CategoryID

-如果我们到达这里，成功！

提交事务

结束尝试

开始接球

-哎呀，有一个错误

回滚事务

-使用引发一个错误

-例外的详细信息

声明@ErrMsg nvarchar(4000)，

@ErrSeverity int

SELECT @ErrMsg=ERROR_MESSAGE()，

@ErrSeverity=ERROR_SEVERITY()

RAISERROR(@ErrMsg，@ErrSeverity，1)

末端捕捉

花些时间将名为Categories_Delete的存储过程添加到Northwind数据库中。具体步骤请参考第一步。

第六步：更新CategoriesTableAdapter

一旦我们添加了Categories_Delete存储过程，DAL层就可以使用专门的SQL语句来删除。但是，我们需要更新CategoriesTableAdapter来使用Categories_Delete存储过程。

注意：在前面的章节中，我们处理的是NorthwindWithSprocs数据集，它只有一个实体——ProductsDataTable，但是我们会遇到处理类别的情况。因此，在本文的后面部分，当我提到数据访问层时，我指的是Northwind数据集，也就是我们在第1章《创建一个数据访问层》中创建的数据集。

打开Northwind数据集，选择CategoriesTableAdapter并打开其属性窗口，该窗口列出了此TableAdapter使用的插入命令、更新命令、删除命令和选择命令。以及名称和数据库连接信息。展开DeleteCommand属性以查看其详细信息。如图15所示，DeleteCommand的ComamndType属性被设置为Text，它的文本信息被用作一个特别的SQL查询。

图15：在CategoriesTableAdapter的属性窗口查看其属性信息

让我们做一些修改。选择文本“(DeleteCommand)”，然后在下拉列表中选择“(New)”，这将清除CommandText、CommandType和Parameters属性的设置。然后将CommandType属性设置为StoredProcedure，然后输入存储过程的名称(dbo。Categories_Delete)。如果先设置CommandType属性，然后按顺序设置CommandText属性，Visual Studio将自动生成Parameters集合。如果不遵循此顺序，则只能单击Parameters属性中的椭圆形区域来打开Parameters Collection Editor对话框并手动添加参数。无论是自动还是手动，我们都应该打开Parameters Collection Editor对话框，检查参数是否正确(如图16所示)。如果在对话框中没有看到任何参数，手动添加参数@CategoryID(不需要添加参数@RETURN_VALUE)。

图16：确保参数设置正确

当DAL完成更新后，删除一个类别会自动删除其对应的所有产品。这些操作都在一个事务中。我们来验证一下，回到上一页。当您单击某个类别的删除按钮时，该类别及其所有产品都将被删除。

注意：在测试Categories_Delete存储过程之前，最好对数据库进行备份，因为存储过程将删除所选的类别及其相应的产品。如果你用西北方。App_Data文件夹中的MDF数据库，只需要关闭Visual Studio，将文件夹中的MDF和LDF文件复制到其他文件夹中即可。测试结束后，关闭Visual Studio并用备份的MDF和LDF文件覆盖App_Data文件夹中的相应文件。

结语：

虽然TableAdapter向导可以自动生成存储过程，但在某些时候我们需要使用现有的存储过程。本文研究如何在Visual Studio环境中手动添加存储过程，并指导TableAdapter的方法使用这些存储过程。此外，我们还研究了用于在存储过程中启动、提交和回滚事务的T-SQL命令和脚本模式。

编程快乐！

作者简介

Scott Mitchell，本教程系列的作者，他写了六本关于ASP/ASP的书。NET，是4GuysFromRolla.com的创始人，从1998年开始应用微软的Web技术。你可以点击查看所有教程《[翻译]Scott Mitchell 的ASP.NET 2.0数据教程》，希望对你学习ASP.NET有所帮助。