分页+组合查询

一、分页+组合查询

HTML代码：

<form id="form1" runat="server"> <div id="one"> <asp:Literal ID="Literal1" runat="server"></asp:Literal> <div id="top"> <div id="two"> <span>Hello,Welcome Back! </span> </div> <div id="three"> <input type="button" id="button1" runat="server" value="退出系统" /> </div> </div> <div id="left"> <div class="one"><span>基本信息</span></div> <div id="seven" class="two"><span>基本信息</span></div> <div class="one"><span>查询</span></div> <div id="eight" class="two"><span>查询</span></div> <div class="one"><span>其它</span></div> <div id="nine" class="two"><span>其它</span></div> </div> <div id="fill"> <div id="four"> <asp:Repeater ID="Repeater1" runat="server" OnItemCommand="Repeater1_ItemCommand"> <HeaderTemplate> <table class="table"> <tr class="tr"> <td style="width: 8%">用户名</td> <td style="width: 12%">籍贯</td> <td style="width: 8%">民族</td> <td style="width: 12%">生日</td> <td style="width: 5%">年龄</td> <td style="width: 5%">性别</td> <td style="width: 10%">手机号</td> <td style="width: 10%">QQ号</td> <td style="width: 10%">微信号</td> <td style="width: 10%">邮箱</td> <td style="width: 10%">操作</td> </tr> </HeaderTemplate> <AlternatingItemTemplate> <tr class="item"> <td><%#Eval("Name") %></td> <td><%#Eval("Area") %></td> <td><%#folk() %></td> <td><%#Eval("Birth","{0:yyyy年MM月dd日}") %></td> <td><%#Eval("Age") %></td> <td> <img src="<%#sex() %>" /></td> <td><%#Eval("Phone") %></td> <td><%#Eval("QQ") %></td> <td><%#Eval("WeChat") %></td> <td><%#Eval("Email") %></td> <td> <asp:LinkButton ID="LinkButton1" runat="server" CommandArgument='<%#Eval("Name")%>' CommandName="Delete">删除</asp:LinkButton><br /> <asp:LinkButton ID="LinkButton2" runat="server" CommandArgument='<%#Eval("Name")%>' CommandName="Update">修改</asp:LinkButton></td> </tr> </AlternatingItemTemplate> <ItemTemplate> <tr class="items"> <td><%#Eval("Name") %></td> <td><%#Eval("Area") %></td> <td><%#folk() %></td> <td><%#Eval("Birth","{0:yyyy年MM月dd日}") %></td> <td><%#Eval("Age") %></td> <td> <img src="<%#sex() %>" /></td> <td><%#Eval("Phone") %></td> <td><%#Eval("QQ") %></td> <td><%#Eval("WeChat") %></td> <td><%#Eval("Email") %></td> <td> <asp:LinkButton ID="LinkButton1" runat="server" CommandArgument='<%#Eval("Name")%>' CommandName="Delete">删除</asp:LinkButton><br /> <asp:LinkButton ID="LinkButton2" runat="server" CommandArgument='<%#Eval("Name")%>' CommandName="Update">修改</asp:LinkButton></td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> <div class="three"> <span>当前第：<asp:Label ID="Label1" runat="server" Text=" "></asp:Label> 页</span> <asp:Button ID="first" runat="server" Text="首页" /> <asp:Button ID="previous" runat="server" Text="上一页" /> <asp:Button ID="next" runat="server" Text="下一页" /> <asp:Button ID="last" runat="server" Text="末页" /> <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true"></asp:DropDownList> </div> </div> <div id="five"> <asp:Repeater ID="Repeater2" runat="server" OnItemCommand="Repeater1_ItemCommand"> <HeaderTemplate> <table class="table"> <tr class="tr"> <td style="width: 8%">用户名</td> <td style="width: 12%">籍贯</td> <td style="width: 8%">民族</td> <td style="width: 12%">生日</td> <td style="width: 5%">年龄</td> <td style="width: 5%">性别</td> <td style="width: 10%">手机号</td> <td style="width: 10%">QQ号</td> <td style="width: 10%">微信号</td> <td style="width: 10%">邮箱</td> </tr> </HeaderTemplate> <AlternatingItemTemplate> <tr class="item"> <td><%#Eval("Name") %></td> <td><%#Eval("Area") %></td> <td><%#folk() %></td> <td><%#Eval("Birth","{0:yyyy年MM月dd日}") %></td> <td><%#Eval("Age") %></td> <td> <img src="<%#sex() %>" /></td> <td><%#Eval("Phone") %></td> <td><%#Eval("QQ") %></td> <td><%#Eval("WeChat") %></td> <td><%#Eval("Email") %></td> </tr> </AlternatingItemTemplate> <ItemTemplate> <tr class="items"> <td><%#Eval("Name") %></td> <td><%#Eval("Area") %></td> <td><%#folk() %></td> <td><%#Eval("Birth","{0:yyyy年MM月dd日}") %></td> <td><%#Eval("Age") %></td> <td> <img src="<%#sex() %>" /></td> <td><%#Eval("Phone") %></td> <td><%#Eval("QQ") %></td> <td><%#Eval("WeChat") %></td> <td><%#Eval("Email") %></td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> <div class="three"> <span>当前第：<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label> 页</span> <asp:Button ID="first1" runat="server" Text="首页" /> <asp:Button ID="previous1" runat="server" Text="上一页" /> <asp:Button ID="next1" runat="server" Text="下一页" /> <asp:Button ID="last1" runat="server" Text="末页" /> <asp:DropDownList ID="DropDownList2" runat="server" AutoPostBack="true"></asp:DropDownList> </div> <div id="ten"> <span>&nbsp;&nbsp;&nbsp;&nbsp;性别：</span> <asp:RadioButtonList ID="RadioButtonList1" runat="server" RepeatDirection="Horizontal" RepeatLayout="Flow"> <asp:ListItem Value="true">男</asp:ListItem> <asp:ListItem Value="false">女</asp:ListItem> </asp:RadioButtonList> &nbsp;&nbsp;&nbsp;&nbsp; <span>年龄：</span> <asp:DropDownList ID="DropDownList3" runat="server"> <asp:ListItem Value=">">></asp:ListItem> <asp:ListItem Value="=">=</asp:ListItem> <asp:ListItem Value="<"><</asp:ListItem> </asp:DropDownList> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <asp:Button ID="Button2" runat="server" Text="查询" /> </div> </div> <div id="six"> </div> </div> </div> </form>

