webform 组合查询
界面:
1 <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> 2 3 <!DOCTYPE html> 4 5 <html xmlns="http://www.w3.org/1999/xhtml"> 6 <head runat="server"> 7 <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> 8 <title></title> 9 </head> 10 <body> 11 <form id="form1" runat="server"> 12 <div> 13 14 名称:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> 15 油耗:<asp:DropDownList ID="DropDownList1" runat="server" EnableViewState="True"> 16 <asp:ListItem Value="=">等于</asp:ListItem> 17 <asp:ListItem Value=">=">大于等于</asp:ListItem> 18 <asp:ListItem Value="<=">小于等于</asp:ListItem> 19 </asp:DropDownList><asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> 20 价格:<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>-<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox> 21 <asp:Button ID="Button1" runat="server" Text="提交" /><br /><br /> 22 <asp:Repeater ID="Repeater1" runat="server"> 23 <HeaderTemplate> 24 <table style="background-color: blue; width: 100%; text-align:center"> 25 <thead> 26 <tr style="color: white;"> 27 <td>编号</td> 28 <td>名称</td> 29 <td>品牌</td> 30 <td>上市时间</td> 31 <td>油耗</td> 32 <td>动力</td> 33 <td>排量</td> 34 <td>价格</td> 35 <td>图片</td> 36 </tr> 37 </thead> 38 <tbody> 39 </HeaderTemplate> 40 <ItemTemplate> 41 <tr style="background-color: #808080"> 42 <td><%#Eval("Code") %></td> 43 <td><%#Eval("Name") %></td> 44 <td><%#Eval("Brand") %></td> 45 <td><%#Eval("Time") %></td> 46 <td><%#Eval("Oil") %></td> 47 <td><%#Eval("Power") %></td> 48 <td><%#Eval("Exhaust") %></td> 49 <td><%#Eval("Price") %></td> 50 <td><%#Eval("Pic") %></td> 51 </tr> 52 </ItemTemplate> 53 <FooterTemplate> 54 </tbody> 55 </table> 56 </FooterTemplate> 57 </asp:Repeater> 58 </div> 59 </form> 60 </body> 61 </html>
后台:
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 6 /// <summary> 7 /// Car 的摘要说明 8 /// </summary> 9 public class Car 10 { 11 public Car() 12 { 13 // 14 // TODO: 在此处添加构造函数逻辑 15 // 16 } 17 public string Code { get; set; } 18 public string Name { get; set; } 19 public string Brand { get; set; } 20 public DateTime Time { get; set; } 21 public decimal Oil { get; set; } 22 public int Power { get; set; } 23 public decimal Exhaust { get; set; } 24 public decimal Price { get; set; } 25 public string Pic { get; set; } 26 }
1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Web; 5 using System.Data.SqlClient; 6 using System.Collections; 7 8 /// <summary> 9 /// CarData 的摘要说明 10 /// </summary> 11 public class CarData 12 { 13 SqlConnection conn = null; 14 SqlCommand cmd = null; 15 public CarData() 16 { 17 conn = new SqlConnection("server=.;database=mydb;user=sa;pwd=123"); 18 cmd = conn.CreateCommand(); 19 } 20 21 public List<Car> Select() 22 { 23 List<Car> clist = new List<Car>(); 24 cmd.CommandText = "select *from Car"; 25 26 conn.Open(); 27 SqlDataReader dr = cmd.ExecuteReader(); 28 if (dr.HasRows) 29 { 30 while (dr.Read()) 31 { 32 Car c = new Car(); 33 c.Code = dr[0].ToString(); 34 c.Name = dr[1].ToString(); 35 c.Brand = dr[2].ToString(); 36 c.Time = Convert.ToDateTime(dr[3]); 37 c.Oil = Convert.ToDecimal(dr[4]); 38 c.Power = Convert.ToInt32(dr[5]); 39 c.Exhaust = Convert.ToInt32(dr[6]); 40 c.Price = Convert.ToDecimal(dr[7]); 41 c.Pic = dr[8].ToString(); 42 43 clist.Add(c); 44 } 45 } 46 conn.Close(); 47 return clist; 48 } 49 50 51 public List<Car> Select(int count,int nowpage) 52 { 53 List<Car> clist = new List<Car>(); 54 cmd.CommandText = "select top "+count+" *from Car where Code not in (select top "+((nowpage-1)*count)+" Code from Car) "; 55 56 conn.Open(); 57 SqlDataReader dr = cmd.ExecuteReader(); 58 if (dr.HasRows) 59 { 60 while (dr.Read()) 61 { 62 Car c = new Car(); 63 c.Code = dr[0].ToString(); 64 c.Name = dr[1].ToString(); 65 c.Brand = dr[2].ToString(); 66 c.Time = Convert.ToDateTime(dr[3]); 67 c.Oil = Convert.ToDecimal(dr[4]); 68 c.Power = Convert.ToInt32(dr[5]); 69 c.Exhaust = Convert.ToInt32(dr[6]); 70 c.Price = Convert.ToDecimal(dr[7]); 71 c.Pic = dr[8].ToString(); 72 73 clist.Add(c); 74 } 75 } 76 conn.Close(); 77 return clist; 78 } 79 80 public List<Car> Select(string sql,Hashtable hat) 81 { 82 List<Car> clist = new List<Car>(); 83 cmd.CommandText = sql; 84 cmd.Parameters.Clear(); 85 86 foreach(string s in hat.Keys) 87 { 88 cmd.Parameters.AddWithValue(s,hat[s]); 89 } 90 91 conn.Open(); 92 SqlDataReader dr = cmd.ExecuteReader(); 93 if (dr.HasRows) 94 { 95 while (dr.Read()) 96 { 97 Car c = new Car(); 98 c.Code = dr[0].ToString(); 99 c.Name = dr[1].ToString(); 100 c.Brand = dr[2].ToString(); 101 c.Time = Convert.ToDateTime(dr[3]); 102 c.Oil = Convert.ToDecimal(dr[4]); 103 c.Power = Convert.ToInt32(dr[5]); 104 c.Exhaust = Convert.ToInt32(dr[6]); 105 c.Price = Convert.ToDecimal(dr[7]); 106 c.Pic = dr[8].ToString(); 107 108 clist.Add(c); 109 } 110 } 111 conn.Close(); 112 return clist; 113 } 114 }
1 using System; 2 using System.Collections; 3 using System.Collections.Generic; 4 using System.Linq; 5 using System.Web; 6 using System.Web.UI; 7 using System.Web.UI.WebControls; 8 9 public partial class _Default : System.Web.UI.Page 10 { 11 protected void Page_Load(object sender, EventArgs e) 12 { 13 if (!IsPostBack) 14 { 15 Repeater1.DataSource = new CarData().Select(); 16 Repeater1.DataBind(); 17 } 18 Button1.Click += Button1_Click; 19 } 20 21 void Button1_Click(object sender, EventArgs e) 22 { 23 Hashtable has=new Hashtable (); 24 string tsql="select *from Car"; 25 //判断文本框中是否有内容需要查询 26 if (TextBox1.Text.Trim().Length > 0) 27 {//如果有内容,那么就拼接到Tsql语句中去 28 tsql += " where name like @name"; 29 has.Add("@name","%"+TextBox1.Text.Trim().ToUpper()+"%"); 30 } 31 else 32 { 33 tsql += " where 1=1"; 34 } 35 if (TextBox2.Text.Trim().Length > 0) 36 { 37 tsql += " and oil "+DropDownList1.SelectedValue+"@oil"; 38 has.Add("@oil", TextBox2.Text.Trim()); 39 } 40 else 41 { 42 tsql += " and 1=1"; 43 } 44 if (TextBox3.Text.Trim().Length > 0) 45 { 46 tsql += " and price>=@price1"; 47 has.Add("@price1", TextBox3.Text.Trim()); 48 } 49 else 50 { 51 tsql += " and 1=1"; 52 } 53 if (TextBox4.Text.Trim().Length > 0) 54 { 55 tsql += " and price<=@price2"; 56 has.Add("@price2", TextBox4.Text.Trim()); 57 } 58 //将拼接好的Tsql语句执行后进行数据绑定 59 Repeater1.DataSource = new CarData().Select(tsql,has); 60 Repeater1.DataBind(); 61 } 62 }
1 using System; 2 using System.Collections; 3 using System.Collections.Generic; 4 using System.Linq; 5 using System.Web; 6 using System.Web.UI; 7 using System.Web.UI.WebControls; 8 9 public partial class _Default : System.Web.UI.Page 10 { 11 protected void Page_Load(object sender, EventArgs e) 12 { 13 if (!IsPostBack) 14 { 15 Repeater1.DataSource = new CarData().Select(); 16 Repeater1.DataBind(); 17 } 18 Button1.Click += Button1_Click; 19 } 20 21 void Button1_Click(object sender, EventArgs e) 22 { 23 Hashtable has = new Hashtable(); 24 string tsql = "select *from Car"; 25 int count = 0; 26 //判断文本框中是否有内容需要查询 27 if (TextBox1.Text.Trim().Length > 0) 28 {//如果有内容,那么就拼接到Tsql语句中去 29 tsql += " where name like @name"; 30 has.Add("@name", "%" + TextBox1.Text.Trim().ToUpper() + "%"); 31 count++; 32 } 33 34 if (TextBox2.Text.Trim().Length > 0) 35 { 36 if (count > 0) 37 tsql += " and oil " + DropDownList1.SelectedValue + "@oil"; 38 else 39 tsql += " where oil " + DropDownList1.SelectedValue + "@oil"; 40 41 has.Add("@oil", TextBox2.Text.Trim()); 42 count++; 43 } 44 45 if (TextBox3.Text.Trim().Length > 0) 46 { 47 if (count > 0) 48 tsql += " and price>=@price1"; 49 else 50 tsql += " where price>=@price1"; 51 has.Add("@price1", TextBox3.Text.Trim()); 52 count++; 53 } 54 55 if (TextBox4.Text.Trim().Length > 0) 56 { 57 if (count > 0) 58 tsql += " and price<=@price2"; 59 else 60 tsql += " where price<=@price2"; 61 has.Add("@price2", TextBox4.Text.Trim()); 62 } 63 //将拼接好的Tsql语句执行后进行数据绑定 64 Repeater1.DataSource = new CarData().Select(tsql, has); 65 Repeater1.DataBind(); 66 } 67 }