在TableAdapter向导里定义的SELECT查询返回Products表里的列,这些列又作为存储过程的输入参数并运用到INSERT statement中.紧接着的是一个SELECT查询,返回Products表里最新添加的记录的各列的值(包括ProductID)。当使用Batch Update模式添加一个新记录时,刷新功能是很有用的。因为它将最新添加的ProductRow instances实例的ProductID属性赋值为数据库指派的自增值.
下面的代码说明了该功能.代码创建了基于NorthwindWithSprocs数据集的ProductsTableAdapter以及ProductsDataTable。要向数据库添加一个新的产品,我们要创建一个ProductsRow instance实例,对其赋值,并调用TableAdapter的Update方法,再传递给ProductsDataTable.在内部,TableAdapter的Update方法遍历传递给DataTable的所有ProductsRow instance实例(在本例,只有一个。因为我们只添加了一个产品),并执行相应的insert, update, 或delete命令。此时,执行Products_Insert存储过程,其向Products表添加一条新记录,并返回该记录的详细信息,然后更新ProductsRow instance实例的ProductID值。Update方法完成后,我们就可以通过ProductsRow的ProductID属性访问新添加记录的ProductID值了.
// Create the ProductsTableAdapter and ProductsDataTable NorthwindWithSprocsTableAdapters.ProductsTableAdapter productsAPI = new NorthwindWithSprocsTableAdapters.ProductsTableAdapter(); NorthwindWithSprocs.ProductsDataTable products = new NorthwindWithSprocs.ProductsDataTable(); // Create a new ProductsRow instance and set its properties NorthwindWithSprocs.ProductsRow product = products.NewProductsRow(); product.ProductName = "New Product"; product.CategoryID = 1; // Beverages product.Discontinued = false; // Add the ProductsRow instance to the DataTable products.AddProductsRow(product); // Update the DataTable using the Batch Update pattern productsAPI.Update(products); // At this point, we can determine the value of the newly-added record's ProductID int newlyAddedProductIDValue = product.ProductID;
类似的,Products_Update存储过程的UPDATE statement后面也包含一个SELECT statement,如下:
ALTER PROCEDURE dbo.Products_Update ( @ProductName nvarchar(40), @SupplierID int, @CategoryID int, @QuantityPerUnit nvarchar(20), @UnitPrice money, @UnitsInStock smallint, @UnitsOnOrder smallint, @ReorderLevel smallint, @Discontinued bit, @Original_ProductID int, @ProductID int ) AS SET NOCOUNT OFF; UPDATE [Products] SET [ProductName] = @ProductName, [SupplierID] = @SupplierID, [CategoryID] = @CategoryID, [QuantityPerUnit] = @QuantityPerUnit, [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel, [Discontinued] = @Discontinued WHERE (([ProductID] = @Original_ProductID)); SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE (ProductID = @ProductID)
我们注意到该存储过程有2个关于ProductID的参数,即@Original_ProductID 和@ProductID,这样以来我们就可以对主键值进行改动了.举个例子:有一个employee(雇员)数据库,每条employee记录都用雇员的社保号码作为其主键值.要想更改某条记录的社保号码,必须提供新的号码以及原始号码.不过对Products表来说用不着,因为列ProductID是一个唯一标识列(IDENTITY column),不应对其更改.实际上,Products_Update存储过程里的UPDATE statement并没有包含ProductID列,因此,如果在UPDATE statement的WHERE字句里使用@Original_ProductID的话,显得多此一举,而应该使用@ProductID参数.当更新某个存储过程的参数时,TableAdapter里所有那些调用该存储过程方法都应该进行更新.
第四步:修改存储过程的参数并更新TableAdapter
由于@Original_ProductID参数是多余的,让我们将其从Products_Update存储过程里完全清除.打开Products_Update存储过程,删除@Original_ProductID参数,在UPDATE statement的WHERE字句里将@Original_ProductID改为@ProductID. 完成上述修改后,该存储过程里的T-SQL看起来应该和下面的差不多:
ALTER PROCEDURE dbo.Products_Update ( @ProductName nvarchar(40), @SupplierID int, @CategoryID int, @QuantityPerUnit 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, [QuantityPerUnit] = @QuantityPerUnit, [UnitPrice] = @UnitPrice, [UnitsInStock] = @UnitsInStock, [UnitsOnOrder] = @UnitsOnOrder, [ReorderLevel] = @ReorderLevel, [Discontinued] = @Discontinued WHERE (([ProductID] = @ProductID)); SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products WHERE (ProductID = @ProductID)