曾今的代码系列——自己的分页控件+存储过程实现分页

项目里面的测试代码，仅供参考

LoginByAjax

    <title>Ajax登陆</title>
    <script src="Scripts/common.js" type="text/javascript"></script>
    <script type="text/javascript">
        var xhr;
        window.onload = function () {
            xhr = new createXmlHttp();
            gel("btnLogin").onclick = loginByAjax;
        }
        function loginByAjax() {
            gel("msgImg").src = "Images/load.gif";
            var uName = gel("txtUserName").value;
            var uPwd = gel("txtPwd").value;
            var urlStr = "DoLogin.ashx"
            var data = "uName=" + uName + "&uPwd=" + uPwd;
            xhr.open("POST", urlStr, true);
            xhr.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
            xhr.onreadystatechange = function () {
                if (xhr.readyState == 4) {
                    if (xhr.status == 200) {
                        var responseText = xhr.responseText;
                        if (responseText == "1") {
                            alert("登陆成功");
                            window.location = "Default.aspx";
                        }
                        else {
                            alert("登陆失败");
                        }
                    }
                }
            }
            xhr.send(data);

        }
    </script>

登陆成功后Default.aspx

public partial class Default : System.Web.UI.Page
    {
        protected StringBuilder sbTableContent = new StringBuilder();
        protected string PageHtml = string.Empty;
        protected int nextPage = 1;
        protected int nowPage = 1;
        
        BLL.StudentManager studentManager = new BLL.StudentManager();
        protected void Page_Load(object sender, EventArgs e)
        {
            //加载
            //加载之前先判断是否登陆
            if (new WebHelper().IsLogin())
            {
                LoadList();
            }
            else
            {
                Response.Redirect("AjaxLogin.aspx");
            }
       
        }
     
        private void LoadList()
        {
            int initRowCount=0;
            int initPageCount=0;
            MyPage page = new MyPage();
            page.TryParseCurrentPageIndex(Request.QueryString["page"]);

            List<Model.Students> listStudents = studentManager.QueryListByPageIndexPro(page.CurrentPageIndex,page.PageSize , out initRowCount, out initPageCount);
            page.PageCount = initPageCount;
            page.TotalCount = initRowCount;

            page.UrlFormat = "Default.aspx?page={n}";
            PageHtml = page.RenderToHTML();
            int index = (page.CurrentPageIndex - 1) * page.PageSize + 1;
            if (listStudents!=null)
            {
                foreach (Model.Students model in listStudents)
                {
                    sbTableContent.Append("<tr><td>" + index.ToString()+ "</td>");
                    sbTableContent.Append("<td>"+model.SClasses.CName+"</td>");
                    sbTableContent.Append("<td>"+model.SLoginName+"</td>");
                    sbTableContent.Append("<td>"+model.SCnName+"</td>");
                    sbTableContent.Append("<td>"+(model.SGender.Value?"男":"女")+"</td>");
                    sbTableContent.Append("<td>"+model.STel+"</td>");
                    sbTableContent.Append("<td>"+model.SAddtime.Value.ToShortDateString()+"</td>");
                    sbTableContent.Append("<td><a href='Modify.aspx?id="+model.SID.ToString()+"'>修改</a>&nbsp;<a href='javascript:doDel("+model.SID.ToString()+")'>删除</a>");
                    index++;
                }
            }

        }
    }

