【2017-5-25】WebForm 条件查询 + 分页 组合
实体类
public int ids { get; set; } public string code { get; set; } public string name { get; set; } public decimal oil { get; set; } public decimal price { get; set; }
封装类
SqlConnection conn = null; SqlCommand cmd = null; public carData() { conn = new SqlConnection("server=.;database=One;user=sa;pwd=123"); cmd = conn.CreateCommand(); } //查询全部 public List<car> SelectAll() { List<car> clist = 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.oil = Convert.ToDecimal(dr["oil"]); c.price = Convert.ToDecimal(dr["price"]); clist.Add(c); } } conn.Close(); return clist; } //分页查询 public List<car> SelectAll(int count, int number) { List<car> clist = new List<car>(); cmd.CommandText = "select top " + count + " * from car where ids not in(select top " + (count * (number - 1)) + " ids 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.oil = Convert.ToDecimal(dr["oil"]); c.price = Convert.ToDecimal(dr["price"]); clist.Add(c); } } conn.Close(); return clist; } //查询数据条数 public int SelectCount() { int a = 0; cmd.CommandText = "select count(*) from car"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); dr.Read(); a = Convert.ToInt32(dr[0]); conn.Close(); return a; } //查询数据条数传值 public int SelectCount(string tsql, Hashtable hs) { int a = 0; cmd.CommandText = tsql; cmd.Parameters.Clear(); foreach (string h in hs.Keys) { cmd.Parameters.AddWithValue(h, hs[h]); } conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); dr.Read(); a = Convert.ToInt32(dr[0]); conn.Close(); return a; } //条件查询 public List<car> SelectAll(string tsql, Hashtable hs) { List<car> clist = new List<car>(); cmd.CommandText = tsql; cmd.Parameters.Clear(); foreach (string h in hs.Keys) { cmd.Parameters.AddWithValue(h, hs[h]); } 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.oil = Convert.ToDecimal(dr["oil"]); c.price = Convert.ToDecimal(dr["price"]); clist.Add(c); } } conn.Close(); return clist; }
前端
名称：<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> 油耗：<asp:DropDownList ID="DropDownList2" 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="TextBox2" runat="server"></asp:TextBox> 价格：<asp:DropDownList ID="DropDownList3" runat="server"> <asp:ListItem text="任意金额" Value="null"></asp:ListItem> <asp:ListItem text="小于30万" Value="price < 30"></asp:ListItem> <asp:ListItem text="大于30万小于40万" Value="price > 30 and price < 40"></asp:ListItem> <asp:ListItem text="大于40万小于50万" Value="price > 40 and price < 50"></asp:ListItem> <asp:ListItem text="大于50万" Value="price > 40"></asp:ListItem> </asp:DropDownList> <asp:Button ID="Button6" runat="server" Text="查询" /> <br /> <%--显示拼接的字符串--%> <asp:Label ID="Label3" runat="server" Text="Label"></asp:Label> <br /> <br /> <br /> <br /> <br /> <br /> <table style="width: 100%; background-color: #0094ff; text-align: center;"> <tr> <td>Ids</td> <td>编号</td> <td>名称</td> <td>油耗</td> <td>价格</td> </tr> <asp:Repeater ID="Repeater1" runat="server"> <ItemTemplate> <tr style="background-color: #fff;"> <td><%#Eval("ids") %></td> <td><%#Eval("code") %></td> <td><%#Eval("name") %></td> <td><%#Eval("oil") %></td> <td><%#Eval("price") %></td> </tr> </ItemTemplate> </asp:Repeater> </table> <br /> 当前第【<asp:Label ID="Label1" runat="server" Text="1"></asp:Label>】页 共【<asp:Label ID="Label2" runat="server" Text="1"></asp:Label>】页 <asp:Button ID="Button1" runat="server" Text="首页" /> <asp:Button ID="Button2" runat="server" Text="上一页" /> <asp:Button ID="Button3" runat="server" Text="下一页" /> <asp:Button ID="Button4" runat="server" Text="尾页" /> <asp:DropDownList ID="DropDownList1" runat="server"></asp:DropDownList><asp:Button ID="Button5" runat="server" Text="跳转" />
后台
int count = 5;//每页显示条数 protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Repeater1.DataSource = new carData().SelectAll(count, 1); Repeater1.DataBind(); Label2.Text = MaxPageNumber().ToString(); for (int i = 1; i <= MaxPageNumber(); i++) { ListItem li = new ListItem(i.ToString(), i.ToString()); DropDownList1.Items.Add(li); } } //改变按钮是否可用状态 if (Label1.Text == "1") { Button1.Enabled = false; Button2.Enabled = false; } if (Label1.Text != "1") { Button1.Enabled = true; Button2.Enabled = true; } if (Label1.Text == MaxPageNumber().ToString()) { Button3.Enabled = false; Button4.Enabled = false; } if (Label1.Text != MaxPageNumber().ToString()) { Button3.Enabled = true; Button4.Enabled = true; } //按钮点击事件 Button4.Click += Button4_Click; Button3.Click += Button3_Click; Button2.Click += Button2_Click; Button1.Click += Button1_Click; Button5.Click += Button5_Click; Button6.Click += Button6_Click; } void Button6_Click(object sender, EventArgs e) { Repeater1.DataSource = EndData(1); Repeater1.DataBind(); Label1.Text = "1"; Label2.Text = MaxPageNumber2().ToString(); } //跳转 void Button5_Click(object sender, EventArgs e) { int a = Convert.ToInt32(DropDownList1.SelectedValue); //将下一页数据绑定到 Repeater1.DataSource = EndData(a); Repeater1.DataBind(); //将当前显示的页数改变到页面上去 Label1.Text = a.ToString(); //改变按钮是否可用状态 if (Label1.Text == "1") { Button1.Enabled = false; Button2.Enabled = false; } if (Label1.Text != "1") { Button1.Enabled = true; Button2.Enabled = true; } if (Label1.Text == MaxPageNumber2().ToString()) { Button3.Enabled = false; Button4.Enabled = false; } if (Label1.Text != MaxPageNumber2().ToString()) { Button3.Enabled = true; Button4.Enabled = true; } } //首页 void Button1_Click(object sender, EventArgs e) { //将下一页数据绑定 Repeater1.DataSource = EndData(1); Repeater1.DataBind(); //将当前显示的页数改变 Label1.Text = "1"; //改变按钮是否可用状态 if (Label1.Text == "1") { Button1.Enabled = false; Button2.Enabled = false; } else { Button1.Enabled = true; Button2.Enabled = true; } } //上一页 void Button2_Click(object sender, EventArgs e) { //获取当前页数，计算上一页页数 int NextNumber = Convert.ToInt32(Label1.Text) - 1; if (NextNumber < 1) { return; } //将上一页数据绑定 Repeater1.DataSource = EndData(NextNumber); Repeater1.DataBind(); //将当前显示的页数改变 Label1.Text = NextNumber.ToString(); //改变按钮是否可用状态 if (Label1.Text == "1") { Button1.Enabled = false; Button2.Enabled = false; } else { Button1.Enabled = true; Button2.Enabled = true; } if (Label1.Text == MaxPageNumber2().ToString()) { Button3.Enabled = false; Button4.Enabled = false; } else { Button3.Enabled = true; Button4.Enabled = true; } } //下一页 void Button3_Click(object sender, EventArgs e) { //获取当前页数，计算下一页页数 int NextNumber = Convert.ToInt32(Label1.Text) + 1; if (NextNumber > MaxPageNumber2()) { return; } //将下一页数据绑定 Repeater1.DataSource = EndData(NextNumber); Repeater1.DataBind(); //将当前显示的页数改变 Label1.Text = NextNumber.ToString(); //改变按钮是否可用状态 if (Label1.Text == MaxPageNumber2().ToString()) { Button3.Enabled = false; Button4.Enabled = false; } else { Button3.Enabled = true; Button4.Enabled = true; } if (Label1.Text == "1") { Button1.Enabled = false; Button2.Enabled = false; } else { Button1.Enabled = true; Button2.Enabled = true; } } //尾页 void Button4_Click(object sender, EventArgs e) { //将下一页数据绑定 Repeater1.DataSource = EndData(MaxPageNumber2()); Repeater1.DataBind(); //将当前显示的页数改变 Label1.Text = MaxPageNumber2().ToString(); //改变按钮是否可用状态 if (Label1.Text == "1") { Button1.Enabled = false; Button2.Enabled = false; } else { Button1.Enabled = true; Button2.Enabled = true; } if (Label1.Text == MaxPageNumber2().ToString()) { Button3.Enabled = false; Button4.Enabled = false; } else { Button3.Enabled = true; Button4.Enabled = true; } } //取最大页数 public int MaxPageNumber() { int a = 0; int maxcount = new carData().SelectCount(); decimal d = Convert.ToDecimal(maxcount) / count; a = Convert.ToInt32(Math.Ceiling(d)); return a; } //条件查询分页最后数据 public List<car> EndData(int n) { int a = 0; Hashtable hs = new Hashtable(); string tsql = "select top " + count + " * from car "; string sql1 = ""; if (TextBox1.Text.Trim().Length > 0) { sql1 += "where name like @a "; hs.Add("@a", "%" + TextBox1.Text + "%"); a++; } if (TextBox2.Text.Trim().Length > 0) { if (a > 0) { sql1 += "and oil " + DropDownList2.SelectedValue + " @b "; } else { sql1 += "where oil " + DropDownList2.SelectedValue + " @b "; } hs.Add("@b", TextBox2.Text); a++; } if (DropDownList3.SelectedValue != "null") { if (a > 0) { sql1 += "and " + DropDownList3.SelectedValue; } else { sql1 += "where " + DropDownList3.SelectedValue; } a++; } tsql += sql1; if (a > 0) { tsql += "and ids not in (select top " + count * (n - 1) + " ids from car " + sql1 + ")"; } else { tsql += "where ids not in (select top " + count * (n - 1) + " ids from car " + sql1 + ")"; } Label3.Text = tsql; List<car> clist = new carData().SelectAll(tsql, hs); return clist; } public int MaxPageNumber2() { int end = 0; int a = 0; Hashtable hs = new Hashtable(); string tsql = "select count(*) from car "; string sql1 = ""; if (TextBox1.Text.Trim().Length > 0) { sql1 += "where name like @a "; hs.Add("@a", "%" + TextBox1.Text + "%"); a++; } if (TextBox2.Text.Trim().Length > 0) { if (a > 0) { sql1 += "and oil " + DropDownList2.SelectedValue + " @b "; } else { sql1 += "where oil " + DropDownList2.SelectedValue + " @b "; } hs.Add("@b", TextBox2.Text); a++; } if (DropDownList3.SelectedValue != "null") { if (a > 0) { sql1 += "and " + DropDownList3.SelectedValue; } else { sql1 += "where " + DropDownList3.SelectedValue; } a++; } tsql += sql1; int aaa = new carData().SelectCount(tsql, hs); end = Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(aaa) / count)); return end; }
