在ASP.NET 2.0中操作数据之六十五：在TableAdapters中创建新的存储过程

CREATE PROCEDURE GetProductsByCategoryID
(
 @CategoryID int
)
AS

SELECT ProductID, ProductName, UnitPrice, Discontinued
FROM Products
WHERE CategoryID = @CategoryID

EXEC GetProductsByCategory categoryID

<siteMapNode url="~/AdvancedDAL/Default.aspx"
 title="Advanced DAL Scenarios"
 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 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." />
 <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="~/AdvancedDAL/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"
 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 connection 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 procedures
 using managed code." />
</siteMapNode>

SELECT ProductID, ProductName, SupplierID, CategoryID,
 QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
 ReorderLevel, Discontinued
FROM Products

ALTER PROCEDURE dbo.Products_Insert
(
 @ProductName nvarchar(40),
 @SupplierID int,
 @CategoryID int,
 @QuantityPerUnit nvarchar(20),
 @UnitPrice money,
 @UnitsInStock smallint,
 @UnitsOnOrder smallint,
 @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,
 @UnitsInStock, @UnitsOnOrder, @ReorderLevel, @Discontinued);
 
SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,
 UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products
WHERE (ProductID = SCOPE_IDENTITY())

// 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;

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)

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)

SELECT ProductID, ProductName, SupplierID, CategoryID,
 QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
 ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID

ALTER PROCEDURE dbo.Products_SelectByProductID
(
 @ProductID int
)
AS
 SET NOCOUNT ON;

SELECT ProductID, ProductName, SupplierID, CategoryID,
 QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder,
 ReorderLevel, Discontinued
FROM Products
WHERE ProductID = @ProductID

using 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.DataObject]
public class ProductsBLLWithSprocs
{
 private ProductsTableAdapter _productsAdapter = null;
 protected ProductsTableAdapter Adapter
 {
 get
 {
  if (_productsAdapter == null)
  _productsAdapter = new ProductsTableAdapter();

  return _productsAdapter;
 }
 }

