Linq to SQL学习

在.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.tbGuestBook
                         select 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.tbGuestBook
                          select new
                          {
                              姓名 = c.UserName,
                              时间 = c.PostTime,
                              消息 = c.Messages,
                          };
            this.gv.DataSource = resutl;
            this.gv.DataBind();
        }
        //排除重复
        void SetBindByDistinct()
        {
            var resutl = (from c in guestbook.tbGuestBook
                         select c.UserName).Distinct();
            this.gv.DataSource = resutl;
            this.gv.DataBind();
        }
        //排序
        void SetBindByOrderby()
        {
            var resutl = from c in guestbook.tbGuestBook
                          orderby c.UserName descending,c.ID ascending
                          select new
                          {
                              编号 = c.ID,
                              姓名 = c.UserName,
                              消息 = c.Messages,
                          };
            this.gv.DataSource = resutl;
            this.gv.DataBind();
        }
        //查询包含
        void SetBindByContains()
        {
            var resutl = from c in guestbook.tbGuestBook
                          where 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.tbGuestBook
                         where 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.tbGuestBook
                        select new
                        {
                            编号 = c.ID,
                            姓名 = c.UserName,
                            消息 = c.Messages,
                        }).Take(5);
            this.gv.DataSource=resutl;
            this.gv.DataBind();
        }
        //分页(排除前面的行数)
        void SetBindBySkip()
        {
            var resutl = (from c in guestbook.tbGuestBook
                          select new
                          {
                              编号 = c.ID,
                              姓名 = c.UserName,
                              消息 = c.Messages,
                          }).Skip(5);
            this.gv.DataSource = resutl;
            this.gv.DataBind();
        }
        //ToList
        void SetBindByToList()
        {
            var resutl = (from c in guestbook.tbGuestBook
                          select new
                          {
                              编号 = c.ID,
                              姓名 = c.UserName,
                              消息 = c.Messages,
                          }).ToList();
            this.gv.DataSource = resutl;
            this.gv.DataBind();
        }
        //分组排序
        void SetBindByGroup()
        {
            var resutl = from c in guestbook.tbGuestBook
                          group c by c.UserName into g
                          where g.Count()>2
                          orderby g.Count()
                          select new
                          {
                              编号 = g.Key,
                              行数 = g.Count(),
                          };
            this.gv.DataSource = resutl;
            this.gv.DataBind();
        }
        //************************************************************组合查询********************************************
        //Union,过滤
        void SetBindByUnion()
        {
            var resutl = (from c in guestbook.tbGuestBook
                          where c.UserName.Contains("s")
                          select c).Union
                         (from c in guestbook.tbGuestBook
                          where c.UserName.StartsWith("s")
                          select c);
            this.gv.DataSource = resutl;
            this.gv.DataBind();
        }
        //Contact,不过滤
        void SetBindByConcat()
        {
            var resutl = (from c in guestbook.tbGuestBook
                          where c.UserName.Contains("s")
                          select c).Concat
                         (from c in guestbook.tbGuestBook
                          where c.UserName.StartsWith("s")
                          select c);
            this.gv.DataSource = resutl;
            this.gv.DataBind();
        }
        //Intersect,取相交项
        void SetBindByIntersect()
        {
            var resutl = (from c in guestbook.tbGuestBook
                          where c.Messages.Contains("1")
                          select c).Intersect
                         (from c in guestbook.tbGuestBook
                          where c.UserName.StartsWith("s")
                          select c);
            this.gv.DataSource = resutl;
            this.gv.DataBind();
        }
        //Except,排除相交项
        void SetBindByExcept()
        {
            var resutl = (from c in guestbook.tbGuestBook
                          where c.Messages.Contains("1")
                          select c).Except
                         (from c in guestbook.tbGuestBook
                          where c.UserName.StartsWith("s")
                          select c);
            this.gv.DataSource = resutl;
            this.gv.DataBind();
        }
        //子查询
        void SetBindByChildren()
        {
            var resutl = from c in guestbook.tbGuestBook
                         where
                         (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.tbGuestBook
                         where new string[]{"1","sdf"}.Contains(c.UserName)
                         select c;
            this.gv.DataSource = resutl;
            this.gv.DataBind();
        }
        //内连接,排除相同的
        void SetBindByJoin()
        {
            var resutl = (from c in guestbook.tbGuestBook
                         join
                         g in guestbook.tbGuestBook
                         on c.UserName equals g.Messages
                         select 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();
        }
    }
}