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