ADO.NET组合查询及其分页合并
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title></title> <style type="text/css"> #tab1 { width: 100%; text-align: center; background-color: navy; border-spacing: 1px; /*边框之间的空隙变成1像素*/ } #tab1 thead tr { color: white; } #tab1 tbody tr { background-color: white; } #tab1 td { padding: 7px 5px; } #tab1 tbody tr:hover { background-color: #f00; /*//鼠标移入时候的背景颜色*/ } #Button1 { margin-right:0px; margin-top:0px; } #TextBox1 { width:50px; } </style> </head> <body> <form id="form1" runat="server"> <div> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> <asp:Button ID="Button1" runat="server" Text="退出" Height="21px" style="margin-left: 1141px" Width="73px" /> <br /> <br /> 昵称：<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> 性别：<asp:DropDownList ID="DropDownList1" runat="server"> <asp:ListItem Value="true">全部</asp:ListItem> <asp:ListItem value="1">男</asp:ListItem> <asp:ListItem Value="0">女</asp:ListItem> </asp:DropDownList> 生日：<asp:DropDownList ID="DropDownList2" runat="server"> <asp:ListItem Value=">=">大于等于</asp:ListItem> <asp:ListItem Value="<=">小于等于</asp:ListItem> </asp:DropDownList> <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox> <asp:Button ID="Button3" runat="server" Text="查询" /> <asp:Label ID="ceshi" runat="server" Text="Label"></asp:Label> <br /> <br /> <table id="tab1"> <thead> <tr> <td>ID</td> <td>编号</td> <td>用户名</td> <td>密码</td> <td>昵称</td> <td>性别</td> <td>生日</td> <td>民族</td> <td>操作</td> </tr> </thead> <tbody> <asp:Repeater ID="Repeater1" runat="server"> <%-- 在内容里加Repeater和TtemTemplate--%> <ItemTemplate> <tr> <td><%#Eval("ID") %></td> <td><%#Eval("ucode") %></td> <td><%#Eval("username") %></td> <td><%#Eval("password") %></td> <td><%#Eval("nickname") %></td> <td><%#Eval("sexstr") %> <%-- <img src="<%#Eval("sexImg") %>" />--%> <%--使用图片当性别区分--%> </td> <td><%#Eval("birthday","{0:yyyy年MM月dd日}") %></td> <%-- 日期的转换--%> <td><%#Eval("NationName") %></td> <td> <a href="update2.aspx?ID=<%#Eval("ID") %>">修改</a> <%--添加两个操作,里面的两个ID不要写错了--%> <a href="delete2.aspx?ID=<%#Eval("ID") %>" onclick="return confirm('确定要删除吗？');">删除</a> </td> </tr> </ItemTemplate> </asp:Repeater> </tbody> </table> <a href="insertu.aspx ">添加</a> <%--<a href="updateu.aspx ">修改</a>--%> </div> <div style="width: 100%; text-align: center; line-height: 30px;"> 当前第[ <asp:Label ID="lab_NowPageNumber" runat="server" Text="1"></asp:Label> ]页 共[ <asp:Label ID="lab_MaxPageNumber" runat="server" Text="1"></asp:Label> ]页<br /> <asp:Button ID="btn_First" runat="server" Text="首页" /> <asp:Button ID="btn_Prev" runat="server" Text="上一页" /> <asp:Button ID="btn_Next" runat="server" Text="下一页" /> <asp:Button ID="btn_Last" runat="server" Text="尾页" /><br /> <%-- <asp:DropDownList ID="dr_drop" AutoPostBack="true" runat="server"></asp:DropDownList>--%> <asp:TextBox ID="TextBox1" runat="server" ></asp:TextBox> <asp:Button ID="Button2" runat="server" Text="跳转" /> </div> </form> </body> </html> <%--表单： 文本： <input type ="text" name="" id="" value=""/> 文本框 <input type="password" name="" id="" value="" /> 密码框 <input type="hidden" name="" id="" value =""/> 隐藏域 <textarea name="" id="" cols=""(字符多少) rows=""(几行高)></textarea> 一般都是写评论的那种 按钮： <input type="submit" name="" id="" value=""/> 提交按钮 <input type="reset" name="" id="" value=""/> 重置按钮 <input type="button" name="" id="" value=""/> 普通按钮 <input type="image" name="" id="" value="" src="图片地址"/> 选择： <input type="radio" name=""value=""/> 单选按钮 <input type="checkbox" name="" value=""/> 复选框组 <input type="file" name="" id=""/> 文件上传 <select name="" id="" size="" multiple="multiple"(多选)> <option value="值" selected="selected"(设为默认)>内容1</option> <option value="值">内容2</option> <option value="值">内容3</option> <option value="值">内容4</option> </select>--%>
后台代码：
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; public partial class _Default : System.Web.UI.Page { int pagecount = 5; protected void Page_Load(object sender, EventArgs e) { Button1.Click += Button1_Click; //退出登录 Button2.Click += Button2_Click; //分页跳转 Button3.Click += Button3_Click; //组合查询按钮 btn_Last.Click += btn_Last_Click; //尾页 btn_Next.Click += btn_Next_Click; //下一页 btn_Prev.Click += btn_Prev_Click; //上一页 btn_First.Click += btn_First_Click; //首页 TextBox1.TextChanged += TextBox1_TextChanged; if (Session["user"] != null) { Label1.Text = (Session["user"] as Users).nickname + "，欢迎您！";//接受传过来的值 Session["hehe"] as Users变成对象,可以取里面的内容。 } if (!IsPostBack) { Repeater1.DataSource = new UsersData().Selectsql("select top " + pagecount + " * from Users", new Hashtable()); Repeater1.DataBind(); lab_MaxPageNumber.Text = getMaxNumber().ToString(); btn_First.Enabled = false; btn_Prev.Enabled = false; Button2.Enabled = false; } } void TextBox1_TextChanged(object sender, EventArgs e) { Button2.Enabled = true; } void btn_First_Click(object sender, EventArgs e) //首页 { int skip = 1; string tsql2 = @"select top " + pagecount + " * from Users"; string tsql3 = ""; int cou = 0; lab_NowPageNumber.Text = skip.ToString(); if (TextBox2.Text.Trim().Length > 0) { tsql3 += " where nickname like '%" + TextBox2.Text.Trim() + "%'"; cou++; } if (DropDownList1.SelectedIndex != 0) { if (cou > 0) tsql3 += " and"; else tsql3 += " where"; tsql3 += " sex= " + DropDownList1.SelectedValue + ""; cou++; } if (TextBox3.Text.Trim().Length > 0) { if (cou > 0) tsql3 += " and"; else tsql3 += " where"; tsql3 += " birthday " + DropDownList2.SelectedValue + " " + TextBox3.Text.Trim(); cou++; } string fy = "ID not in (select top " + ((skip - 1) * pagecount) + " ID from Users " + tsql3 + ")"; string endsql = tsql2 + tsql3 + ((cou > 0) ? " and " : " where ") + fy; lab_NowPageNumber.Text = skip.ToString(); Repeater1.DataSource = new UsersData().Selectsql(endsql, new Hashtable()); Repeater1.DataBind(); btn_First.Enabled = false; btn_Prev.Enabled = false; btn_Next.Enabled = true; btn_Last.Enabled = true; } void btn_Prev_Click(object sender, EventArgs e) //上一页 { btn_Next.Enabled = true; btn_Last.Enabled = true; //上一页 int skip = Convert.ToInt32(lab_NowPageNumber.Text) - 1; string tsql2 = @"select top " + pagecount + " * from Users"; string tsql3 = ""; int cou = 0; if (TextBox2.Text.Trim().Length > 0) { tsql3 += " where nickname like '%" + TextBox2.Text.Trim() + "%'"; cou++; } if (DropDownList1.SelectedIndex != 0) { if (cou > 0) tsql3 += " and"; else tsql3 += " where"; tsql3 += " sex= " + DropDownList1.SelectedValue + ""; cou++; } if (TextBox3.Text.Trim().Length > 0) { if (cou > 0) tsql3 += " and"; else tsql3 += " where"; tsql3 += " birthday " + DropDownList2.SelectedValue + " " + TextBox3.Text.Trim(); cou++; } string fy = "ID not in (select top " + ((skip - 1) * pagecount) + " ID from Users " + tsql3 + ")"; string endsql = tsql2 + tsql3 + ((cou > 0) ? " and " : " where ") + fy; ceshi.Text = endsql; lab_NowPageNumber.Text = skip.ToString(); Repeater1.DataSource = new UsersData().Selectsql(endsql, new Hashtable()); Repeater1.DataBind(); string ss = "select * from Users"; string aa = Convert.ToInt32(Math.Ceiling(((decimal)new UsersData().Selectsql(ss, new Hashtable()).Count) / pagecount)).ToString(); //总页数 if (lab_NowPageNumber.Text == "1") { btn_Prev.Enabled = false; btn_First.Enabled = false; } } void btn_Next_Click(object sender, EventArgs e) //下一页 { //下一页 btn_First.Enabled = true; btn_Prev.Enabled = true; int skip = Convert.ToInt32(lab_NowPageNumber.Text) + 1; //这里是为了给下面提供应该查询前几页的数据提供的。 string tsql2 = @"select top " + pagecount + " * from Users"; string tsql3 = ""; int cou = 0; lab_NowPageNumber.Text = skip.ToString(); if (TextBox2.Text.Trim().Length > 0) { tsql3 += " where nickname like '%" + TextBox2.Text.Trim() + "%'"; cou++; } if (DropDownList1.SelectedIndex != 0) { if (cou > 0) tsql3 += " and"; else tsql3 += " where"; tsql3 += " sex= " + DropDownList1.SelectedValue + ""; cou++; } if (TextBox3.Text.Trim().Length > 0) { if (cou > 0) tsql3 += " and"; else tsql3 += " where"; tsql3 += " birthday " + DropDownList2.SelectedValue + " " + TextBox3.Text.Trim(); cou++; } string fy = "ID not in (select top " + ((skip - 1) * pagecount) + " ID from Users " + tsql3 + ")"; string endsql = tsql2 + tsql3 + ((cou > 0) ? " and " : " where ") + fy; lab_NowPageNumber.Text = skip.ToString(); Repeater1.DataSource = new UsersData().Selectsql(endsql, new Hashtable()); Repeater1.DataBind(); if (lab_NowPageNumber.Text == lab_MaxPageNumber.Text) { btn_Next.Enabled = false; btn_Last.Enabled = false; } } void btn_Last_Click(object sender, EventArgs e) //尾页 { btn_First.Enabled = true; btn_Prev.Enabled = true; btn_Next.Enabled = false; btn_Last.Enabled = false; int skip = Convert.ToInt32(lab_MaxPageNumber.Text); string tsql2 = @"select top " + pagecount + " * from Users"; string tsql3 = ""; int cou = 0; lab_NowPageNumber.Text = skip.ToString(); if (TextBox2.Text.Trim().Length > 0) { tsql3 += " where nickname like '%" + TextBox2.Text.Trim() + "%'"; cou++; } if (DropDownList1.SelectedIndex != 0) { if (cou > 0) tsql3 += " and"; else tsql3 += " where"; tsql3 += " sex= " + DropDownList1.SelectedValue + ""; cou++; } if (TextBox3.Text.Trim().Length > 0) { if (cou > 0) tsql3 += " and"; else tsql3 += " where"; tsql3 += " birthday " + DropDownList2.SelectedValue + " " + TextBox3.Text.Trim(); cou++; } string fy = "ID not in (select top " + ((skip - 1) * pagecount) + " ID from Users " + tsql3 + ")"; string endsql = tsql2 + tsql3 + ((cou > 0) ? " and " : " where ") + fy; lab_NowPageNumber.Text = skip.ToString(); Repeater1.DataSource = new UsersData().Selectsql(endsql, new Hashtable()); Repeater1.DataBind(); } void Button3_Click(object sender, EventArgs e) //组合查询按钮 { int cou = 0; string tsql = "select top " + pagecount + " *from Users"; string tsql2 = "select * from Users"; string tsql3 = ""; //查询按钮 if (TextBox2.Text.Trim().Length > 0) { tsql3 += " where nickname like '%" + TextBox2.Text.Trim() + "%'"; cou++; } if (DropDownList1.SelectedIndex != 0) { if (cou > 0) tsql3 += " and"; else tsql3 += " where"; tsql3 += " sex= " + DropDownList1.SelectedValue + ""; cou++; } if (TextBox3.Text.Trim().Length > 0) { if (cou > 0) tsql3 += " and"; else tsql3 += " where"; tsql3 += " birthday " + DropDownList2.SelectedValue + "" + TextBox3.Text.Trim(); cou++; } lab_NowPageNumber.Text = "1"; ceshi.Text = tsql2 + tsql3; Repeater1.DataSource = new UsersData().Selectsql(tsql + tsql3, new Hashtable()); Repeater1.DataBind(); lab_MaxPageNumber.Text = Convert.ToInt32(Math.Ceiling(((decimal)new UsersData().Selectsql(tsql2 + tsql3, new Hashtable()).Count) / pagecount)).ToString(); //总页数 } void Button2_Click(object sender, EventArgs e) //分页跳转 { btn_First.Enabled = true; btn_Prev.Enabled = true; int skip = Convert.ToInt32(TextBox1.Text); if (skip <= Convert.ToInt32(lab_MaxPageNumber.Text) && skip > 0) { string tsql2 = @"select top " + pagecount + " * from Users"; string tsql3 = ""; int cou = 0; lab_NowPageNumber.Text = skip.ToString(); if (TextBox2.Text.Trim().Length > 0) { tsql3 += " where nickname like '%" + TextBox2.Text.Trim() + "%'"; cou++; } if (DropDownList1.SelectedIndex != 0) { if (cou > 0) tsql3 += " and"; else tsql3 += " where"; tsql3 += " sex= " + DropDownList1.SelectedValue + ""; cou++; } if (TextBox3.Text.Trim().Length > 0) { if (cou > 0) tsql3 += " and"; else tsql3 += " where"; tsql3 += " birthday " + DropDownList2.SelectedValue + " " + TextBox3.Text.Trim(); cou++; } string fy = "ID not in (select top " + ((skip - 1) * pagecount) + " ID from Users " + tsql3 + ")"; string endsql = tsql2 + tsql3 + ((cou > 0) ? " and " : " where ") + fy; lab_NowPageNumber.Text = skip.ToString(); Repeater1.DataSource = new UsersData().Selectsql(endsql, new Hashtable()); Repeater1.DataBind(); TextBox1.Text = ""; } else { } } void Button1_Click(object sender, EventArgs e) //退出登录 { Response.Cookies["user"].Expires = DateTime.Now.AddDays(-10); Response.Redirect("Login.aspx"); //跳转页面，到登录页面 } public int getMaxNumber() //最大的页数 { int end = 1; string sql = "select * from Users"; Hashtable hs = new Hashtable(); //（数据总条数/每页显示条数）取上限 就会显示最大页数 int allCount = new UsersData().Selectsql(sql, hs).Count; //查询全部数据条数 allCount decimal a = Convert.ToDecimal(allCount) / pagecount; //页数 end = Convert.ToInt32(Math.Ceiling(a)); //取上限 return end; } }
组合查询和分页合在一起，关键是弄明白查询语句应该怎写。
