六十五：在TableAdapters中创建新的存储过程

1、在ASP.NET 2.0中操作数据之六十五：在TableAdapters中创建新的存储过程作者：heker2007 字体：增加 减小 类型：转载 时间：2016-05-18 我要评论本文主要讲解使用TableAdapter设置向导自动创建增删改查的存储过程，虽然自动创建存储过程可以节省时间，但他们会包含一些无用的参数，下节我们会介绍TableAdapter使用现有的存储过程。导言：本教程的Data Access Layer (DAL)使用的是类型化的数据集(Typed DataSets).就像我们在第一章创建一个数据访问层里探讨的一样，该类型化的数据集由强类型的DataTa

2、ble和TableAdapter构成。DataTable描绘的是系统里的逻辑实体而TableAdapter引用相关数据库执行数据访问，包括对DataTable填充数据、执行返回标量数据(scalar data)的请求、添加，更新，删除数据库里的记录等.TableAdapter执行的SQL命令要么是某个特定的SQL statements,比如SELECT columnList FROM TableName；要么是存储过程.本教程前面部分的TableAdapter使用的是SQL statements.不过很多开发者和数据库管理员基于安全、便于维护等方面的考虑，偏爱使用存储过程；不过也有的人出于灵活

3、性的考虑偏爱使用SQL statement.就我自己而言，我也偏向于存储过程.在前面的文章，出于简化的目的我选用的是SQL statements.当定义一个新TableAdapter或添加新方法时，使用TableAdapter的设置向导，我们可以很容易的创建新的或使用现有的存储过程.在本文，我们将考察如何使用设置向导自动的生产存储过程。在下一章我们考察如何设置TableAdapter的方法使用现有的或手动创建存储过程.注意：关于讨论到底使用存储过程还是使用SQL statements的问题，可参考Rob Howard的博客文章Don't Use Stored Procedures Ye

4、t?（ Bouma的博客文章Stored Procedures are Bad, M'Kay?（存储过程基础一个存储过程由一系列的T-SQL statement组成，当调用该存储过程时就执行这些T-SQL statement.存储过程可以接受0到多个输入参数，返回标量值、输出参数，或最常见的返回SELECT查询值.注意：存储过程Stored procedures也经常引用为“sprocs” or “SPs”.可以使用T-SQL statement语句CREATE PROCEDURE来创建存储过程.比如下面的T-SQL脚本创建了一个名为GetProductsByCategoryID的存储

5、过程，它有一个名为 CategoryID的参数，并且将表Products里与CategoryID值相吻合的那条记录的ProductID, ProductName, UnitPrice,以及Discontinued值返回.?123456789CREATE PROCEDURE GetProductsByCategoryID( CategoryID int)AS SELECT ProductID, ProductName, UnitPrice, DiscontinuedFROM ProductsWHERE CategoryID = CategoryID创建后，我们可以用下面的代码

6、调用它：?1EXEC GetProductsByCategory categoryID注意：在下篇文章我们将在Visual Studio IDE集成环境里创建存储过程.不过在本文，我们将用TableAdapter向导来自动创建存储过程.除了返回数据外，我们还可以在一个事务里用存储过程执行多条数据库命令.比如，假如有一个名为DeleteCategory的存储过程，其包含一个输入参数CategoryID，并执行2个DELETE statemets，第一个是删除相关的products，第二个是删除category。存储过程里面的多个statements并不是自动的封装在一个事务里的.我们应添加额外的

7、T-SQL commands以确保存储过程里的多条数据库命令当成原子操作处理.我们将在后面的内容考察如何用事务来封装存储过程的命令.当在体系的某个层使用存储过程时，Data Access Layer的方法将调用某个具体的存储过程而不是发出一个SQL statement命令.这样一来我们可以发现、分析发出的查询命令.并可以更清楚的看到数据库是如何使用的.有关存储过程基本原理的更多信息，可参考本文结束部分的延伸阅读.第一步：创建数据访问层高级场景的Web页面在开始之前，让我们花点时间创建本文及后面几篇文章要用到的页面。新建一个名为AdvancedDAL的文件夹，然后添加如下的ASP.NET页面，记

