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