将前面的条件查询功能与分页显示整合到一个页面中
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title></title> </head> <body> <form id="form1" runat="server"> <br /> 名称：<asp:TextBox ID="txt_name" runat="server"></asp:TextBox> 油耗：<asp:DropDownList ID="dr_oil" runat="server"> <asp:ListItem Text="大于" Value=">"></asp:ListItem> <asp:ListItem Text="小于" Value="<"></asp:ListItem> <asp:ListItem Text="大于等于" Value=">="></asp:ListItem> <asp:ListItem Text="小于等于" Value="<="></asp:ListItem> <asp:ListItem Text="等于" Value="="></asp:ListItem> </asp:DropDownList> <asp:TextBox ID="txt_oil" runat="server"></asp:TextBox> 价格：<asp:DropDownList ID="dr_Price" runat="server"> <asp:ListItem Text="任意价格" Value="null"></asp:ListItem> <asp:ListItem Text="20万至30万" Value="price >=20 and price <=30"></asp:ListItem> <asp:ListItem Text="30万至40万" Value="price >=30 and price <=40"></asp:ListItem> <asp:ListItem Text="大于40万" Value="price > 40"></asp:ListItem> </asp:DropDownList> <asp:Button ID="Button1" runat="server" Text="查询" /> <br /><br /> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> <br /><br /> <table style=" 100%; text-align: center; background-color: navy;"> <tr style="color: white;"> <td>ids</td> <td>编号</td> <td>名称</td> <td>油耗</td> <td>马力</td> <td>排量</td> <td>价格</td> </tr> <asp:Repeater ID="Repeater1" runat="server"> <ItemTemplate> <tr style="background-color: white;"> <td><%#Eval("Ids") %></td> <td><%#Eval("Code") %></td> <td><%#Eval("Name") %></td> <td><%#Eval("Oil") %></td> <td><%#Eval("Powers") %></td> <td><%#Eval("Exhaust") %></td> <td><%#Eval("Price") %></td> </tr> </ItemTemplate> </asp:Repeater> </table> <br /> 当前第[ <asp:Literal ID="lit_NowNumber" runat="server" Text="1"></asp:Literal> ]页 共[ <asp:Literal ID="lit_MaxNumber" runat="server" Text="1"></asp:Literal> ]页 <asp:Button ID="btn_first" runat="server" Text="首页" /> <asp:Button ID="btn_prev" runat="server" Text="上一页" /> <asp:Button ID="btn_next" runat="server" Text="下一页" /> <asp:Button ID="btn_last" runat="server" Text="尾页" /> <asp:DropDownList AutoPostBack="true" ID="DropDownList1" runat="server"></asp:DropDownList> <asp:Button ID="btn_jump" runat="server" Text="跳转" /> <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label> </form> </body> </html>
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; public partial class _Default : System.Web.UI.Page { int PageCount = 5; //每页显示条数 protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Repeater1.DataSource = new carData().Select(PageCount, 1);//绑定所有数据 Repeater1.DataBind(); lit_MaxNumber.Text = MaxPageNumber().ToString(); //将一共有多少页绑定到下拉列表中 for (int i = 1; i <= MaxPageNumber(); i++) { ListItem li = new ListItem(i.ToString(), i.ToString()); DropDownList1.Items.Add(li); } } btn_last.Click += btn_last_Click; btn_next.Click += btn_next_Click; btn_prev.Click += btn_prev_Click; btn_first.Click += btn_first_Click; btn_jump.Click += btn_jump_Click; DropDownList1.SelectedIndexChanged += btn_jump_Click;//下拉列表选值改变时，查询结果立刻改变 Button1.Click += Button1_Click; } //组合查询 void Button1_Click(object sender, EventArgs e) { Repeater1.DataSource = EndData(1); Repeater1.DataBind(); lit_NowNumber.Text = "1"; //最大页数改变******* lit_MaxNumber.Text = MaxPageNumber2().ToString(); } //跳转 void btn_jump_Click(object sender, EventArgs e) { int a = Convert.ToInt32(DropDownList1.SelectedValue); //将下一页数据绑定 Repeater1.DataSource = new carData().Select(PageCount, a); Repeater1.DataBind(); //将当前显示的页数改变到页面上去 lit_NowNumber.Text = a.ToString(); } //首页 void btn_first_Click(object sender, EventArgs e) { //将第一页数据绑定 Repeater1.DataSource = EndData(1); Repeater1.DataBind(); //将当前显示的页数改变到页面上去 lit_NowNumber.Text = "1"; } //上一页 void btn_prev_Click(object sender, EventArgs e) { //获取当前页数，计算上一页页数 int nextNumber = Convert.ToInt32(lit_NowNumber.Text) - 1; if (nextNumber < 1) { return; } //将下一页数据绑定到 Repeater1.DataSource = EndData(nextNumber); Repeater1.DataBind(); //将当前显示的页数改变到页面上去 lit_NowNumber.Text = nextNumber.ToString(); } //下一页 void btn_next_Click(object sender, EventArgs e) { //获取当前页数，计算下一页页数 int nextNumber = Convert.ToInt32(lit_NowNumber.Text) + 1; if (nextNumber > MaxPageNumber2()) { return; } //将下一页数据绑定到 Repeater1.DataSource = EndData(nextNumber); Repeater1.DataBind(); //将当前显示的页数改变到页面上去 lit_NowNumber.Text = nextNumber.ToString(); } //尾页 void btn_last_Click(object sender, EventArgs e) { //将尾页数据绑定到 Repeater1.DataSource = new carData().Select(PageCount, MaxPageNumber2()); Repeater1.DataBind(); //将当前显示的页数改变到页面上去 lit_NowNumber.Text = MaxPageNumber().ToString(); } //计算页数 public int MaxPageNumber() { int a = 0; int maxcount = new carData().SelectCount(); decimal d = Convert.ToDecimal(maxcount) / PageCount; //两个 int 计算 获得 int 类型的数据 a = Convert.ToInt32(Math.Ceiling(d)); return a; } //查询每一页的方法 public List<car> EndData(int n) { int count = 0; string tsql = "select top " + PageCount + " *from car "; string sql1 = ""; if (txt_name.Text.Trim().Length > 0) { sql1 += "where name like '%" + txt_name.Text.Trim() + "%' "; count++; } if (txt_oil.Text.Trim().Length > 0) { if (count > 0) { sql1 += "and oil " + dr_oil.SelectedValue + " " + txt_oil.Text.Trim() + " "; } else { sql1 += "where oil " + dr_oil.SelectedValue + " " + txt_oil.Text.Trim() + " "; } count++; } if (dr_Price.SelectedValue != "null") { if (count > 0) { sql1 += " and " + dr_Price.SelectedValue; } else { sql1 += " where " + dr_Price.SelectedValue; } count++; } tsql += sql1; if (count > 0) { tsql += " and ids not in(select top " + (PageCount * (n - 1)) + " ids from car " + sql1 + ")"; } else { tsql += " where ids not in(select top " + (PageCount * (n - 1)) + " ids from car " + sql1 + ")"; } Label1.Text = tsql; List<car> clist = new carData().SelectAll(tsql, new Hashtable()); return clist; } //查询所有记录的方法 public int MaxPageNumber2() { int end = 0; int count = 0; string tsql = "select count(*) from car "; string sql1 = ""; if (txt_name.Text.Trim().Length > 0) { sql1 += "where name like '%" + txt_name.Text.Trim() + "%' "; count++; } if (txt_oil.Text.Trim().Length > 0) { if (count > 0) { sql1 += "and oil " + dr_oil.SelectedValue + " " + txt_oil.Text.Trim() + " "; } else { sql1 += "where oil " + dr_oil.SelectedValue + " " + txt_oil.Text.Trim() + " "; } count++; } if (dr_Price.SelectedValue != "null") { if (count > 0) { sql1 += " and " + dr_Price.SelectedValue; } else { sql1 += " where " + dr_Price.SelectedValue; } count++; } tsql += sql1; int aaa = new carData().SelectCount(tsql); Label2.Text = aaa.ToString(); end = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(aaa) / PageCount)); return end; } }
using System; using System.Collections; using System.Collections.Generic; using System.Data.SqlClient; using System.Linq; using System.Web; /// <summary> /// carData 的摘要说明 /// </summary> public class carData { SqlConnection conn = null; SqlCommand cmd = null; public carData() { conn = new SqlConnection("server=.;database=Data0216;user=sa;pwd=123"); cmd = conn.CreateCommand(); } //第一次加载时查询所有数据（ 每条显示多少， 第几页） public List<car> Select(int pcount, int pnumber) { List<car> clist = new List<car>(); cmd.CommandText = "select top " + pcount + " * from car where ids not in(select top " + (pcount * (pnumber - 1)) + " ids from car)"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { car c = new car(); c.Ids = Convert.ToInt32(dr["ids"]); c.Code = dr["code"].ToString(); c.Name = dr["name"].ToString(); c.Oil = Convert.ToDecimal(dr["oil"]); c.Powers = Convert.ToInt32(dr["powers"]); c.Exhaust = Convert.ToInt32(dr["exhaust"]); c.Price = Convert.ToDecimal(dr["price"]); clist.Add(c); } conn.Close(); return clist; } //组合查询的所有结果 public int SelectCount(string tsql) { int a = 0; cmd.CommandText = tsql; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); dr.Read(); a = Convert.ToInt32(dr[0]); conn.Close(); return a; } //将查询语句放入字符串中传值， public List<car> SelectAll(string tsql, Hashtable hh) { List<car> clist = new List<car>(); cmd.CommandText = tsql; cmd.Parameters.Clear(); foreach (string s in hh.Keys) { cmd.Parameters.Add(s, hh[s]); } conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { car c = new car(); c.Ids = Convert.ToInt32(dr["ids"]); c.Code = dr["code"].ToString(); c.Name = dr["name"].ToString(); c.Oil = Convert.ToDecimal(dr["oil"]); c.Powers = Convert.ToInt32(dr["powers"]); c.Exhaust = Convert.ToInt32(dr["exhaust"]); c.Price = Convert.ToDecimal(dr["price"]); clist.Add(c); } conn.Close(); return clist; } }