存储过程分页 练习

后台

/// <summary>
        /// 存储过程分页
        /// </summary>
        /// <param name="PageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public PageList<UserModel> show(int PageIndex, int PageSize, string name = "")
        {
            var count = 0;
            SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=UserPrivilege;Integrated Security=True");
            SqlCommand cmd = new SqlCommand("Proc_User", conn);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@PageIndex", System.Data.SqlDbType.Int)).Value = PageIndex;
            cmd.Parameters.Add(new SqlParameter("@PageSize", System.Data.SqlDbType.Int)).Value = PageSize;
            cmd.Parameters.Add(new SqlParameter("@Count", System.Data.SqlDbType.Int));


            if (!string.IsNullOrWhiteSpace(name))
            {
                cmd.Parameters.Add(new SqlParameter("@Name", System.Data.SqlDbType.NVarChar, 50)).Value = name;
            }
            else
            {
                cmd.Parameters.Add(new SqlParameter("@Name", System.Data.SqlDbType.NVarChar, 50)).Value = name;
            }

            cmd.Parameters["@Count"].Direction = ParameterDirection.Output;
            SqlDataAdapter dr = new SqlDataAdapter(cmd);
            DataTable ds = new DataTable();
            dr.Fill(ds);
            count = Convert.ToInt32(cmd.Parameters["@Count"].Value);

            var reust = JsonConvert.SerializeObject(ds);
            var reust2 = JsonConvert.DeserializeObject<List<UserModel>>(reust);

            PageList<UserModel> ss = new PageList<UserModel>();
            ss.page = reust2;
            ss.PageIndex = PageIndex;
            ss.PageSize = PageSize;
            if (count % PageSize == 0)
            {
                ss.Count = count / PageSize;
            }
            else
            {
                ss.Count = count / PageSize + 1;
            }
            return ss;
        }

BLL

    public PageList<UserModel> show(int PageIndex, int PageSize, string name = "")
        {
            return dal.show(PageIndex, PageSize,name);
        }

控制器

  [HttpPost]
        public ActionResult Index(int  PageIndex,string name = "")
        {
            var reust = new BLL.UserBLL().show(PageIndex, 3,name);
            return Json(reust);
        }

前台

 <div>
        <input id="Text1" type="text" />
        <input id="Button1" type="button" value="查询" onclick="cha()" />
        <table>
            <tr>
                <td>编号</td>
                <td>用户名</td>
                <td>密码</td>
            </tr>
            <tbody id="idname">

            </tbody>
        </table>
    </div>
    <div id="pageHtml">

    </div>
    <script>
        $(function () {
            show(1, "pageHtml");
        })
        function show(PageIndex, pageHtml,name)
        {
            $.ajax({
                url: "/show/Index",
                type: "post",
                data: { PageIndex: PageIndex ,name:name},
                dataType:"json",
                success: function (data) {
                    alert(data);
                    var str = "";
                    for (var i = 0; i <data.page.length; i++) {
                        str += "<tr>";
                        str += "<td>" + data.page[i].ID + "</td>";
                        str += "<td>" + data.page[i].Name + "</td>";
                        str += "<td>" + data.page[i].Pwd + "</td>";
                        str += "</tr>";
                    }
                    $("#idname").html(str);
                    debugger;
                    var page = "";
                    var totalPage = data.Count;
                    var first = PageIndex < 2 ? 1 : PageIndex - 1;
                    var nest = PageIndex == totalPage ? totalPage : PageIndex + 1;

                    page += " 当前页：<span style=\"color:red;font-size:18px;\" >" + PageIndex + "/" + totalPage + "</span>总页数";
                    page += " <span   onclick=\"show(1)\">首页</span>";
                    page += "   <span  onclick=\"show(" + first + ")\">上一页</span>";
                    page += "   <span  onclick=\"show(" + nest + ")\">下一页</span>";
                    page += "   <span onclick=\"show(" + totalPage + ")\">尾页</span>";

                    $("#pageHtml").html(page);
                }


            })

        }
        function cha()
        {
            var name = $("#Text1").val();
            show(1, "pageHtml",name);
        }
    </script>