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=" 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=" padding: 0px; color: rgb(128, 0, 0); line-height: 1.5 !important;">">
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 }
————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
Default.aspx.cs 方法一：
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 }
————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
Default.aspx.cs 方法二：
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 }
————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————
效果如图：
