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

导言：

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

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

步骤1:向罗斯文数据库添加存储过程。

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

图1:右键单击存储过程文件夹并选择“添加新的存储过程”。

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

创建过程数据库。Products_SelectByCategoryID(@CategoryID int)ASSELECT ProductID、ProductName、SupplierID、CategoryID、QuantityPerUnit、UnitPrice、库存单位、unitsonorder、再订购级别、从产品折扣此处categoryid=@ categoryid执行脚本时，一个名为Products _ SelectByCategoryID的新存储过程将被添加到数据库中，该数据库接受一个输入参数(@ CategoryID，type int)，并返回与CategoryID值匹配的所有产品。

执行CREATE PROCEDURE脚本将向数据库添加一个存储过程，单击工具栏中的保存按钮或按Ctrl-S.然后，刷新存储过程文件夹以显示最近添加的存储过程。这样，ilai部分就从“创建过程dbo”变成了。products _ selectproductbycategoryid”更改为“ALTER PROCESSing dbo”。products _ selectproductbycategoryid”。create过程用于添加新的存储过程，而alter过程用于更新现有的存储过程。由于脚本的开头已更改为ALTER PROCEDURE，我们可以通过更改输入参数或SQL语句并单击保存按钮来更新存储过程。图2显示了保存Products_SelectByCategoryID存储过程后的图片。

图2:2:Products _ SelectByCategoryID存储过程已添加到数据库中。

步骤2:设置TableAdapter使用现有的存储过程。

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

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

图3:选择项目“使用现有存储过程”。

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

图4:选择Products_SelectByCategoryID存储过程。

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

图5:指定存储过程返回表格数据。

然后，需要指定采用的方法模式和方法名称。同时，选择“填充数据表”和“返回数据表”。将这两个方法重命名为FillByCategoryID和GetProductsByCategoryID。单击“下一步”，如果设置正确，则单击“完成”完成设置。

图6:命名FillByCategoryID和GetProductsByCategoryID方法。

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

步骤3:向BLL层添加一个getproductbycategoryid(Categoryid)方法。

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

[系统。组件模型。component model . DataObjectMethodType . select，false)])公共NorthwindWithSprocs。productdatatable GetProductByCategoryID(int categoryID){返回适配器。getproductbycategoryid(categoryID)；} BLL图层方法只通过ProductsTableAdapter的GetProductsByCategoryID()方法返回ProductsDataTable。由于使用了DataObjectMethodAttribute属性，因此当我们使用对象数据源的设置数据源向导时，该方法将出现在选择选项卡的下拉列表中。

第四步：展示产品。

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

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

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

图7:调用CategoriesBLL类的GetCategories方法。

图8:在更新、插入和删除标签中选择“(无)”。

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

ASP : DropDownList ID=' Categories ' runat=' server ' AutoPostBack=' True ' DataSourceID=' categoriesdata source ' DataTextField=' CategoryName ' DataValueField=' CategoryID '/ASP : objectdata source ID=' categoriesdata source ' runat=' server ' oldvaluesparametformatstring=' origin _ { 0 } Selectmethod=' getcategories ' type name=' Categories bll '/ASP 3:并将其绑定到名为ProductsByCategoryDataSource的ObjectDataSource控件，该控件调用getproductsbycategoryid类的getProductBYcategoryid(categoryid)方法。由于此GridView控件仅用于显示数据，请在“更新”、“插入”和“删除”标签中选择“(无)”，然后单击“下一步”。

图9:设置ObjectDataSource控件ProductsBLLWithSprocs类类。

图10:调用getproductbycategoryid(Categoryid)方法。

接下来，选择参数源。我们在参数源的下拉列表中选择“控制”。从控制标识下拉列表中选择“类别”。单击“完成”完成设置。

图11:让参数categoryID来自于ID为Categories的DropDownList控件。

完成对象数据源向导后，Visual Studio将自动添加边界字段列和检查框字段列。您可以根据自己的内心内容定制它的外观。

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

图12:显示了产品类的所有产品。

步骤5:用事务封装存储过程命令。

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

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

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