8、得使用母版页Site.master：Default.aspxNewSprocs.aspxExistingSprocs.aspxJOINs.aspxAddingColumns.aspxComputedColumns.aspxEncryptingConfigSections.aspxManagedFunctionsAndSprocs.aspx图1：添加相关的页面像其它文件夹一样，Default.aspx页面将列出本部分的内容，记得SectionLevelTutorialListing.ascx用户控件提供了该功能。因此，将其从解决资源管理器里拖放到Default.aspx页面.图2：将Sectio

9、nLevelTutorialListing.ascx用户控件拖到Default.aspx页面最后，将这些页面添加到Web.sitemap文件里。特别的，把下面的代码放在“Working with Batched Data”<siteMapNode>标签后面：?1234567891011121314151617181920212223242526272829303132<siteMapNode url="/AdvancedDAL/Default.aspx" title="Advanced DAL Scenarios" 

10、description="Explore a number of advanced Data Access Layer scenarios.">   <siteMapNode url="/AdvancedDAL/NewSprocs.aspx" title="Creating New Stored Procedures for TableAdapters" description="Learn how to have the TableAdapter wizard

11、automatically create and use stored procedures." /> <siteMapNode url="/AdvancedDAL/ExistingSprocs.aspx" title="Using Existing Stored Procedures for TableAdapters" description="See how to plug existing stored procedures into a TableAdapter.

12、" /> <siteMapNode url="/AdvancedDAL/JOINs.aspx" title="Returning Data Using JOINs" description="Learn how to augment your DataTables to work with data returned from multiple tables via a JOIN query." /> <siteMapNode url="/A

13、dvancedDAL/AddingColumns.aspx" title="Adding DataColumns to a DataTable" description="Master adding new columns to an existing DataTable." /> <siteMapNode url="/AdvancedDAL/ComputedColumns.aspx" title="Working with Computed Columns&qu

14、ot; description="Explore how to work with computed columns when using Typed DataSets." /> <siteMapNode url="/AdvancedDAL/EncryptingConfigSections.aspx" title="Protected Connection Strings in Web.config" description="Protect your conne

15、ction string information in Web.config using encryption." /> <siteMapNode url="/AdvancedDAL/ManagedFunctionsAndSprocs.aspx" title="Creating Managed SQL Functions and Stored Procedures" description="See how to create SQL functions and stored pro

16、cedures using managed code." /></siteMapNode>更新Web.sitemap文件后，花点时间在浏览器里查看，左边的菜单将包括本部分的内容.图3：网站地图现在包含了不部分的页面第二步：设置TableAdapter创建新的存储过程我们在/App_Code/DAL文件夹里创建一个类型化的DataSet，名称为NorthwindWithSprocs.xsd.由于我们在以前的教程里已经详细探讨了创建细节，因此我们这里一笔带过，如果你想知道详细的创建过程请参阅前面的第1章创建一个数据访问层在DAL文件夹上右击鼠标选“添加新项”，

17、选DataSet模板，如图4所示.图4：新建一个名为NorthwindWithSprocs.xsd的数据集这样将会创建一个新的类型化的DataSet，打开设计器，创建一个新的TableAdapter，展开TableAdapter设置向导.向导的第一步是让我们选择要连接的数据库.在下拉列表里有一个连接到Northwind数据库的连接字符串，选中它，再点下一步。接下来的界面让我们选择TableAdapter以哪种方式访问数据库.在以前的教程里我们选择的是“Use SQL statements”，不过在本文我们选第二项：“Create new stored procedures”，点下一步.图5：设

18、置TableAdpater创建新的存储过程接下来，我们要指定主查询(main query).我们将创建一个存储过程来包含SELECT查询.使用下面的SELECT查询：?1234SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, DiscontinuedFROM Products图6：键入SELECT查询注意：在名为Northwind的数据集里的ProductsTableAdapt

