DLINQ(LINQ to SQL)之面向对象的添加、查询、更新和删除

介绍
  以Northwind为示例数据库，DLINQ(LINQ to SQL)之完全面向对象的添加操作、查询操作、更新操作和删除操作


  示例
  Sample.aspx

  
  

   
   
   
   <%
   
   @ Page Language
   
   =
   
   "
   
   C#
   
   "
   
    MasterPageFile
   
   =
   
   "
   
   ~/Site.master
   
   "
   
    AutoEventWireup
   
   =
   
   "
   
   true
   
   "
   
    
  
  
  
  

   
   CodeFile
   
   =
   
   "
   
   Sample.aspx.cs"
   
   

   
       Inherits
   
   =
   
   "
   
   LINQ_DLINQ_Sample
   
   "
   
    Title
   
   =
   
   "
   
   面向对象的添加、查询、更新和删除
   
   "
   
    
   
   %>
   
   

   
   <
   
   asp:Content 
   
   ID
   
   ="Content1"
   
    ContentPlaceHolderID
   
   ="head"
   
    runat
   
   ="Server"
   
   >
   
   

   
   </
   
   asp:Content
   
   >
   
   

   
   <
   
   asp:Content 
   
   ID
   
   ="Content2"
   
    ContentPlaceHolderID
   
   ="ContentPlaceHolder1"
   
    
  
  
  
  

   
   runat
   
   ="Server"
   
   >
   
   

   
   <
   
   p
   
   >
   
   
分类名称：
   
   <
   
   asp:TextBox 
   
   ID
   
   ="txtCategoryName"
   
    runat
   
   ="server"
   
   ></
   
   asp:TextBox
   
   >
   
   
分类描述：
   
   <
   
   asp:TextBox 
   
   ID
   
   ="txtDescription"
   
    runat
   
   ="server"
   
   ></
   
   asp:TextBox
   
   >
   
   

   
   <
   
   asp:Button 
   
   ID
   
   ="btnAdd"
   
    runat
   
   ="server"
   
    Text
   
   ="添加"
   
    OnClick
   
   ="btnAdd_Click"
   
    
   
   />
   
   

   
   </
   
   p
   
   >
   
   

   
   <
   
   asp:GridView 
   
   ID
   
   ="gvCategory"
   
    runat
   
   ="server"
   
    DataKeyNames
   
   ="CategoryID"
   
    
  
  
  
  

   
   OnSelectedIndexChanged
   
   ="gvCategory_SelectedIndexChanged"
   
   
OnRowDeleting
   
   ="gvCategory_RowDeleting"
   
    
  
  
  
  

   
   OnRowCancelingEdit
   
   ="gvCategory_RowCancelingEdit"
   
   
