2017-5-25 WebForm中分页组合查询合并使用
1.html页面代码：
<body>
<form id="form1" runat="server">
<br /> <br />
用户名：<asp:TextBox ID="Text_Uname" runat="server"></asp:TextBox>
成绩：<asp:DropDownList ID="Drop_Score" runat="server">
<asp:listitem text="全部成绩" Value="全部成绩"></asp:listitem>
<asp:listitem text="60分以下" Value="Score <60"></asp:listitem>
<asp:listitem Text="60分至70分" Value="Score >=60 and Score <70"></asp:listitem>
<asp:listitem Text="70分至80分" Value="Score >=70 and Score <80"></asp:listitem>
<asp:listitem Text="80分至90分" Value="Score >=80 and Score <90"></asp:listitem>
<asp:listitem Text="90分以上" Value="Score >=90"></asp:listitem>
</asp:DropDownList>
班级：<asp:DropDownList ID="Drop_Class" runat="server">
<asp:ListItem Text="语文" Value="C001"></asp:ListItem>
<asp:ListItem Text="数学" Value="C002"></asp:ListItem>
<asp:ListItem Text="计算机" Value="C003"></asp:ListItem>
<asp:ListItem Text="英语" Value="C004"></asp:ListItem>
</asp:DropDownList>
<asp:Button ID="But_Cha" runat="server" Text="Button" /><br />
<asp:Literal ID="Literal3" runat="server"></asp:Literal>
<br /> <br />
<table style="text-align: center; background-color: red; color: black; width: 100%">
<tr>
<td>Ids</td>
<td>用户名</td>
<td>密码</td>
<td>内容</td>
<td>分数</td>
<td>班级</td>
</tr>
<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
<tr style="background-color: white;">
<td><%#Eval("Ids") %></td>
<td><%#Eval("UserName") %></td>
<td><%#Eval("PassWord") %></td>
<td><%#Eval("Title") %></td>
<td><%#Eval("Score") %></td>
<td><%#Eval("Class") %></td>
</tr>
</ItemTemplate>
</asp:Repeater>
</table>
<div style="text-align:center;">
当前[<asp:Label ID="Label1" runat="server" Text="1"></asp:Label>]页，
共[<asp:Label ID="Label2" runat="server" Text="1"></asp:Label>]页
<asp:Button ID="But_First" runat="server" Text="首页" />
<asp:Button ID="But_Shang" runat="server" Text="上一页" />
<asp:Button ID="But_Next" runat="server" Text="下一页" />
<asp:Button ID="But_End" runat="server" Text="尾页" />
<asp:DropDownList ID="DropDownList1" runat="server"></asp:DropDownList>
<asp:Button ID="But_Drop" runat="server" Text="跳转" />
</div>
</form>
</body>
后台代码：
int PageCount = 5; protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { Repeater1.DataSource = new abData().selectIds(PageCount, 1); Repeater1.DataBind(); for (int i = 1; i <= SumPageCount(); i++) { ListItem li = new ListItem(i.ToString(), i.ToString()); DropDownList1.Items.Add(li); } } Label2.Text = SumPageCount().ToString(); //组合查询事件 But_Cha.Click += But_Cha_Click; //分页的点击事件 But_Drop.Click += But_Drop_Click; But_End.Click += But_End_Click; But_Next.Click += But_Next_Click; But_Shang.Click += But_Shang_Click; But_First.Click += But_First_Click; } //查询按钮事件，并且以分页的形式显示 void But_Cha_Click(object sender, EventArgs e) { Repeater1.DataSource = chaxun(1); Repeater1.DataBind(); Label1.Text = "1"; Label2.Text = aaa().ToString(); DropDownList1.Items.Clear(); for (int i = 1; i <= aaa(); i++) { ListItem li = new ListItem(i.ToString(), i.ToString()); DropDownList1.Items.Add(li); } } public List<ab> chaxun(int nextNumber ) { Hashtable hh = new Hashtable(); int count = 0; string sql = "select top " + PageCount + " * from ab "; string sql2 = ""; if (Text_Uname.Text.Trim().Length > 0) { sql2 += "where UserName like @a "; hh.Add("@a", "%" + Text_Uname.Text.Trim() + "%"); count++; } if (Drop_Score.SelectedValue != "null") { if (Drop_Score.SelectedValue == "全部成绩") { } else { if (count > 0) { sql2 += "and " + Drop_Score.SelectedValue + " "; } else { sql2 += "where " + Drop_Score.SelectedValue + " "; } count++; } } if (Drop_Class.SelectedValue != "null") { if (count > 0) { sql2 += "and Class='" + Drop_Class.SelectedValue + "' "; } else { sql2 += "where Class='" + Drop_Class.SelectedValue + "' "; } count++; } //获取当前页数 //最终查询的sql3语句 string sql3 = ""; if (count > 0) { sql3 = sql + sql2 + "and Ids not in (select top " + (nextNumber-1) * PageCount + " Ids from ab " + sql2 + ")"; } else { sql3 = sql + sql2; } Literal3.Text = sql3; List<ab> ablist = new abData().selectZHCX(sql3, hh); return ablist; } //页面跳转 void But_Drop_Click(object sender, EventArgs e) { int page = Convert.ToInt32(DropDownList1.SelectedValue); if (page == aaa()) { But_Next.Visible = false; But_Shang.Visible = true; } else if (page == 1) { But_Shang.Visible = false; But_Next.Visible = true; } else { But_Shang.Visible = true; But_Next.Visible = true; } List<ab> alist = chaxun(Convert.ToInt32(DropDownList1.SelectedValue)); Repeater1.DataSource = alist; Repeater1.DataBind(); Label1.Text = DropDownList1.SelectedValue; Label2.Text = aaa().ToString(); } //首页 void But_First_Click(object sender, EventArgs e) { But_Next.Visible = true; List<ab> alist = chaxun(1); Repeater1.DataSource = alist; Repeater1.DataBind(); Label1.Text = "1"; Label2.Text = aaa().ToString(); } //上一页 void But_Shang_Click(object sender, EventArgs e) { But_Next.Visible = true; //上一页 int page = Convert.ToInt32(Label1.Text) - 1; if (page == 1) { But_Shang.Visible = false; } if (page == 0) { page = 1; } Label2.Text = aaa().ToString(); List<ab> alist = chaxun(page); Repeater1.DataSource = alist; Repeater1.DataBind(); Label1.Text = page.ToString(); } //下一页 void But_Next_Click(object sender, EventArgs e) { But_Next.Visible = true; But_Shang.Visible = true; //下一页 int page = Convert.ToInt32(Label1.Text) +1; if (page >= aaa()) { But_Next.Visible = false; } Label2.Text = aaa().ToString(); List<ab> alist = chaxun(page); Repeater1.DataSource = alist; Repeater1.DataBind(); Label1.Text = page.ToString(); } //尾页 void But_End_Click(object sender, EventArgs e) { But_Next.Visible = false; List<ab> alist = chaxun(aaa()); Repeater1.DataSource = alist; Repeater1.DataBind(); Label1.Text = aaa().ToString(); Label2.Text = aaa().ToString(); } //计算一共要显示的页数 public int SumPageCount() { int sum = 1; List<ab> alist = new abData().selectAll(); decimal aa = Convert.ToDecimal(alist.Count) / PageCount; sum = Convert.ToInt32(Math.Ceiling(aa)); return sum; } //计算组合查询的需要显示的页数 public int aaa() { int end = 0; Hashtable hh = new Hashtable(); int count = 0; string sql = "select * from ab "; string sql2 = ""; if (Text_Uname.Text.Trim().Length > 0) { sql2 += "where UserName like @a "; hh.Add("@a", "%" + Text_Uname.Text.Trim() + "%"); count++; } if (Drop_Score.SelectedValue != "null") { if (Drop_Score.SelectedValue == "全部成绩") { } else { if (count > 0) { sql2 += "and " + Drop_Score.SelectedValue + " "; } else { sql2 += "where " + Drop_Score.SelectedValue + " "; } count++; } } if (Drop_Class.SelectedValue != "null") { if (count > 0) { sql2 += "and Class='" + Drop_Class.SelectedValue + "' "; } else { sql2 += "where Class='" + Drop_Class.SelectedValue + "' "; } count++; } //组合查询的全部数据 string sqlend = sql + sql2; List<ab> aalist = new abData().selectZHCX2(sqlend, hh); decimal aa = Convert.ToDecimal(aalist.Count) / PageCount; end = Convert.ToInt32(Math.Ceiling(aa)); return end; }
实体类
public class ab { public int Ids { get; set; } public string UserName { get; set; } public string PassWord { get; set; } public string Title { get; set; } public string Score { get; set; } public string Class { get; set; } }
数据操作类：
public class abData { SqlConnection conn = null; SqlCommand cmd = null; public abData() { conn = new SqlConnection("server=.;database=stu0314;user=sa;pwd=123"); cmd = conn.CreateCommand(); } //查询所有的信息 public List<ab> selectAll() { List<ab> alist = new List<ab>(); cmd.CommandText = "select * from ab"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { ab a = new ab(); a.Ids = Convert.ToInt32(dr[0]); a.UserName = dr[1].ToString(); a.PassWord = dr[2].ToString(); a.Title = dr[3].ToString(); a.Score = dr[4].ToString(); a.Class = dr[5].ToString(); alist.Add(a); } } conn.Close(); return alist; } //单纯分页查询前5行数据的信息 public List<ab> selectIds(int PageCount,int Page) { List<ab> alist = new List<ab>(); cmd.CommandText = "select top "+PageCount+" * from ab where Ids not in (select top "+PageCount*(Page-1)+" Ids from ab)"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { ab a = new ab(); a.Ids = Convert.ToInt32(dr[0]); a.UserName = dr[1].ToString(); a.PassWord = dr[2].ToString(); a.Title = dr[3].ToString(); a.Score = dr[4].ToString(); a.Class = dr[5].ToString(); alist.Add(a); } } conn.Close(); return alist; } //按条件查询 public List<ab> selectAll(string sql,Hashtable hh) { List<ab> alist = new List<ab>(); cmd.CommandText = sql; foreach(string s in hh.Keys) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue(s,hh[s]); } conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { ab a = new ab(); a.Ids = Convert.ToInt32(dr[0]); a.UserName = dr[1].ToString(); a.PassWord = dr[2].ToString(); a.Title = dr[3].ToString(); a.Score = dr[4].ToString(); a.Class = dr[5].ToString(); alist.Add(a); } } conn.Close(); return alist; } //组合查询前5行数据信息 public List<ab> selectZHCX(string sql,Hashtable hh1) { List<ab> alist = new List<ab>(); cmd.CommandText = sql; foreach(string s in hh1.Keys) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue(s,hh1[s]); } conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { ab a = new ab(); a.Ids = Convert.ToInt32(dr[0]); a.UserName = dr[1].ToString(); a.PassWord = dr[2].ToString(); a.Title = dr[3].ToString(); a.Score = dr[4].ToString(); a.Class = dr[5].ToString(); alist.Add(a); } } conn.Close(); return alist; } //根据组合查询总行数 public List<ab> selectZHCX2(string sql2, Hashtable hh1) { List<ab> alist = new List<ab>(); cmd.CommandText = sql2; foreach (string s in hh1.Keys) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue(s, hh1[s]); } conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { ab a = new ab(); a.Ids = Convert.ToInt32(dr[0]); a.UserName = dr[1].ToString(); a.PassWord = dr[2].ToString(); a.Title = dr[3].ToString(); a.Score = dr[4].ToString(); a.Class = dr[5].ToString(); alist.Add(a); } } conn.Close(); return alist; } }
