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             名称：<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
14         油耗：<asp:DropDownList ID="DropDownList3" runat="server" EnableViewState="True">
15             <asp:ListItem Value="=">等于</asp:ListItem>
16             <asp:ListItem Value="&gt;=">大于等于</asp:ListItem>
17             <asp:ListItem Value="&lt;=">小于等于</asp:ListItem>
18         </asp:DropDownList><asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
19         价格：<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>-<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
20         <asp:Button ID="Button2" runat="server" Text="提交" /><br /><br />
21             <asp:Repeater ID="Repeater1" runat="server">
22                 <HeaderTemplate>
23                     <table style="background-color: blue;  100%; text-align:center">
24                         <thead>
25                             <tr style="color: white;">
26                                 <td>编号</td>
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                             </tr>
36                         </thead>
37                         <tbody>
38                 </HeaderTemplate>
39                 <ItemTemplate>
40                     <tr style="background-color: #808080">
41                         <td><%#Eval("Code") %></td>
42                         <td><%#Eval("Name") %></td>
43                         <td><%#Eval("Brand") %></td>
44                         <td><%#Eval("Time") %></td>
45                         <td><%#Eval("Oil") %></td>
46                         <td><%#Eval("Power") %></td>
47                         <td><%#Eval("Exhaust") %></td>
48                         <td><%#Eval("Price") %></td>
49                         <td><%#Eval("Pic") %></td>
50                     </tr>
51                 </ItemTemplate>
52                 <FooterTemplate>
53                     </tbody>
54             </table>
55                 </FooterTemplate>
56             </asp:Repeater>
57             <div style=" 50%; position: relative; float: left; height: 25px; line-height: 25px; text-indent: 20px;">
58                 共
59                 <asp:Label ID="Label_Sum" runat="server" Text="Label"></asp:Label>
60                 条记录，共
61                 <asp:Label ID="Label_Maxpage" runat="server" Text="Label"></asp:Label>
62                 页，第
63                 <asp:Label ID="Label_Nowpage" runat="server" Text="Label"></asp:Label>
64                 页|每页
65                 <asp:DropDownList ID="DropDownList2" runat="server" AutoPostBack="true" ></asp:DropDownList>
66                 条记录
67             </div>
68             <div style=" 30%; position: relative; float: right; height: 25px; line-height: 25px;">
69                 <asp:LinkButton ID="btn_First" runat="server">首页</asp:LinkButton>&nbsp
70                 <asp:LinkButton ID="btn_Prev" runat="server">上一页</asp:LinkButton>&nbsp
71                 <asp:LinkButton ID="btn_Next" runat="server">下一页</asp:LinkButton>&nbsp
72                 <asp:LinkButton ID="btn_Last" runat="server">尾页</asp:LinkButton>&nbsp
73                 <asp:DropDownList ID="DropDownList1" runat="server"></asp:DropDownList>
74                 <asp:Button ID="Button1" runat="server" Text="跳转" />
75             </div>
76         </div>
77     </form>
78 </body>
79 </html>

界面：Default.aspx

后台：

 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     public List<Car> Select(string sql, Hashtable hat )
 80     {
 81         List<Car> clist = new List<Car>();
 82         cmd.CommandText = sql;
 83         cmd.Parameters.Clear();
 84 
 85         foreach (string s in hat.Keys)
 86         {
 87             cmd.Parameters.AddWithValue(s, hat[s]);
 88         }
 89 
 90         conn.Open();
 91         SqlDataReader dr = cmd.ExecuteReader();
 92         if (dr.HasRows)
 93         {
 94             while (dr.Read())
 95             {
 96                 Car c = new Car();
 97                 c.Code = dr[0].ToString();
 98                 c.Name = dr[1].ToString();
 99                 c.Brand = dr[2].ToString();
100                 c.Time = Convert.ToDateTime(dr[3]);
101                 c.Oil = Convert.ToDecimal(dr[4]);
102                 c.Power = Convert.ToInt32(dr[5]);
103                 c.Exhaust = Convert.ToInt32(dr[6]);
104                 c.Price = Convert.ToDecimal(dr[7]);
105                 c.Pic = dr[8].ToString();
106 
107                 clist.Add(c);
108             }
109         }
110         conn.Close();
111         return clist;
112     }
113 }

