Linq 组合查询与分页查询
后台:
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 ZS : System.Web.UI.Page
{
Hashtable ht = new Hashtable();
int pcou = 2;
protected void Page_Load(object sender, EventArgs e)
{
Button1.Click += Button1_Click;
Button2.Click += Button2_Click;
//////////////////////////////////////////////////////////////////////
if (!IsPostBack)
{
Repeater1.DataSource = new StusData().SelectTJ(TSQL(1), ht);
Repeater1.DataBind();
//最大页数绑定
PageAll.Text = PageAAll().ToString();
////下拉菜单绑定
for (int x = 1; x <= PageAAll(); x++)
{
ListItem li = new ListItem(x.ToString(), x.ToString());
DropDownPage.Items.Add(li);
}
}
PageJump.Click += PageJump_Click;
PageNext.Click += PageNext_Click;
PageLast.Click += PageLast_Click;
PageOld.Click += PageOld_Click;
PageFirst.Click += PageFirst_Click;
Select.Click += Select_Click;
}
//拼写TSQL语句查询所有信息*****
public string TSQL(int zz)
{
ht.Clear();
int cc = 0;//记录下一条查询数据
string sql = "select top " + pcou + "* from Stus ";
string t1 = "";
//1.将条件查询的语句拼完
if (Name.Text.Trim().Length > 0)
{
cc++;
t1 += " where Name like @a ";
ht.Add("@a", "%" + Name.Text.Trim() + "%");
}
if (Sex.SelectedValue != "")
{
if (cc == 0)
{
t1 += " where " + Sex.SelectedValue;
cc++;
}
else
{
t1 += " and " + Sex.SelectedValue;
}
}
if (Score.Text.Trim().Length > 0)
{
if (cc == 0)
{
t1 += " where Score " + ScorePD.SelectedValue + " @b ";
ht.Add("@b", Score.Text.Trim());
cc++;
}
else
{
t1 += " and Score " + ScorePD.SelectedValue + " @b ";
ht.Add("@b", Score.Text.Trim());
}
}
//2.将分页的语句拼完
if (cc == 0)
{
sql += " where Ids not in (select top " + (pcou * (zz - 1)) + " Ids from Stus " + t1 + " )";
}
else
{ sql += t1 + " and Ids not in (select top " + (pcou * (zz - 1)) + " Ids from Stus " + t1 + " )"; }
return sql;
}
//拼写tsql1语句获取共有多少条信息*****
public string tsql1()
{
ht.Clear();
int cc = 0;//记录下一条查询数据
string sql = "select count(*) from Stus";
string t1 = "";
//1.将条件查询的语句拼完
//string aa = " select *from Stus ";
if (Name.Text.Trim().Length > 0)
{
cc++;
t1 += " where Name like @a ";
ht.Add("@a", "%" + Name.Text.Trim() + "%");
}
if (Sex.SelectedValue != "")
{
if (cc == 0)
{
t1 += " where " + Sex.SelectedValue;
cc++;
}
else
{
t1 += " and " + Sex.SelectedValue;
}
}
if (Score.Text.Trim().Length > 0)
{
if (cc == 0)
{
t1 += " where Score " + ScorePD.SelectedValue + " @b ";
ht.Add("@b", Score.Text.Trim());
}
else
{
t1 += " and Score " + ScorePD.SelectedValue + " @b ";
ht.Add("@b", Score.Text.Trim());
}
}
sql += t1;
return sql;
}
//获取最大页数
public int PageAAll()
{
int cc = 0;
Decimal bb = Convert.ToDecimal(new StusData().SelectPage(tsql1(), ht));
cc = Convert.ToInt32(Math.Ceiling(bb / pcou));
return cc;
}
// 查询按钮点击事件
void Select_Click(object sender, EventArgs e)
{
DropDownPage.Items.Clear();
Repeater1.DataSource = new StusData().SelectTJ(TSQL(1), ht);
Repeater1.DataBind();
//最大页数绑定
PageAll.Text = PageAAll().ToString();
//下拉菜单绑定
for (int x = 1; x <= PageAAll(); x++)
{
ListItem li = new ListItem(x.ToString(), x.ToString());
DropDownPage.Items.Add(li);
}
}
//首页****
void PageFirst_Click(object sender, EventArgs e)
{
Repeater1.DataSource = new StusData().SelectTJ(TSQL(1), ht);
Repeater1.DataBind();
//当前页数变换
PageNow.Text = "1";
DropDownPage.SelectedValue = "1";
}
//上一页****
void PageOld_Click(object sender, EventArgs e)
{
//获取上一页的页数
int n = Convert.ToInt32(PageNow.Text) - 1;
//如果页数小于最小页数
if (n < 1)
{
return;
}
//绑定数据
Repeater1.DataSource = new StusData().SelectTJ(TSQL(n), ht);
Repeater1.DataBind();
//当前页数变换
PageNow.Text = n.ToString();
DropDownPage.SelectedValue = n.ToString();
}
//尾页****
void PageLast_Click(object sender, EventArgs e)
{
//绑定数据
Repeater1.DataSource = new StusData().SelectTJ(TSQL(PageAAll()), ht);
Repeater1.DataBind();
//当前页数变换
PageNow.Text = PageAAll().ToString();
DropDownPage.SelectedValue = PageAAll().ToString();
}
//下一页*****
void PageNext_Click(object sender, EventArgs e)
{
//获取下一页的页数
int n = Convert.ToInt32(PageNow.Text) + 1;
//如果页数超过了最大页数
if (n > PageAAll())
{
return;
}
//绑定数据
Repeater1.DataSource = new StusData().SelectTJ(TSQL(n), ht);
Repeater1.DataBind();
//当前页数变换
PageNow.Text = n.ToString();
DropDownPage.SelectedValue = n.ToString();
}
//跳转按钮****
void PageJump_Click(object sender, EventArgs e)
{
int a = Convert.ToInt32(DropDownPage.SelectedValue);
//绑定数据
Repeater1.DataSource = new StusData().SelectTJ(TSQL(a), ht);
Repeater1.DataBind();
//当前页数变换
PageNow.Text = a.ToString();
}
void Button2_Click(object sender, EventArgs e)
{
Response.Cookies["username"].Expires = DateTime.Now.AddDays(-10);
Response.Redirect("DL.aspx");
}
void Button1_Click(object sender, EventArgs e)
{
//Response.Redirect("TJXS.aspx");
Label2.Text = TSQL(1);
}
}
HTML
<span>当前第</span><asp:Label ID="PageNow" runat="server" Text="1"></asp:Label><span></span>页</span>
<span>共</span><asp:Label ID="PageAll" runat="server" Text="1"></asp:Label><span>页</span>
<asp:DropDownList ID="DropDownPage" runat="server"></asp:DropDownList>
<asp:Button ID="PageJump" runat="server" Text="跳转" />
<asp:Button ID="PageFirst" runat="server" Text="首页" />
<asp:Button ID="PageOld" runat="server" Text="上一页" />
<asp:Button ID="PageNext" runat="server" Text="下一页" />
<asp:Button ID="PageLast" runat="server" Text="尾页" />
<span>查询筛选: 学生姓名:</span>
<asp:TextBox ID="Name" runat="server"></asp:TextBox>
<span>学生性别:</span>
<asp:DropDownList ID="Sex" runat="server">
<asp:ListItem Text="男 和 女" Value=""></asp:ListItem>
<asp:ListItem Text="男" Value="Sex = 1 "></asp:ListItem>
<asp:ListItem Text="女" Value="Sex = 0 "></asp:ListItem>
</asp:DropDownList>
<span>学生成绩:</span>
<asp:DropDownList ID="ScorePD" runat="server">
<asp:ListItem Text="大于等于" Value=">="></asp:ListItem>
<asp:ListItem Text="小于" Value="<"></asp:ListItem>
<asp:ListItem Text="等于" Value="="></asp:ListItem>
</asp:DropDownList>
<asp:TextBox ID="Score" runat="server"></asp:TextBox>
<asp:Button ID="Select" runat="server" Text="查询" />
<div class="d1" style="font-size: 30px;">
学生数据展示
</div>
<div>
<%--数据展示--%>
<table class="d2">
<tr>
<td>编号</td>
<td>姓名</td>
<td>性别</td>
<td>民族</td>
<td>年龄</td>
<td>生日</td>
<td>班级</td>
<td>分数</td>
<td>学生信息操作</td>
</tr>
<asp:Repeater ID="Repeater1" runat="server">
<%--循环展示的数据--%>
<ItemTemplate>
<tr style="">
<td><%#Eval("Ids") %></td>
<td><%#Eval("Name") %></td>
<td><%#Eval("Sexx") %></td>
<td><%#Eval("Nationn") %></td>
<td><%#Eval("Age") %></td>
<td><%#Eval("Bir") %></td>
<td><%#Eval("Classs") %></td>
<td><%#Eval("Score") %></td>
<td>
<input type="button" value="修改" oo="<%#Eval("Ids")%>" class="btn1" />
<a href="SCXS.aspx?i=<%#Eval("Ids") %>">
<input type="button" value="删除" onclick="return confirm('是否要删除 <%#Eval("Name")%> ?');" /></a>
</td>
</tr>
</ItemTemplate>
</asp:Repeater>
</table>
数据访问类:
//分页组合查询统一方法***
public List<Stus> SelectTJ(string s1, Hashtable ht)
{
List<Stus> ss = new List<Stus>();
cmd.CommandText = s1;
foreach (string hh in ht.Keys)
{
cmd.Parameters.AddWithValue(hh, ht[hh]);
}
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
ss = new List<Stus>();
while (dr.Read())
{
Stus s = new Stus();
s.Ids = Convert.ToInt32(dr["Ids"]);
s.Name = dr["Name"].ToString();
s.Sex = Convert.ToBoolean(dr["Sex"]);
s.Birthday = Convert.ToDateTime(dr["Birthday"]);
s.Nation = dr["Nation"].ToString();
s.Class = dr["Class"].ToString();
s.Score = Convert.ToInt32(dr["Score"]);
ss.Add(s);
}
}
conn.Close();
return ss;
}
//分页组合查询查询信息条数****
public int SelectPage(string s, Hashtable ht)
{
int a = 0;
cmd.CommandText = s;
foreach (string hh in ht.Keys)
{
cmd.Parameters.AddWithValue(hh, ht[hh]);
}
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
a = Convert.ToInt32(dr[0]);
}
conn.Close();
return a;
}