js

window.onload = function () { var one = document.getElementsByClassName('one'); for (var i = 0; i < one.length; i++) { one[i].onclick = function () { for (var j = 0; j < one.length; j++) { one[j].nextSibling.nextSibling.style.display = "none"; } if (this.nextSibling.nextSibling.style.display == 'none') { this.nextSibling.nextSibling.style.display = "block"; } else { this.nextSibling.nextSibling.style.display = "none"; } } } var two = document.getElementById('seven'); two.onclick = function () { var three = document.getElementById('four'); three.style.display = "block"; var four = document.getElementById('five'); four.style.display = "none"; var five = document.getElementById('six'); five.style.display = "none"; } var six = document.getElementById('eight'); six.onclick = function () { var seven = document.getElementById('four'); seven.style.display = "none"; var eight = document.getElementById('five'); eight.style.display = "block"; var nine = document.getElementById('six'); nine.style.display = "none"; } var ten = document.getElementById('nine'); ten.onclick = function () { var eleven = document.getElementById('four'); eleven.style.display = "none"; var twelve = document.getElementById('five'); twelve.style.display = "none"; var thirteen = document.getElementById('six'); thirteen.style.display = "block"; } }

css

* { margin: 0; } #one { position: relative; background-image: url(../Images/background2.png); background-repeat: round; opacity: 0.6; width: 100%; height: 579px; } #top { position: relative; width: 100%; height: 60px; background-color: #00BFFF; opacity: 0.2; text-align: center; } #left { position: relative; width: 20%; height: 519px; background-color: black; opacity: 0.6; float: left; } #fill { position: relative; width: 80%; height: 519px; background-color: white; background-repeat: round; opacity: 0.5; float: left; } #two { position: relative; margin-left: 44%; top: 10%; text-align: center; float: left; } #two span { font-family: 'Edwardian Script ITC'; font-size: 36px; color: red; } #three { position: relative; margin-left: 30%; top: 50%; float: left; } .one { position: relative; width: 100%; height: 24px; margin-top: 5px; text-align: center; background-color: navy; opacity: 0.8; } .one span { font-weight: bold; font-family: 楷体; font-size: 20px; color: white; padding: 15px; } .two { position: relative; width: 100%; height: 50px; background-color: white; text-align: center; display: none; } .two span { position: relative; font-weight: bold; font-family: 楷体; font-size: 20px; color: black; padding: 20px; } #four { position: relative; width: 100%; height: 100%; background-color: black; background-repeat: round; display: none; overflow:scroll; } #five { position: relative; width: 100%; height: 100%; background-color: red; background-repeat: round; display: none; overflow:scroll; } #six { position: relative; width: 100%; height: 100%; background-color: blue; background-repeat: round; display: none; overflow:scroll; } .table { background-color: white; position:relative; margin-left:10%; top:10%; width:80%; border:1px solid black; text-align:center; } .tr { font-family:楷体; font:bold; text-align:center; } .item { background-color:aqua; text-align:center; } .items { background-color:purple; text-align:center; } td { padding:5px; } .three { position:absolute; margin-left:62%; top:95%; width:400px; height:24px; background-color:white; } .three span { font-family:隶书; font:bold; } #ten { position:absolute; margin-left:20%; top:88%; width:512px; height:24px; background-color:white; } #ten span{ position:relative; font-family:华文琥珀; }

