做个了分页，只要传入一个SQL字符串，和排序字段就可以了

最新推荐文章于 2020-01-30 14:55:39 发布

原创最新推荐文章于 2020-01-30 14:55:39 发布 · 1k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#sql #asp #textbox #string #server #object

.NET与数据分页专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用SQL实现分页的技术，通过简单的SQL语句和ASP.NET Web表单结合，可以轻松实现数据分页显示的功能。该方法适用于需要对大量数据进行分页处理的应用场景。

今天闲着没事，用SQL做个了分页，只要传入一个SQL字符串，和排序字段就可以了。

分页原理很简单

用SQL分页，比如要取10-20条数据，
先按某个字段升序排列取前20条，
再按这个字段降序排列取前10条，
再按这个字段升序排列取所有
如：表名TestTable ，排序字段No

select * from (

select top 10 * from (

select top 20 * from

bus order by busNo

) Ta

order by busNo desc

) Tb

order by busNo

运行效果如下：

运行效果

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>无标题页</title> </head> <body> <form id="form1" runat="server"> <div> 每页显示<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" onselectedindexchanged="DropDownList1_SelectedIndexChanged"> <asp:ListItem Selected="True" Value="5">5条</asp:ListItem> <asp:ListItem Value="10">10条</asp:ListItem> <asp:ListItem Value="20">20条</asp:ListItem> <asp:ListItem Value="50">50条</asp:ListItem> </asp:DropDownList> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="id" EmptyDataText="没有可显示的数据记录。" Width="505px"> <Columns> <asp:BoundField DataField="busNo" HeaderText="车次（路）" SortExpression="busNo" /> <asp:BoundField DataField="busStart" HeaderText="始发站" SortExpression="busStart" /> <asp:BoundField DataField="busEnd" HeaderText="终点站" SortExpression="busEnd" /> </Columns> </asp:GridView> <br /> <asp:LinkButton ID="lbnFirstPage" runat="server" onclick="lbnFirstPage_Click">首页</asp:LinkButton> <asp:LinkButton ID="lbnPrePage" runat="server" onclick="lbnPrePage_Click">上一页</asp:LinkButton> <asp:LinkButton ID="lbnPageNext" runat="server" onclick="lbnPageNext_Click">下一页</asp:LinkButton> <asp:LinkButton ID="lbnLastPage" runat="server" onclick="lbnLastPage_Click">尾页</asp:LinkButton> <asp:Label ID="Label1" runat="server"></asp:Label> 转到<asp:TextBox ID="TextBox1" runat="server" Width="25px"></asp:TextBox> <asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Go" /><br /> </div> </form> </body> </html>

Default.aspx.cs

