7.22 分页组合查询
<%@ 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> <style type="text/css"> table { width:100%; background-color:navy; text-align:center; font-family:微软雅黑; font-size:14px; } #tr_Head { color:white; } .tr_Main { background-color:#e0e0e0; } td { padding:15px; } </style> </head> <body> <form id="form1" runat="server"> <br /> 名称：<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> 马力：<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> 价格：<asp:DropDownList ID="DropDownList1" runat="server"> <asp:ListItem>=</asp:ListItem> <asp:ListItem>>=</asp:ListItem> <asp:ListItem Value="<="></asp:ListItem> </asp:DropDownList> <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="查询" /> <br /> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label><br /> <br /><br /> <asp:Repeater ID="Repeater1" runat="server"> <HeaderTemplate> <table> <tr id="tr_Head"> <td>编号</td> <td>名称</td> <td>系别</td> <td>时间</td> <td>油耗</td> <td>马力</td> <td>排量</td> <td>价格</td> <td>操作</td> </tr> </HeaderTemplate> <ItemTemplate> <tr class="tr_Main"> <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("Exhaust") %></td> <td><%#Eval("Price") %></td> <td>aaaa</td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater><br /> 当前第[<asp:Label ID="txt_PageCount" runat="server" Text="Label"></asp:Label>]页， <asp:LinkButton ID="Btn_First" runat="server">首页</asp:LinkButton> <asp:LinkButton ID="Btn_Prev" runat="server">上一页</asp:LinkButton> <asp:LinkButton ID="Btn_Next" runat="server">下一页</asp:LinkButton> <asp:LinkButton ID="Btn_Last" runat="server">末页</asp:LinkButton> <asp:DropDownList ID="aaaa" runat="server" AutoPostBack="true"></asp:DropDownList> </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 Count = 5;//每页显示多少条 protected void Page_Load(object sender, EventArgs e) { Btn_Prev.Click += Btn_Prev_Click;//上一页按钮 Btn_Next.Click += Btn_Next_Click;//下一页按钮 Btn_First.Click += Btn_First_Click;//首页 Btn_Last.Click += Btn_Last_Click;//末页 Button1.Click += Button1_Click;//查询按钮 aaaa.SelectedIndexChanged += aaaa_SelectedIndexChanged; if (IsPostBack == false) { Repeater1.DataSource = new CarData().SelectFZ(TSQL(1)); Repeater1.DataBind(); txt_PageCount.Text = "1"; Btn_Prev.Enabled = false; Btn_First.Enabled = false; //添加下拉列表数据 for (int i = 1; i <= MaxNumber(); i++) { ListItem li = new ListItem(i.ToString(), i.ToString()); aaaa.Items.Add(li); } } } //组合查询 void Button1_Click(object sender, EventArgs e) { //1、将语句拼完 - 调用TSQL()方法 //2、用拼完的语句查数据并绑定 Repeater1.DataSource = new CarData().SelectFZ(TSQL(1)); Repeater1.DataBind(); Label1.Text = TSQL(1); aaaa.Items.Clear(); //添加下拉列表数据 for (int i = 1; i <= MaxNumber(); i++) { ListItem li = new ListItem(i.ToString(), i.ToString()); aaaa.Items.Add(li); } txt_PageCount.Text = "1"; aaaa.SelectedValue ="1"; Btn_Prev.Enabled = false; Btn_First.Enabled = false; if (txt_PageCount.Text == MaxNumber().ToString()) { Btn_Next.Enabled = false; Btn_Last.Enabled = false; } } //下拉列表 void aaaa_SelectedIndexChanged(object sender, EventArgs e) { //取出来要去往的页数 int yy = Convert.ToInt32(aaaa.SelectedItem.Value); Repeater1.DataSource = new CarData().SelectFZ(TSQL(yy)); Repeater1.DataBind(); txt_PageCount.Text = yy.ToString(); if (yy == 1) { Btn_First.Enabled = false; Btn_Prev.Enabled = false; Btn_Last.Enabled = true; Btn_Next.Enabled = true; } if (yy == MaxNumber()) { Btn_First.Enabled = true; Btn_Prev.Enabled = true; Btn_Last.Enabled = false; Btn_Next.Enabled = false; } if (yy != 1 && yy != MaxNumber()) { Btn_First.Enabled = true; Btn_Prev.Enabled = true; Btn_Last.Enabled = true; Btn_Next.Enabled = true; } } //末页 void Btn_Last_Click(object sender, EventArgs e) { Repeater1.DataSource = new CarData().SelectFZ(TSQL(MaxNumber())); Repeater1.DataBind(); txt_PageCount.Text = MaxNumber().ToString(); aaaa.SelectedValue = MaxNumber().ToString(); Btn_Next.Enabled = false; Btn_Last.Enabled = false; Btn_First.Enabled = true; Btn_Prev.Enabled = true; } //首页 void Btn_First_Click(object sender, EventArgs e) { Repeater1.DataSource = new CarData().SelectFZ(TSQL(1)); Repeater1.DataBind(); txt_PageCount.Text = "1"; aaaa.SelectedValue = "1"; Btn_First.Enabled = false; Btn_Prev.Enabled = false; Btn_Last.Enabled = true; Btn_Next.Enabled = true; } //下一页按钮 void Btn_Next_Click(object sender, EventArgs e) { //先看看当前是第几页，然后+1 int NowNum = Convert.ToInt32(txt_PageCount.Text) + 1; Repeater1.DataSource = new CarData().SelectFZ(TSQL(NowNum)); Repeater1.DataBind(); txt_PageCount.Text = NowNum.ToString(); aaaa.SelectedValue = NowNum.ToString(); //如果当前页数已经等于最大页数了，就将下一页按钮变为不可用 if (txt_PageCount.Text == MaxNumber().ToString()) { Btn_Next.Enabled = false; //下一页不可用 Btn_Last.Enabled = false; //末页不可用 } Btn_Prev.Enabled = true; //上一页按钮变为可用 Btn_First.Enabled = true; //首页可用 } //上一页按钮 void Btn_Prev_Click(object sender, EventArgs e) { //先看看当前是第几页，然后-1 int NowNum = Convert.ToInt32(txt_PageCount.Text) - 1; Repeater1.DataSource = new CarData().SelectFZ(TSQL(NowNum)); Repeater1.DataBind(); txt_PageCount.Text = NowNum.ToString(); aaaa.SelectedValue = NowNum.ToString(); //只要按下上一页按钮，就让下一页按钮变为可用 Btn_Next.Enabled = true; Btn_Last.Enabled = true; if (txt_PageCount.Text == "1") { Btn_Prev.Enabled = false; Btn_First.Enabled = false; } } //核心，如何返回Tsql语句是难点 private string TSQL(int PageNumber) { int cc = 0;//记录一下查询条数 string sql = "select top " + Count + " * from Car"; string t1 = ""; // string t2 = ""; //1、将条件查询的语句拼完 if (TextBox1.Text != "") { sql += " where name like '%" + TextBox1.Text + "%' "; cc++; t1 += " where name like '%" + TextBox1.Text + "%'"; } if (TextBox2.Text != "") { if (cc > 0) { sql += " and powers like '%" + TextBox2.Text + "%'"; t1 += " and powers like '%" + TextBox2.Text + "%'"; } else { sql += " where powers like '%" + TextBox2.Text + "%'"; t1 += " where powers like '%" + TextBox2.Text + "%'"; } cc++; } if (TextBox3.Text != "") { if (cc > 0) { sql += " and price " + DropDownList1.SelectedItem.Value +TextBox3.Text; t1 += " and price " + DropDownList1.SelectedItem.Value + TextBox3.Text ; } else { sql += " where price " + DropDownList1.SelectedItem.Value +TextBox3.Text; t1 += " where price " + DropDownList1.SelectedItem.Value +TextBox3.Text; } cc++; } //2、将分页的语句拼完 if (cc > 0) { sql += " and Ids not in ( select top " + (Count * (PageNumber - 1)) + " Ids from Car " + t1+ " )"; } else { sql += " where Ids not in ( select top " + (Count * (PageNumber - 1)) + " Ids from Car " + t1+")"; } return sql; } //查询全部的复合条件的数据 private string TSQL1() { int cc = 0;//记录一下查询条数 string sql = "select * from Car"; //1、将条件查询的语句拼完 if (TextBox1.Text != "") { sql += " where name like '%" + TextBox1.Text + "%' "; cc++; } if (TextBox2.Text != "") { if (cc > 0) { sql += " and powers like '%" + TextBox2.Text + "%'"; } else { sql += " where powers like '%" + TextBox2.Text + "%'"; } cc++; } if (TextBox3.Text != "") { if (cc > 0) { sql += " and price " + DropDownList1.SelectedItem.Value + TextBox3.Text; } else { sql += " where price " + DropDownList1.SelectedItem.Value +TextBox3.Text; } cc++; } return sql; } /// <summary> /// 返回最大页数 /// </summary> /// <returns></returns> public int MaxNumber() { List<Car> list = new CarData().SelectFZ(TSQL1());//全部数据的条数 double bb = list.Count / (Count * 1.0); //全部条数除以每页显示条数得到的就是最大页数 return Convert.ToInt32(Math.Ceiling(bb));//将最大页数取上限，返回出去 } }
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=Data0425;user=sa;pwd=123456"); cmd = conn.CreateCommand(); } /// <summary> /// 查询全部汽车表信息 /// </summary> /// <returns></returns> public List<Car> SelectAll() { List<Car> list = new List<Car>(); cmd.CommandText = "select *from car"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { Car c = new Car(); c.Ids = Convert.ToInt32(dr["ids"]); c.Code = dr["code"].ToString(); c.Name = dr["name"].ToString(); c.Brand = dr["brand"].ToString(); c.Time = Convert.ToDateTime(dr["time"]); c.Oil = Convert.ToDecimal(dr["oil"]); c.Powers = Convert.ToInt32(dr["powers"]); c.Exhaust = Convert.ToInt32(dr["exhaust"]); c.Price = Convert.ToDecimal(dr["price"]); list.Add(c); } } conn.Close(); return list; } /// <summary> /// 分页查询 /// </summary> /// <param name="Count">每页显示几条</param> /// <param name="Num">当前是第几页</param> /// <returns></returns> public List<Car> Select(int Count, int Num) { List<Car> list = new List<Car>(); cmd.CommandText = "select top " + Count + " *from car where code not in(select top " + (Count * (Num - 1)) + " code from car)"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { Car c = new Car(); c.Ids = Convert.ToInt32(dr["ids"]); c.Code = dr["code"].ToString(); c.Name = dr["name"].ToString(); c.Brand = dr["brand"].ToString(); c.Time = Convert.ToDateTime(dr["time"]); c.Oil = Convert.ToDecimal(dr["oil"]); c.Powers = Convert.ToInt32(dr["powers"]); c.Exhaust = Convert.ToInt32(dr["exhaust"]); c.Price = Convert.ToDecimal(dr["price"]); list.Add(c); } } conn.Close(); return list; } // select top 5 *from car where name like '%宝马%' and code not in(select top 3 code from car where name like '%宝马%') /// <summary> /// 组合查询 /// </summary> /// <param name="Tsql">SQL语句</param> /// <param name="hs">哈希表</param> /// <returns></returns> public List<Car> Select(string Tsql, Hashtable hs) { List<Car> list = new List<Car>(); cmd.CommandText = Tsql; cmd.Parameters.Clear(); foreach (string s in hs.Keys) { cmd.Parameters.Add(s, hs[s]); } conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { Car c = new Car(); c.Ids = Convert.ToInt32(dr["ids"]); c.Code = dr["code"].ToString(); c.Name = dr["name"].ToString(); c.Brand = dr["brand"].ToString(); c.Time = Convert.ToDateTime(dr["time"]); c.Oil = Convert.ToDecimal(dr["oil"]); c.Powers = Convert.ToInt32(dr["powers"]); c.Exhaust = Convert.ToInt32(dr["exhaust"]); c.Price = Convert.ToDecimal(dr["price"]); list.Add(c); } } conn.Close(); return list; } /// <summary> /// 分页组合查询 /// </summary> /// <param name="Tsql">Tsql语句</param> /// <returns></returns> public List<Car> SelectFZ(string Tsql) { List<Car> list = new List<Car>(); cmd.CommandText = Tsql; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { Car c = new Car(); c.Ids = Convert.ToInt32(dr[0]); c.Code = dr[1].ToString(); c.Name = dr[2].ToString(); c.Brand = dr[3].ToString(); c.Time = Convert.ToDateTime(dr[4]); c.Oil = Convert.ToDecimal(dr[5]); c.Powers = Convert.ToInt32(dr[6]); c.Exhaust = Convert.ToInt32(dr[7]); c.Price = Convert.ToDecimal(dr[8]); list.Add(c); } } conn.Close(); return list; } }
页面一加载：
价格>=30：
宝马 价格>=30:
