EF 增删改查

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data.SqlClient;
using System.Data;
using Newtonsoft.Json;

namespace WebApplication1.Controllers
{
    public class EFController : Controller
    {
        // GET: EF
        TestEntities test = new TestEntities();
        public ActionResult Index(int num = 1)
        {
            int rows;
            //如果为""就赋值为1
            if (Request["num"] == "")
            {
                rows = 1;
            }
            //如果为null就赋值为1
            rows = Convert.ToInt32(Request["num"] ?? "1");
            //ef使用sql查询
            string sql = string.Format(@"select * from
                        (
                            select *, ROW_NUMBER()  over(order by Salary desc) rownumber  from userInfo
                        ) temp where rownumber between {0} and {1}", (rows - 1) * 5 + 1, 5 * rows);
            // List<userInfo> userlist2 = test.Database.SqlQuery<userInfo>(sql).ToList();
            //ef使用skip  take  查询
            List<userInfo> userlist = test.userInfo.OrderByDescending(a => a.Salary).Skip((rows - 1) * 5).Take(5).ToList();

            //得到所有条数
            int allcount = test.userInfo.Count();
            //得到所有页数
            int allpage = allcount / 5;
            //判断是否除尽或页数是否为0
            if (allcount % 5 != 0 || allpage == 0)
            {
                allpage = allpage + 1;
            }
            //传值到页面
            ViewBag.allpage = allpage;//总页数
            ViewBag.rows = rows;//第几页
            return View(userlist);
        }
        public JsonResult Add()
        {
            userInfo user = JsonConvert.DeserializeObject<userInfo>(Request["json"]);
            //Random rd = new Random();
            //userInfo user = new userInfo
            //{
            //    Name = Guid.NewGuid().ToString().Substring(0, 10).Replace("-", "").ToLower(),
            //    Salary = rd.Next(3000, 5000)
            //};
            test.userInfo.Add(user);
            int count = test.SaveChanges();
            return Json(count,JsonRequestBehavior.AllowGet);
        }

        public JsonResult Update(userInfo user)
        {
            //获取给定实体
            var update = test.Entry(user);
            //设置给定实体状态
            update.State = System.Data.Entity.EntityState.Unchanged;
            update.Property("Name").IsModified = true;
            update.Property("Salary").IsModified = true;
            int count = test.SaveChanges();
            return Json(count, JsonRequestBehavior.AllowGet);
        }
        public JsonResult Delete(userInfo user)
        {
            //删除方法一
            userInfo user1 = new userInfo()
            {
                Id = 1
            };
            test.Entry<userInfo>(user1).State = System.Data.Entity.EntityState.Deleted;
            //删除方法二
            //Attach的实体事先不能已经在内存中,否则上下文会追踪到两个相同键名的实体
            test.userInfo.Attach(user);//将对象添加到EF管理容器中 ObjectStateManager     
            test.userInfo.Remove(user); //将对象包装类状态标识为删除
            //ef执行存储过程或删除语句
            SqlParameter[] para = new SqlParameter[] {
                new SqlParameter("@Id",user.Id + 1)
            };
            test.Database.ExecuteSqlCommand("delete  from userInfo  where Id=@Id", para);
            int count = test.SaveChanges();
            return Json(count, JsonRequestBehavior.AllowGet);
        }
    }
}

@{
    ViewBag.Title = "Index";
}
@model  System.Collections.Generic.List<WebApplication1.userInfo>

<link href="~/Content/getpage/css/page.css" rel="stylesheet" />
<link href="~/Content/layer//mobile/need/layer.css" rel="stylesheet" />
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Content/getpage/js/jqPaginator.js"></script>
<script src="~/Content/layer/layer.js"></script>
<script src="~/Scripts/jQuery-Jsonp.js"></script>
<style>
    .page {
        padding: 0px;
    }

