在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? supplierID, int? categoryID,
  string quantityPerUnit, decimal? unitPrice, short? unitsInStock,
  short? unitsOnOrder, short? reorderLevel, bool discontinued)
 {
 // Create a new ProductRow instance
 NorthwindWithSprocs.ProductsDataTable products =
  new NorthwindWithSprocs.ProductsDataTable();
 NorthwindWithSprocs.ProductsRow product = products.NewProductsRow();

 product.ProductName = productName;
 if (supplierID == null)
  product.SetSupplierIDNull();
 else
  product.SupplierID = supplierID.Value;
 if (categoryID == null)
  product.SetCategoryIDNull();
 else
  product.CategoryID = categoryID.Value;
 if (quantityPerUnit == null)
  product.SetQuantityPerUnitNull();
 else
  product.QuantityPerUnit = quantityPerUnit;
 if (unitPrice == null)
  product.SetUnitPriceNull();
 else
  product.UnitPrice = unitPrice.Value;
 if (unitsInStock == null)
  product.SetUnitsInStockNull();
 else
  product.UnitsInStock = 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);
 int rowsAffected = Adapter.Update(products);

 // Return true if precisely one row was inserted, otherwise false
 return rowsAffected == 1;
 }

 [System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Update, true)]
 public bool UpdateProduct
 (string productName, int? supplierID, int? categoryID, string quantityPerUnit,
 decimal? unitPrice, short? unitsInStock, short? unitsOnOrder,
 short? reorderLevel, bool discontinued, int productID)
 {
 NorthwindWithSprocs.ProductsDataTable products =
  Adapter.GetProductByProductID(productID);
 if (products.Count == 0)
  // no matching record found, return false
  return false;

 NorthwindWithSprocs.ProductsRow product = products[0];

 product.ProductName = productName;
 if (supplierID == null)
  product.SetSupplierIDNull();
 else
  product.SupplierID = supplierID.Value;
 if (categoryID == null)
  product.SetCategoryIDNull();
 else
  product.CategoryID = categoryID.Value;
 if (quantityPerUnit == null)
  product.SetQuantityPerUnitNull();
 else
  product.QuantityPerUnit = quantityPerUnit;
 if (unitPrice == null)
  product.SetUnitPriceNull();
 else
  product.UnitPrice = unitPrice.Value;
 if (unitsInStock == null)
  product.SetUnitsInStockNull();
 else
  product.UnitsInStock = 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;

 // Update the product record
 int rowsAffected = Adapter.Update(product);

 // Return true if precisely one row was updated, otherwise false
 return rowsAffected == 1;
 }

 [System.ComponentModel.DataObjectMethodAttribute
 (System.ComponentModel.DataObjectMethodType.Delete, true)]
 public bool DeleteProduct(int productID)
 {
 int rowsAffected = Adapter.Delete(productID);

 // Return true if precisely one row was deleted, otherwise false
 return rowsAffected == 1;
 }
}

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>