using System; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; using System.Text; public partial class _Default : System.Web.UI.Page { protected string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString; protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { PageIndex = 1; PageCount = int.Parse(DropDownList1.SelectedItem.Value); dataBind(PageIndex, PageCount); } } /// <summary> /// 绑定数据显示到页面 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageCount"></param> private void dataBind(int pageIndex, int pageCount) { string strSql = "select * from bus"; DataTable dt = getDataTable(getSQL(strSql,PageIndex, PageCount,"busNo")); GridView1.DataSource = dt; GridView1.DataBind(); ButtonState(); } /// <summary> /// 按钮及提示信息的显示 /// </summary> private void ButtonState() { lbnPrePage.Enabled = (PageIndex > 1) ? true : false; lbnFirstPage.Enabled = (PageIndex > 1) ? true : false; lbnPageNext.Enabled = (PageIndex == LastPage) ? false : true; lbnLastPage.Enabled = (PageIndex == LastPage) ? false : true; string str = ""; if (PageIndex == 1) str += PageIndex.ToString() + "-" + (PageIndex * PageCount).ToString(); else { int recordStart = (PageIndex - 1) * PageCount + 1; int recordEnd =(PageIndex==LastPage)?AllCount:(PageIndex * PageCount); str += recordStart.ToString() + "-" + recordEnd.ToString(); } Label1.Text = "记录(" + str + ") 第" + PageIndex + "页/共" + LastPage+"页"; } /// <summary> /// 选择每页显示的记录数 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) { PageIndex = 1; PageCount = int.Parse(DropDownList1.SelectedItem.Value); dataBind(PageIndex, PageCount); } /// <summary> /// 首页 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void lbnFirstPage_Click(object sender, EventArgs e) { PageIndex = 1; PageCount = int.Parse(DropDownList1.SelectedItem.Value); dataBind(PageIndex, PageCount); } /// <summary> /// 下一页 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void lbnPageNext_Click(object sender, EventArgs e) { PageIndex++; PageCount = int.Parse(DropDownList1.SelectedItem.Value); dataBind(PageIndex, PageCount); } /// <summary> /// 前一页 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void lbnPrePage_Click(object sender, EventArgs e) { PageIndex--; PageCount = int.Parse(DropDownList1.SelectedItem.Value); dataBind(PageIndex, PageCount); } /// <summary> /// 末页 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void lbnLastPage_Click(object sender, EventArgs e) { PageIndex = LastPage; PageCount = int.Parse(DropDownList1.SelectedItem.Value); dataBind(PageIndex, PageCount); } /// <summary> /// 转到指定页 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Button1_Click(object sender, EventArgs e) { try { PageIndex = int.Parse(TextBox1.Text); if (PageIndex <= 0) PageIndex = 1; else if (PageIndex > LastPage) PageIndex = LastPage; } catch { PageIndex = 1; } dataBind(PageIndex, PageCount); TextBox1.Text = ""; } /// <summary> /// 传入查询SQL语句,生成分页的SQL语句 /// </summary> /// <param name="strSQL">查询SQL语句</param> /// <param name="currentPage">当前页</param> /// <param name="numPerpage">每页显示记录数</param> /// <param name="orderField">排序字段</param> /// <returns></returns> protected string getSQL(string strSQL, int pageIndex, int pageCount, string orderField) { GetAllCount(strSQL); //把传入的SQL语句的查询结果起个别名tempTable if (strSQL != "") strSQL = " ( " + strSQL + " ) tempTable"; else return ""; int count = pageIndex * pageCount; if (pageIndex == LastPage) { count = AllCount; pageCount = AllCount % pageCount; } string strSql = ""; strSql += "select * from( "; strSql += "select top " + pageCount.ToString() + " * from ( "; strSql += "select top " + count.ToString() + " * from " + strSQL + " order by " + orderField + ") as Ta order by " + orderField + " desc)as Tb order by " + orderField; return strSql; } /// <summary> /// 根据传入的SQL字符串获得记录数的方法 /// </summary> /// <param name="strSQL"></param> /// <returns></returns> public int GetAllCount(string strSQL) { //把传入的SQL语句的查询结果起个别名tempTable if (strSQL != "") strSQL = "select count(*) from (" + strSQL + ") tempTable"; else return -1; int allCount = int.Parse(getDataTable(strSQL).Rows[0][0].ToString()); ViewState["AllCount"] = allCount; return allCount; } /// <summary> /// 根据查询语句获得一个DataTable对象的基本方法 /// </summary> /// <param name="strSql"></param> /// <returns></returns> public DataTable getDataTable(String strSql) { SqlConnection conn = new SqlConnection(connStr); SqlCommand cmm = new SqlCommand(strSql, conn); SqlDataAdapter dap = new SqlDataAdapter(cmm); DataSet ds = new DataSet(); dap.Fill(ds); return ds.Tables[0]; } /// <summary> /// 总记录数 /// </summary> public int AllCount { get { return (int)ViewState["AllCount"]; } } /// <summary> /// 最后一页 /// </summary> public int LastPage { get { int lastPage = 1; if (AllCount % PageCount == 0) lastPage = AllCount / PageCount; else lastPage = AllCount / PageCount + 1; return lastPage; } } /// <summary> /// 当前页 /// </summary> public int PageIndex { get { string a = ViewState["PageIndex"].ToString(); return int.Parse(ViewState["PageIndex"].ToString()); } set { ViewState["PageIndex"] = value; } } /// <summary> /// 每页显示的条数 /// </summary> public int PageCount { get { return (int)ViewState["PageCount"]; } set { ViewState["PageCount"] = value; } } }

BUG修复1（2011-1-29）

上面代码发现，当数据库记录少的时候，在一页全部显示数据库所有记录，记录结束处不准确，需按以下修改，用方法时，需传入参数

/// <summary> /// 按钮及提示信息的显示 /// </summary> /// <param name="pageCount">每页显示的条数</param> private void ButtonState(int pageCount) { lbnPrePage.Enabled = (PageIndex > 1) ? true : false; lbnFirstPage.Enabled = (PageIndex > 1) ? true : false; lbnPageNext.Enabled = (PageIndex == LastPage) ? false : true; lbnLastPage.Enabled = (PageIndex == LastPage) ? false : true; string str = ""; //如果一页就全显示了 if (PageIndex == 1) { //如果每页显示的记录数大于总记录数，则记录结束处以总记录数为准 if (PageIndex == LastPage && pageCount>AllCount) str = PageIndex.ToString() + "-" + AllCount.ToString(); else str = PageIndex.ToString() + "-" + (PageIndex * PageCount).ToString(); } else { //记录开始处 int recordStart = (PageIndex - 1) * PageCount + 1; //记录结束处 int recordEnd = 1; //如果是最后一页 if (PageIndex == LastPage) recordEnd = AllCount; else recordEnd = (PageIndex * PageCount); str = recordStart.ToString() + "-" + recordEnd.ToString(); } Label1.Text = "记录(" + str + ") 第" + PageIndex + "页/共" + LastPage + "页"; }

BUG修复2（2011-1-29）

当记录数，正好和每页显示记录数一样时，SQL出现TOP 0

pageCount = AllCount % pageCount 改为：

pageCount = (AllCount % pageCount == 0) ? pageCount : AllCount % pageCount;