Linq to SQL 练习
public class HomeController : Controller { // // GET: /Home/ empentity entity = new empentity(); public ActionResult Index() { List<EmpCount> lst = (from m in entity.emp.ToList() join n in entity.dept.ToList() on m.DEPTNO equals n.DEPTNO group m by m.DEPTNO into newEmp select new EmpCount { DeptID = newEmp.Key, DName = entity.dept.Where(p => p.DEPTNO == newEmp.Key).FirstOrDefault().DNAME, Count = newEmp.Count() }).ToList(); return View(lst); } public ActionResult SelSal() { List<emp> lst = (from s in entity.emp.ToList() select s).ToList(); return View(lst); } public ActionResult SelEmpSal() { ViewBag.SalgradeId = new SelectList(entity.salgrade.ToList(), "SalgradeId", "GRADE"); return View(entity.emp.ToList()); } [HttpPost] public ActionResult SelEmpSal(int SalgradeId) { ViewBag.SalgradeId = new SelectList(entity.salgrade.ToList(), "SalgradeId", "GRADE"); Salgrade lst = (from s in entity.salgrade.ToList() where s.SalgradeId == SalgradeId select s).FirstOrDefault(); int Max = lst.HISAL; int Min = lst.LOSAL; List<emp> lst1 = (from m in entity.emp.ToList() where m.SAL > Min && m.SAL < Max select m).ToList(); return View("SelEmpSal", lst1); } public ActionResult EmpSal1() { Salgrade sal = (from s in entity.salgrade.ToList() where s.GRADE.Contains("二级") select s).FirstOrDefault(); int Max = sal.HISAL; int Min = sal.LOSAL; List<emp> lst = (from i in entity.emp.ToList() where i.SAL > Min && i.SAL < Max select i).ToList(); return View(lst); } public ActionResult EmpCount() { Salgrade sal = (from s in entity.salgrade.ToList() where s.GRADE.Contains("一级") select s).FirstOrDefault(); int Max = sal.HISAL; int Min = sal.LOSAL; List<EmpCount> count = (from m in entity.emp.ToList() join n in entity.dept.ToList() on m.DEPTNO equals n.DEPTNO where m.SAL > Min && m.SAL < Max group m by m.DEPTNO into newEmp select new EmpCount { DeptID = newEmp.Key, DName = entity.dept.Where(p => p.DEPTNO == newEmp.Key).FirstOrDefault().DNAME, Count = newEmp.Count() }).ToList(); return View(count); } public ActionResult EmpSal2() { Salgrade sal = (from s in entity.salgrade.ToList() where s.GRADE == "二级" select s).FirstOrDefault(); int Max = sal.HISAL; int Min = sal.LOSAL; List<emp> lst = (from i in entity.emp.ToList() where i.SAL > Min && i.SAL < Max select i).ToList(); return View(lst); } public ActionResult EmpCount1() { Salgrade sal = (from s in entity.salgrade.ToList() where s.GRADE == "一级" select s).FirstOrDefault(); int Max = sal.HISAL; int Min = sal.LOSAL; List<EmpCount> lst = (from m in entity.emp.ToList() join n in entity.dept.ToList() on m.DEPTNO equals n.DEPTNO where m.SAL>Min&&m.SAL<Max group m by m.DEPTNO into newEmp select new EmpCount { DeptID = newEmp.Key, DName = entity.dept.Where(p => p.DEPTNO == newEmp.Key).FirstOrDefault().DNAME, Count = newEmp.Count() }).ToList(); return View(lst); } public ActionResult EmpLd() { List<emp> lst = (from n in entity.emp.ToList() join m in entity.emp.ToList() on n.EMPNO equals m.MGR select n).Distinct().ToList(); ViewBag.DEPTNO = new SelectList(entity.dept.ToList(), "DEPTNO", "DNAME"); ViewBag.SalgradeId = new SelectList(entity.salgrade.ToList(), "SalgradeId", "GRADE"); return View(lst); } [HttpPost] public ActionResult EmpLd1(int DEPTNO, int SalgradeId) { ViewBag.DEPTNO = new SelectList(entity.dept.ToList(), "DEPTNO", "DNAME"); ViewBag.SalgradeId = new SelectList(entity.salgrade.ToList(), "SalgradeId", "GRADE"); Salgrade sal = entity.salgrade.Find(SalgradeId); int Max = sal.HISAL; int Min = sal.LOSAL; List<emp> lst = (from n in entity.emp.ToList() join m in entity.emp.ToList() on n.EMPNO equals m.MGR where n.SAL > Min && n.SAL < Max && n.DEPTNO == DEPTNO select n).Distinct().ToList(); foreach (emp item in lst) { emp s = entity.emp.Find(item.EMPNO); s.SAL = item.SAL + 2000; } entity.SaveChanges(); return RedirectToAction("EmpLd"); } public ActionResult EmpLd2() { List<emp> ld = (from m in entity.emp.ToList() join n in entity.emp.ToList() on m.EMPNO equals n.MGR select m).Distinct().ToList(); ViewBag.DEPTNO = new SelectList(entity.dept.ToList(), "DEPTNO", "DNAME"); ViewBag.SalgradeId = new SelectList(entity.salgrade.ToList(), "SalgradeId", "GRADE"); return View(ld); } [HttpPost] public ActionResult EmpLd3(int DEPTNO, int SalgradeId) { ViewBag.DEPTNO = new SelectList(entity.dept.ToList(), "DEPTNO", "DNAME"); ViewBag.SalgradeId = new SelectList(entity.salgrade.ToList(), "SalgradeId", "GRADE"); Salgrade sal = entity.salgrade.Find(SalgradeId); List<emp> lst = (from n in entity.emp.ToList() join m in entity.emp.ToList() on n.EMPNO equals m.MGR where n.SAL > sal.LOSAL && n.SAL < sal.HISAL && n.DEPTNO == DEPTNO select n).Distinct().ToList(); foreach (emp item in lst) { emp s = entity.emp.Find(item.EMPNO); s.SAL = item.SAL + 2000; } entity.SaveChanges(); return RedirectToAction("EmpLd2"); } }
精神共享,智慧共融!