组合查询和分页的核心思想就是拼接SQL语句，根据自己想要显示的内容写出SQL语句查询显示。
组合查询和分页小练习：
Model：
1 private string _Code; 2 /// <summary> 3 /// 编号 4 /// </summary> 5 public string Code 6 { 7 get { return _Code; } 8 set { _Code = value; } 9 } 10 private string _Name; 11 /// <summary> 12 /// 名字 13 /// </summary> 14 public string Name 15 { 16 get { return _Name; } 17 set { _Name = value; } 18 } 19 private decimal _Oil; 20 /// <summary> 21 /// 油耗 22 /// </summary> 23 public decimal Oil 24 { 25 get { return _Oil; } 26 set { _Oil = value; } 27 } 28 private decimal _Price; 29 /// <summary> 30 /// 价格 31 /// </summary> 32 public decimal Price 33 { 34 get { return _Price; } 35 set { _Price = value; } 36 } 37 private int _Powers; 38 /// <summary> 39 /// 马力 40 /// </summary> 41 public int Powers 42 { 43 get { return _Powers; } 44 set { _Powers = value; } 45 }
Operation：
1 SqlConnection conn = null; 2 SqlCommand com = null; 3 public Operation() 4 { 5 conn = new SqlConnection("server=.;database=ADOlianxi0425;user=sa;pwd=123;"); 6 com = conn.CreateCommand(); 7 } 8 9 /// <summary> 10 /// 查询全部信息 11 /// </summary> 12 /// <returns></returns> 13 public List<Model> selectall(string SQL, Hashtable HS) 14 { 15 List<Model> list = new List<Model>(); 16 com.CommandText = SQL; 17 com.Parameters.Clear(); 18 foreach (string s in HS.Keys) 19 { 20 com.Parameters.Add(s, HS[s]); 21 } 22 conn.Open(); 23 SqlDataReader dr = com.ExecuteReader(); 24 if (dr.HasRows) 25 { 26 while (dr.Read()) 27 { 28 Model M = new Model(); 29 M.Code = dr["code"].ToString(); 30 M.Name = dr["name"].ToString(); 31 M.Oil = Convert.ToDecimal(dr["oil"]); 32 M.Powers = Convert.ToInt32(dr["powers"]); 33 M.Price = Convert.ToDecimal(dr["price"]); 34 list.Add(M); 35 } 36 } 37 conn.Close(); 38 return list; 39 }
HTML界面：
1 <style> 2 #Span1 { 3 position: relative; 4 left: 15%; 5 top: 30px; 6 font-family: 微软雅黑; 7 } 8 9 #Button1 { 10 width: 50px; 11 height: 30px; 12 color: white; 13 background-color: black; 14 font-family: 微软雅黑; 15 } 16 17 #Table { 18 width: 73%; 19 left: 15%; 20 top: 30px; 21 position: relative; 22 } 23 24 #Span2 { 25 left: 70%; 26 top: 280px; 27 position: fixed; 28 font-family: 微软雅黑; 29 } 30 </style> 31 </head> 32 <body> 33 <form id="form1" runat="server"> 34 <span id="Span1">名字：<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> 35 油耗：<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> 36 马力：<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox> 37 价格：<asp:DropDownList ID="DropDownList1" runat="server"></asp:DropDownList><asp:TextBox ID="TextBox4" runat="server"></asp:TextBox> 38 <asp:Button ID="Button1" runat="server" Text="查询" /> 39 </span> 40 <br /> 41 <br /> 42 <table id="Table"> 43 <asp:Repeater ID="Repeater1" runat="server"> 44 <HeaderTemplate> 45 <tr> 46 <td>编号</td> 47 <td>名字</td> 48 <td>马力</td> 49 <td>油耗</td> 50 <td>价格</td> 51 </tr> 52 </HeaderTemplate> 53 <ItemTemplate> 54 <tr> 55 <td><%#Eval("Code") %></td> 56 <td><%#Eval("Name") %></td> 57 <td><%#Eval("Powers") %></td> 58 <td><%#Eval("Oil") %></td> 59 <td><%#Eval("Price") %></td> 60 </tr> 61 </ItemTemplate> 62 </asp:Repeater> 63 </table> 64 <span id="Span2">第[<asp:Label ID="Label1" runat="server"></asp:Label>]页 65 <asp:LinkButton ID="LinkButton1" runat="server">上一页</asp:LinkButton> 66 <asp:LinkButton ID="LinkButton2" runat="server">下一页</asp:LinkButton> 67 </span> 68 </form> 69 </body> 70 </html>
C#后台：
1 public partial class _Default : System.Web.UI.Page 2 { 3 string SQL = "select top 3 * from Car "; 4 string SQL1 = "select * from Car "; 5 string SQL2 = ""; 6 Hashtable HS = new Hashtable(); 7 int num = 0; 8 protected void Page_Load(object sender, EventArgs e) 9 { 10 Button1.Click += Button1_Click;//点击查询 11 LinkButton1.Click += LinkButton1_Click;//点击上一页 12 LinkButton2.Click += LinkButton2_Click;//点击下一页 13 if (IsPostBack == false)//只有在网页打开时执行一次 14 { 15 Repeater1.DataSource = new Operation().selectall(SQL, HS);//绑定数据 16 Repeater1.DataBind(); 17 ListItem List = new ListItem();//向DropDownList中添加数据 18 List.Text = "="; 19 List.Value = "="; 20 DropDownList1.Items.Add(List); 21 ListItem List1 = new ListItem(); 22 List1.Text = ">="; 23 List1.Value = ">="; 24 DropDownList1.Items.Add(List1); 25 ListItem List2 = new ListItem(); 26 List2.Text = "<="; 27 List2.Value = "<="; 28 DropDownList1.Items.Add(List2); 29 Label1.Text = "1"; 30 LinkButton1.Enabled = false; 31 Session["SQL"] = SQL;//记录想要显示内容的SQL语句 32 Session["SQL1"] = SQL1;//记录想要查询数据总数量的SQL语句 33 Session["SQL2"] = SQL2;//记录想要查询数据条件的查询语句 34 Session["HS"] = HS;//用Hashtable集合记录防攻击字符串（即查询条件） 35 Session["NUM"] = num;//记录语句拼接时是否拼接过，确定where还是and 36 //以上五条可以不用Session记录，直接封装成一个方法调用也可以 37 } 38 } 39 /// <summary> 40 /// 下一页 41 /// </summary> 42 /// <param name="sender"></param> 43 /// <param name="e"></param> 44 void LinkButton2_Click(object sender, EventArgs e) 45 { 46 int num = 1; 47 if (Session["HS"] != null) 48 { 49 HS = (Hashtable)Session["HS"]; 50 } 51 if (Session["SQL1"] != null) 52 { 53 num = Number(Session["SQL1"].ToString()); 54 } 55 if (Session["SQL"] != null && Session["NUM"] != null && Session["SQL2"] != null) 56 { 57 if (Convert.ToInt32(Session["NUM"]) == 0) 58 { 59 SQL = Session["SQL"].ToString() + "where code not in (select top " + Convert.ToInt32(Label1.Text) * 3 + " code from car " + Session["SQL2"].ToString() + ") "; 60 } 61 else 62 { 63 SQL = Session["SQL"].ToString() + "and code not in (select top " + Convert.ToInt32(Label1.Text) * 3 + " code from car " + Session["SQL2"].ToString() + ") "; 64 } 65 } 66 Repeater1.DataSource = new Operation().selectall(SQL, HS); 67 Repeater1.DataBind(); 68 Label1.Text = (Convert.ToInt32(Label1.Text) + 1).ToString(); 69 LinkButton1.Enabled = true; 70 if (Label1.Text == num.ToString()) 71 { 72 LinkButton2.Enabled = false; 73 } 74 } 75 /// <summary> 76 /// 上一页 77 /// </summary> 78 /// <param name="sender"></param> 79 /// <param name="e"></param> 80 void LinkButton1_Click(object sender, EventArgs e) 81 { 82 if (Session["HS"] != null) 83 { 84 HS = (Hashtable)Session["HS"]; 85 } 86 if (Session["SQL"] != null && Session["NUM"] != null && Session["SQL2"] != null) 87 { 88 if (Convert.ToInt32(Session["NUM"]) == 0) 89 { 90 SQL = Session["SQL"].ToString() + "where code not in (select top " + (Convert.ToInt32(Label1.Text) - 2) * 3 + " code from car " + Session["SQL2"].ToString() + ") "; 91 } 92 else 93 { 94 SQL = Session["SQL"].ToString() + "and code not in (select top " + (Convert.ToInt32(Label1.Text) - 2) * 3 + " code from car " + Session["SQL2"].ToString() + ") "; 95 } 96 } 97 Repeater1.DataSource = new Operation().selectall(SQL, HS); 98 Repeater1.DataBind(); 99 Label1.Text = (Convert.ToInt32(Label1.Text) - 1).ToString(); 100 LinkButton2.Enabled = true; 101 if (Label1.Text == "1") 102 { 103 LinkButton1.Enabled = false; 104 } 105 } 106 /// <summary> 107 /// 点击查询 108 /// </summary> 109 /// <param name="sender"></param> 110 /// <param name="e"></param> 111 void Button1_Click(object sender, EventArgs e) 112 { 113 SQL = "select top 3 * from Car "; 114 SQL1 = "select * from car "; 115 HS = new Hashtable(); 116 num = 0; 117 if (TextBox1.Text != "") 118 { 119 if (num == 0) 120 { 121 SQL += "where name like @name "; 122 SQL1 += "where name like @name "; 123 SQL2 += "where name like @name "; 124 } 125 else 126 { 127 SQL += "and name like @name "; 128 SQL1 += "and name like @name "; 129 SQL2 += "and name like @name "; 130 } 131 HS.Add("@name", "%" + TextBox1.Text + "%"); 132 num++; 133 } 134 if (TextBox2.Text != "") 135 { 136 if (num == 0) 137 { 138 SQL += "where oil like @oil "; 139 SQL1 += "where oil like @oil "; 140 SQL2 += "where oil like @oil "; 141 } 142 else 143 { 144 SQL += "and oil like @oil "; 145 SQL1 += "and oil like @oil "; 146 SQL2 += "and oil like @oil "; 147 } 148 HS.Add("@oil", "%" + TextBox2.Text + "%"); 149 num++; 150 } 151 if (TextBox3.Text != "") 152 { 153 if (num == 0) 154 { 155 SQL += "where powers like @powers "; 156 SQL1 += "where powers like @powers "; 157 SQL2 += "where powers like @powers "; 158 } 159 else 160 { 161 SQL += "and powers like @powers "; 162 SQL1 += "and powers like @powers "; 163 SQL2 += "and powers like @powers "; 164 } 165 HS.Add("@powers", "%" + TextBox3.Text + "%"); 166 num++; 167 } 168 if (TextBox4.Text != "") 169 { 170 if (num == 0) 171 { 172 SQL += "where price " + DropDownList1.SelectedItem.Text + " @price "; 173 SQL1 += "where price " + DropDownList1.SelectedItem.Text + " @price "; 174 SQL2 += "where price " + DropDownList1.SelectedItem.Text + " @price "; 175 } 176 else 177 { 178 SQL += "and price " + DropDownList1.SelectedItem.Text + " @price "; 179 SQL1 += "and price " + DropDownList1.SelectedItem.Text + " @price "; 180 SQL2 += "and price " + DropDownList1.SelectedItem.Text + " @price "; 181 } 182 HS.Add("@price", TextBox4.Text); 183 num++; 184 } 185 Repeater1.DataSource = new Operation().selectall(SQL, HS); 186 Repeater1.DataBind(); 187 Label1.Text = "1"; 188 LinkButton1.Enabled = false; 189 int AAA = Number(SQL1); 190 if (Label1.Text == AAA.ToString()) 191 { 192 LinkButton2.Enabled = false; 193 } 194 else 195 { 196 LinkButton2.Enabled = true; 197 } 198 Session["SQL"] = SQL; 199 Session["SQL1"] = SQL1; 200 Session["SQL2"] = SQL2; 201 Session["HS"] = HS; 202 Session["NUM"] = num; 203 } 204 205 /// <summary> 206 /// 查询出来的总数 207 /// </summary> 208 /// <returns></returns> 209 public int Number(string sql) 210 { 211 int a = new Operation().selectall(sql, HS).Count; 212 int num = Convert.ToInt32(Math.Ceiling(a / (3 * 1.0))); 213 return num; 214 } 215 }