自绘分页控件

 public class MyPage
    {
        public int TotalCount { get; set; }
        public int PageSize { get; set; }
        public int CurrentPageIndex { get; set; }
        public int MaxPageCount { get; set; }
        public string UrlFormat { get; set; }
        public int PageCount { get; set; }
        public MyPage()
        {
            PageSize = 4;
            MaxPageCount = 10;
        }
        private void Check()
        {
            System.Diagnostics.Debug.Assert(PageSize>0);
            System.Diagnostics.Debug.Assert(CurrentPageIndex>0);
            System.Diagnostics.Debug.Assert(!string.IsNullOrWhiteSpace(UrlFormat));
        }

        public void TryParseCurrentPageIndex(string pn)
        {
            int temp;
            if (int.TryParse(pn,out temp))
            {
                CurrentPageIndex = temp;
            }
            else
            {
                CurrentPageIndex = 1;
            }
        }
        public string RenderToHTML()
        {
            Check();
            StringBuilder sb = new StringBuilder();
            //double tempCount = (TotalCount+(0.0) )/ PageSize;
            //int pageCount = (int)Math.Ceiling(tempCount);
            int visibleStart = CurrentPageIndex - MaxPageCount / 2;
            if (visibleStart<1)//6还是没区别
            {
                visibleStart = 1;
            }
            int visibleEnd = visibleStart + MaxPageCount;
            if (visibleEnd > PageCount)
            {
                visibleEnd = PageCount;
            }
            if (CurrentPageIndex>1)
            {
                sb.Append(GetPageLink(1,"首页"));
                sb.Append(GetPageLink(CurrentPageIndex-1,"上一页"));
            }
            else
            {
                sb.Append("<span>首页</span>");
                sb.Append("<span>上一页</span>");
            }
            for (int i = visibleStart; i <=visibleEnd; i++)
            {
                if (i==CurrentPageIndex)
                {
                    sb.Append("<span>").Append(i).Append("</span>");
                }
                else
                {
                    sb.Append(GetPageLink(i,i.ToString()));
                }
            }
            if (CurrentPageIndex < PageCount)
            {
                sb.Append(GetPageLink(CurrentPageIndex+1,"下一页"));
                sb.Append(GetPageLink(PageCount, "末页"));
            }
            else
            {
                sb.Append("<span>下一页</span>");
                sb.Append("<span>末页</span>");
            }
            return sb.ToString();
        }
        private string GetPageLink(int i,string text)
        {
            StringBuilder sb = new StringBuilder();
            string url = UrlFormat.Replace("{n}",i.ToString());
            sb.Append("<a href='").Append(url).Append("'>").Append(text).Append("</a>")   ;
            return sb.ToString();
        }
    }

数据访问层的调用存储过程代码:

 public List<Model.Students> QueryListByPageIndexPro(int pageIndex,int pageSize,out int rowCount,out int pageCount)
        {
            Model.Students model = null;
            DataTable dt = dbHelper.ExecProForPageList(pageIndex, pageSize, out rowCount, out pageCount);
            List<Model.Students> list = null;
            if (dt.Rows.Count>0)
            {
                list = new List<Model.Students>();
                foreach (DataRow dr in dt.Rows)
                {
                    model = new Model.Students();
                    SetDr2Model(dr,model);
                    list.Add(model);
                }
            }
            return list;
        }

       public DataTable ExecProForPageList(int pageIndex, int pageSize, out int rowCount, out int pageCount)
        {
       
            SqlParameter[] parameters = 
            {
                new SqlParameter("@PageIndex",SqlDbType.Int,4),
                new SqlParameter("@PageSize",SqlDbType.Int,4),
                new SqlParameter("@RowCount",SqlDbType.Int,4),
                new SqlParameter("@PageCount",SqlDbType.Int,4)
            };
            parameters[0].Value = pageIndex;
            parameters[1].Value = pageSize;
            parameters[2].Direction = ParameterDirection.Output;
            parameters[3].Direction = ParameterDirection.Output;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "GetPageDataOutRowPageCount";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = Connection;
            cmd.Parameters.AddRange(parameters);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            rowCount = Convert.ToInt32(parameters[2].Value);
            pageCount = Convert.ToInt32(parameters[3].Value);
            return dt;

        }

存储过程代码：

Create PROCEDURE [dbo].[GetPageDataOutRowPageCount]
(
@PageIndex int = 1,--当前页数 
@PageSize  int = 1,--每页大小
@RowCount int output,--总行数(传出参数)
@PageCount int output--总页数(传出参数)
)
AS
begin
    DECLARE @sql NVARCHAR(225),@sqlCount NVARCHAR(225)
    select @RowCount =COUNT(SID),@PageCount=CEILING((COUNT(SID)+0.0)/@PageSize) FROM Students where SIsDel=0
    SET @sql='SELECT TOP '+str(@PageSize) +' * FROM Students,Classes where SCID=CID and SIsDel=0 and SID not in(select top '+str((@PageIndex-1)*@PageSize) +' SID from Students where SIsDel=0)order by SID'
    print @sql
    EXEC(@sql)
end

总结