1.表示交易已经开启。2.执行事务中包含的SQL语句3。3.如果第二步中的任何语句出错，回滚事务。4.如果第二步中的所有语句都正确执行，则提交事务。

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

开始尝试开始交易-开始交易.执行构成事务的SQL语句.-如果我们到达这里，成功！COMMIT transactionnend TRY begin CATCH-呜呜，出现错误ROLLBACK TRANSACTION -引发错误异常的-详细信息DECLARE @ErrMsg nvarchar(4000)，@ErrSeverity int SELECT @ ErrMsg=ERROR _ MESSAGE()，@ ErrSeverity=ERROR _ SEVERITY()RAISERROR(@ ErrMsg，@ ErrSeverity，1)1)END CATCH代码以TRY的新结构开始.捕捉模式——SQL服务器2005。就像TRY一样.C#中的CATCH模式，这个SQL TRY.catch模式在try区域执行语句，如果有任何语句出错，它会立即转到CATCH区域。

如果执行正确，COMMIT TRANSACTION将提交更改并完成事务；如果出现执行错误，CATCH区域中的ROLLBACK TRANSACTION会将数据库返回到启动前的状态。存储过程还将通过RAISERROR命令引发一个SqlException异常。注意：尝试.上述代码的CATCH模式是在SQL Server 2005中新增的。如果您使用的是稍旧版本的Microsoft SQL Server，上述代码将无法成功执行。不过，你可以参考这篇文章《Managing Transactions in SQL Server Stored Procedures》(http://www.4guysfromrolla.com/webtech/080305-1.shtml)寻求帮助。

我们来看一个真实的例子。“类别”表和“产品”表之间存在外键约束，这意味着“产品”表中的“类别标识”列必须与“类别”表中的“类别标识”值相匹配。如果一个类别有对应的产品，我们在尝试删除它时会违反外键约束。我们来演示一下。登录这个页面(~/binary data/updatingdeleting . aspx)，里面列出了系统中所有的类别，每一行都包含了Edit和Delete按钮(如图13所示)。例如，如果您试图删除具有相应产品的类别，Beverages——将无法删除，因为它违反了外键约束(如图14所示)。

图13:每个类别记录包含编辑和删除按钮。

图14:您不能删除具有相应产品的类别。

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

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

创建过程数据库。类别_删除(@CategoryID int)AS -首先，删除关联的产品.从产品中删除类别号=@类别号-现在删除类别号从类别中删除类别号哪里类别号=@类别号上面的代码明确删除了相关的产品和类别，但是它不在事务中。假设基于类别表的CategoryID值还有其他外键约束，那么这种情况下就会出现问题：对于这个类别，它的所有相关产品都被删除了，但是这个类别仍然保留在数据库中，因为它和其他表有外键约束。

如果存储过程在事务中，删除类别表失败将导致产品表的删除回滚。以下存储过程脚本使用事务来确保这两个these语句的原子操作：

创建过程数据库。categories _ Delete(@ CategoryID int)ASBEGIN TRY BEGIN TRANSACTION-启动事务-首先，删除关联的产品.从类别号=@类别号-现在删除类别从类别号=@类别号-如果我们到达这里，成功！COMMIT TRANSACTION end try begin CATCH-呜呜，出现了一个错误ROLLBACK TRANSACTION -引发了一个错误异常的-details DECLARE @ ErrMsg nvarchar(4000)，@ errseverity int select @ ErrMsg=error _ message()，@ errseverity=error _ severity()raiserror(@ ErrMsg，@ errseverity，1) endcatch将名为Categories_Delete的存储过程添加到Northwind数据库需要一些时间。具体步骤参见第一步。

第6步：更新分类算法适配器。

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

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

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

图15:在其属性窗口中查看CategoriesTableAdapter的属性信息。

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

图16:确保参数设置正确。

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

注意：在测试Categories_Delete存储过程之前，最好备份数据库，因为存储过程将删除选定的类别及其相应的产品。如果使用NORTHWND。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年以来一直使用微软的网络技术。你可以点击查看所有教程《[翻译]Scott Mitchell 的ASP.NET 2.0数据教程》，希望能帮助你学习ASP.NET。