(EF实体数据模型生成方法已经在上一篇文章。)
要操作的数据数据库表结构:
1.添加数据
private void add_Click(object sender, EventArgs e)
{
//添加一条数据 以添加部门为例
department dept = new department();//构建一个对象
dept.deptId = 10;
dept.deptName = "测试部门";
arsEntities addDept = new arsEntities();//此类在*.Context.cs文件中查看 继承Context类 具体作用百度可知
addDept.department.Add(dept);//将要操作的数据添加到EF上,并且打上“添加”标记
addDept.SaveChanges();//与数据库打交道
}
2.查询数据(linq查询和 lambda查询)
- linq表达式查询
private void linqSearch_Click(object sender, EventArgs e)
{
//linq查询一条数据
arsEntities linqDept = new arsEntities();
var deptList = from u in linqDept.department//此处deptList为IQueryable<T>类型
where u.deptId == 1
select u;
//以上linq表达式类似于select * from department where deptId==1
var dept = deptList.FirstOrDefault();//防止集合为空报错
foreach (var dempt in deptList)
{
//此处dempt为department类型
}
}
- linq查询部分列
private void linqSeatoPart_Click(object sender, EventArgs e)
{
//查询部分列数据
arsEntities linqDept = new arsEntities();
var deptList = from u in linqDept.department
select new { dptName = u.deptName };//匿名函数
var dept = deptList.FirstOrDefault();
foreach (var dempt in deptList)
{
//此处dempt为department类型
}
}
- lambda表达式查询(包含查询部分列方法)
private void lambdaSearch_Click(object sender, EventArgs e)
{
//lambda查询
arsEntities lambdaDept = new arsEntities();
var dept = lambdaDept.department.Where<department>(d => d.deptId == 10).FirstOrDefault();//此处where不是关键字 是一个有过滤作用的扩展方法 传一个委托类型的参数
//lambda查询部分列
var depts = lambdaDept.department.Where<department>(d => d.deptId == 10).Select(p=>new {p.deptId,p.deptName}).FirstOrDefault();
}
3.删除数据
private void del_Click(object sender, EventArgs e)
{
//删除一条数据
arsEntities delDept = new arsEntities();
var deptList = from u in delDept.department
where u.deptId == 10
select u;
var dept = deptList.FirstOrDefault();//FristOrDefault返回集合中第一个元素 如果没有默认值为NULL 这里要删除的元素集合可能只有一条数据或没有数据
//如果不用这条语句当集合为空时会报错
if (dept != null)
{
//删除的第一种标记方式
//delDept.department.Remove(dept);//在EF上打上“删除”标记
//第二种标记方式
delDept.Entry<department>(dept).State = System.Data.Entity.EntityState.Deleted;
delDept.SaveChanges();
}
//可以以直接构建对象的方式删除数据 但要用第二种标记方式
//department department = new department() { deptId = 10 };
//delDept.Entry<department>(department).State = System.Data.Entity.EntityState.Deleted;
}
4.修改数据
private void upde_Click(object sender, EventArgs e)
{
//修改一条数据
arsEntities updDept = new arsEntities();
var deptList = from u in updDept.department
where u.deptId == 10
select u;
var dept = deptList.FirstOrDefault();
dept.deptName = "测试修改";
updDept.Entry<department>(dept).State = System.Data.Entity.EntityState.Modified;//以Modified标记更新相当于update
updDept.SaveChanges();
}
5.数据分页
private void paging_Click(object sender, EventArgs e)
{
//分页操作
arsEntities deptLists = new arsEntities();
//Skip表示取的时候跳过多少条数据 Take表示取出多少条数据
//var deptList = deptLists.department.Where<department>(dept => true).OrderBy<department, int>(c => c.deptId).Skip<department>(2).Take<department>(2);
//上一行代码表示跳过两条数据 去两条 及从3开始取数据3、4
int pageIndex = 2;//页码
int pageSize = 2;//每页展示对少数据
var deptList = deptLists.department.Where<department>(dept => true).OrderBy<department, int>(c => c.deptId).Skip<department>((pageIndex-1)*pageSize).Take<department>(pageSize);
foreach (var dempt in deptList)
{
}
以上分页代码等价于
//IQueryable<department> depList=(from d in deptLists.department
// orderby d.deptId descending
// where d.deptId>0
// select d).Skip<department>((pageIndex - 1) * pageSize).Take<department>(pageSize);
}
6.数据排序
private void sort_Click(object sender, EventArgs e)
{
//对数据进行排序
//OrderBy为升序排序
arsEntities deptLists = new arsEntities();
//var deptList = deptLists.department.Where<department>(dept => true).OrderBy<department, int>(c => c.deptId); // 按ID排序
//OrderByDescending为降序排序
var deptList = deptLists.department.Where<department>(dept => true).OrderByDescending<department, int>(c => c.deptId);
//按多字段排序
//var deptList = deptLists.department.Where<department>(dept => true).OrderBy<department, int>(c => c.deptId).OrderByDescending<department, string>(c => c.deptName);
this.dataGridView1.Rows.Clear();
foreach (var dempt in deptList)
{
//此处dempt为department类型
}
}