组合分页查询结合体
封装类
using System; using System.Collections.Generic; using System.Web; /// <summary> /// Car 的摘要说明 /// </summary> public class Car { private string _Code; public string Code { get { return _Code; } set { _Code = value; } } private string _Name; public string Name { get { return _Name; } set { _Name = value; } } private string _Brand; public string Brand { get { return _Brand; } set { _Brand = value; } } private DateTime _Time; public DateTime Time { get { return _Time; } set { _Time = value; } } private decimal _Oil; public decimal Oil { get { return _Oil; } set { _Oil = value; } } private int _Powers; public int Powers { get { return _Powers; } set { _Powers = value; } } private decimal _Price; public decimal Price { get { return _Price; } set { _Price = value; } } }
建立方法
using System; using System.Collections; using System.Collections.Generic; using System.Data.SqlClient; using System.Web; /// <summary> /// CarData 的摘要说明 /// </summary> public class CarData { SqlConnection cnn = null; SqlCommand cmd = null; public CarData() { cnn = new SqlConnection("server=.;database=mydb;user=sa;pwd=123"); cmd = cnn.CreateCommand(); // // TODO: 在此处添加构造函数逻辑 // } public List<Car> select(int meiyeshu,int dijiye) { List<Car> clist = null; cmd.CommandText = "select top " + meiyeshu + " * from Car where Code not in(select top "+(meiyeshu*(dijiye-1)) +" Code from Car)"; cnn.Open(); SqlDataReader ss = cmd.ExecuteReader(); if (ss.HasRows) { clist = new List<Car>(); while (ss.Read()) { Car c = new Car(); c.Code = ss[0].ToString(); c.Name = ss[1].ToString(); c.Brand = ss[2].ToString(); c.Time = Convert.ToDateTime(ss[3]); c.Oil = Convert.ToDecimal(ss[4]); c.Powers = Convert.ToInt32(ss[5]); c.Price = Convert.ToDecimal(ss["Price"]); clist.Add(c); } } cnn.Close(); return clist; } public List<Car> quan() { List<Car> ulist = null; cmd.CommandText = "select * from car"; cnn.Open(); SqlDataReader ss = cmd.ExecuteReader(); if (ss.HasRows) { ulist = new List<Car>(); while (ss.Read()) { Car c = new Car(); c.Code = ss[0].ToString(); c.Name = ss[1].ToString(); c.Brand = ss[2].ToString(); c.Time = Convert.ToDateTime(ss[3]); c.Oil = Convert.ToDecimal(ss[4]); c.Powers = Convert.ToInt32(ss[5]); c.Price = Convert.ToDecimal(ss["Price"]); ulist.Add(c); } } cnn.Close(); return ulist; } public List<Car> zuhecha(string sql,Hashtable h) { List<Car> ulist = null; cmd.CommandText = sql; cmd.Parameters.Clear(); foreach (string k in h.Keys) { cmd.Parameters.Add(k, h[k]); } cnn.Open(); SqlDataReader ss = cmd.ExecuteReader(); if (ss.HasRows) { ulist = new List<Car>(); while (ss.Read()) { Car c = new Car(); c.Code = ss[0].ToString(); c.Name = ss[1].ToString(); c.Brand = ss[2].ToString(); c.Time = Convert.ToDateTime(ss[3]); c.Oil = Convert.ToDecimal(ss[4]); c.Powers = Convert.ToInt32(ss[5]); c.Price = Convert.ToDecimal(ss["Price"]); ulist.Add(c); } } cnn.Close(); return ulist; } }
建立框架
<%@ 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"> 名字<asp:TextBox ID="name_text" runat="server"></asp:TextBox> 油耗<asp:DropDownList ID="oil_dd" runat="server"> <asp:ListItem>>=</asp:ListItem> <asp:ListItem><=</asp:ListItem> </asp:DropDownList><asp:TextBox ID="oil_text" runat="server"></asp:TextBox>  价格<asp:DropDownList ID="price_dd" runat="server"> <asp:ListItem>>=</asp:ListItem> <asp:ListItem><=</asp:ListItem> </asp:DropDownList><asp:TextBox ID="price_text" runat="server"></asp:TextBox><asp:Button ID="chaxun_btn" runat="server" Text="查询" /> <br /> <br /> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> <asp:Repeater ID="Repeater1" runat="server"> <HeaderTemplate> <table style="text-align:center;width:100%"> <tr style="background-color:blue;"> <td>序号</td> <td>名字</td> <td>品牌</td> <td>出场时间</td> <td>油耗</td> <td>马力</td> <td>价格</td> </tr> </HeaderTemplate> <ItemTemplate> <tr style="background-color:gray; color:white;"> <td><%#Eval("Code") %></td> <td><%#Eval("Name") %></td> <td><%#Eval("Brand") %></td> <td><%#Eval("Time") %></td> <td><%#Eval("Oil") %></td> <td><%#Eval("Powers") %></td> <td><%#Eval("Price") %></td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> 当前第<asp:Label ID="dijiye" runat="server" Text="Label"></asp:Label>页，共<asp:Label ID="gongjiye" runat="server" Text="Label"></asp:Label>页  <asp:LinkButton ID="shangyiye_btn" runat="server">上一页</asp:LinkButton>  <asp:LinkButton ID="xiayiye_btn" runat="server">下一页</asp:LinkButton>   跳转到<asp:DropDownList ID="tiaozhuan_dd" AutoPostBack="true" runat="server"></asp:DropDownList>页 </form> </body> </html>
建立功能（只有查询和下一页功能）
using System; using System.Collections; using System.Collections.Generic; 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) { string sql = "select top "+pagecount+"*from Car"; string sql2 = "select * from Car"; Repeater1.DataSource = new CarData().zuhecha(sql,new Hashtable()); Repeater1.DataBind(); dijiye.Text = "1"; dijiye.Text = "1"; gongjiye.Text = maxpage(sql2, new Hashtable()).ToString(); } chaxun_btn.Click += chaxun_btn_Click;//查询事件 shangyiye_btn.Click += shangyiye_btn_Click; xiayiye_btn.Click += xiayiye_btn_Click; } void xiayiye_btn_Click(object sender, EventArgs e) { int next = Convert.ToInt32(dijiye.Text)+1; int i = 0; Hashtable h = new Hashtable(); string sql = "select top " + pagecount + " * from Car"; string sq2 = "select * from Car";//为了共几页而查 string tj = ""; if (!String.IsNullOrEmpty(name_text.Text.Trim())) { sql += " where Name like @a"; sq2 += " where Name like @a"; h.Add("@a", "%" + name_text.Text.Trim() + "%"); i++; tj = " where Name like @a"; } if (!String.IsNullOrEmpty(oil_text.Text)) { if (i == 0) { sql += " where Oil " + oil_dd.SelectedValue + " @b"; sq2 += " where Oil " + oil_dd.SelectedValue + " @b"; tj += " where Oil " + oil_dd.SelectedValue + " @b"; } else { sql += " and Oil " + oil_dd.SelectedValue + " @b"; sq2 += " and Oil " + oil_dd.SelectedValue + " @b"; tj += " and Oil " + oil_dd.SelectedValue + " @b"; } h.Add("@b", oil_text.Text); i++; } if (!String.IsNullOrEmpty(price_text.Text)) { if (i == 0) { sql += " where Price " + price_dd.SelectedValue + " @c"; sq2 += " where Price " + price_dd.SelectedValue + " @c"; tj += " where Price " + price_dd.SelectedValue + " @c"; } else { sql += " and Price " + price_dd.SelectedValue + " @c"; sq2 += " and Price " + price_dd.SelectedValue + " @c"; tj += " and Price " + price_dd.SelectedValue + " @c"; } h.Add("@c", price_text.Text); } if (i > 0) { sql += " and Code not in (select top " + (pagecount * (next - 1)) + " Code from Car " + tj + ")"; } else { sql += " where Code not in (select top " + (pagecount * (next - 1)) + " Code from Car " + tj + ")"; } Label1.Text = sql; if (next > maxpage(sq2, h)) { return; } dijiye.Text = next.ToString(); gongjiye.Text = maxpage(sq2, h).ToString(); Repeater1.DataSource = new CarData().zuhecha(sql, h); Repeater1.DataBind(); } void shangyiye_btn_Click(object sender, EventArgs e) { throw new NotImplementedException(); } void chaxun_btn_Click(object sender, EventArgs e)//查询事件 { int i = 0; Hashtable h = new Hashtable(); string sql = "select top "+pagecount+" * from Car"; string sq2 = "select * from Car"; dijiye.Text = "1"; if (!String.IsNullOrEmpty(name_text.Text.Trim())) { sql += " where Name like @a"; sq2 += " where Name like @a"; h.Add("@a", "%" + name_text.Text.Trim() + "%"); i++; } if (!String.IsNullOrEmpty(oil_text.Text)) { if (i == 0) { sql += " where Oil "+oil_dd.SelectedValue+" @b"; sq2 += " where Oil " + oil_dd.SelectedValue + " @b"; } else { sql += " and Oil " + oil_dd.SelectedValue + " @b" ; sq2 += " and Oil " + oil_dd.SelectedValue + " @b"; } h.Add("@b", oil_text.Text); i++; } if (!String.IsNullOrEmpty(price_text.Text)) { if (i == 0) { sql += " where Price " + price_dd.SelectedValue + " @c"; sq2 += " where Price " + price_dd.SelectedValue + " @c"; } else { sql += " and Price " + price_dd.SelectedValue + " @c"; sq2 += " and Price " + price_dd.SelectedValue + " @c"; } h.Add("@c", price_text.Text); } gongjiye.Text = maxpage(sq2,h).ToString(); Label1.Text = sql; Repeater1.DataSource = new CarData().zuhecha(sql,h); Repeater1.DataBind(); } public int maxpage(String sql,Hashtable h) { List<Car> clist = new CarData().zuhecha(sql,h); int i =Convert.ToInt32( Math.Ceiling(clist.Count / (pagecount * 1.0))); return i; } }
完！！