19、er的主查询与上面本文定义的主查询有所不同。那个主查询还返回了每个产品的category名称和company名称.不过在后面的文章我们将对本文的TableAdapter添加这些相关的代码.再点“Advanced Options”按钮.我们可以指定是否让向导为TableAdapter自动生成insert, update和delete statements；是否使用开发式并发操作(optimistic concurrency);是否完成inserts 和 update操作后刷新数据表.在默认情况下，自动选中“Generate Insert, Update and Delete statements

20、”选项。另外，本文不用选择“Use optimistic concurrency”项.当选择自动创建存储过程时，“Refresh the data table”项将被忽略掉.不管是否选中该项，最终的insert 和update存储过程都会检索刚添加或刚更新(just-inserted or just-updated record)的记录，我们将在第三步看到.图7：选中“Generate Insert, Update and Delete statements”项注意：当选中“Use optimistic concurrency”项的时候，向导会在WHERE语句里添加额外的条件，当其它列的值发生

21、改动的话，将阻止数据更新.关于使用TableAdapter内置的optimistic concurrency功能请参阅第21章实现开放式并发输入SELECT主查询并选取“Generate Insert, Update and Delete statements”项后，点下一步，接下来的界面，如图8所示，让我们为selecting, inserting, updating, 和deleting数据的存储过程命名.将这些存储过程的名字改为Products_Select, Products_Insert, Products_Update, 和Products_Delete.图8：为存储过程重命名向导

22、创建了4个存储过程，点“Preview SQL Script”按钮，你可以在Preview SQL Script 对话框里将脚本保存在一个文件里或复制到剪贴板.图9：预览生成的存储过程对存储过程重命名后，点下一步，对TableAdapter相应的方法命名.就像使用SQL statements一样，我们可以创建方法来填充一个现有的DataTable或返回一个新的DataTable；我们也一个指定TableAdapter是否采用DB-Direct模式来插入、更新、删除记录.全选这3项，只不过将Return a DataTable方法重命名为GetProducts，如图10所示：图10：将方法重命名

23、为Fill 和GetProducts点Next总览向导将执行的步骤.点Finish按钮完成设置.一旦向导结束后，将返回DataSet设计器，它此时将包括ProductsDataTable.图11：DataSet设计器将显示刚刚添加的ProductsDataTable第三步：考察刚刚创建的存储过程我们在第二步里用向导创建了选择、插入、更新、删除数据的存储过程.这些存储过程可以通过Visual Studio查看或修改.打开服务器资源管理器，点到数据库的存储过程文件夹。如图12所示，Northwind数据库包含了4个新的存储过程，Products_Delete, Products_Insert, P

24、roducts_Select, and Products_Update.图12：可以在Stored Procedures文件夹里找到我们创建的4个存储过程注意：如果你看不到服务器资源管理器，点“View”菜单，选Server Explorer项.如果你无法找到新创建的存储过程，右击Stored Procedures文件夹，选“刷新”.要查看或修改某个存储过程，在服务器资源管理器里双击其名字或右击该存储过程，选”打开“。如13显示的是打开Products_Delete存储过程的画面.图13：可以在Visual Studio里打开并修改存储过程Products_Delete和Products_Se

