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();
}
}