    .active {
        width: inherit;
    }
</style>

<script>
    $(function () {
        (function ($) {
            $.extend({
                "myJsonp1": function (data) {  //没有复选框时用的方法
                    var jsonstr = "{";
                    for (var i = 0; i < data.length; i++) {
                        jsonstr += "\"" + data[i].name + "\":\"" + data[i].value + "\"";
                        if (i != data.length - 1) {
                            jsonstr += ",";
                        }
                    }
                    jsonstr += "}";

                    return jsonstr;
                }
            })
        })(jQuery)
        //添加
        $("#tab").on("click", ".add", function () { 
           
            //清空试卷名称的文本框
            $("#addform input[type='text']").val("");
            layer.open({
                title: "新增工资",
                type: 1,
                area: ["350px", "300px"],//宽和高
                content: $("#addform"),
                btn: ["添加", "取消"],
                btn1: function () {
                    var data = $("#addform").serializeArray();
                    //post提交到后台
                    $.post("/EF/Add", { json: $.myJsonp1(data) }, function (res) {
                        if (res > 0) {
                            alert("添加成功");
                            $("#addform").css({ "display": "none" });
                            layer.closeAll();
                            location.href = "Index";
                        } else {
                            alert("添加失败");
                        }
                    });
                },
                btn2: function () {
                    $("#addform").css({"display":"none"});
                    layer.closeAll();
                
                },
                cancel: function () {
                    $("#addform").css({"display":"none"});
                    layer.closeAll();
                }
            });
        });

        //点击删除
        $("#tab").on("click", ".delete", function () {
            //得到需要的td
            var needtd = $(this).parent().parent().find("td");
            var Id = $(this).attr("value");//得到Id
            layer.open({
                title: "温馨提示",
                content: "删除这条信息'" + needtd.eq(0).text() + "'吗?",
                btn: ["确认", "取消"],
                btn1: function () {
                    layer.closeAll();
                    $.post("/EF/Delete", { Id: Id }, function (res) {
                        if (res > 0) {
                            alert("删除成功");
                            location.href = "Index";
                        } else {
                            alert("删除失败");
                        }

                    });
                },
                btn2: function () {
                    layer.closeAll();
                }
            });

        });

        //修改之前的数组
        var updatebefore = new Array();
        //修改之后的数组
        var updateafter = new Array();

        //点击修改
        $("#tab").on("click", ".update", function () {
            //得到所需要的tr
            var needtr = $(this).parent().parent();
            //克隆旧的tr
            var oldtr = needtr.clone();
            //添加到修改之前的集合
            updatebefore.push(oldtr);
            var Id = $(this).attr("value");
            var parent = $(this).parent();

            parent.html("<a  href='#' value='" + Id + "' class='save' >保存</a>|<a  href='#' value='" + Id + "' class='cancel' >取消</a>");

            var tds = needtr.find("td");
            tds.eq(0).html("<input type='text'  value='" + tds.eq(0).html() + "'/>");
            tds.eq(1).html("<input type='text'  value='" + tds.eq(1).html() + "'/>");
            //添加到修改之后的集合
            updateafter.push(needtr);
            //点击取消
            $(".cancel").click(function () {
                $(this).parent().parent().html(oldtr.html());

            });

        });

        //点击保存
        $("#tab").on("click", ".save", function () {
            var needtr = $(this).parent().parent();
            var Name = needtr.find("td").eq(0).children().val();
            var Salary = needtr.find("td").eq(1).children().val();
            var Id = $(this).attr("value");
            $.post("/EF/Update", { Name: Name, Salary: Salary, Id: Id }, function (res) {
                if (res > 0) {
                    alert("修改成功");
                    var tds = needtr.find("td");
                    needtr.find("td:last").html("<a class='add' style='color:lightgreen'  href='#'  value=" + Id + ">新增</a>|<a class='update' style='color:deepskyblue' href='#' value=" + Id + ">修改</a>|<a class='delete' style='color:deepskyblue'  href='#'  value=" + Id + ">删除</a>");
                    tds.eq(0).html(tds.eq(0).children().val());
                    tds.eq(1).html(tds.eq(1).children().val());

                } else {
                    alert("修改失败");
                    location.href = "Index";
                }
            });

        });
        //分页
        //$.jqPaginator('#pagination1', {
        //    totalPages: parseInt($("#allpage").val()),
        //    visiblePages: 10,
        //    currentPage: parseInt($("#num").val()),
        //    first: '<li class="first"><a href="javascript:;">首页</a></li>',
        //    prev: '<li class="prev"><a href="javascript:;">上一页</a></li>',
        //    next: '<li class="next"><a href="javascript:;">下一页</a></li>',
        //    last: '<li class="last"><a href="javascript:;">尾页</a></li>',
        //    page: '<li class="page"><a href="javascript:;">{{page}}</a></li>',
        //    onPageChange: function (num, type) {
        //        if (type != "init") {
        //            //给隐藏域赋值
        //            $("#num").val(num);
        //            $("#form1").submit();
        //        }
        //    }
        //});
    })



</script>
<form id="form1" action="Index" method="post">
    <input type="hidden" id="num" value="@ViewBag.rows" name="num" />
    <input type="hidden" id="allpage" value="@ViewBag.allpage" name="allpage" />
    <table class="table table-hover" id="tab" style="margin-left:20px">
        <tr>
            <th>姓名</th>
            <th>工资</th>
            <th>操作</th>
        </tr>

        @foreach (var item in Model)
        {
            <tr>
                <td>@item.Name</td>
                <td>@item.Salary</td>
                <td><a class="add" style="color:lightgreen" value="@item.Id">新增</a> | <a class="update" style="color:deepskyblue" value="@item.Id">编辑</a> | <a class="delete" style="color:red" value="@item.Id">删除</a></td>
            </tr>
        }
    </table>
</form>
    <p id="p1"></p>
    <ul class="pagination" id="pagination1"></ul>


    <!--添加工资表单-->
    <form id="addform" style="display: none" enctype="multipart/form-data">

        <div style="margin: auto; text-align: center; margin-top: 40px;">
            姓名：<input type="text" name="Name" id="Name" placeholder="姓名" /><br />
            <br />
            工资：<input type="text" name="Salary" id="Salary" placeholder="工资" /><br />
            <br />
        </div>

    </form>