OnRowEditing
   
   ="gvCategory_RowEditing"
   
    OnRowUpdating
   
   ="gvCategory_RowUpdating"
   
   >
   
   

   
   <
   
   Columns
   
   >
   
   

   
   <
   
   asp:CommandField 
   
   ShowSelectButton
   
   ="True"
   
    ShowEditButton
   
   ="True"
   
    
  
  
  
  

   
   ShowDeleteButton
   
   ="True"
   
   >
   
   

   
   </
   
   asp:CommandField
   
   >
   
   

   
   </
   
   Columns
   
   >
   
   

   
   </
   
   asp:GridView
   
   >
   
   

   
   <
   
   br 
   
   />
   
   

   
   <
   
   asp:DetailsView 
   
   ID
   
   ="dvProduct"
   
    runat
   
   ="server"
   
    DataKeyNames
   
   ="ProductID"
   
   >
   
   

   
   </
   
   asp:DetailsView
   
   >
   
   

   
   </
   
   asp:Content
   
   >
  
  

  Sample.aspx.cs

  
  

   
   
   
   using
   
    System;

   
   using
   
    System.Data;

   
   using
   
    System.Configuration;

   
   using
   
    System.Collections;

   
   using
   
    System.Linq;

   
   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
   
    System.Xml.Linq;

   
   using
   
    DAL;

   
   public
   
    partial 
   
   class
   
    LINQ_DLINQ_Sample : System.Web.UI.Page
{

   
   //
   
    实例化一个NorthwindDataContext（DataContext）
   
   

   
   NorthwindDataContext _ctx 
   
   =
   
    
   
   new
   
    NorthwindDataContext();

   
   protected
   
    
   
   void
   
    Page_Load(
   
   object
   
    sender, EventArgs e)
{

   
   if
   
    (
   
   !
   
   Page.IsPostBack)
{
BindCategory();
}
}

   
   private
   
    
   
   void
   
    BindCategory()
{

   
   //
   
    NorthwindDataContext对象的Category属性就是Category集合
   
   

   
   var categories 
   
   =
   
    _ctx.Categories;
gvCategory.DataSource 
   
   =
   
    categories;
gvCategory.DataBind();
}

   
   protected
   
    
   
   void
   
    btnAdd_Click(
   
   object
   
    sender, EventArgs e)
{

   
   //
   
    实例化一个Category
   
   

   
   Categories c 
   
   =
   
    
   
   new
   
    Categories();

   
   //
   
    设置Category对象的相关属性
   
   

   
   c.CategoryName 
   
   =
   
    txtCategoryName.Text;
c.Description 
   
   =
   
    txtDescription.Text;

   
   //
   
    使用NorthwindDataContext对象的InsertOnSubmit()方法添加Category对象
   
   

   
   _ctx.Categories.InsertOnSubmit(c);

   
   //
   
    生成并执行相应的SQL命令
   
   

   
   _ctx.SubmitChanges();
gvCategory.EditIndex 
   
   =
   
    
   
   -
   
   1
   
   ;
BindCategory();
}

   
   protected
   
    
   
   void
   
    gvCategory_SelectedIndexChanged(
   
   object
   
    sender, EventArgs e)
{

   
   //
   
    使用查询语法获得Product集合
   
   

   
   var products 
   
   =
   
    from p 
   
   in
   
    _ctx.Products
where p.Categories.CategoryID 
   
   ==
   
    (
   
   int
   
   )gvCategory.SelectedValue
select p;
dvProduct.DataSource 
   
   =
   
    products;
dvProduct.DataBind();
}

   
   protected
   
    
   
   void
   
    gvCategory_RowDeleting(
   
   object
   
    sender, GridViewDeleteEventArgs e)
{

   
   //
   
    使用Single查询操作符获取指定的Category对象
   
   

   
   Categories category 
   
   =
   
    _ctx.Categories.Single(c 
   
   =>
   
    c.CategoryID 
   
   ==
   
    
  
  
  
  

   
   (
   
   int
   
   )gvCategory.DataKeys[e.RowIndex].Value);

   
   //
   
    使用DeleteOnSubmit()方法删除NorthwindDataContext对象的Category集合中的指定
  
  
  
  

   
   Category对象
   
   

   
   _ctx.Categories.DeleteOnSubmit(category);

   
   //
   
    生成并执行相应的SQL命令
   
   

   
   _ctx.SubmitChanges();
gvCategory.EditIndex 
   
   =
   
    
   
   -
   
   1
   
   ;
BindCategory();
}

   
   protected
   
    
   
   void
   
    gvCategory_RowUpdating(
   
   object
   
    sender, GridViewUpdateEventArgs e)
{

   
   //
   
    使用查询语法和Single查询操作符获取指定的Category对象
   
   

   
   Categories category 
   
   =
   
    (from c 
   
   in
   
    _ctx.Categories
where c.CategoryID 
   
   ==
   
    (
   
   int
   
   )gvCategory.DataKeys[e.RowIndex].Value
select c).Single();

   
   //
   
    设置Category对象的相关属性
   
   

   
   category.CategoryName 
   
   =
   
    ((TextBox)
  
  
  
  

   
   gvCategory.Rows[e.RowIndex].Cells[
   
   2
   
   ].Controls[
   
   0
   
   ]).Text;
category.Description 
   
   =
   
    ((TextBox)
  
  
  
  

   
   gvCategory.Rows[e.RowIndex].Cells[
   
   3
   
   ].Controls[
   
   0
   
   ]).Text;

   
   //
   
    生成并执行相应的SQL命令
   
   

   
   _ctx.SubmitChanges();
gvCategory.EditIndex 
   
   =
   
    
   
   -
   
   1
   
   ;
BindCategory();
}

   
   protected
   
    
   
   void
   
    gvCategory_RowEditing(
   
   object
   
    sender, GridViewEditEventArgs e)
{
gvCategory.EditIndex 
   
   =
   
    e.NewEditIndex;
BindCategory();
}

   
   protected
   
    
   
   void
   
    gvCategory_RowCancelingEdit(
   
   object
   
    sender, 
  
  
  
  

   
   GridViewCancelEditEventArgs e)
{
gvCategory.EditIndex 
   
   =
   
    
   
   -
   
   1
   
   ;
BindCategory();
}
}