top分页和row_number分页方法

自己在用的分页方法，感觉性能还可以，如果大家有更好的方法可以交流一下。

 

    #region 生成分页的SQL语句 top方法
    /// <summary>
    /// 生成分页的SQL语句 top方法
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="whereString">条件语句</param>
    /// <param name="orderByString">排序列名</param>
    /// <param name="pageSize">分页大小</param>
    /// <param name="pageNumber">页号</param>
    /// <returns></returns>
    public string CreateSqlString_top(string tableName,string whereString,string orderByString,int pageSize,int pageNumber)
    { 
        //SELECT TOP 20 * FROM (
        //SELECT TOP 20 * FROM (
        //SELECT TOP (20*6) * FROM excel_gtcs ORDER BY id ASC) a 
        //ORDER BY a.id DESC) b ORDER BY b.id ASC
        int itemCount = CountTable(tableName, whereString);
        int countPage = itemCount / pageSize;
        int yushu = itemCount % pageSize;
        if (yushu > 0) countPage++;
        StringBuilder sb = new StringBuilder();
        sb.Append("SELECT TOP ").Append(pageSize).Append(" * FROM ( ");
        sb.Append(" SELECT TOP ").Append(pageSize).Append(" * FROM ( ");
        //
        sb.Append(" SELECT TOP (");
        if (countPage == pageNumber && yushu > 0) //最后一页，且余数大于0
        {
            sb.Append(yushu);
        }
        else
        {
            sb.Append(pageSize * pageNumber);
        }
        sb.Append(") * FROM ").Append(tableName).Append(" "); 
        if (!string.IsNullOrEmpty(whereString)) //如果有条件语句
        {
            sb.Append(" WHERE ").Append(whereString).Append(" ");
        }
        sb.Append(" ORDER BY ").Append(orderByString);
        if (countPage == pageNumber && yushu > 0) //最后一页，且余数大于0
        {
            sb.Append(" DESC) a "); //ORDER BY id DESC) a 
        }
        else
        {
            sb.Append(" ASC) a "); //ORDER BY id ASC) a 
        }
        sb.Append(" ORDER BY a.").Append(orderByString).Append(" DESC) b ORDER BY b.").Append(orderByString).Append(" ASC ");
        return sb.ToString();
    }
    #endregion

 

 

    #region 生成分页的SQL语句 row_number方法
    //SELECT row,* FROM(
    //SELECT ROW_NUMBER() OVER (ORDER BY id) AS row,* FROM excel_gtcs ) AS aaaa WHERE row>2000 AND row<2010
    /// <summary>
    /// 生成分页的SQL语句 row_number方法
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="whereString">条件语句</param>
    /// <param name="orderByString">排序列名</param>
    /// <param name="pageSize">分页大小</param>
    /// <param name="pageNumber">页号</param>
    /// <returns></returns>
    public string CreateSqlString_row(string tableName, string whereString, string orderByString, int pageSize, int pageNumber)
    {
        int beginItem = pageNumber==1 ? 0 : pageSize * (pageNumber-1);
        int endItem = pageNumber==1 ? pageSize : pageSize * pageNumber;
        StringBuilder sb = new StringBuilder();
        sb.Append("SELECT * FROM( ");
        sb.Append(" SELECT ROW_NUMBER() OVER (ORDER BY ").Append(orderByString).Append(") AS row,* FROM ").Append(tableName).Append(" ) AS aa WHERE row>").Append(beginItem).Append(" AND row<").Append(endItem).Append(" ");
        if (!string.IsNullOrEmpty(whereString))
        {
            sb.Append(" AND ").Append(whereString);
        }
        return sb.ToString();
    }
    #endregion

 

 

#region 生成上下页链接
    /// <summary>
    /// 生成上下页链接
    /// </summary>
    /// <param name="pageName">当前页面名称</param>
    /// <param name="tableName">表名</param>
    /// <param name="whereString">条件</param>
    /// <param name="pageSize">分页大小</param>
    /// <param name="pageNumber">页号</param>
    /// <returns></returns>
    public string GridPager(string pageName,string tableName, string whereString, int pageSize, int pageNumber)
    {
        int itemCount = CountTable(tableName, whereString);
        int countPage = itemCount / pageSize;
        int yushu = itemCount % pageSize;
        if (yushu > 0) countPage++;
        StringBuilder sb = new StringBuilder();
        sb.Append(string.Format("总记录：{0} ", itemCount));
        sb.Append(string.Format("当前第 {0}/{1} 页 ", pageNumber,countPage));
        if (pageNumber > 1)
        {
            sb.Append(string.Format("<a href=\"{0}?pageNumber={1}&pageSize={2}\" >第一页</a> ", pageName, 1, pageSize));
            sb.Append(string.Format("<a href=\"{0}?pageNumber={1}&pageSize={2}\" >上一页</a> ", pageName, pageNumber - 1, pageSize));
        }
        else
        {
            sb.Append("<a href=\"#\" >第一页</a> ");
            sb.Append("<a href=\"#\" >上一页</a> ");
        }
        if (pageNumber >= countPage)
        {
            sb.Append("<a href=\"#\" >下一页</a> ");
            sb.Append("<a href=\"#\" >最末页</a> ");
        }
        else
        {
            sb.Append(string.Format("<a href=\"{0}?pageNumber={1}&pageSize={2}\" >下一页</a> ", pageName, pageNumber + 1, pageSize));
            sb.Append(string.Format("<a href=\"{0}?pageNumber={1}&pageSize={2}\" >最末页</a> ", pageName, countPage, pageSize));
        }
        return sb.ToString();
    }
    #endregion

 

 

    /// <summary>
    /// 统计表中记录总数
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="whereString"></param>
    /// <returns></returns>
    public int CountTable(string tableName, string whereString)
    {
        string strSQL = string.Format("SELECT COUNT(*) FROM {0} ", tableName);
        if (!string.IsNullOrEmpty(whereString))
        {
            strSQL += string.Format(strSQL + " WHERE {0} ", whereString);
        }
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            SqlCommand cmd = new SqlCommand(strSQL, conn);
            conn.Open();
            object temp = cmd.ExecuteScalar();
            conn.Close();
            if (temp != null)
            {
                return int.Parse(temp.ToString());
            }
            else
            {
                return 0;
            }
        }
    }

 

public DataSet GridDs(string strSQL)
    {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            SqlDataAdapter da = new SqlDataAdapter(strSQL, conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }
    }

 

HTML页代码

    <div>
    
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
    
    </div>
    <div id="pager" runat="server"></div>

 

页面调用

        Stopwatch stopwatch = new Stopwatch();
        stopwatch.Start();
        int pageSize = Request.QueryString["pageSize"] == null ? 20 : int.Parse(Request.QueryString["pageSize"].ToString());
        int pageNumber = Request.QueryString["pageNumber"] == null ? 1 : int.Parse(Request.QueryString["pageNumber"].ToString());
        myGridPage mgp = new myGridPage();
        string sql = mgp.CreateSqlString_top("表名", "", "id", pageSize, pageNumber);
        //string sql = mgp.CreateSqlString_row("表名", "", "id", pageSize, pageNumber);
        Response.Write(sql);
        this.GridView1.DataSource = mgp.GridDs(sql);
        this.GridView1.DataBind();

        string pageName = Request.Url.AbsolutePath.ToString();
        this.pager.InnerHtml = mgp.GridPager(pageName, "excel_gtcs", "", pageSize, pageNumber);
        stopwatch.Stop();
        Response.Write(string.Format("<br>用时{0}毫秒",stopwatch.ElapsedMilliseconds));