虽然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有所帮助。
郑重声明：本文由网友发布，不代表盛行IT的观点，版权归原作者所有，仅为传播更多信息之目的，如有侵权请联系，我们将第一时间修改或删除，多谢。