分页

在自定义分页中，我所做的步骤是

1.用count（*），和Max（index）取出记录总数和最大索引

2.设置pagesize和page

page=max（index）/pagesize 或者是 max（index）/pagesize+1

3.拼接SQL语句

index between （indexnoew-1）*pagesize  and  （indexnoew-1）*pagesize+pagesize

（这样每页显示的是pagesize+1条记录）

4.执行SQL语句，获取数据，并绑定数据控件

 

 

前台

<asp:Label ID="Label1" runat="server"></asp:Label>   <asp:Label ID="Label2" runat="server"></asp:Label>   <asp:HyperLink ID="HyperLink1" runat="server">首页</asp:HyperLink>  <asp:LinkButton ID="LinkButton1" runat="server" OnClick="LinkButton1_Click">上一页</asp:LinkButton>  <asp:LinkButton ID="LinkButton2" runat="server" OnClick="LinkButton2_Click">下一页</asp:LinkButton>  <asp:HyperLink ID="HyperLink2" runat="server">尾页</asp:HyperLink>   <asp:HyperLink ID="HyperLink3" runat="server">上一页</asp:HyperLink>  <asp:HyperLink ID="HyperLink4" runat="server">下一页</asp:HyperLink>

后台

public partial class WebForm2 : System.Web.UI.Page { static int RecordCount = 0;//总记录 static int CurPage = 0;//总页数 static int pagesize = 0;//每页记录 static int index = 0;//当前页 static int maxindex = 0;//最大索引值 static string sql = ""; static string conStr = "Server=127.0.0.1;UserId=root;Password=root;Database=etggg"; static string startDate = ""; static string endDate = ""; protected void Page_Load(object sender, EventArgs e) { if (!flag) { startDate = string.Format("{0:u}", System.DateTime.Now).Substring(0, 10);//"2009-11-01"; endDate = string.Format("{0:u}", System.DateTime.Now).Substring(0, 10);// "2009-11-24"; getCount();//只执行一次 } if (!IsPostBack) { if (RecordCount != 0) { bind(); } ASPxDateEdit1.Date = Convert.ToDateTime(startDate); ASPxDateEdit2.Date = Convert.ToDateTime(endDate); } } protected void LinkButton1_Click(object sender, EventArgs e)//上一页 { if (RecordCount != 0) { string t = Request.CurrentExecutionFilePath + "?page=" + Convert.ToInt32(index - 1); Response.Redirect(t); } } protected void LinkButton2_Click(object sender, EventArgs e)//下一页 { if (RecordCount != 0) { string t = Request.CurrentExecutionFilePath + "?page=" + Convert.ToInt32(index + 1); Response.Redirect(t); } } protected void Button1_Click(object sender, EventArgs e) { startDate = string.Format("{0:u}", Convert.ToDateTime(ASPxDateEdit1.Value.ToString().Trim())).Substring(0, 10); endDate = string.Format("{0:u}", Convert.ToDateTime(ASPxDateEdit2.Value.ToString().Trim())).Substring(0, 10); getCount(); if (RecordCount != 0) { bind(); } // Response.Redirect(Request.CurrentExecutionFilePath); } private void bind() { //当前页面从Page查询参数获取 if (Request.QueryString["page"] != null) index = Convert.ToInt32(Request.QueryString["page"]); else index = 1; //sql = " SELECT * FROM stockistorder LIMIT " + Convert.ToString((index - 1) * pagesize) + "," + pagesize; sql = "SELECT * FROM stockistorder WHERE soid BETWEEN " + Convert.ToString((index - 1) * pagesize) + " AND " + Convert.ToString((index - 1) * pagesize + pagesize) + " AND SUBSTR(stiem,1,10) BETWEEN '" + startDate + "' AND '" + endDate + "'"; Label1.Text = "AllRecord:" + RecordCount; Label2.Text = "Index/Page:" + index + "/" + CurPage; if (index == 1) { LinkButton1.Enabled = false; HyperLink1.NavigateUrl = ""; HyperLink3.NavigateUrl = ""; } else { LinkButton1.Enabled = true; HyperLink1.NavigateUrl = Request.CurrentExecutionFilePath + "?page=1"; HyperLink3.NavigateUrl = Request.CurrentExecutionFilePath + "?page=" + (index - 1);//上一页 } if (CurPage == index) { LinkButton2.Enabled = false; HyperLink2.NavigateUrl = ""; HyperLink4.NavigateUrl = ""; } else { LinkButton2.Enabled = true; HyperLink2.NavigateUrl = Request.CurrentExecutionFilePath + "?page=" + CurPage; HyperLink4.NavigateUrl = Request.CurrentExecutionFilePath + "?page=" + (index + 1);//下一页 } DataTable ds = DataSource(sql);//获取数据 GridView3.DataSource = ds; GridView3.DataBind(); } static bool flag = false; private static void getCount() { MySqlConnection objConn = new MySqlConnection(conStr); string sql1, sql2; sql1 = "select COUNT(soid) from stockistorder" + " where SUBSTR(stiem,1,10) BETWEEN '" + startDate + "' AND '" + endDate + "'"; sql2 = "select MAX(soid) from stockistorder" + " where SUBSTR(stiem,1,10) BETWEEN '" + startDate + "' AND '" + endDate + "'"; MySqlCommand Cmd = new MySqlCommand(sql1, objConn); objConn.Open(); string count = Cmd.ExecuteScalar().ToString().Trim(); if (count.Length == 0) count = "0"; MySqlCommand Cmd1 = new MySqlCommand(sql2, objConn); string count1 = Cmd1.ExecuteScalar().ToString().Trim(); if (count1.Length == 0) count1 = "0"; RecordCount = Convert.ToInt32(count);//总记录 maxindex = Convert.ToInt32(count1);//总记录 objConn.Close(); if (RecordCount != 0) { pagesize = 100;//每页记录 //总页数 string pagecount1 = Convert.ToString((double)maxindex / pagesize); if (pagecount1.IndexOf('.') == -1) { CurPage = Convert.ToInt32(pagecount1); } else { CurPage = Convert.ToInt32(pagecount1.Substring(0, pagecount1.IndexOf('.'))) + 1; } } flag = true; } private DataTable DataSource(string sql) { MySqlConnection objConn = new MySqlConnection(conStr); MySqlDataAdapter objCommand = new MySqlDataAdapter(sql, objConn); DataTable ds = new DataTable(); objCommand.Fill(ds); return ds; } }