数据访问类

  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     Hashtable has = new Hashtable();
 12     protected void Page_Load(object sender, EventArgs e)
 13     {
 14         if (!IsPostBack)
 15         {
 16             DropDownList2.Items.Clear();
 17             string  tsql2 = "select *from Car";
 18             for (int i = 1; i <= Sum(tsql2,has); i++)
 19             {
 20                 DropDownList2.Items.Add(new ListItem(i.ToString(), i.ToString()));
 21             }
 22             
 23             DropDownList2.SelectedValue = "5";//默认每页五条
 24 
 25             Change();
 26             int count = Convert.ToInt32(DropDownList2.SelectedValue);//每页的数据条数
 27             //Repeater1.DataSource = new CarData().Select(count, 1);
 28             //Repeater1.DataBind();
 29         }
 30 
 31         btn_First.Click += btn_First_Click;//首页
 32         btn_Prev.Click += btn_Prev_Click;//上一页
 33         btn_Next.Click += btn_Next_Click;//下一页
 34         btn_Last.Click += btn_Last_Click;//尾页
 35         Button1.Click += Button1_Click;//跳转按钮
 36         DropDownList2.SelectedIndexChanged += DropDownList2_SelectedIndexChanged;//每页数据条数发生改变
 37         Button2.Click += Button2_Click;
 38     }
 39     //每页数据条数发生改变时执行
 40     private void Change()
 41     {
 42         string tsql;//拼接查询前count条数据的语句
 43         string tsql2;//查询所有的语句
 44         string tj;//用于分页查询与sql等拼接
 45         Tsql(out tsql, out tsql2, out tj);
 46         //int count = Convert.ToInt32(DropDownList2.SelectedValue);//每页的数据条数
 47         Label_Sum.Text = Sum(tsql2, has).ToString();//总数据条数
 48         Label_Maxpage.Text = MaxPage(tsql2, has).ToString();//总页数
 49         Label_Nowpage.Text = "1";//当前页
 50         Repeater1.DataSource = new CarData().Select(tsql, has);
 51         Repeater1.DataBind();
 52 
 53         DropDownList1.Items.Clear();
 54         for (int i = 1; i <= MaxPage(tsql2, has); i++)
 55         {
 56             DropDownList1.Items.Add(new ListItem(i.ToString(), i.ToString()));
 57         }
 58         btn_First.Enabled = false;
 59         btn_Prev.Enabled = false;
 60         btn_Next.Enabled = true;
 61         btn_Last.Enabled = true;
 62     }
 63     /// <summary>
 64     /// 返回所有条件的Tsql语句
 65     /// </summary>
 66     /// <param name="tsql">拼接查询前count条数据的语句</param>
 67     /// <param name="tsql2">查询所有的语句</param>
 68     /// <param name="tj">用于分页查询与sql等拼接</param>
 69     private void Tsql(out string tsql, out string tsql2, out string tj)
 70     {
 71         int count = Convert.ToInt32(DropDownList2.SelectedValue);//每页的数据条数
 72         tsql = "select top " + count + " *from Car";
 73         tsql2 = "select *from Car";
 74         tj = "";
 75         //判断文本框中是否有内容需要查询
 76         if (TextBox1.Text.Trim().Length > 0)
 77         {//如果有内容，那么就拼接到Tsql语句中去
 78             tsql += " where name like @name";
 79             tsql2 += " where name like @name";
 80             tj += " where name like @name";
 81             has.Add("@name", "%" + TextBox1.Text.Trim().ToUpper() + "%");
 82         }
 83         else
 84         {
 85             tsql += " where 1=1";
 86             tsql2 += " where 1=1";
 87             tj += " where 1=1";
 88         }
 89         if (TextBox2.Text.Trim().Length > 0)
 90         {
 91             tsql += " and oil " + DropDownList3.SelectedValue + "@oil";
 92             tsql2 += " and oil " + DropDownList3.SelectedValue + "@oil";
 93             tj += " and oil " + DropDownList3.SelectedValue + "@oil";
 94             has.Add("@oil", TextBox2.Text.Trim());
 95         }
 96         else
 97         {
 98             tsql += " and 1=1";
 99             tsql2 += " and 1=1";
100             tj += " and 1=1";
101         }
102         if (TextBox3.Text.Trim().Length > 0)
103         {
104             tsql += " and price>=@price1";
105             tsql2 += " and price>=@price1";
106             tj += " and price>=@price1";
107             has.Add("@price1", TextBox3.Text.Trim());
108         }
109         else
110         {
111             tsql += " and 1=1";
112             tsql2 += " and 1=1";
113             tj += " and 1=1";
114         }
115         if (TextBox4.Text.Trim().Length > 0)
116         {
117             tsql += " and price<=@price2";
118             tsql2 += " and price<=@price2";
119             tj += " and price<=@price2";
120             has.Add("@price2", TextBox4.Text.Trim());
121         }
122     }
123 
124     //组合查询按钮
125     void Button2_Click(object sender, EventArgs e)
126     {
127 
128         string tsql;//拼接查询前count条数据的语句
129         string tsql2;//查询所有的语句
130         string tj;
131         Tsql(out tsql, out tsql2, out tj);
132         Repeater1.DataSource = new CarData().Select(tsql, has);//数据指向
133         Repeater1.DataBind();
134         Label_Sum.Text = Sum(tsql2, has).ToString();//总数据条数
135         Label_Maxpage.Text = MaxPage(tsql2, has).ToString();//总页数
136         Label_Nowpage.Text = "1";//当前页
137 
138         DropDownList1.Items.Clear();
139         for (int i = 1; i <= MaxPage(tsql2, has); i++)
140         {
141             DropDownList1.Items.Add(new ListItem(i.ToString(), i.ToString()));
142         }
143         btn_First.Enabled = false;
144         btn_Prev.Enabled = false;
145         btn_Next.Enabled = true;
146         btn_Last.Enabled = true;
147 
148         if (Sum(tsql2, has) <= Convert.ToInt32(DropDownList2.SelectedValue))
149         {
150             btn_Next.Enabled = false;
151             btn_Last.Enabled = false;
152         }
153         if (Sum(tsql2, has)==0)
154         {
155             Label_Nowpage.Text = "0";
156         }
157 
158         string mi = DropDownList2.SelectedValue;
159         DropDownList2.Items.Clear();
160         for (int i = 1; i <= Sum(tsql2, has); i++)
161         {
162             DropDownList2.Items.Add(new ListItem(i.ToString(), i.ToString()));
163         }
164         if (Sum(tsql, has) <= Convert.ToUInt32(mi))
165         {
166             DropDownList2.SelectedValue = Sum(tsql, has).ToString();//默认每页条数
167         }
168     }
169 
170    
171 
172     //每页数据条数发生改变
173     void DropDownList2_SelectedIndexChanged(object sender, EventArgs e)
174     {
175         Change();
176         if (DropDownList2.SelectedValue == "18")
177         {
178             btn_Next.Enabled = false;
179             btn_Last.Enabled = false;
180         }
181     }
182 
183 
184     //跳转按钮
185     void Button1_Click(object sender, EventArgs e)
186     {
187         string tsql;//拼接查询前count条数据的语句
188         string tsql2;//查询所有的语句
189         string tj;
190         Tsql(out tsql, out tsql2, out tj);
191         int count = Convert.ToInt32(DropDownList2.SelectedValue);//每页的数据条数
192 
193         tsql += "  and Code not in(select top " + (count * (Convert.ToInt32(DropDownList1.SelectedValue) - 1)) + " Code from Car " + tj + ")";
194         //按照页数获取绑定数据
195         Repeater1.DataSource = new CarData().Select(tsql,has);
196         Repeater1.DataBind();
197         //修改显示页数
198         Label_Nowpage.Text = DropDownList1.SelectedValue;
199         if (Convert.ToInt32(Label_Nowpage.Text) <= 1)//若为首页禁用上一页和首页按钮
200         {
201             btn_First.Enabled = false;
202             btn_Prev.Enabled = false;
203             btn_Next.Enabled = true;
204             btn_Last.Enabled = true;
205         }
206         if (Convert.ToInt32(Label_Nowpage.Text) >= MaxPage(tsql2, has))//若为最后一页禁用下一页和尾页按钮
207         {
208             btn_Next.Enabled = false;
209             btn_Last.Enabled = false;
210             btn_First.Enabled = true;
211             btn_Prev.Enabled = true;
212         }
213     }
214 
215     //首页
216     void btn_First_Click(object sender, EventArgs e)
217     {
218         string tsql;//拼接查询前count条数据的语句
219         string tsql2;//查询所有的语句
220         string tj;//用于分页查询与sql等拼接
221         Tsql(out tsql, out tsql2, out tj);
222         int count = Convert.ToInt32(DropDownList2.SelectedValue);//每页的数据条数
223 
224         tsql += "  and Code not in(select top 0 Code from Car " + tj + ")";
225         //按照页数获取绑定数据
226         Repeater1.DataSource = new CarData().Select(tsql,has);
227         Repeater1.DataBind();
228         //修改显示页数
229         Label_Nowpage.Text = "1";
230         //当前为首页禁用上一页和首页按钮
231         btn_First.Enabled = false;
232         btn_Prev.Enabled = false;
233 
234         btn_Next.Enabled = true;
235         btn_Last.Enabled = true;
236     }
237 
238     //上一页
239     void btn_Prev_Click(object sender, EventArgs e)
240     {
241         string tsql;//拼接查询前count条数据的语句
242         string tsql2;//查询所有的语句
243         string tj;//用于分页查询与sql等拼接
244         Tsql(out tsql, out tsql2, out tj);
245         int count = Convert.ToInt32(DropDownList2.SelectedValue);//每页的数据条数
246         //获取当前页，计算上一页页数
247         int nextpage = Convert.ToInt32(Label_Nowpage.Text) - 1;
248 
249         tsql += "  and Code not in(select top " + (count * (nextpage - 1)) + " Code from Car " + tj + ")";
250         //按照页数获取绑定数据
251         Repeater1.DataSource = new CarData().Select(tsql, has);
252         Repeater1.DataBind();
253         //修改显示页数
254         Label_Nowpage.Text = nextpage.ToString();
255         if (nextpage <= 1)//若为首页禁用上一页和首页按钮
256         {
257             btn_First.Enabled = false;
258             btn_Prev.Enabled = false;
259         }
260         btn_Next.Enabled = true;
261         btn_Last.Enabled = true;
262     }
263     //下一页
264     void btn_Next_Click(object sender, EventArgs e)
265     {
266         string tsql;//拼接查询前count条数据的语句
267         string tsql2;//查询所有的语句
268         string tj;//用于分页查询与sql等拼接
269         Tsql(out tsql, out tsql2, out tj);
270         int count = Convert.ToInt32(DropDownList2.SelectedValue);//每页的数据条数
271         //获取当前页，计算下一页页数
272         int nextpage = Convert.ToInt32(Label_Nowpage.Text) + 1;
273 
274         tsql += "  and Code not in(select top " + (count * (nextpage - 1)) + " Code from Car " + tj + ")";
275         //按照页数获取绑定数据
276         Repeater1.DataSource = new CarData().Select(tsql, has);
277         Repeater1.DataBind();
278         //修改显示页数
279         Label_Nowpage.Text = nextpage.ToString();
280         if (nextpage >= MaxPage(tsql2, has))//若为最后一页禁用下一页和尾页按钮
281         {
282             btn_Next.Enabled = false;
283             btn_Last.Enabled = false;
284         }
285         btn_First.Enabled = true;
286         btn_Prev.Enabled = true;
287     }
288 
289 
290     //尾页
291     void btn_Last_Click(object sender, EventArgs e)
292     {
293         string tsql;//拼接查询前count条数据的语句
294         string tsql2;//查询所有的语句
295         string tj;//用于分页查询与sql等拼接
296         Tsql(out tsql, out tsql2, out tj);
297         int count = Convert.ToInt32(DropDownList2.SelectedValue);//每页的数据条数
298 
299         tsql += "  and Code not in(select top " + (count * (MaxPage(tsql2,has) - 1)) + " Code from Car " + tj + ")";
300         //按照页数获取绑定数据
301         Repeater1.DataSource = new CarData().Select(tsql,has);
302         Repeater1.DataBind();
303         //修改显示页数
304         Label_Nowpage.Text = MaxPage(tsql2, has).ToString();
305         //若为最后一页禁用下一页和尾页按钮
306         btn_Next.Enabled = false;
307         btn_Last.Enabled = false;
308 
309         btn_First.Enabled = true;
310         btn_Prev.Enabled = true;
311     }
312 
313     //总的数据条数
314     public int Sum(string tsql,Hashtable has)
315     {
316         List<Car> clist = new CarData().Select(tsql,has);
317         int a = clist.Count;
318         return a;
319     }
320 
321     //总页数
322     public int MaxPage(string tsql, Hashtable has)
323     {
324         int count = Convert.ToInt32(DropDownList2.SelectedValue);//每页的数据条数
325         List<Car> clist = new CarData().Select(tsql, has);
326         int a = clist.Count;
327         int end = Convert.ToInt32(Math.Ceiling(a / (count * 1.0)));
328         return end;
329     }
330 }

Default.aspx.cs