EF框架基础如何深入理解与实践？
- 内容介绍
- 文章标签
- 相关推荐
本文共计4696个文字，预计阅读时间需要19分钟。
ORM概述：ORM全称是对象-关系映射。ORM是将关系数据库中的数据用对象的形式来表示，并通过面向对象的方式来操作这些数据。ORM将数据库中的表映射为对象，通过面向对象的方法来组织和管理这些对象，从而实现系统业务逻辑的处理。Entity Framework（实体框架）：是一种流行的ORM框架。
ORM概述：
ORM全称是“对象 - 关系映射” 。
ORM是将关系数据库中的数据用对象的形式表现出来，并通过面向对象的方式将这些对象组织起来，实现系统业务逻辑的过程。
Entity Framework（简称EF）：
ASP.NET MVC应用程序推荐使用的ORM框架；
支持多种数据库；
映射引擎支持存储过程；
提供Visual Studio集成工具，执行可视化操作；
一、资料准备本教程使用具体实例进行演示EF的基本使用，需要用数据库以及一些工具类。
数据表结构脚本：
create table Dept --部门信息
(
DeptId int primary key identity(1,1),
DeptName varchar(50) not null
)
create table Employee --员工信息
(
EmpId int primary key identity(1,1),
DeptId int not null,
EmpName varchar(50) not null,
EmpPhone varchar(50) not null,
EmpArea varchar(50) not null,
EmpSalary decimal(18,2) not null
)
insert into Dept(DeptName) values('开发部')
insert into Dept(DeptName) values('测试部')
insert into Dept(DeptName) values('实施部')
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(1,'刘德华','13887855552','武汉',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(2,'张学友','13556528634','深圳',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(3,'刘亦菲','13448494546','广州',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(1,'周杰伦','13888666855','北京',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(2,'许巍','13868654219','上海',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(3,'孙燕姿','13895133572','成都',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(1,'朴树','13458788896','武汉',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(2,'周润发','13554588745','南京',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(3,'李连杰','13998759654','上海',6500)
select * from Dept;
select * from Employee;
匿名类集合转动态属性：
public class MyDynamic
{
public static List<ExpandoObject> ToExpandoList(object query)
{
List<ExpandoObject> listExpando = new List<ExpandoObject>();
foreach (var entity in (IEnumerable)query)
{
Type type = entity.GetType();
dynamic dyEntity = new ExpandoObject();
IDictionary<string, object> dict = new Dictionary<string, object>();
dict = dyEntity as ExpandoObject;
PropertyInfo[] arrProperty = type.GetProperties();
foreach (PropertyInfo prop in arrProperty)
{
string a = prop.Name;
string b = prop.GetValue(entity, null).ToString();
dict.Add(prop.Name, prop.GetValue(entity, null));
}
listExpando.Add(dict as dynamic);
}
return listExpando;
}
}
二、EF实现增删改查
在控制器中创建数据库操作类对象：
DBTESTEntities db = new DBTESTEntities();
（1）实现数据列表
Action:
public ActionResult Index()
{
var listView = from emp in db.Employee
join dept in db.Dept on emp.DeptId equals dept.DeptId
select new
{
EmpId = emp.EmpId,
DeptId = emp.DeptId,
DeptName = dept.DeptName,
EmpName = emp.EmpName,
EmpPhone = emp.EmpPhone,
EmpArea = emp.EmpArea,
EmpSalary = emp.EmpSalary
};
ViewBag.listView = MyDynamic.ToExpandoList(listView);
return View();
}
View:
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<style type="text/css">
div, table, tr, td {
margin: 0px;
padding: 0px;
}
.myTable {
width: 800px;
margin: 20px;
border-collapse: collapse;
}
.myTable td,.myTable th {
height: 30px;
line-height: 30px;
padding: 6px;
}
</style>
</head>
<body>
<div>
<h1>查询员工信息</h1>
<a href="AddForm">添加员工</a> <br /><br />
<table width="1000" border="1" class="myTable">
<tr>
<th>员工编号</th>
<th>部门名称</th>
<th>员工姓名</th>
<th>员工电话</th>
<th>所在地区</th>
<th>员工工资</th>
<th>操作</th>
</tr>
@foreach (var item in ViewBag.listView)
{
<tr>
<td>@item.EmpId</td>
<td>@item.DeptName</td>
<td>@item.EmpName</td>
<td>@item.EmpPhone</td>
<td>@item.EmpArea</td>
<td>@item.EmpSalary.ToString("F2")</td>
<td>
<a href="UpdateForm?EmpId=@item.EmpId">编辑</a> |
<a href="Delete?EmpId=@item.EmpId" onclick="return confirm('确定删除吗');">删除</a>
</td>
</tr>
}
</table>
</div>
</body>
</html>
（2）实现数据新增
新增页面Action:
public ActionResult AddForm()
{
//查询所有部门
ViewBag.listDept = db.Dept.ToList();
return View();
}
新增页面视图：
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>AddForm</title>
<style type="text/css">
div, table, tr, td {
margin: 0px;
padding: 0px;
}
.myTable {
width: 800px;
margin: 20px auto;
border-collapse: collapse;
}
.myTable td {
height: 30px;
line-height: 30px;
padding: 6px;
}
</style>
</head>
<body>
<form method="post" action="~/Home/Add">
<div style="text-align:center;">
<table width="800" class="myTable" border="1">
<tr>
<td colspan="2" align="center" style="font-weight:bold;">员工新增</td>
</tr>
<tr>
<td width="200" align="right">所属部门：</td>
<td width="600" align="left">
<select name="DeptId" id="DeptId">
<option value="0">--请选择--</option>
@foreach (var item in ViewBag.listDept)
{
<option value="@item.DeptId">@item.DeptName</option>
}
</select>
</td>
</tr>
<tr>
<td width="200" align="right">员工姓名：</td>
<td width="600" align="left"><input type="text" name="txtRealName" /></td>
</tr>
<tr>
<td width="200" align="right">员工电话：</td>
<td width="600" align="left"><input type="text" name="txtPhone" /></td>
</tr>
<tr>
<td width="200" align="right">所在城市：</td>
<td width="600" align="left"><input type="text" name="txtArea" /></td>
</tr>
<tr>
<td width="200" align="right">月薪：</td>
<td width="600" align="left"><input type="text" name="txtSalary" /></td>
</tr>
<tr>
<td width="200" align="right"></td>
<td width="600" align="left">
<input type="submit" value="新增" />
<a href="Index">返回首页</a>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
处理新增提交请求的Action：
public ActionResult Add()
{
//执行添加操作
//Employee emp = new Employee();
//emp.DeptId = int.Parse(Request["DeptId"]);
//emp.EmpName = Request["txtRealName"];
//emp.EmpPhone = Request["txtPhone"];
//emp.EmpArea = Request["txtArea"];
//emp.EmpSalary = decimal.Parse(Request["txtSalary"]);
//db.Employee.Add(emp);
//db.SaveChanges();
//EF框架执行sql语句
string sql = "insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary) values(@DeptId,@EmpName,@EmpPhone,@EmpArea,@EmpSalary)";
SqlParameter DeptId = new SqlParameter("@DeptId", int.Parse(Request["DeptId"]));
SqlParameter EmpName = new SqlParameter("@EmpName", Request["txtRealName"]);
SqlParameter EmpPhone = new SqlParameter("@EmpPhone", Request["txtPhone"]);
SqlParameter EmpArea = new SqlParameter("@EmpArea", Request["txtArea"]);
SqlParameter EmpSalary = new SqlParameter("@EmpSalary", decimal.Parse(Request["txtSalary"]));
int rowCount = db.Database.ExecuteSqlCommand(sql,DeptId, EmpName, EmpPhone, EmpArea, EmpSalary);
return Content("<script >alert('添加成功！');window.location.href='AddForm';</script >", "text/html");
}
（3）实现数据编辑修改
编辑页面Action:
public ActionResult UpdateForm()
{
//查询所有部门
ViewBag.listDept = db.Dept.ToList();
//查询员工详情
ViewBag.emp = db.Employee.Find(int.Parse(Request["EmpId"]));
return View();
}
编辑页面视图：
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>UpdateForm</title>
<style type="text/css">
div, table, tr, td {
margin: 0px;
padding: 0px;
}
.myTable {
width: 800px;
margin: 20px auto;
border-collapse: collapse;
}
.myTable td {
height: 30px;
line-height: 30px;
padding: 6px;
}
</style>
</head>
<body>
<form method="post" action="~/Home/Update">
<div style="text-align:center;">
<input type="hidden" name="hdEmpId" value="@ViewBag.emp.EmpId" />
<table width="800" class="myTable" border="1">
<tr>
<td colspan="2" align="center" style="font-weight:bold;">员工修改</td>
</tr>
<tr>
<td width="200" align="right">所属部门：</td>
<td width="600" align="left">
<select name="DeptId" id="DeptId">
<option value="0">--请选择--</option>
@foreach (var item in ViewBag.listDept)
{
<option value="@item.DeptId" @(item.DeptId == ViewBag.emp.DeptId ? "selected" : "")>@item.DeptName</option>
}
</select>
</td>
</tr>
<tr>
<td width="200" align="right">员工姓名：</td>
<td width="600" align="left"><input type="text" name="txtRealName" value="@ViewBag.emp.EmpName" /></td>
</tr>
<tr>
<td width="200" align="right">员工电话：</td>
<td width="600" align="left"><input type="text" name="txtPhone" value="@ViewBag.emp.EmpPhone" /></td>
</tr>
<tr>
<td width="200" align="right">所在城市：</td>
<td width="600" align="left"><input type="text" name="txtArea" value="@ViewBag.emp.EmpArea" /></td>
</tr>
<tr>
<td width="200" align="right">月薪：</td>
<td width="600" align="left"><input type="text" name="txtSalary" value="@ViewBag.emp.EmpSalary" /></td>
</tr>
<tr>
<td width="200" align="right"></td>
<td width="600" align="left">
<input type="submit" value="修改" />
<a href="Index">返回首页</a>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
处理修改请求的Action:
public ActionResult Update()
{
//EF框架执行修改操作
//int empId = int.Parse(Request["hdEmpId"]);
//Employee emp = db.Employee.Find(empId);
//emp.DeptId = int.Parse(Request["DeptId"]);
//emp.EmpName = Request["txtRealName"];
//emp.EmpPhone = Request["txtPhone"];
//emp.EmpArea = Request["txtArea"];
//emp.EmpSalary = decimal.Parse(Request["txtSalary"]);
//db.SaveChanges();
//EF框架执行sql语句
int empId = int.Parse(Request["hdEmpId"]);
string sql = "update Employee set DeptId=@DeptId,EmpName=@EmpName,EmpPhone=@EmpPhone,EmpArea=@EmpArea,EmpSalary=@EmpSalary where EmpId=@EmpId";
SqlParameter DeptId = new SqlParameter("@DeptId", int.Parse(Request["DeptId"]));
SqlParameter EmpName = new SqlParameter("@EmpName", Request["txtRealName"]);
SqlParameter EmpPhone = new SqlParameter("@EmpPhone", Request["txtPhone"]);
SqlParameter EmpArea = new SqlParameter("@EmpArea", Request["txtArea"]);
SqlParameter EmpSalary = new SqlParameter("@EmpSalary", decimal.Parse(Request["txtSalary"]));
SqlParameter EmpId = new SqlParameter("@EmpId", empId);
int rowCount = db.Database.ExecuteSqlCommand(sql,DeptId, EmpName, EmpPhone, EmpArea, EmpSalary, EmpId);
return Content("<script >alert('修改成功！');window.location.href='UpdateForm?EmpId="+ empId + "';</script >", "text/html");
}
（4）实现数据的删除
处理删除请求的Action:
public ActionResult Delete()
{
//EF框架执行删除操作
//int empId = int.Parse(Request["EmpId"]);
//Employee emp = db.Employee.Find(empId);
//db.Employee.Remove(emp);
//db.SaveChanges();
//EF框架执行SQL语句
int empId = int.Parse(Request["EmpId"]);
string sql = "delete from Employee where EmpId = @EmpId";
SqlParameter EmpId = new SqlParameter("@EmpId", empId);
int rowCount = db.Database.ExecuteSqlCommand(sql,EmpId);
return Content("<script >alert('删除成功！');window.location.href='Index';</script >", "text/html");
}
三、组合条件搜索+分页
搜索页面Action:
public ActionResult SearchForm()
{
var listView = from emp in db.Employee
join dept in db.Dept on emp.DeptId equals dept.DeptId
select new
{
EmpId = emp.EmpId,
DeptId = emp.DeptId,
DeptName = dept.DeptName,
EmpName = emp.EmpName,
EmpPhone = emp.EmpPhone,
EmpArea = emp.EmpArea,
EmpSalary = emp.EmpSalary
};
//添加查询条件
if (!string.IsNullOrEmpty(Request["ddlDept"]))
{
//linq to entity,不能在lambda里面进行int.parse,或者其它的显示隐式的转换,所以将类型转换写在外面 或者下述方案也可以解决
//此处如果进行类型转换例如ToString(),int.Parse()会报错,可以上面的listView转换为LinqToObject,即db.Employee和db.Dept都调用AsEnumerable。
int deptId = int.Parse(Request["ddlDept"]);
listView = listView.Where(p => p.DeptId == deptId);
}
if (!string.IsNullOrEmpty(Request["txtRealName"]))
{
//linq to entity,不能在lambda里面进行int.parse,或者其它的显示隐式的转换
string realName = Request["txtRealName"].ToString();
listView = listView.Where(p => p.EmpName.Contains(realName));
}
//处理分页
int pageSize = 5; //页码大小
int pageIndex = 1; //当前页码
if (!string.IsNullOrEmpty(Request["page"]))
pageIndex = int.Parse(Request["page"]);
int recordCount = listView.Count(); //总记录条数量
//总共页数
int pageCount = recordCount % pageSize == 0 ? recordCount / pageSize : recordCount / pageSize + 1;
if (pageIndex > pageCount) pageIndex = pageCount;
if (pageIndex < 1) pageIndex = 1;
listView = listView.OrderBy(p => p.EmpId).Skip(pageSize * (pageIndex - 1)).Take(pageSize);
ViewBag.listView = MyDynamic.ToExpandoList(listView);
//查询所有部门
ViewBag.listDept = db.Dept.ToList();
//记录页码大小，当前页和总页数
ViewBag.pageSize = pageSize;
ViewBag.pageIndex = pageIndex;
ViewBag.pageCount = pageCount;
return View();
}
搜索页面视图：
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>多条件组合搜索+分页</title>
</head>
<body>
<h1>查询员工信息</h1>
<form action="SearchForm" method="post">
<div style="height:30px; line-height:30px;">
所属部门:
<select name="ddlDept" id="ddlDept">
<option value="">--请选择--</option>
@foreach (var item in ViewBag.listDept)
{
int deptId = 0;
if (!string.IsNullOrEmpty(Request["ddlDept"]))
{
deptId = int.Parse(Request["ddlDept"]);
}
<option value="@item.DeptId" @(item.DeptId == deptId ? "selected" : "")>@item.DeptName</option>
}
</select>
员工姓名:
<input type="text" name="txtRealName" id="txtRealName" value="@Request["txtRealName"]" />
<input type="submit" value="搜索" />
</div>
</form>
<div>
<table width="1000" border="1" class="myTable">
<tr>
<th>员工编号</th>
<th>部门名称</th>
<th>员工姓名</th>
<th>员工电话</th>
<th>所在地区</th>
<th>员工工资</th>
</tr>
@foreach (var item in ViewBag.listView)
{
<tr>
<td>@item.EmpId</td>
<td>@item.DeptName</td>
<td>@item.EmpName</td>
<td>@item.EmpPhone</td>
<td>@item.EmpArea</td>
<td>@item.EmpSalary.ToString("F2")</td>
</tr>
}
</table>
</div>
@{string urlParama = "ddlDept=" + Request["ddlDept"] + "&txtRealName=" + Request["txtRealName"];}
<form action="SearchForm?@urlParama" method="post">
<div style="height:30px; line-height:30px;">
<a href="SearchForm?page=1&@urlParama">首页</a>
<a href="SearchForm?page=@(ViewBag.pageIndex-1)&@urlParama">上一页</a>
<a href="SearchForm?page=@(ViewBag.pageIndex+1)&@urlParama">下一页</a>
<a href="SearchForm?page=@ViewBag.pageCount&@urlParama">末页</a>
转到:<input name="page" type="text" style="width:30px;" value="@ViewBag.pageIndex" />页
<input type="submit" value="GO" />
当前第<span style="color:orangered;">@ViewBag.pageIndex</span>页
共<span style="color:orangered;">@ViewBag.pageCount</span>页
</div>
</form>
</body>
</html>
四、JPager插件实现分页
本案例同样实现组合条件+分页的功能，只是分页功能使用Jpager来实现，JPager插件可以在Nuget中进行安装。
安装完成后引入命名空间：
using JPager.Net;
Action：
public ActionResult Index(PagerInBase param)
{
param.PageSize = 3;
var list = from emp in db.Employee
join dept in db.Dept on emp.DeptId equals dept.DeptId
select new EmpAndDept
{
EmpId = emp.EmpId,
DeptId = emp.DeptId,
DeptName = dept.DeptName,
EmpName = emp.EmpName,
EmpPhone = emp.EmpPhone,
EmpArea = emp.EmpArea,
EmpSalary = emp.EmpSalary
};
if (!string.IsNullOrEmpty(Request["DeptId"]))
{
int deptId = int.Parse(Request["DeptId"]);
list = list.Where(p => p.DeptId == deptId);
}
if (!string.IsNullOrEmpty(Request["EmpName"]))
{
string EmpName = Request["EmpName"].ToString();
list = list.Where(p => p.EmpName.Contains(EmpName));
}
var data = list.OrderBy(p=>p.EmpId).Skip(param.Skip).Take(param.PageSize);
var count = list.Count();
var res = new PagerResult<EmpAndDept>
{
Code = 0,
DataList = data,
Total = count,
PageSize = param.PageSize,
PageIndex = param.PageIndex,
RequestUrl = param.RequetUrl
};
ViewBag.ListDept = db.Dept;
ViewBag.res = res;
return View();
}
View:
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<style type="text/css">
</style>
</head>
<body>
<h1>查询员工信息</h1>
<a href="AddForm">添加员工</a> <br /><br />
<form method="get" action="Index">
<p>
部门:
<select name="DeptId">
<option value="">--请选择--</option>
@foreach (var item in ViewBag.ListDept)
{
int deptId = 0;
if (!string.IsNullOrEmpty(Request["DeptId"]))
{
deptId = int.Parse(Request["DeptId"]);
}
<option value="@item.DeptId" @(item.DeptId == deptId ? "selected" : "")>@item.DeptName</option>
}
</select>
姓名:
<input type="text" name="EmpName" value="@Request["EmpName"]" />
<input type="submit" value="搜 索" />
</p>
</form>
<table width="1000" border="1" class="myTable">
<tr>
<th>员工编号</th>
<th>部门名称</th>
<th>员工姓名</th>
<th>员工电话</th>
<th>所在地区</th>
<th>员工工资</th>
<th>操作</th>
</tr>
@foreach (var item in ViewBag.res.DataList)
{
<tr>
<td>@item.EmpId</td>
<td>@item.DeptName</td>
<td>@item.EmpName</td>
<td>@item.EmpPhone</td>
<td>@item.EmpArea</td>
<td>@item.EmpSalary.ToString("F2")</td>
<td>
<a href="UpdateForm?EmpId=@item.EmpId">编辑</a> |
<a href="Delete?EmpId=@item.EmpId" onclick="return confirm('确定删除吗');">删除</a>
</td>
</tr>
}
</table>
<div id="mypage">
@Html.Raw(ViewBag.res.PagerHtml()) 共 @ViewBag.res.Total 条
</div>
</body>
</html>
五、PagedList插件实现分页
本案例同样实现组合条件+分页的功能，只是分页功能使用PagedList.MVC来实现，PagedList.MVC插件可以在Nuget中进行安装。
安装完成后引入命名空间：
using PagedList;
Action：
public ActionResult Index(int page = 1)
{
var query = from emp in db.Employee
join dept in db.Dept on emp.DeptId equals dept.DeptId
select new EmpAndDept
{
EmpId = emp.EmpId,
DeptId = emp.DeptId,
EmpName = emp.EmpName,
EmpPhone = emp.EmpPhone,
EmpArea = emp.EmpArea,
EmpSalary = emp.EmpSalary,
DeptName = dept.DeptName
};
//添加查询条件
if (!string.IsNullOrEmpty(Request["DeptId"]))
{
//linq to entity,不能在lambda里面进行int.parse,或者其它的显示隐式的转换,所以将类型转换写在外面 或者下述方案也可以解决
//此处如果进行类型转换例如ToString(),int.Parse()会报错,可以上面的listView转换为LinqToObject,即db.Employee和db.Dept都调用AsEnumerable。
int deptId = int.Parse(Request["DeptId"]);
query = query.Where(p => p.DeptId == deptId);
}
if (!string.IsNullOrEmpty(Request["EmpName"]))
{
//linq to entity,不能在lambda里面进行int.parse,或者其它的显示隐式的转换
string realName = Request["EmpName"].ToString();
query = query.Where(p => p.EmpName.Contains(realName));
}
int pagesize = 2;
var data = query.OrderByDescending(p => p.EmpId).ToPagedList(page, pagesize);
ViewBag.ListDept = db.Dept;
ViewBag.DataList = data;
return View();
}
View：
@using PagedList;
@using PagedList.Mvc;
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<h1>查询员工信息</h1>
<a href="AddForm">添加员工</a> <br /><br />
<form method="get">
<p>
部门:
<select name="DeptId">
<option value="">--请选择--</option>
@foreach (var item in ViewBag.ListDept)
{
int deptId = 0;
if (!string.IsNullOrEmpty(Request["DeptId"]))
{
deptId = int.Parse(Request["DeptId"]);
}
<option value="@item.DeptId" @(item.DeptId == deptId ? "selected" : "")>@item.DeptName</option>
}
</select>
姓名:
<input type="text" name="EmpName" value="@Request["EmpName"]" />
<input type="submit" value="搜 索" />
</p>
</form>
<table width="1000" border="1" class="myTable">
<tr>
<th>员工编号</th>
<th>部门名称</th>
<th>员工姓名</th>
<th>员工电话</th>
<th>所在地区</th>
<th>员工工资</th>
<th>操作</th>
</tr>
@foreach (var item in ViewBag.DataList)
{
<tr>
<td>@item.EmpId</td>
<td>@item.DeptName</td>
<td>@item.EmpName</td>
<td>@item.EmpPhone</td>
<td>@item.EmpArea</td>
<td>@item.EmpSalary.ToString("F2")</td>
<td>
<a href="UpdateForm?EmpId=@item.EmpId">编辑</a> |
<a href="Delete?EmpId=@item.EmpId" onclick="return confirm('确定删除吗');">删除</a>
</td>
</tr>
}
</table>
<div>
@Html.PagedListPager((IPagedList)ViewBag.DataList, page => Url.Action("Index", new { page, DeptId=Request["DeptId"], EmpName=Request["EmpName"] }))
</div>
</body>
</html>
六、EF中执行SQL查询
在EF中执行非查询操作，在前面的例子代码中已经出现过，此处不做描述，此处主要描述在EF中做SQL查询操作。
Action:
public ActionResult SqlQueryForm()
{
DBTESTEntities db = new DBTESTEntities();
var count = db.Database.SqlQuery<int>("select count(*) from Employee");
ViewBag.count = count.FirstOrDefault();
var query1 = db.Database.SqlQuery<Employee>("select * from Employee");
ViewBag.listView1 = query1;
//如果查询的结果找不到对应的实体，需要单独定义一个类，返回的数据必须数量和名字都与此类属性相同
//如果查询出的数据需要修改，有如下2种方案
//方案一:
//Employee emp = db.Employee.SqlQuery("select * from Employee where EmpId = 1").FirstOrDefault();
//emp.EmpSalary += 100;
//db.SaveChanges();
//方案二:
//Employee emp = db.Database.SqlQuery<Employee>("select * from Employee where EmpId = 1").FirstOrDefault();
//emp.EmpSalary += 100;
//db.Entry<Employee>(emp).State = System.Data.Entity.EntityState.Modified;
//db.SaveChanges();
return View();
}
View:
<div>
<h2>使用EF中SQL语句进行查询员工数据,员工数量:@ViewBag.count</h2>
<table width="1000" border="1" class="myTable">
<tr>
<th>员工编号</th>
<th>部门编号</th>
<th>员工姓名</th>
<th>员工电话</th>
<th>所在地区</th>
<th>员工工资</th>
</tr>
@foreach (var item in ViewBag.listView1)
{
<tr>
<td>@item.EmpId</td>
<td>@item.DeptId</td>
<td>@item.EmpName</td>
<td>@item.EmpPhone</td>
<td>@item.EmpArea</td>
<td>@item.EmpSalary.ToString("F2")</td>
</tr>
}
</table>
</div>
本文共计4696个文字，预计阅读时间需要19分钟。
ORM概述：ORM全称是对象-关系映射。ORM是将关系数据库中的数据用对象的形式来表示，并通过面向对象的方式来操作这些数据。ORM将数据库中的表映射为对象，通过面向对象的方法来组织和管理这些对象，从而实现系统业务逻辑的处理。Entity Framework（实体框架）：是一种流行的ORM框架。
ORM概述：
ORM全称是“对象 - 关系映射” 。
ORM是将关系数据库中的数据用对象的形式表现出来，并通过面向对象的方式将这些对象组织起来，实现系统业务逻辑的过程。
Entity Framework（简称EF）：
ASP.NET MVC应用程序推荐使用的ORM框架；
支持多种数据库；
映射引擎支持存储过程；
提供Visual Studio集成工具，执行可视化操作；
一、资料准备本教程使用具体实例进行演示EF的基本使用，需要用数据库以及一些工具类。
数据表结构脚本：
create table Dept --部门信息
(
DeptId int primary key identity(1,1),
DeptName varchar(50) not null
)
create table Employee --员工信息
(
EmpId int primary key identity(1,1),
DeptId int not null,
EmpName varchar(50) not null,
EmpPhone varchar(50) not null,
EmpArea varchar(50) not null,
EmpSalary decimal(18,2) not null
)
insert into Dept(DeptName) values('开发部')
insert into Dept(DeptName) values('测试部')
insert into Dept(DeptName) values('实施部')
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(1,'刘德华','13887855552','武汉',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(2,'张学友','13556528634','深圳',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(3,'刘亦菲','13448494546','广州',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(1,'周杰伦','13888666855','北京',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(2,'许巍','13868654219','上海',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(3,'孙燕姿','13895133572','成都',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(1,'朴树','13458788896','武汉',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(2,'周润发','13554588745','南京',6500)
insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary)
values(3,'李连杰','13998759654','上海',6500)
select * from Dept;
select * from Employee;
匿名类集合转动态属性：
public class MyDynamic
{
public static List<ExpandoObject> ToExpandoList(object query)
{
List<ExpandoObject> listExpando = new List<ExpandoObject>();
foreach (var entity in (IEnumerable)query)
{
Type type = entity.GetType();
dynamic dyEntity = new ExpandoObject();
IDictionary<string, object> dict = new Dictionary<string, object>();
dict = dyEntity as ExpandoObject;
PropertyInfo[] arrProperty = type.GetProperties();
foreach (PropertyInfo prop in arrProperty)
{
string a = prop.Name;
string b = prop.GetValue(entity, null).ToString();
dict.Add(prop.Name, prop.GetValue(entity, null));
}
listExpando.Add(dict as dynamic);
}
return listExpando;
}
}
二、EF实现增删改查
在控制器中创建数据库操作类对象：
DBTESTEntities db = new DBTESTEntities();
（1）实现数据列表
Action:
public ActionResult Index()
{
var listView = from emp in db.Employee
join dept in db.Dept on emp.DeptId equals dept.DeptId
select new
{
EmpId = emp.EmpId,
DeptId = emp.DeptId,
DeptName = dept.DeptName,
EmpName = emp.EmpName,
EmpPhone = emp.EmpPhone,
EmpArea = emp.EmpArea,
EmpSalary = emp.EmpSalary
};
ViewBag.listView = MyDynamic.ToExpandoList(listView);
return View();
}
View:
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<style type="text/css">
div, table, tr, td {
margin: 0px;
padding: 0px;
}
.myTable {
width: 800px;
margin: 20px;
border-collapse: collapse;
}
.myTable td,.myTable th {
height: 30px;
line-height: 30px;
padding: 6px;
}
</style>
</head>
<body>
<div>
<h1>查询员工信息</h1>
<a href="AddForm">添加员工</a> <br /><br />
<table width="1000" border="1" class="myTable">
<tr>
<th>员工编号</th>
<th>部门名称</th>
<th>员工姓名</th>
<th>员工电话</th>
<th>所在地区</th>
<th>员工工资</th>
<th>操作</th>
</tr>
@foreach (var item in ViewBag.listView)
{
<tr>
<td>@item.EmpId</td>
<td>@item.DeptName</td>
<td>@item.EmpName</td>
<td>@item.EmpPhone</td>
<td>@item.EmpArea</td>
<td>@item.EmpSalary.ToString("F2")</td>
<td>
<a href="UpdateForm?EmpId=@item.EmpId">编辑</a> |
<a href="Delete?EmpId=@item.EmpId" onclick="return confirm('确定删除吗');">删除</a>
</td>
</tr>
}
</table>
</div>
</body>
</html>
（2）实现数据新增
新增页面Action:
public ActionResult AddForm()
{
//查询所有部门
ViewBag.listDept = db.Dept.ToList();
return View();
}
新增页面视图：
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>AddForm</title>
<style type="text/css">
div, table, tr, td {
margin: 0px;
padding: 0px;
}
.myTable {
width: 800px;
margin: 20px auto;
border-collapse: collapse;
}
.myTable td {
height: 30px;
line-height: 30px;
padding: 6px;
}
</style>
</head>
<body>
<form method="post" action="~/Home/Add">
<div style="text-align:center;">
<table width="800" class="myTable" border="1">
<tr>
<td colspan="2" align="center" style="font-weight:bold;">员工新增</td>
</tr>
<tr>
<td width="200" align="right">所属部门：</td>
<td width="600" align="left">
<select name="DeptId" id="DeptId">
<option value="0">--请选择--</option>
@foreach (var item in ViewBag.listDept)
{
<option value="@item.DeptId">@item.DeptName</option>
}
</select>
</td>
</tr>
<tr>
<td width="200" align="right">员工姓名：</td>
<td width="600" align="left"><input type="text" name="txtRealName" /></td>
</tr>
<tr>
<td width="200" align="right">员工电话：</td>
<td width="600" align="left"><input type="text" name="txtPhone" /></td>
</tr>
<tr>
<td width="200" align="right">所在城市：</td>
<td width="600" align="left"><input type="text" name="txtArea" /></td>
</tr>
<tr>
<td width="200" align="right">月薪：</td>
<td width="600" align="left"><input type="text" name="txtSalary" /></td>
</tr>
<tr>
<td width="200" align="right"></td>
<td width="600" align="left">
<input type="submit" value="新增" />
<a href="Index">返回首页</a>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
处理新增提交请求的Action：
public ActionResult Add()
{
//执行添加操作
//Employee emp = new Employee();
//emp.DeptId = int.Parse(Request["DeptId"]);
//emp.EmpName = Request["txtRealName"];
//emp.EmpPhone = Request["txtPhone"];
//emp.EmpArea = Request["txtArea"];
//emp.EmpSalary = decimal.Parse(Request["txtSalary"]);
//db.Employee.Add(emp);
//db.SaveChanges();
//EF框架执行sql语句
string sql = "insert into Employee(DeptId,EmpName,EmpPhone,EmpArea,EmpSalary) values(@DeptId,@EmpName,@EmpPhone,@EmpArea,@EmpSalary)";
SqlParameter DeptId = new SqlParameter("@DeptId", int.Parse(Request["DeptId"]));
SqlParameter EmpName = new SqlParameter("@EmpName", Request["txtRealName"]);
SqlParameter EmpPhone = new SqlParameter("@EmpPhone", Request["txtPhone"]);
SqlParameter EmpArea = new SqlParameter("@EmpArea", Request["txtArea"]);
SqlParameter EmpSalary = new SqlParameter("@EmpSalary", decimal.Parse(Request["txtSalary"]));
int rowCount = db.Database.ExecuteSqlCommand(sql,DeptId, EmpName, EmpPhone, EmpArea, EmpSalary);
return Content("<script >alert('添加成功！');window.location.href='AddForm';</script >", "text/html");
}
（3）实现数据编辑修改
编辑页面Action:
public ActionResult UpdateForm()
{
//查询所有部门
ViewBag.listDept = db.Dept.ToList();
//查询员工详情
ViewBag.emp = db.Employee.Find(int.Parse(Request["EmpId"]));
return View();
}
编辑页面视图：
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>UpdateForm</title>
<style type="text/css">
div, table, tr, td {
margin: 0px;
padding: 0px;
}
.myTable {
width: 800px;
margin: 20px auto;
border-collapse: collapse;
}
.myTable td {
height: 30px;
line-height: 30px;
padding: 6px;
}
</style>
</head>
<body>
<form method="post" action="~/Home/Update">
<div style="text-align:center;">
<input type="hidden" name="hdEmpId" value="@ViewBag.emp.EmpId" />
<table width="800" class="myTable" border="1">
<tr>
<td colspan="2" align="center" style="font-weight:bold;">员工修改</td>
</tr>
<tr>
<td width="200" align="right">所属部门：</td>
<td width="600" align="left">
<select name="DeptId" id="DeptId">
<option value="0">--请选择--</option>
@foreach (var item in ViewBag.listDept)
{
<option value="@item.DeptId" @(item.DeptId == ViewBag.emp.DeptId ? "selected" : "")>@item.DeptName</option>
}
</select>
</td>
</tr>
<tr>
<td width="200" align="right">员工姓名：</td>
<td width="600" align="left"><input type="text" name="txtRealName" value="@ViewBag.emp.EmpName" /></td>
</tr>
<tr>
<td width="200" align="right">员工电话：</td>
<td width="600" align="left"><input type="text" name="txtPhone" value="@ViewBag.emp.EmpPhone" /></td>
</tr>
<tr>
<td width="200" align="right">所在城市：</td>
<td width="600" align="left"><input type="text" name="txtArea" value="@ViewBag.emp.EmpArea" /></td>
</tr>
<tr>
<td width="200" align="right">月薪：</td>
<td width="600" align="left"><input type="text" name="txtSalary" value="@ViewBag.emp.EmpSalary" /></td>
</tr>
<tr>
<td width="200" align="right"></td>
<td width="600" align="left">
<input type="submit" value="修改" />
<a href="Index">返回首页</a>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
处理修改请求的Action:
public ActionResult Update()
{
//EF框架执行修改操作
//int empId = int.Parse(Request["hdEmpId"]);
//Employee emp = db.Employee.Find(empId);
//emp.DeptId = int.Parse(Request["DeptId"]);
//emp.EmpName = Request["txtRealName"];
//emp.EmpPhone = Request["txtPhone"];
//emp.EmpArea = Request["txtArea"];
//emp.EmpSalary = decimal.Parse(Request["txtSalary"]);
//db.SaveChanges();
//EF框架执行sql语句
int empId = int.Parse(Request["hdEmpId"]);
string sql = "update Employee set DeptId=@DeptId,EmpName=@EmpName,EmpPhone=@EmpPhone,EmpArea=@EmpArea,EmpSalary=@EmpSalary where EmpId=@EmpId";
SqlParameter DeptId = new SqlParameter("@DeptId", int.Parse(Request["DeptId"]));
SqlParameter EmpName = new SqlParameter("@EmpName", Request["txtRealName"]);
SqlParameter EmpPhone = new SqlParameter("@EmpPhone", Request["txtPhone"]);
SqlParameter EmpArea = new SqlParameter("@EmpArea", Request["txtArea"]);
SqlParameter EmpSalary = new SqlParameter("@EmpSalary", decimal.Parse(Request["txtSalary"]));
SqlParameter EmpId = new SqlParameter("@EmpId", empId);
int rowCount = db.Database.ExecuteSqlCommand(sql,DeptId, EmpName, EmpPhone, EmpArea, EmpSalary, EmpId);
return Content("<script >alert('修改成功！');window.location.href='UpdateForm?EmpId="+ empId + "';</script >", "text/html");
}
（4）实现数据的删除
处理删除请求的Action:
public ActionResult Delete()
{
//EF框架执行删除操作
//int empId = int.Parse(Request["EmpId"]);
//Employee emp = db.Employee.Find(empId);
//db.Employee.Remove(emp);
//db.SaveChanges();
//EF框架执行SQL语句
int empId = int.Parse(Request["EmpId"]);
string sql = "delete from Employee where EmpId = @EmpId";
SqlParameter EmpId = new SqlParameter("@EmpId", empId);
int rowCount = db.Database.ExecuteSqlCommand(sql,EmpId);
return Content("<script >alert('删除成功！');window.location.href='Index';</script >", "text/html");
}
三、组合条件搜索+分页
搜索页面Action:
public ActionResult SearchForm()
{
var listView = from emp in db.Employee
join dept in db.Dept on emp.DeptId equals dept.DeptId
select new
{
EmpId = emp.EmpId,
DeptId = emp.DeptId,
DeptName = dept.DeptName,
EmpName = emp.EmpName,
EmpPhone = emp.EmpPhone,
EmpArea = emp.EmpArea,
EmpSalary = emp.EmpSalary
};
//添加查询条件
if (!string.IsNullOrEmpty(Request["ddlDept"]))
{
//linq to entity,不能在lambda里面进行int.parse,或者其它的显示隐式的转换,所以将类型转换写在外面 或者下述方案也可以解决
//此处如果进行类型转换例如ToString(),int.Parse()会报错,可以上面的listView转换为LinqToObject,即db.Employee和db.Dept都调用AsEnumerable。
int deptId = int.Parse(Request["ddlDept"]);
listView = listView.Where(p => p.DeptId == deptId);
}
if (!string.IsNullOrEmpty(Request["txtRealName"]))
{
//linq to entity,不能在lambda里面进行int.parse,或者其它的显示隐式的转换
string realName = Request["txtRealName"].ToString();
listView = listView.Where(p => p.EmpName.Contains(realName));
}
//处理分页
int pageSize = 5; //页码大小
int pageIndex = 1; //当前页码
if (!string.IsNullOrEmpty(Request["page"]))
pageIndex = int.Parse(Request["page"]);
int recordCount = listView.Count(); //总记录条数量
//总共页数
int pageCount = recordCount % pageSize == 0 ? recordCount / pageSize : recordCount / pageSize + 1;
if (pageIndex > pageCount) pageIndex = pageCount;
if (pageIndex < 1) pageIndex = 1;
listView = listView.OrderBy(p => p.EmpId).Skip(pageSize * (pageIndex - 1)).Take(pageSize);
ViewBag.listView = MyDynamic.ToExpandoList(listView);
//查询所有部门
ViewBag.listDept = db.Dept.ToList();
//记录页码大小，当前页和总页数
ViewBag.pageSize = pageSize;
ViewBag.pageIndex = pageIndex;
ViewBag.pageCount = pageCount;
return View();
}
搜索页面视图：
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>多条件组合搜索+分页</title>
</head>
<body>
<h1>查询员工信息</h1>
<form action="SearchForm" method="post">
<div style="height:30px; line-height:30px;">
所属部门:
<select name="ddlDept" id="ddlDept">
<option value="">--请选择--</option>
@foreach (var item in ViewBag.listDept)
{
int deptId = 0;
if (!string.IsNullOrEmpty(Request["ddlDept"]))
{
deptId = int.Parse(Request["ddlDept"]);
}
<option value="@item.DeptId" @(item.DeptId == deptId ? "selected" : "")>@item.DeptName</option>
}
</select>
员工姓名:
<input type="text" name="txtRealName" id="txtRealName" value="@Request["txtRealName"]" />
<input type="submit" value="搜索" />
</div>
</form>
<div>
<table width="1000" border="1" class="myTable">
<tr>
<th>员工编号</th>
<th>部门名称</th>
<th>员工姓名</th>
<th>员工电话</th>
<th>所在地区</th>
<th>员工工资</th>
</tr>
@foreach (var item in ViewBag.listView)
{
<tr>
<td>@item.EmpId</td>
<td>@item.DeptName</td>
<td>@item.EmpName</td>
<td>@item.EmpPhone</td>
<td>@item.EmpArea</td>
<td>@item.EmpSalary.ToString("F2")</td>
</tr>
}
</table>
</div>
@{string urlParama = "ddlDept=" + Request["ddlDept"] + "&txtRealName=" + Request["txtRealName"];}
<form action="SearchForm?@urlParama" method="post">
<div style="height:30px; line-height:30px;">
<a href="SearchForm?page=1&@urlParama">首页</a>
<a href="SearchForm?page=@(ViewBag.pageIndex-1)&@urlParama">上一页</a>
<a href="SearchForm?page=@(ViewBag.pageIndex+1)&@urlParama">下一页</a>
<a href="SearchForm?page=@ViewBag.pageCount&@urlParama">末页</a>
转到:<input name="page" type="text" style="width:30px;" value="@ViewBag.pageIndex" />页
<input type="submit" value="GO" />
当前第<span style="color:orangered;">@ViewBag.pageIndex</span>页
共<span style="color:orangered;">@ViewBag.pageCount</span>页
</div>
</form>
</body>
</html>
四、JPager插件实现分页
本案例同样实现组合条件+分页的功能，只是分页功能使用Jpager来实现，JPager插件可以在Nuget中进行安装。
安装完成后引入命名空间：
using JPager.Net;
Action：
public ActionResult Index(PagerInBase param)
{
param.PageSize = 3;
var list = from emp in db.Employee
join dept in db.Dept on emp.DeptId equals dept.DeptId
select new EmpAndDept
{
EmpId = emp.EmpId,
DeptId = emp.DeptId,
DeptName = dept.DeptName,
EmpName = emp.EmpName,
EmpPhone = emp.EmpPhone,
EmpArea = emp.EmpArea,
EmpSalary = emp.EmpSalary
};
if (!string.IsNullOrEmpty(Request["DeptId"]))
{
int deptId = int.Parse(Request["DeptId"]);
list = list.Where(p => p.DeptId == deptId);
}
if (!string.IsNullOrEmpty(Request["EmpName"]))
{
string EmpName = Request["EmpName"].ToString();
list = list.Where(p => p.EmpName.Contains(EmpName));
}
var data = list.OrderBy(p=>p.EmpId).Skip(param.Skip).Take(param.PageSize);
var count = list.Count();
var res = new PagerResult<EmpAndDept>
{
Code = 0,
DataList = data,
Total = count,
PageSize = param.PageSize,
PageIndex = param.PageIndex,
RequestUrl = param.RequetUrl
};
ViewBag.ListDept = db.Dept;
ViewBag.res = res;
return View();
}
View:
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<style type="text/css">
</style>
</head>
<body>
<h1>查询员工信息</h1>
<a href="AddForm">添加员工</a> <br /><br />
<form method="get" action="Index">
<p>
部门:
<select name="DeptId">
<option value="">--请选择--</option>
@foreach (var item in ViewBag.ListDept)
{
int deptId = 0;
if (!string.IsNullOrEmpty(Request["DeptId"]))
{
deptId = int.Parse(Request["DeptId"]);
}
<option value="@item.DeptId" @(item.DeptId == deptId ? "selected" : "")>@item.DeptName</option>
}
</select>
姓名:
<input type="text" name="EmpName" value="@Request["EmpName"]" />
<input type="submit" value="搜 索" />
</p>
</form>
<table width="1000" border="1" class="myTable">
<tr>
<th>员工编号</th>
<th>部门名称</th>
<th>员工姓名</th>
<th>员工电话</th>
<th>所在地区</th>
<th>员工工资</th>
<th>操作</th>
</tr>
@foreach (var item in ViewBag.res.DataList)
{
<tr>
<td>@item.EmpId</td>
<td>@item.DeptName</td>
<td>@item.EmpName</td>
<td>@item.EmpPhone</td>
<td>@item.EmpArea</td>
<td>@item.EmpSalary.ToString("F2")</td>
<td>
<a href="UpdateForm?EmpId=@item.EmpId">编辑</a> |
<a href="Delete?EmpId=@item.EmpId" onclick="return confirm('确定删除吗');">删除</a>
</td>
</tr>
}
</table>
<div id="mypage">
@Html.Raw(ViewBag.res.PagerHtml()) 共 @ViewBag.res.Total 条
</div>
</body>
</html>
五、PagedList插件实现分页
本案例同样实现组合条件+分页的功能，只是分页功能使用PagedList.MVC来实现，PagedList.MVC插件可以在Nuget中进行安装。
安装完成后引入命名空间：
using PagedList;
Action：
public ActionResult Index(int page = 1)
{
var query = from emp in db.Employee
join dept in db.Dept on emp.DeptId equals dept.DeptId
select new EmpAndDept
{
EmpId = emp.EmpId,
DeptId = emp.DeptId,
EmpName = emp.EmpName,
EmpPhone = emp.EmpPhone,
EmpArea = emp.EmpArea,
EmpSalary = emp.EmpSalary,
DeptName = dept.DeptName
};
//添加查询条件
if (!string.IsNullOrEmpty(Request["DeptId"]))
{
//linq to entity,不能在lambda里面进行int.parse,或者其它的显示隐式的转换,所以将类型转换写在外面 或者下述方案也可以解决
//此处如果进行类型转换例如ToString(),int.Parse()会报错,可以上面的listView转换为LinqToObject,即db.Employee和db.Dept都调用AsEnumerable。
int deptId = int.Parse(Request["DeptId"]);
query = query.Where(p => p.DeptId == deptId);
}
if (!string.IsNullOrEmpty(Request["EmpName"]))
{
//linq to entity,不能在lambda里面进行int.parse,或者其它的显示隐式的转换
string realName = Request["EmpName"].ToString();
query = query.Where(p => p.EmpName.Contains(realName));
}
int pagesize = 2;
var data = query.OrderByDescending(p => p.EmpId).ToPagedList(page, pagesize);
ViewBag.ListDept = db.Dept;
ViewBag.DataList = data;
return View();
}
View：
@using PagedList;
@using PagedList.Mvc;
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<h1>查询员工信息</h1>
<a href="AddForm">添加员工</a> <br /><br />
<form method="get">
<p>
部门:
<select name="DeptId">
<option value="">--请选择--</option>
@foreach (var item in ViewBag.ListDept)
{
int deptId = 0;
if (!string.IsNullOrEmpty(Request["DeptId"]))
{
deptId = int.Parse(Request["DeptId"]);
}
<option value="@item.DeptId" @(item.DeptId == deptId ? "selected" : "")>@item.DeptName</option>
}
</select>
姓名:
<input type="text" name="EmpName" value="@Request["EmpName"]" />
<input type="submit" value="搜 索" />
</p>
</form>
<table width="1000" border="1" class="myTable">
<tr>
<th>员工编号</th>
<th>部门名称</th>
<th>员工姓名</th>
<th>员工电话</th>
<th>所在地区</th>
<th>员工工资</th>
<th>操作</th>
</tr>
@foreach (var item in ViewBag.DataList)
{
<tr>
<td>@item.EmpId</td>
<td>@item.DeptName</td>
<td>@item.EmpName</td>
<td>@item.EmpPhone</td>
<td>@item.EmpArea</td>
<td>@item.EmpSalary.ToString("F2")</td>
<td>
<a href="UpdateForm?EmpId=@item.EmpId">编辑</a> |
<a href="Delete?EmpId=@item.EmpId" onclick="return confirm('确定删除吗');">删除</a>
</td>
</tr>
}
</table>
<div>
@Html.PagedListPager((IPagedList)ViewBag.DataList, page => Url.Action("Index", new { page, DeptId=Request["DeptId"], EmpName=Request["EmpName"] }))
</div>
</body>
</html>
六、EF中执行SQL查询
在EF中执行非查询操作，在前面的例子代码中已经出现过，此处不做描述，此处主要描述在EF中做SQL查询操作。
Action:
public ActionResult SqlQueryForm()
{
DBTESTEntities db = new DBTESTEntities();
var count = db.Database.SqlQuery<int>("select count(*) from Employee");
ViewBag.count = count.FirstOrDefault();
var query1 = db.Database.SqlQuery<Employee>("select * from Employee");
ViewBag.listView1 = query1;
//如果查询的结果找不到对应的实体，需要单独定义一个类，返回的数据必须数量和名字都与此类属性相同
//如果查询出的数据需要修改，有如下2种方案
//方案一:
//Employee emp = db.Employee.SqlQuery("select * from Employee where EmpId = 1").FirstOrDefault();
//emp.EmpSalary += 100;
//db.SaveChanges();
//方案二:
//Employee emp = db.Database.SqlQuery<Employee>("select * from Employee where EmpId = 1").FirstOrDefault();
//emp.EmpSalary += 100;
//db.Entry<Employee>(emp).State = System.Data.Entity.EntityState.Modified;
//db.SaveChanges();
return View();
}
View:
<div>
<h2>使用EF中SQL语句进行查询员工数据,员工数量:@ViewBag.count</h2>
<table width="1000" border="1" class="myTable">
<tr>
<th>员工编号</th>
<th>部门编号</th>
<th>员工姓名</th>
<th>员工电话</th>
<th>所在地区</th>
<th>员工工资</th>
</tr>
@foreach (var item in ViewBag.listView1)
{
<tr>
<td>@item.EmpId</td>
<td>@item.DeptId</td>
<td>@item.EmpName</td>
<td>@item.EmpPhone</td>
<td>@item.EmpArea</td>
<td>@item.EmpSalary.ToString("F2")</td>
</tr>
}
</table>
</div>