自己在用的分页方法，感觉性能还可以，如果大家有更好的方法可以交流一下。
#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));