在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>