web组合查询：
<%@ 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" >
.shuju {
width :260px;
margin :5px 5px;
background-color :pink ;
float :left ;
}
.shuju:hover {
background-color :blue ;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<br /><br />
查询昵称： <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
查询性别：<asp:DropDownList ID="DropDownList2" runat="server">
<asp:ListItem Value ="4" >全部性别</asp:ListItem>
<asp:ListItem Value ="1" >男</asp:ListItem>
<asp:ListItem Value ="0" >女</asp:ListItem>
</asp:DropDownList>
查询生日：<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem Value =">=" >大于等于</asp:ListItem>
<asp:ListItem Value ="<=" >小于等于</asp:ListItem>
<asp:ListItem Value ="=" >等于</asp:ListItem>
</asp:DropDownList>
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="查询" />
<br /><br />
<div style ="width:1080px;margin :0 auto ;background-color :gray ;">
<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate >
<div class ="shuju">
Ids: <%#Eval("Ids") %> <br/>
编号： <%#Eval ("Ucode") %> <br />
用户名： <%#Eval ("UserName") %> <br />
密码： <%#Eval ("PassWord") %> <br/>
昵称： <%#Eval ("NickName") %><br/>
性别： <%#Eval ("SexStr") %><br/>
生日： <%#Eval ("BirthdayStr") %><br/>
民族： <%#Eval ("NationName") %><br/>
</div>
</ItemTemplate>
</asp:Repeater>
<div style =" clear :both ; "></div>
</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="尾页" />
<asp:DropDownList ID="dr_drop" AutoPostBack="true" runat="server"></asp:DropDownList>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<asp:Button ID="Button2" runat="server" Text="跳转" />
</div>
</form>
</body>
</html>
后台代码：
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 = 4;//每页显示条数
protected void Page_Load(object sender, EventArgs e)
{
Button1.Click += Button1_Click;
if (!IsPostBack)
{
Repeater1.DataSource = new UsersData().Select(1, PageCount);
Repeater1.DataBind();
lab_MaxPageNumber.Text = getMaxNumber().ToString();
for (int i = 1; i <= Convert.ToInt32(lab_MaxPageNumber.Text); i++)
{
dr_drop.Items.Add(new ListItem(i.ToString(), i.ToString()));
}
}
btn_Last.Click += btn_Last_Click;
btn_Next.Click += btn_Next_Click;
btn_Prev.Click += btn_Prev_Click;
btn_First.Click += btn_First_Click;
Button2.Click += Button2_Click;
dr_drop.SelectedIndexChanged += dr_drop_SelectedIndexChanged;
}
string Sql = "";
Hashtable Hs = null ;
void Button1_Click(object sender, EventArgs e)
{
int count = 0;
string sql = "select*from Users ";
string tsql = "select top " + PageCount + " *from Users ";
Hashtable hs = new Hashtable();
string tsql3 = "";
if(TextBox1 .Text .Trim ().Length >0){
sql += " where NickName like @a";
tsql += " where NickName like @a";
tsql3 += " where NickName like @a";
hs.Add("@a", "%" + TextBox1.Text.Trim() + "%");
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
count++;
}
if (DropDownList2.SelectedIndex ==0&&TextBox1 .Text .Trim ().Length ==0)
{
sql = "select*from Users ";
tsql = "select top " + PageCount + " *from Users ";
tsql3 = "select top " + PageCount + " *from Users ";
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}else if(DropDownList2.SelectedIndex !=0)
{
if (count > 0)
{
sql += " and Sex = " + DropDownList2.SelectedValue + "";
tsql += " and Sex = " + DropDownList2.SelectedValue + "";
tsql3 += " and Sex = " + DropDownList2.SelectedValue + "";
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}
else
{
sql += " where Sex = " + DropDownList2.SelectedValue + " ";
tsql += " where Sex = " + DropDownList2.SelectedValue + " ";
tsql3 += " where Sex = " + DropDownList2.SelectedValue + " ";
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}
count++;
}
if (TextBox3.Text.Trim().Length > 0)
{
if (count > 0)
{
sql += " and Birthday "+DropDownList1 .SelectedValue +" @d";
tsql += " and Birthday " + DropDownList1.SelectedValue + " @d";
tsql3 += " and Birthday " + DropDownList1.SelectedValue + " @d";
hs.Add("@d", TextBox3.Text.Trim());
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}
else
{
sql += " where Birthday " + DropDownList1.SelectedValue + " @d";
tsql += " where Birthday " + DropDownList1.SelectedValue + " @d";
tsql3 += " where Birthday " + DropDownList1.SelectedValue + " @d";
hs.Add("@d", TextBox3.Text.Trim());
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}
count++;
}
Repeater1.DataSource = new UsersData().SelectCha(tsql, hs);
Repeater1.DataBind();
Label1.Text = Sql;
}
void Button2_Click(object sender, EventArgs e)
{
int a = Convert.ToInt32(TextBox2.Text);
PageDataBind(a);
}
void dr_drop_SelectedIndexChanged(object sender, EventArgs e)
{
int a = Convert.ToInt32(dr_drop.SelectedValue);
PageDataBind(a);
}
public int getMaxNumber()
{
int end = 1;
//（数据总条数/每页显示条数）取上限
int allCount = new UsersData().SelectAll().Count;
decimal a = Convert.ToDecimal(allCount) / PageCount;
end = Convert.ToInt32(Math.Ceiling(a));
return end;
}
public int getMaxNumberTJ()
{
int end = 1;
//（数据总条数/每页显示条数）取上限
int allCount = new UsersData().SelectCha (Sql ,Hs ).Count;
decimal a = Convert.ToDecimal(allCount) / PageCount;
end = Convert.ToInt32(Math.Ceiling(a));
return end;
}
void btn_First_Click(object sender, EventArgs e)
{
int a = 1;
PageDataBind(a);
}
void btn_Prev_Click(object sender, EventArgs e)
{
int a = Convert.ToInt32(lab_NowPageNumber.Text) - 1;
if (a <= Convert.ToInt32(lab_MaxPageNumber.Text))
{
PageDataBind(a);
}
if (a == Convert.ToInt32(lab_MaxPageNumber.Text))
{
}
int ss = Convert.ToInt32(lab_NowPageNumber.Text);
int count = 0;
string sql = "select*from Users ";
string tsql = "select top " + PageCount + " *from Users ";
Hashtable hs = new Hashtable();
string tsql3 = "";
if (TextBox1.Text.Trim().Length > 0)
{
sql += " where NickName like @a";
tsql += " where NickName like @a";
tsql3 += " where NickName like @a";
hs.Add("@a", "%" + TextBox1.Text.Trim() + "%");
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
count++;
}
if (DropDownList2.SelectedIndex == 0 && TextBox1.Text.Trim().Length == 0)
{
//sql = "select*from Users ";
//tsql = "select top " + PageCount + " *from Users ";
//tsql3 = "select top " + PageCount + " *from Users ";
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}
else if (DropDownList2.SelectedIndex != 0)
{
if (count > 0)
{
sql += " and Sex = " + DropDownList2.SelectedValue + "";
tsql += " and Sex = " + DropDownList2.SelectedValue + "";
tsql3 += " and Sex = " + DropDownList2.SelectedValue + "";
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}
else
{
sql += " where Sex = " + DropDownList2.SelectedValue + " ";
tsql += " where Sex = " + DropDownList2.SelectedValue + " ";
tsql3 += " where Sex = " + DropDownList2.SelectedValue + " ";
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}
count++;
}
if (TextBox3.Text.Trim().Length > 0)
{
if (count > 0)
{
sql += " and Birthday " + DropDownList1.SelectedValue + " @d ";
tsql += " and Birthday " + DropDownList1.SelectedValue + " @d ";
tsql3 += " and Birthday " + DropDownList1.SelectedValue + " @d ";
hs.Add("@d", TextBox3.Text.Trim());
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}
else
{
sql += " where Birthday " + DropDownList1.SelectedValue + " @d ";
tsql += " where Birthday " + DropDownList1.SelectedValue + " @d ";
tsql3 += " where Birthday " + DropDownList1.SelectedValue + " @d ";
hs.Add("@d", TextBox3.Text.Trim());
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}
count++;
}
string tsql2 = " Ids not in(select top " + ((ss - 1) * PageCount) + " Ids from Users " + tsql3 + " ) ";
string endsql = tsql + ((count > 0) ? "and" : "where") + tsql2;
Repeater1.DataSource = new UsersData().SelectCha(endsql, hs);
Repeater1.DataBind();
Label1.Text = endsql;
}
void btn_Next_Click(object sender, EventArgs e)
{
int a = Convert.ToInt32(lab_NowPageNumber.Text) + 1;
if (a <= Convert.ToInt32(lab_MaxPageNumber.Text))
{
PageDataBind(a);
}
if (a == Convert.ToInt32(lab_MaxPageNumber.Text))
{
}
int ss = Convert.ToInt32(lab_NowPageNumber .Text );
int count = 0;
string sql = "select*from Users ";
string tsql = "select top " + PageCount + " *from Users ";
Hashtable hs = new Hashtable();
string tsql3 = "";
if (TextBox1.Text.Trim().Length > 0)
{
sql += " where NickName like @a";
tsql += " where NickName like @a";
tsql3 += " where NickName like @a";
hs.Add("@a", "%" + TextBox1.Text.Trim() + "%");
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
count++;
}
if (DropDownList2.SelectedIndex == 0 && TextBox1.Text.Trim().Length == 0)
{
//sql = "select*from Users ";
//tsql = "select*from Users ";
//tsql3 = "select top " + PageCount + " *from Users ";
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}
else if (DropDownList2.SelectedIndex != 0)
{
if (count > 0)
{
sql += " and Sex = " + DropDownList2.SelectedValue + "";
tsql += " and Sex = " + DropDownList2.SelectedValue + "";
tsql3 += " and Sex = " + DropDownList2.SelectedValue + "";
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}
else
{
sql += " where Sex = " + DropDownList2.SelectedValue + " ";
tsql += " where Sex = " + DropDownList2.SelectedValue + " ";
tsql3 += " where Sex = " + DropDownList2.SelectedValue + " ";
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}
count++;
}
if (TextBox3.Text.Trim().Length > 0)
{
if (count > 0)
{
sql += " and Birthday " + DropDownList1.SelectedValue + " @d ";
tsql += " and Birthday " + DropDownList1.SelectedValue + " @d ";
tsql3 += " and Birthday " + DropDownList1.SelectedValue + " @d ";
hs.Add("@d", TextBox3.Text.Trim());
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}
else
{
sql += " where Birthday " + DropDownList1.SelectedValue + " @d ";
tsql += " where Birthday " + DropDownList1.SelectedValue + " @d ";
tsql3 += " where Birthday " + DropDownList1.SelectedValue + " @d ";
hs.Add("@d", TextBox3.Text.Trim());
Sql = sql;
Hs = hs;
lab_MaxPageNumber.Text = getMaxNumberTJ().ToString();
}
count++;
}
string tsql2 = " Ids not in(select top " + ((ss - 1) * PageCount) + " Ids from Users "+ tsql3 +" ) ";
string endsql = tsql +((count >0)? "and" : "where" )+ tsql2 ;
Repeater1.DataSource = new UsersData().SelectCha(endsql, hs);
Repeater1.DataBind();
Label1.Text = endsql ;
}
void btn_Last_Click(object sender, EventArgs e)
{
int a = Convert.ToInt32(lab_MaxPageNumber.Text);
PageDataBind(a);
}
public void PageDataBind(int a)
{
Repeater1.DataSource = new UsersData().Select(a, PageCount);
Repeater1.DataBind();
lab_NowPageNumber.Text = a.ToString();
}
}
