Linq to SQL学习

wslsg

　　在.net3.5中出现了一个很新的技术，那就是Linq to SQL,一直听论坛里面的朋友说这Linq to SQL做起事来很方便，如果用他的话就会喜欢上这个，最近就怀着好奇的思想搞了一把，确实不错，能做的东西很多，也很方便。下面是我的具体操作
一，示例数据库

字段名	字段类型	允许空	字段说明
ID	uniqueidentifier		表主键字段
UserName	varchar(50)		留言用户名
PostTime	datetime		留言时间
Message	varchar(400)	√	留言内容
IsReplied	bit		留言是否回复
Reply	varchar(400)	√	留言管理员回复

在数据库中创建一个名为GuestBook的数据库，在里面创建一个tbGuestBook的表，结构如上表。

二，生成实体类
右键点击网站项目，选择添加新项，然后选择“Linq to sql Classes”，命名为GuestBook。然后打开App_Code里面的GuestBook.dbml。设计视图上的文字提示你可以从服务器资源管理器或者攻击箱拖动项到设计界面上来创建实体类。
那么，我们就在服务器资源管理器中创建一个指向GuestBook数据库的数据连接，然后把tbGuestBook表拖动到GuestBook.dbml的设计视图上，按CTRL+S保存。打开GuestBook.designer.cs可以发现系统自动创建了GuestBook数据库中tbGuestBook表的映射。


