Linq To SQL学习(Part1)

从今天开始，学习Linq To SQL
数据库：Northwind，如果你的机器上还没有该数据库，点击这儿下载
[Demo下载]
(什么是Linq To SQL以及使用LINQ To SQL对数据库建模在这里就不做介绍了)

目的：用Linq To SQL实现对数据库的查询、操作(增,删,改)、以及调用存储过程和在服务器端分页查询

为了测试，这里会用到下面一个存储过程：
GetProductsByCategory.sql

USE [Northwind]

/****** Object: StoredProcedure [dbo].[GetProductsByCategory] Script Date: 04/08/2008 17:02:08 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[GetProductsByCategory]

@categoryID int

SELECT *

FROM Products

WHERE CategoryID=@categoryID

注：以下代码只是为了做测试，所以在写的过程中没有按照严格的编码规范来写，望谅解。

前台页面：LinqSample.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="LinqSample.aspx.cs" Inherits="LinqSample" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Product</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
        <asp:Button ID="Select" runat="server" Text="Show" onclick="Select_Click" />
        <asp:Button ID="Update" runat="server" Text="Update" onclick="Update_Click" />
        <asp:Button ID="Insert" runat="server" Text="Insert" onclick="Insert_Click" />
        <asp:Button ID="Delete" runat="server" Text="Delete" onclick="Delete_Click" />
        <asp:Button ID="WithProcedure" runat="server" Text="WithProcedure" onclick="WithProcedure_Click" />
        <asp:Button ID="DivisionPage" runat="server" Text="在服务器端分页查询"
            onclick="DivisionPage_Click" />
    </div>
    <div runat="server" id="msg" style="color:Red; font-size:larger"></div>
    </form>
</body>
</html>

后台代码：LinqSample.aspx.cs

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

public partial class LinqSample : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

//从数据库读取数据并显示在GridView上

protected void Select_Click(object sender, EventArgs e)

{

NorthwindDataContext db = new NorthwindDataContext();

var products = from product in db.Products

where product.Category.CategoryName == "Beverages"

select product;

GridView1.DataSource = products;

GridView1.DataBind();

}

//更新数据

protected void Update_Click(object sender, EventArgs e)

{

try

{

NorthwindDataContext db = new NorthwindDataContext();

Product product = db.Products.Single(p => p.ProductName == "Chai");

product.UnitPrice = 22;

product.UnitsInStock = 22;

db.SubmitChanges();

msg.InnerText="Update Success!";

}

catch(Exception ex)

{

msg.InnerText = ex.Message;

}

//添加新纪录

protected void Insert_Click(object sender, EventArgs e)

{

try

{

NorthwindDataContext db = new NorthwindDataContext();

Category category = new Category();

category.CategoryName = "WANGXQ Toy";

Product p1 = new Product();

p1.ProductName = "Toy 1";

Product p2 = new Product();

p2.ProductName = "Toy 2";

category.Products.Add(p1);

category.Products.Add(p2);

db.Categories.InsertOnSubmit(category);

db.SubmitChanges();

msg.InnerText = "Insert Success";

}

catch (Exception ex)

{

msg.InnerText = ex.Message;

}

//删除记录

protected void Delete_Click(object sender, EventArgs e)

{

try

{

NorthwindDataContext db = new NorthwindDataContext();

var toyProducts = from product in db.Products

where product.ProductName == "Toy 1"

select product;

db.Products.DeleteAllOnSubmit(toyProducts);

db.SubmitChanges();

msg.InnerText = "Delete Success!";

}

catch (Exception ex)

{

msg.InnerText = ex.Message;

}

//调用存储过程

protected void WithProcedure_Click(object sender, EventArgs e)

{

try

{

NorthwindDataContext db = new NorthwindDataContext();

var products = db.GetProductsByCategory(2);

GridView1.DataSource = products;

GridView1.DataBind();

msg.InnerText = "调用存储过程成功";

}

catch (Exception ex)

{

msg.InnerText = ex.Message;

}

//通过LINQ查询语法实现高效的服务器端数据库分页查询。通过使用下面的Skip()和Take()操作符，我们从数据库中只查询出从20行开始的8条记录：

protected void DivisionPage_Click(object sender, EventArgs e)

{

try

{

NorthwindDataContext db = new NorthwindDataContext();

var products = (from product in db.Products

select product).Skip(20).Take(8);

GridView1.DataSource = products;

GridView1.DataBind();

msg.InnerText = "调用服务器端分页成功";

}

catch (Exception ex)

{

msg.InnerText = ex.Message;

}

[Demo下载]

posted on 2008-04-08 17:17 北漂浪子阅读(287) 评论(0) 收藏举报