首页 > 代码库 > 【2017-5-24】WebForm 条件查询
【2017-5-24】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; }
封装类
注意引用using System.Data.SqlClient;
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 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="DropDownList1" runat="server"> <asp:ListItem Text="小于" Value=http://www.mamicode.com/"<"></asp:ListItem> <asp:ListItem Text="小于等于" Value=http://www.mamicode.com/"<="></asp:ListItem> <asp:ListItem Text="等于" Value=http://www.mamicode.com/"="></asp:ListItem> <asp:ListItem Text="大于等于" Value=http://www.mamicode.com/">="></asp:ListItem> <asp:ListItem Text="大于" Value=http://www.mamicode.com/">"></asp:ListItem> </asp:DropDownList> <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> 价格：<asp:DropDownList ID="DropDownList2" runat="server"> <asp:ListItem text="任意金额" Value=http://www.mamicode.com/"null"></asp:ListItem> <asp:ListItem text="小于30万" Value=http://www.mamicode.com/"price < 30"></asp:ListItem> <asp:ListItem text="大于30万小于40万" Value=http://www.mamicode.com/"price > 30 and price < 40"></asp:ListItem> <asp:ListItem text="大于40万小于50万" Value=http://www.mamicode.com/"price > 40 and price < 50"></asp:ListItem> <asp:ListItem text="大于50万" Value=http://www.mamicode.com/"price > 40"></asp:ListItem> </asp:DropDownList> <asp:Button ID="Button1" runat="server" Text="查询" /> <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>
后台
if (!IsPostBack) { Repeater1.DataSource = new carData().SelectAll(); Repeater1.DataBind(); } //查询按钮 Button1.Click += Button1_Click; } void Button1_Click(object sender, EventArgs e) { //防止字符串注入攻击，将用户自己输得内容放到哈希表集合当中 Hashtable hs = new Hashtable(); int count = 0; string tsql = "select * from car "; if (TextBox1.Text.Trim().Length > 0) { tsql += "where name like @a "; hs.Add("@a", "%" + TextBox1.Text.Trim() + "%"); count++; } if (TextBox2.Text.Trim().Length > 0) { if (count > 0) { tsql += "and oil " + DropDownList1.SelectedValue + " @b "; } else { tsql += "where oil " + DropDownList1.SelectedValue + " @b "; } hs.Add("@b", "TextBox2.Text.Trim()"); count++; } if (DropDownList2.SelectedValue != "null") { if (count > 0) { tsql += "and " + DropDownList2.SelectedValue; } else { tsql += "where " + DropDownList2.SelectedValue; } count++; } //数据绑定 Repeater1.DataSource = new carData().SelectAll(tsql,hs); Repeater1.DataBind(); }
【2017-5-24】WebForm 条件查询
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。