 [System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Select, true)]
 public NorthwindWithSprocs.ProductsDataTable GetProducts()
 {
 return Adapter.GetProducts();
 }


 [System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Select, false)]
 public NorthwindWithSprocs.ProductsDataTable GetProductByProductID(int productID)
 {
 return Adapter.GetProductByProductID(productID);
 }


 [System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Insert, true)]
 public bool AddProduct
 (string productName, int"//img.jbzj.com/file_images/article/201605/2016051811440088.png" src="/UploadFiles/2021-04-02/2016051811440088.png">

图22：设置ObjectDataSource调用ProductsBLLWithSprocs类

　　SELECT标签的下拉列表里有2个方法，GetProducts()和GetProductByProductID().由于我们将在GridView里显示所有的产品,所以我们选GetProducts()方法.在UPDATE, INSERT, 和DELETE标签里都只有一个方法，确保选中它们，点Finish按钮。
　　完成设置后，Visual Studio会向GridView添加BoundFields列以及一个CheckBoxField列， 启用GridView控件的“编辑”和“删除”功能.


图23：页面包含一个可以分页和排序的GridView控件.
　　就像在以前的教程里探讨过的一样，完成ObjectDataSource的设置后，Visual Studio 会自动的将OldValuesParameterFormatString属性设置为“original_{0}”. 为使数据修改功能正常工作，要么将该属性删除，要么将其设置为“{0}”.
　　在我们完成设置、启用“编辑”和“删除”功能、将OldValuesParameterFormatString属性设为其默认值后，页面的声明代码看起来应该和下面的差不多：


<asp:GridView ID="Products" runat="server" AutoGenerateColumns="False"
 DataKeyNames="ProductID" DataSourceID="ProductsDataSource">
 <Columns>
 <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
 <asp:BoundField DataField="ProductID" HeaderText="ProductID"
  InsertVisible="False" ReadOnly="True"
  SortExpression="ProductID" />
 <asp:BoundField DataField="ProductName" HeaderText="ProductName"
  SortExpression="ProductName" />
 <asp:BoundField DataField="SupplierID" HeaderText="SupplierID"
  SortExpression="SupplierID" />
 <asp:BoundField DataField="CategoryID" HeaderText="CategoryID"
  SortExpression="CategoryID" />
 <asp:BoundField DataField="QuantityPerUnit" HeaderText="QuantityPerUnit"
  SortExpression="QuantityPerUnit" />
 <asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice"
  SortExpression="UnitPrice" />
 <asp:BoundField DataField="UnitsInStock" HeaderText="UnitsInStock"
  SortExpression="UnitsInStock" />
 <asp:BoundField DataField="UnitsOnOrder" HeaderText="UnitsOnOrder"
  SortExpression="UnitsOnOrder" />
 <asp:BoundField DataField="ReorderLevel" HeaderText="ReorderLevel"
  SortExpression="ReorderLevel" />
 <asp:CheckBoxField DataField="Discontinued" HeaderText="Discontinued"
  SortExpression="Discontinued" />
 </Columns>
</asp:GridView>

<asp:ObjectDataSource ID="ProductsDataSource" runat="server"
 DeleteMethod="DeleteProduct" InsertMethod="AddProduct"
 SelectMethod="GetProducts" TypeName="ProductsBLLWithSprocs"
 UpdateMethod="UpdateProduct">
 <DeleteParameters>
 <asp:Parameter Name="productID" Type="Int32" />
 </DeleteParameters>
 <UpdateParameters>
 <asp:Parameter Name="productName" Type="String" />
 <asp:Parameter Name="supplierID" Type="Int32" />
 <asp:Parameter Name="categoryID" Type="Int32" />
 <asp:Parameter Name="quantityPerUnit" Type="String" />
 <asp:Parameter Name="unitPrice" Type="Decimal" />
 <asp:Parameter Name="unitsInStock" Type="Int16" />
 <asp:Parameter Name="unitsOnOrder" Type="Int16" />
 <asp:Parameter Name="reorderLevel" Type="Int16" />
 <asp:Parameter Name="discontinued" Type="Boolean" />
 <asp:Parameter Name="productID" Type="Int32" />
 </UpdateParameters>
 <InsertParameters>
 <asp:Parameter Name="productName" Type="String" />
 <asp:Parameter Name="supplierID" Type="Int32" />
 <asp:Parameter Name="categoryID" Type="Int32" />
 <asp:Parameter Name="quantityPerUnit" Type="String" />
 <asp:Parameter Name="unitPrice" Type="Decimal" />
 <asp:Parameter Name="unitsInStock" Type="Int16" />
 <asp:Parameter Name="unitsOnOrder" Type="Int16" />
 <asp:Parameter Name="reorderLevel" Type="Int16" />
 <asp:Parameter Name="discontinued" Type="Boolean" />
 </InsertParameters>
</asp:ObjectDataSource>




　　此时，我们可以对GridView控件做些修改，比如在编辑界面里使用确认控件，在CategoryID 和 SupplierID列放置DropDownList控件，当点击Delete按钮时弹出确认框等.由于在以前的教程我们探讨过这些主题，我不打算在此多花笔墨。
　　不管你做没做这些改进，让我们在浏览器里对页面测试，如图24所示.在GridView控件里每行都可以编辑和删除.


图24：可以通过GridView对产品进行查看、编辑、删除
结语：
　　类型化数据集里的TableAdapters可以通过ad-hoc SQL statement或存储过程访问数据库里的数据.当处理存储过程时，我们要么使用现有的存储过程，要么使用TableAdapter向导创建一个基于SELECT查询的新的存储过程.在本文，我们考察了如何自动的创建一个存储过程.
　　虽然自动创建可以节省时间，但是在某些情况下，向导自动创建的存储过程与我们的期望值还是有差距.比如自动创建的Products_Update存储过程，它包含@Original_ProductID 和 @ProductID这2个参数，但@Original_ProductID参数对我们来说是多余的.
在接下来的文章，我们将考察TableAdapter使用现有的存储过程的情况.
　　祝编程快乐！
作者简介
　　本系列教程作者 Scott Mitchell，著有六本ASP/ASP.NET方面的书，是4GuysFromRolla.com的创始人，自1998年以来一直应用 微软Web技术。大家可以点击查看全部教程《[翻译]Scott Mitchell 的ASP.NET 2.0数据教程》，希望对大家的学习ASP.NET有所帮助。