25、lect存储过程的内容很好理解。比如下面的代码构成了Products_Insert存储过程.?1234567891011121314151617181920212223ALTER PROCEDURE dbo.Products_Insert( ProductName nvarchar(40), SupplierID int, CategoryID int, QuantityPerUnit nvarchar(20), UnitPrice money, UnitsInStock smallint, UnitsOnOrder sma

26、llint, ReorderLevel smallint, Discontinued bit)AS SET NOCOUNT OFF;INSERT INTO Products (ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)VALUES (ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,

27、 UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued);  SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, DiscontinuedFROM ProductsWHERE (ProductID = SCOPE_IDENTITY()在TableAdapter向导里定义的SELECT查询返回Product

28、s表里的列，这些列又作为存储过程的输入参数并运用到INSERT statement中.紧接着的是一个SELECT查询，返回Products表里最新添加的记录的各列的值(包括ProductID)。当使用Batch Update模式添加一个新记录时，刷新功能是很有用的。因为它将最新添加的ProductRow instances实例的ProductID属性赋值为数据库指派的自增值.下面的代码说明了该功能.代码创建了基于NorthwindWithSprocs数据集的ProductsTableAdapter以及ProductsDataTable。要向数据库添加一个新的产品，我们要创建一个Products

29、Row instance实例，对其赋值，并调用TableAdapter的Update方法，再传递给ProductsDataTable.在内部，TableAdapter的Update方法遍历传递给DataTable的所有ProductsRow instance实例(在本例，只有一个。因为我们只添加了一个产品)，并执行相应的insert, update, 或delete命令。此时，执行Products_Insert存储过程，其向Products表添加一条新记录，并返回该记录的详细信息，然后更新ProductsRow instance实例的ProductID值。Update方法完成后，我们就可以通过

30、ProductsRow的ProductID属性访问新添加记录的ProductID值了.?1234567891011121314151617181920/ Create the ProductsTableAdapter and ProductsDataTableNorthwindWithSprocsTableAdapters.ProductsTableAdapter productsAPI = new NorthwindWithSprocsTableAdapters.ProductsTableAdapter();NorthwindWithSprocs.ProductsDataTable

31、 products = new NorthwindWithSprocs.ProductsDataTable(); / Create a new ProductsRow instance and set its propertiesNorthwindWithSprocs.ProductsRow product = products.NewProductsRow();product.ProductName = "New Product"product.CategoryID = 1; / Beveragesproduct.Discontinued = fals

32、e; / Add the ProductsRow instance to the DataTableproducts.AddProductsRow(product); / Update the DataTable using the Batch Update patternproductsAPI.Update(products); / At this point, we can determine the value of the newly-added record's ProductIDint newlyAddedProductIDValue = pr

33、oduct.ProductID;类似的，Products_Update存储过程的UPDATE statement后面也包含一个SELECT statement，如下：?12345678910111213141516171819202122232425262728ALTER PROCEDURE dbo.Products_Update( ProductName nvarchar(40), SupplierID int, CategoryID int, QuantityPerUnit nvarchar(20), UnitPrice money,

34、60;UnitsInStock smallint, UnitsOnOrder smallint, ReorderLevel smallint, Discontinued bit, Original_ProductID int, ProductID int)AS SET NOCOUNT OFF;UPDATE ProductsSET ProductName = ProductName, SupplierID = SupplierID, CategoryID = CategoryID, QuantityPerUnit = Quan

35、tityPerUnit, UnitPrice = UnitPrice, UnitsInStock = UnitsInStock, UnitsOnOrder = UnitsOnOrder, ReorderLevel = ReorderLevel, Discontinued = DiscontinuedWHERE (ProductID = Original_ProductID);  SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPri

36、ce, UnitsInStock, UnitsOnOrder, ReorderLevel, DiscontinuedFROM ProductsWHERE (ProductID = ProductID)我们注意到该存储过程有2个关于ProductID的参数，即Original_ProductID 和ProductID，这样以来我们就可以对主键值进行改动了.举个例子：有一个employee(雇员)数据库，每条employee记录都用雇员的社保号码作为其主键值.要想更改某条记录的社保号码，必须提供新的号码以及原始号码.不过对Products表来说用不着，因为列ProductID是一个唯一标识列(ID

37、ENTITY column)，不应对其更改.实际上，Products_Update存储过程里的UPDATE statement并没有包含ProductID列，因此，如果在UPDATE statement的WHERE字句里使用Original_ProductID的话，显得多此一举，而应该使用ProductID参数.当更新某个存储过程的参数时，TableAdapter里所有那些调用该存储过程方法都应该进行更新.第四步：修改存储过程的参数并更新TableAdapter由于Original_ProductID参数是多余的，让我们将其从Products_Update存储过程里完全清除.打开Product

38、s_Update存储过程，删除Original_ProductID参数，在UPDATE statement的WHERE字句里将Original_ProductID改为ProductID. 完成上述修改后，该存储过程里的T-SQL看起来应该和下面的差不多：?1234567891011121314151617181920212223242526ALTER PROCEDURE dbo.Products_Update( ProductName nvarchar(40), SupplierID int, CategoryID int, QuantityPerUni

39、t nvarchar(20), UnitPrice money, UnitsInStock smallint, UnitsOnOrder smallint, ReorderLevel smallint, Discontinued bit, ProductID int)AS SET NOCOUNT OFF;UPDATE Products SET ProductName = ProductName, SupplierID = SupplierID, CategoryID = CategoryID, QuantityPe

40、rUnit = QuantityPerUnit, UnitPrice = UnitPrice, UnitsInStock = UnitsInStock, UnitsOnOrder = UnitsOnOrder, ReorderLevel = ReorderLevel, Discontinued = DiscontinuedWHERE (ProductID = ProductID);  SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, Unit

41、Price, UnitsInStock, UnitsOnOrder, ReorderLevel, DiscontinuedFROM ProductsWHERE (ProductID = ProductID)按Ctrl+S或点工具栏里的“保存”图标，保存更改.此时，Products_Update存储过程不会执行Original_ProductID参数，但TableAdapter仍然会传递该参数.要想查看TableAdapter传递给Products_Update存储过程的参数，你可以在设计器里选中TableAdapter，转到属性窗口，点更新命令的参数集(UpdateCommand'sP

42、arameters collection)里的椭圆型区域，这样将转到Parameters Collection Editor对话框，如图14所示：图14：对话框里列出了传递给Products_Update存储过程的参数要删除参数，只需选中它，再点Remove按钮.要刷新参数的话，你也可以在设计器里选中TableAdapter，点右键选“设置”，这将会开启TableAdapter设置向导，它列出了用于select, insert, updat和delete的存储过程,并列出了这些存储过程的输入参数.如果你在Update下拉列表里选Products_Update的话，你可以看到该存储过程包含的输入

43、参数里已经没有包含Original_ProductID了(见图15)，点Finish将对TableAdapter使用的参数集自动更新. 图15：你可以通过使用TableAdapter的设置向导来刷新参数集第五步：添加额外的TableAdapter方法我们在第二步说过，当创建一个新的TableAdapter时，很容易自动地生成相应的存储过程，同样我们也可以向TableAdapter添加额外的方法.作为演示，让我们向ProductsTableAdapter添加一个方法GetProductByProductID(productID)，该方法将一个ProductID作为输入参数，并返回该产品

44、的详细信息.在ProductsTableAdapter上点击右键，选择“添加查询”.图16：向TableAdapter添加新查询这将开启TableAdapter查询设置向导。首先，向导将询问以何种方式访问数据库，我们将创建一个新的存储过程，因此选“Create a new stored procedure”，再点Next.图17：选中“Create a new stored procedure”项接下来，向导询问我们执行哪种查询，是返回一系列行？一个标量值？又或者执行UPDATE, INSERT,或 DELETE statement.由于GetProductByProductID(produc

45、tID)方法将返回一行，我们选择“SELECT which returns row”项，再点Next.图18：选择“SELECT which returns row” 项接下来的界面将展示TableAdapter的主查询，其仅仅列出了存储过程的名字(也就是dbo.Products_Select).将其删除，替换为如下的SELECT statement，它返回某个具体产品的所有列.?12345SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, U

46、nitsOnOrder, ReorderLevel, DiscontinuedFROM ProductsWHERE ProductID = ProductID图19：将存储过程的名字替换为一个SELECT查询.接下来要对创建的存储过程命名，输入Products_SelectByProductID，点Next.图20：将新存储过程命名为Products_SelectByProductID最后一步将要我们对自动生成的名字重新命名，并指定是否使用Fill a DataTable模式、是否使用Return a DataTable模式，抑或这2种模式都采用.就本文而言，都选中这2项并将方法重命

47、名为FillByProductID 和 GetProductByProductID.点Next，再点Finish完成设置向导. 图21：将TableAdapter的方法重命名为FillByProductID 和 GetProductByProductID完成向导后，TableAdapter将包含一个新的可用方法GetProductByProductID(productID)，当调用该方法时，将执行我们刚刚创建的Products_SelectByProductID存储过程.花点时间在服务器资源管理器里查看该存储过程，点Stored Procedures文件夹，并打开Products_S

48、electByProductID(如果你没看到它，在Stored Procedures文件夹上右击鼠标，选“刷新”).请注意，SelectByProductID存储过程将ProductID作为输入参数，并执行我们在向导里输入的SELECT Statement，如下：?123456789101112ALTER PROCEDURE dbo.Products_SelectByProductID( ProductID int)AS SET NOCOUNT ON; SELECT ProductID, ProductName, SupplierID, CategoryID,&

49、#160;QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, DiscontinuedFROM ProductsWHERE ProductID = ProductID第六步：创建一个业务逻辑层类在我们打算从表现层访问产品前，我们首先需要为新添加的数据集创建一个BLL class，在/App_Code/BLL文件夹里创建一个ProductsBLLWithSprocs.cs文件，如下：?1234567891011121314151617181920212223242526272829303132333

50、435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551

51、56using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using NorthwindWithSprocsTableAdapters; System.ComponentModel.Data

52、Objectpublic class ProductsBLLWithSprocs private ProductsTableAdapter _productsAdapter = null; protected ProductsTableAdapter Adapter  get   if (_productsAdapter = null)  _productsAdapter = new ProductsTableAdapter();   return _productsAdap

53、ter;    System.ComponentModel.DataObjectMethodAttribute (System.ComponentModel.DataObjectMethodType.Select, true) public NorthwindWithSprocs.ProductsDataTable GetProducts()  return Adapter.GetProducts();    System.ComponentModel.DataObjectM

54、ethodAttribute (System.ComponentModel.DataObjectMethodType.Select, false) public NorthwindWithSprocs.ProductsDataTable GetProductByProductID(int productID)  return Adapter.GetProductByProductID(productID);    System.ComponentModel.DataObjectMethodAttribute

55、0;(System.ComponentModel.DataObjectMethodType.Insert, true) public bool AddProduct (string productName, int? supplierID, int? categoryID,  string quantityPerUnit, decimal? unitPrice, short? unitsInStock,  short? unitsOnOrder, short? reorderLevel, bool discontinued) 

56、; / Create a new ProductRow instance NorthwindWithSprocs.ProductsDataTable products =  new NorthwindWithSprocs.ProductsDataTable(); NorthwindWithSprocs.ProductsRow product = products.NewProductsRow();  product.ProductName = productName; if (supplierID = null)&

57、#160; product.SetSupplierIDNull(); else  product.SupplierID = supplierID.Value; if (categoryID = null)  product.SetCategoryIDNull(); else  product.CategoryID = categoryID.Value; if (quantityPerUnit = null)  product.SetQuantityPerUnitNu

58、ll(); else  product.QuantityPerUnit = quantityPerUnit; if (unitPrice = null)  product.SetUnitPriceNull(); else  product.UnitPrice = unitPrice.Value; if (unitsInStock = null)  product.SetUnitsInStockNull(); else  product.Units

59、InStock = unitsInStock.Value; if (unitsOnOrder = null)  product.SetUnitsOnOrderNull(); else  product.UnitsOnOrder = unitsOnOrder.Value; if (reorderLevel = null)  product.SetReorderLevelNull(); else  product.ReorderLevel = reorderLevel.Value; product.Discontinued = discontinued;  / Add the new product products.AddProductsRow(product);