服务器代码

public partial class Index : System.Web.UI.Page { int count = 5; Hashtable cmd = new Hashtable(); protected void Page_Load(object sender, EventArgs e) { first.Click += first_Click; previous.Click += previous_Click; next.Click += next_Click; last.Click += last_Click; DropDownList1.SelectedIndexChanged += DropDownList1_SelectedIndexChanged; first1.Click += first1_Click; previous1.Click += previous1_Click; next1.Click += next1_Click; last1.Click += last1_Click; DropDownList2.SelectedIndexChanged += DropDownList2_SelectedIndexChanged; Button2.Click += Button2_Click; if (Session["ok"] != null) { bool okok = Convert.ToBoolean(Session["ok"]); if (okok) { Response.Write("<script>alert('删除成功');</script>"); } else { Response.Write("<script>alert('删除失败');</script>"); } Session["ok"] = null; } if (IsPostBack == false) { bind(count, 1);//绑定数据 Label1.Text = "1";//设置初始页码 first.Enabled = false;//首页按钮不可用 previous.Enabled = false;//上一页按钮不可用 for (int i = 1; i <= max(); i++) { ListItem num = new ListItem(i.ToString(), i.ToString()); DropDownList1.Items.Add(num);//循环添加页码 } Bind(cmd, 1); Label2.Text = "1"; first1.Enabled = false; previous1.Enabled = false; for (int i = 1; i <= max(); i++) { ListItem num = new ListItem(i.ToString(), i.ToString()); DropDownList2.Items.Add(num); } } } /// <summary> /// 绑定数据 /// </summary> /// <param name="Count">每页显示条数</param> /// <param name="Page">页码</param> public void bind(int Count,int Page) { Repeater1.DataSource = new index2().select(Count,Page); Repeater1.DataBind(); } /// <summary> /// 绑定数据 /// </summary> /// <param name="cmd">哈希表</param> /// <param name="page">页码</param> public void Bind(Hashtable cmd, int page) { Repeater2.DataSource = new index2().select(TSQL(page), cmd); Repeater2.DataBind(); } /// <summary> /// 查询按钮 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void Button2_Click(object sender, EventArgs e) { DropDownList2.Items.Clear();//清空页码下拉菜单 for (int i = 1; i <= max(); i++) { ListItem num = new ListItem(i.ToString(), i.ToString()); DropDownList2.Items.Add(num);//重新循环加入页码 } Bind(cmd, 1);//绑定数据 Label2.Text = "1";//首页页码为1 } /// <summary> /// 首页按钮 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void first_Click(object sender, EventArgs e) { bind(count, 1); Label1.Text = "1"; first.Enabled = false; previous.Enabled = false; next.Enabled = true; last.Enabled = true; DropDownList1.SelectedIndex = Convert.ToInt32(Label1.Text) - 1; } /// <summary> /// 上一页按钮 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void previous_Click(object sender, EventArgs e) { int page = Convert.ToInt32(Label1.Text) - 1; bind(count, page); Label1.Text = page.ToString(); if (Label1.Text == "1") { first.Enabled = false; previous.Enabled = false; } next.Enabled = true; last.Enabled = true; DropDownList1.SelectedIndex = Convert.ToInt32(Label1.Text) - 1; } /// <summary> /// 下一页按钮 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void next_Click(object sender, EventArgs e) { int page = Convert.ToInt32(Label1.Text) + 1; bind(count, page); Label1.Text = page.ToString(); first.Enabled = true; previous.Enabled = true; if (Label1.Text == max().ToString()) { next.Enabled = false; last.Enabled = false; } DropDownList1.SelectedIndex = Convert.ToInt32(Label1.Text) - 1; } /// <summary> /// 末页按钮 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void last_Click(object sender, EventArgs e) { bind(count, Max()); Label1.Text = Max().ToString(); first.Enabled = true; previous.Enabled = true; next.Enabled = false; last.Enabled = false; DropDownList1.SelectedIndex = Convert.ToInt32(Label1.Text) - 1; } /// <summary> /// 下拉页码菜单 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) { int page = Convert.ToInt32(DropDownList1.SelectedItem.Value); bind(count, page); Label1.Text = page.ToString(); if (Label1.Text == "1") { first.Enabled = false; previous.Enabled = false; next.Enabled = true; last.Enabled = true; } if (Label1.Text == Max().ToString()) { first.Enabled = true; previous.Enabled = true; next.Enabled = false; last.Enabled = false; } if (Label1.Text != "1" && Label1.Text != Max().ToString()) { first.Enabled = true; previous.Enabled = true; next.Enabled = true; last.Enabled = true; } } /// <summary> /// 首页按钮 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void first1_Click(object sender, EventArgs e) { Bind(cmd, 1); Label2.Text = "1"; first1.Enabled = false; previous1.Enabled = false; next1.Enabled = true; last1.Enabled = true; DropDownList2.SelectedIndex = Convert.ToInt32(Label2.Text) - 1; } /// <summary> /// 上一页按钮 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void previous1_Click(object sender, EventArgs e) { int page = Convert.ToInt32(Label2.Text) - 1; Bind(cmd, page); Label2.Text = page.ToString(); if (Label2.Text == "1") { first.Enabled = false; previous.Enabled = false; } next1.Enabled = true; last1.Enabled = true; DropDownList2.SelectedIndex = Convert.ToInt32(Label2.Text) - 1; } /// <summary> /// 下一页按钮 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void next1_Click(object sender, EventArgs e) { int page = Convert.ToInt32(Label2.Text) + 1; Bind(cmd, page); Label2.Text = page.ToString(); first1.Enabled = true; previous1.Enabled = true; if (Label2.Text == max().ToString()) { next1.Enabled = false; last1.Enabled = false; } DropDownList2.SelectedIndex = Convert.ToInt32(Label2.Text) - 1; } /// <summary> /// 末页按钮 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void last1_Click(object sender, EventArgs e) { Bind(cmd, max()); Label2.Text = max().ToString(); first1.Enabled = true; previous1.Enabled = true; next1.Enabled = false; last1.Enabled = false; DropDownList2.SelectedIndex = Convert.ToInt32(Label2.Text) - 1; } /// <summary> /// 下拉页码菜单 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> void DropDownList2_SelectedIndexChanged(object sender, EventArgs e) { int page = Convert.ToInt32(DropDownList2.SelectedItem.Value); Bind(cmd, page); Label2.Text = page.ToString(); if (Label2.Text == "1") { first1.Enabled = false; previous1.Enabled = false; next1.Enabled = true; last1.Enabled = true; } if (Label2.Text == max().ToString()) { first1.Enabled = true; previous1.Enabled = true; next1.Enabled = false; last1.Enabled = false; } if (Label2.Text != "1" && Label2.Text != max().ToString()) { first1.Enabled = true; previous1.Enabled = true; next1.Enabled = true; last1.Enabled = true; } } /// <summary> /// Repeater重复器的点击事件 /// </summary> /// <param name="source"></param> /// <param name="e"></param> protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e) { if (e.CommandName == "Delete") { bool isok = new update2().Delete(e.CommandArgument.ToString()); if (isok) { Literal1.Text = "<script>alert('删除成功');</script>"; bind(count, 1); } } else if (e.CommandName == "Update") { Response.Redirect("Update.aspx?aaa=" + e.CommandArgument.ToString()); } } public string sex() { string address = null; if (Convert.ToBoolean(Eval("Sex"))) { address = "Images/men.png"; } else { address = "Images/women.png"; } return address; } public string folk() { string nation = new index2().folk(Eval("Folk").ToString()); return nation; } /// <summary> /// 最大页码 /// </summary> /// <returns></returns> public int Max() { List<index1> list = new index2().select(); int num = (list.Count / count) + 1; return num; } /// <summary> /// 最大页码 /// </summary> /// <returns></returns> public int max() { List<index1> list = new index2().select(TSql(), cmd); int num = (list.Count / count) + 1; return num; } /// <summary> /// SQL语句拼接 /// </summary> /// <param name="Page"></param> /// <returns></returns> public string TSQL(int Page) { cmd.Clear(); int Count = 0;//记录一下查询条数 string sql = "select top " + count + " * from register"; string one = ""; string two = ""; //1、将条件查询的语句拼完 if (RadioButtonList1.Items[0].Selected || RadioButtonList1.Items[1].Selected) { sql += " where sex = @sex"; one = " where sex = @sex"; cmd.Add("@sex", RadioButtonList1.SelectedItem.Value); Count++; } if (TextBox1.Text != "") { if (Count > 0) { sql += " and age " + DropDownList3.SelectedItem.Value + " @age"; two = " and age " + DropDownList3.SelectedItem.Value + " @age"; } else { sql += " where age " + DropDownList3.SelectedItem.Value + " @age"; two = " where age " + DropDownList3.SelectedItem.Value + " @age"; } cmd.Add("@age", TextBox1.Text); Count++; } //2、将分页的语句拼完 if (Count > 0) { sql += " and ID not in ( select top " + (count * (Page - 1)) + " ID from register " + one + two + " )"; } else { sql += " where ID not in ( select top " + (count * (Page - 1)) + " ID from register " + one + two + ")"; } return sql; } private string TSql() { cmd.Clear(); int Count = 0;//记录一下查询条数 string sql = "select * from register"; //1、将条件查询的语句拼完 if (RadioButtonList1.Items[0].Selected || RadioButtonList1.Items[1].Selected) { sql += " where sex = @sex"; cmd.Add("@sex", RadioButtonList1.SelectedItem.Value); Count++; } if (TextBox1.Text != "") { if (Count > 0) { sql += " and age " + DropDownList3.SelectedItem.Value + " @age"; } else { sql += " where age " + DropDownList3.SelectedItem.Value + " @age"; } cmd.Add("@age", TextBox1.Text); Count++; } return sql; } }

封装的方法

public class index2 { SqlConnection data = null; SqlCommand cmd = null; public index2() { data = new SqlConnection("server=.;database=users;user=sa;pwd=123456;"); cmd = data.CreateCommand(); } public List<index1> select() { List<index1> list = new List<index1>(); cmd.CommandText = "select*from register"; data.Open(); SqlDataReader read = cmd.ExecuteReader(); if (read.HasRows) { while (read.Read()) { index1 context = new index1(); context.Name = read["name"].ToString(); context.Area = read["area"].ToString(); context.Folk = read["folk"].ToString(); context.Birth = Convert.ToDateTime(read["birth"]); context.Age = Convert.ToInt32(read["age"]); context.Sex = Convert.ToBoolean(read["sex"]); context.Phone = read["phone"].ToString(); context.QQ = read["QQ"].ToString(); context.WeChat = read["WeChat"].ToString(); context.Email = read["Email"].ToString(); list.Add(context); } } data.Close(); return list; } public string folk(string num) { string folk = null; index1 one = new index1(); cmd.CommandText = "select*from folk where ID=@id"; cmd.Parameters.Clear(); cmd.Parameters.Add("@id", num); data.Open(); SqlDataReader read = cmd.ExecuteReader(); if (read.HasRows) { read.Read(); one.Folk = read["name"].ToString(); } data.Close(); folk = one.Folk; return folk; } public List<index1> select(int count, int page) { List<index1> list = new List<index1>(); cmd.CommandText = "select top "+count+" *from register where id not in(select top "+(count*(page-1))+" id from register)"; data.Open(); SqlDataReader read = cmd.ExecuteReader(); if (read.HasRows) { while (read.Read()) { index1 context = new index1(); context.Name = read["name"].ToString(); context.Area = read["area"].ToString(); context.Folk = read["folk"].ToString(); context.Birth = Convert.ToDateTime(read["birth"]); context.Age = Convert.ToInt32(read["age"]); context.Sex = Convert.ToBoolean(read["sex"]); context.Phone = read["phone"].ToString(); context.QQ = read["QQ"].ToString(); context.WeChat = read["WeChat"].ToString(); context.Email = read["Email"].ToString(); list.Add(context); } } data.Close(); return list; } public List<index1> select(string sql, Hashtable Cmd) { List<index1> list = new List<index1>(); cmd.CommandText = sql; cmd.Parameters.Clear(); foreach (string command in Cmd.Keys) { cmd.Parameters.Add(command,Cmd[command]); } data.Open(); SqlDataReader read = cmd.ExecuteReader(); if (read.HasRows) { while (read.Read()) { index1 context = new index1(); context.Name = read["name"].ToString(); context.Area = read["area"].ToString(); context.Folk = read["folk"].ToString(); context.Birth = Convert.ToDateTime(read["birth"]); context.Age = Convert.ToInt32(read["age"]); context.Sex = Convert.ToBoolean(read["sex"]); context.Phone = read["phone"].ToString(); context.QQ = read["QQ"].ToString(); context.WeChat = read["WeChat"].ToString(); context.Email = read["Email"].ToString(); list.Add(context); } } data.Close(); return list; } }

结果

 

转载于:https://www.cnblogs.com/bosamvs/p/5712900.html

相关资源：Spring Data JPA 复杂/多条件组合分页查询