sql server2005 分页特性

在sql server 2005的新功能中，比较西黄分页功能，通过查询函数row_number over(order by field)实现是提取分页数据当页的记录，此功能原理上和临时表差不多，不过通过插叙语句操作就快捷多了，做了个demo，分页效果很好。
1、新建一个default.aspx页面，内容很简单，就一个数据控件和一个分页导航标记条。
<body>
    <form id="form1" runat="server">
    <div>
<asp:DataGrid ID="datalist" runat="server"></asp:DataGrid>
<asp:Label ID="pager" runat="server"></asp:Label>
    </div>
    </form>

</body>

2、在代码里，主要根据传送的参数去数据表里去当前页的记录，每次都只去一页的记录。

protected void Page_Load(object sender, EventArgs e)
        {
            Bind();
        }
        //获取记录总数
        private int RowCount
        {
            get
            {
                string connStr = ConfigurationManager.ConnectionStrings["connstr"].ToString();
                SqlConnection conn = new SqlConnection(connStr);
                conn.Open();
                string sqlStr = "select count(1) from tbl_MailList";
                SqlDataAdapter ada = new SqlDataAdapter(sqlStr, conn);
                DataSet ds = new DataSet();
                ada.Fill(ds);
                conn.Close();
                return int.Parse(ds.Tables[0].Rows[0][0].ToString());
            }
        }
        //绑定数据
        private void Bind()
        {
            //首页地址
            string url="default.aspx";

            //每页数量
            int size = 10;
            //总记录
            int TotalCount = RowCount;
            //开始记录
            int start=1;
            try
            {
                start=int.Parse(Request.Params["start"].ToString());
            }
            catch
            {

            }
            //结束记录
            int end=size;
            try
            {
                end=int.Parse(Request.Params["end"].ToString());
            }
            catch
            {
            }

            string connStr = ConfigurationManager.ConnectionStrings["connstr"].ToString();
            SqlConnection conn = new SqlConnection(connStr);
            conn.Open();
            string sqlStr = "select * from (select *,row_number() over(order by AddrId) as row from tbl_maillist) as mail where row between "+start+" and "+end;
            SqlDataAdapter ada = new SqlDataAdapter(sqlStr, conn);
            DataSet ds = new DataSet();
            ada.Fill(ds);
            conn.Close();
            datalist.DataSource = ds.Tables[0];
            datalist.DataBind();
            //当前页码
            int CurrentPage=1;
            try
            {
                CurrentPage=int.Parse(Request.Params["CurrentPage"].ToString());
            }
            catch
            {
            }
            //分页数量
            int PageCount = TotalCount / size;
            if (TotalCount % size != 0)
            {
                PageCount=PageCount+1;
            }
            //呈现页面导航
            StringBuilder sb = new StringBuilder();
            //只有1页
            if (PageCount < 2||CurrentPage == 1)
            {
                sb.Append("首页");
                sb.Append("  上一页");
            }
            else
            {
                sb.Append("<a href="+url+"?start=1&end="+size+"&CurrentPage=1>首页</a>");
                sb.Append("  <a href="+url+"?start="+((CurrentPage-2)*size+1) +"&end="+ (CurrentPage-1)*size+"&CurrntPage="+(CurrentPage-1)+">上一页</a>");
            }
            //超过10页只显示10页
            if (PageCount > 10)
            {
                for (int intLoop = 1; intLoop < 11; intLoop++)
                {
                    //当前页没有超连接
                    if (CurrentPage == intLoop)
                    {
                        sb.Append(" " + intLoop.ToString());
                    }
                    else
                    {
                        sb.Append(" <a href=" + url + "?start=" + ((intLoop - 1) * size + 1) + "&end=" + (intLoop * size) + "&CurrentPage=" + intLoop + ">" + intLoop + "</a>");
                    }
                }
                sb.Append("

");
            }
            else
            {
                for (int intLoop = 1; intLoop < PageCount + 1; intLoop++)
                {
                    //当前页没有超连接
                    if (CurrentPage == intLoop)
                    {
                        sb.Append(" " + intLoop.ToString());
                    }
                    else
                    {
                        sb.Append(" <a href=" + url + "?start=" + ((intLoop - 1) * size + 1) + "&end=" + (intLoop * size) + "&CurrentPage=" + intLoop + ">" + intLoop + "</a>");
                    }
                }
            }

            if (PageCount < 2||CurrentPage==PageCount)
            {

                sb.Append("  下一页");
                sb.Append("  末页");
            }
            else
            {
                sb.Append("  <a href="+url+"?start="+((CurrentPage*size)+1)+"&end="+(CurrentPage+1)*size+"&CurrentPage="+(CurrentPage+1)+">下一页</a>");
                sb.Append("  <a href="+url+"?start="+(((PageCount-1)*size)+1)+"&end="+PageCount*size+"&CurrentPage="+PageCount+">末页</a>");
            }

            pager.Text = sb.ToString();

        }

执行效果很快，适合大量数据的分页。