LINQ to SQL用法总结

灰太狼 2022-01-22 03:57 318阅读 0赞

假设dbml名称为SHSunMisDataClasses.dbml，那么对应的Context为SHSunMisDataClassesDataContext,以Customer表为例:

SHSunMisDataClassesDataContext sdcd=new SHSunMisDataClassesDataContext();

1.增加数据:

var cus=new Customer\{

UserID=1,

CreateTime=DateTime.Now,

EditTime=DateTime.Now

\};

sdcd.Customer.InsertOnSubmit(cus);

sdcd.SubmitChanges();//真正往数据库插入数据

2.删除数据：

Customer cus=sdcd.Customer.SingleOrDefault(p=>p.CustomerID==1);

if(cus!=null)

sdcd.Customer.DeleteOnSubmit(cus);

sdcd.SubmitChanges();

3.修改数据

Customer cus=sdcd.Customer.SingleOrDefault(p=>p.CustomerID==1);

if(cus!=null)

cus.UserID=2;

cus.EditTime=DateTime.Now

sdcd.SubmitChanges();

4.查询数据：

a.获取表对象；

Customer cus=sdcd.Customer.SingleOrDefault(p=>p.CustomerID==1);

b.获取集合：

List<Customer> list=sdcd.Customer.OrderBy(p=>p.CustomerID).Where(p=>p.UserID==1).ToList();

获取集合中某个字段的所有值：

List<int> CustomerIDs = list.Select(s => s.CustomerID).ToList();//获取所有的CustomerID

在另外一张表(PlanOfSysObj)里找到所以符合上述ID的记录

List<string> CustomerIDStrs = CustomerIDs.ConvertAll<string>(new Converter<int, string>(m => m.ToString())).ToList();  
                        List<PlanOfSysObj> planlist = sdcd.PlanOfSysObj.Where(p => p.SysObjectID == 1 && CustomerIDStrs.Contains(p.RealtionPK)).ToList();

c.获取满足条件的记录数量：

int count=sdcd.Customer.OrderBy(p=>p.CustomerID).Where(p=>p.UserID==1).Count();

5,where条件

Expression<Func<Customer, bool>> expr = p => true;

//and条件

expr = expr.And(p=>p.CreateTime>DateTime.Parse('2013-06-07'));

//or条件

expr = expr.Or(p=>p.EditTime<=DateTime.Parse('2013-06-07'));

6.分页：

Grid1.DataSource = GetPagedDataTable(Grid1.PageIndex, Grid1.PageSize, sort);  
        Grid1.DataBind();

public List<Customer> GetPagedDataTable(int pageIndex, int pageSize)

return   sdcd.Customer.OrderBy(p=>p.CustomerID).Where(p=>p.UserID==1).Skip(pageIndex \* pageSize).Take(pageSize).OrderBy(p => p.CustomerID).ToList();