三，相关的CRUD方法，基本包含常用的方法，如下
　　using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using System.Data.Linq;using Model;namespace Default{public partial class AllList : System.Web.UI.Page{GuestBookDataContext guestbook = new GuestBookDataContext("server=.;database=GuestBook;uid=sa;pwd=111");protected void Page_Load(object sender, EventArgs e){if (!IsPostBack){LoadData();}}void LoadData(){SetBindByBasic();var resutl = from c in guestbook.tbGuestBookselect new{编号 = c.ID,姓名 = c.UserName,};this.ddlID1.DataSource = resutl;this.ddlID1.DataValueField = "编号";this.ddlID1.DataTextField = "编号";this.ddlID1.DataBind();this.ddlID2.DataSource = resutl;this.ddlID2.DataValueField = "编号";this.ddlID2.DataTextField = "编号";this.ddlID2.DataBind();this.ddlID3.DataSource = resutl;this.ddlID3.DataValueField = "编号";this.ddlID3.DataTextField = "编号";this.ddlID3.DataBind();}//************************************************************简单查询********************************************//基础查询,单项返回void SetBindBySingle(){var resutl = from c in guestbook.tbGuestBook select c.ID;this.gv.DataSource = resutl;this.gv.DataBind();}//基础查询void SetBindByBasic(){var resutl = from c in guestbook.tbGuestBook select c;this.gv.DataSource = resutl;this.gv.DataBind();}//where条件查询void SetBindByWhere(){var resutl = from c in guestbook.tbGuestBook where c.ID > 2 select c;this.gv.DataSource = resutl;this.gv.DataBind();}//挑选列void SetBindByNew(){var resutl = from c in guestbook.tbGuestBookselect new{姓名 = c.UserName,时间 = c.PostTime,消息 = c.Messages,};this.gv.DataSource = resutl;this.gv.DataBind();}//排除重复void SetBindByDistinct(){var resutl = (from c in guestbook.tbGuestBookselect c.UserName).Distinct();this.gv.DataSource = resutl;this.gv.DataBind();}//排序void SetBindByOrderby(){var resutl = from c in guestbook.tbGuestBookorderby c.UserName descending,c.ID ascendingselect new{编号 = c.ID,姓名 = c.UserName,消息 = c.Messages,};this.gv.DataSource = resutl;this.gv.DataBind();}//查询包含void SetBindByContains(){var resutl = from c in guestbook.tbGuestBookwhere c.UserName.Contains("s")select new{编号 = c.ID,姓名 = c.UserName,消息 = c.Messages,};this.gv.DataSource = resutl;this.gv.DataBind();}//查询以什么开头void SetBindByStartWith(){var resutl = from c in guestbook.tbGuestBookwhere c.UserName.StartsWith("s")select new{编号 = c.ID,姓名 = c.UserName,消息 = c.Messages,};this.gv.DataSource = resutl;this.gv.DataBind();}//行数void SetBindByTake(){var resutl = (from c in guestbook.tbGuestBookselect new{编号 = c.ID,姓名 = c.UserName,消息 = c.Messages,}).Take(5);this.gv.DataSource=resutl;this.gv.DataBind();}//分页(排除前面的行数)void SetBindBySkip(){var resutl = (from c in guestbook.tbGuestBookselect new{编号 = c.ID,姓名 = c.UserName,消息 = c.Messages,}).Skip(5);this.gv.DataSource = resutl;this.gv.DataBind();}//ToListvoid SetBindByToList(){var resutl = (from c in guestbook.tbGuestBookselect new{编号 = c.ID,姓名 = c.UserName,消息 = c.Messages,}).ToList();this.gv.DataSource = resutl;this.gv.DataBind();}//分组排序void SetBindByGroup(){var resutl = from c in guestbook.tbGuestBookgroup c by c.UserName into gwhere g.Count()>2orderby g.Count()select new{编号 = g.Key,行数 = g.Count(),};this.gv.DataSource = resutl;this.gv.DataBind();}//************************************************************组合查询********************************************//Union,过滤void SetBindByUnion(){var resutl = (from c in guestbook.tbGuestBookwhere c.UserName.Contains("s")select c).Union(from c in guestbook.tbGuestBookwhere c.UserName.StartsWith("s")select c);this.gv.DataSource = resutl;this.gv.DataBind();}//Contact,不过滤void SetBindByConcat(){var resutl = (from c in guestbook.tbGuestBookwhere c.UserName.Contains("s")select c).Concat(from c in guestbook.tbGuestBookwhere c.UserName.StartsWith("s")select c);this.gv.DataSource = resutl;this.gv.DataBind();}//Intersect,取相交项void SetBindByIntersect(){var resutl = (from c in guestbook.tbGuestBookwhere c.Messages.Contains("1")select c).Intersect(from c in guestbook.tbGuestBookwhere c.UserName.StartsWith("s")select c);this.gv.DataSource = resutl;this.gv.DataBind();}//Except,排除相交项void SetBindByExcept(){var resutl = (from c in guestbook.tbGuestBookwhere c.Messages.Contains("1")select c).Except(from c in guestbook.tbGuestBookwhere c.UserName.StartsWith("s")select c);this.gv.DataSource = resutl;this.gv.DataBind();}//子查询void SetBindByChildren(){var resutl = from c in guestbook.tbGuestBookwhere(from b in guestbook.tbGuestBook where b.Messages == "1" select b.UserName).Contains("1")select c;this.gv.DataSource = resutl;this.gv.DataBind();}//数组子查询void SetBindByArray(){var resutl = from c in guestbook.tbGuestBookwhere new string[]{"1","sdf"}.Contains(c.UserName)select c;this.gv.DataSource = resutl;this.gv.DataBind();}//内连接,排除相同的void SetBindByJoin(){var resutl = (from c in guestbook.tbGuestBookjoing in guestbook.tbGuestBookon c.UserName equals g.Messagesselect c).Distinct();this.gv.DataSource = resutl;this.gv.DataBind();}//************************************************************高级查询********************************************//存储过程void SetBindByProc(){var resutl = from c in guestbook.sp_singleresultset()select c;this.gv.DataSource = resutl;this.gv.DataBind();}//存储过程,带参数void SetBindByProcParts(){int ?resutl = -1;guestbook.sp_withparameter("1", ref resutl);this.gv.DataSource = resutl.ToString();this.gv.DataBind();}//延迟执行,如果使用两次循环输出的话，会把两次都输出，最好的做法就是把前一个用ToList()存下来void SetBindByQueryLater(){IQueryable query = from c in guestbook.tbGuestBook select c;foreach (tbGuestBook gb in query)Response.Write(gb.ID + "-----" + gb.Messages);Response.Write("<br/>");foreach (tbGuestBook gb in query)Response.Write(gb.ID + "-----" + gb.Messages);}//单条数据void SetBindBySingle1(){tbGuestBook tb = guestbook.tbGuestBook.Single(resutl => resutl.ID == 2);Response.Write(tb.ID + "-----" + tb.Messages);}//************************************************************删除，添加，修改********************************************void Delete(){tbGuestBook gb = guestbook.tbGuestBook.Single(c =>c.ID == int.Parse(this.ddlID1.SelectedValue));guestbook.tbGuestBook.DeleteOnSubmit(gb);guestbook.SubmitChanges();}void Update(){tbGuestBook gb = guestbook.tbGuestBook.Single(c => c.ID == int.Parse(this.ddlID2.SelectedValue));gb.Messages=this.txtMessage1.Text;guestbook.SubmitChanges();}void Insert(){tbGuestBook gb = new tbGuestBook();gb.UserName = this.txtName.Text;gb.PostTime = DateTime.Now;gb.Messages = this.txtMessage2.Text;gb.IsReplied = false;gb.Reply = "";guestbook.tbGuestBook.InsertOnSubmit(gb);guestbook.SubmitChanges();}protected void btnDelete_Click(object sender, EventArgs e){Delete();LoadData();}protected void btnUpdate_Click(object sender, EventArgs e){Update();LoadData();}protected void btnInsert_Click(object sender, EventArgs e){Insert();LoadData();}}}