分页和组合查询都是通过拼接SQL语句到数据库查询进行实现
到汽车表(car)中查询 ,汽车表选取了“编号 code”,“车名 name”,“日期 time”,“油耗 oil ”,“马力 powers”这几列
分页查询语句:每页只显示五条数据
查询前五条数据: select top 5 from car
点击下一页按钮:select top 5 from car where code not in(select top 5 code from car)//第二页的时候通过编号,查询前五条之后的数据,第三页括号里top 10,每加一页跳过前面查过的条数,一次类推,需要定义变量等于1,每次加一页给这个变量重新赋值,让5乘以这个变量
上一页按钮也是用此方法
组合查询语句:根据车名、油耗、和马力查询
根据选中的条件进行拼接SQL语句:select * from car where name like '%xxx%' and oil like '%xxx%' and powers > xxx ;
通过选择的任意条件,进行判断,如果前面条件选中,后面跟and xxx,如果前面条件没选中就直接where xxx
分页和组合查询连起来就是:select top 5 from car where name like '%xxx%' and oil like '%xxx%' and powers > xxx and code not in(select top 5 code from car);根据不同的选择条件进行拼接,在数据库写一个查询的方法,将拼接好的SQL语句传参到方法中进行执行,调用此方法在前台显示
代码:
数据库实体类:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web; /// <summary>
/// Car 的摘要说明
/// </summary>
public class Car
{
public Car()
{
//
// TODO: 在此处添加构造函数逻辑
//
} //封装一个汽车表的实体类
private string _code;//汽车编号 public string Code
{
get { return _code; }
set { _code = value; }
}
private string _name;//汽车名字 public string Name
{
get { return _name; }
set { _name = value; }
}
private DateTime _time;//出产日期 public DateTime Time
{
get { return _time; }
set { _time = value; }
}
private decimal _oil;//油耗 public decimal Oil
{
get { return _oil; }
set { _oil = value; }
}
private int _powers;//马力 public int Powers
{
get { return _powers; }
set { _powers = value; }
}
}
数据库操作类:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Collections; /// <summary>
/// CarData 的摘要说明
/// </summary>
public class CarData
{
SqlConnection conn = null;
SqlCommand cmd = null;
public CarData()
{
conn = new SqlConnection("server=.;database=netlx;user=sa;pwd=123;");
cmd = conn.CreateCommand();
} /// <summary>
/// 查询车辆信息
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public List<Car> select(string sql,Hashtable has)
{
List<Car> list = new List<Car>();
cmd.CommandText = sql;
cmd.Parameters.Clear(); foreach (string s in has.Keys)
{
cmd.Parameters.Add(s, has[s]);
}
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
Car cc = new Car();
cc.Code = dr["code"].ToString();
cc.Name = dr["name"].ToString();
cc.Time = Convert.ToDateTime(dr["time"]);
cc.Oil = Convert.ToDecimal(dr["oil"]);
cc.Powers = Convert.ToInt32(dr["powers"]);
list.Add(cc);
}
}
conn.Close();
return list;
}
}
页面前台代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default1.aspx.cs" Inherits="Default1" %> <!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">
#Bt {
position: relative;
font-family: 黑体;
font-size: 50px;
text-align: center;
letter-spacing: 5px; /*字符间距*/
}
#Cx {
height:30px;
margin-top:20px;
}
table {
background-color: #07066f;
width: 100%;
} #Bt1 {
font-family: 微软雅黑;
font-size: 25px;
color: white;
text-align: center;
} #Nr {
font-family: 微软雅黑;
font-size: 25px;
background-color: white;
text-align: center;
} td {
padding: 10px;
}
</style>
</head>
<body>
<div id="Bt">汽车信息展示</div>
<form id="form1" runat="server">
<div id="Cx">
车名:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
油耗:<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> 马力:<asp:DropDownList ID="Tjf" runat="server">
<asp:ListItem>></asp:ListItem>
<asp:ListItem><</asp:ListItem>
<asp:ListItem>=</asp:ListItem>
</asp:DropDownList><asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
<asp:Button ID="Select" runat="server" Text="确 定" />
</div>
<asp:Repeater ID="Repeater1" runat="server">
<HeaderTemplate>
<table>
<tr id="Bt1">
<td>编号</td>
<td>车名</td>
<td>日期</td>
<td>油耗</td>
<td>马力</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr id="Nr">
<td><%#Eval("Code") %></td>
<td><%#Eval("Name") %></td>
<td><%#Eval("Time") %></td>
<td><%#Eval("Oil") %></td>
<td><%#Eval("Powers") %></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
当前是第[<asp:Label ID="Yecode" runat="server" Text="Label"></asp:Label>]页
<asp:LinkButton ID="First" runat="server">首页</asp:LinkButton>
<asp:LinkButton ID="Up" runat="server">上一页</asp:LinkButton>
<asp:LinkButton ID="Down" runat="server">下一页</asp:LinkButton>
<asp:LinkButton ID="Over" runat="server">末页</asp:LinkButton>
<asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="true"></asp:DropDownList>
</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 Default1 : System.Web.UI.Page
{
int count = ;
Hashtable hs = new Hashtable();//创建一个全局的哈希表集合,用来放SQL语句中的条件,防攻击
protected void Page_Load(object sender, EventArgs e)
{
Select.Click += Select_Click;//条件查询的确定按钮
Down.Click += Down_Click;//下一页按钮点击事件
Up.Click += Up_Click;//上一页按钮点击事件
First.Click += First_Click;//首页按钮点击事件
Over.Click += Over_Click;//末页按钮点击事件
DropDownList1.SelectedIndexChanged += DropDownList1_SelectedIndexChanged;//下拉列表选中提交事件
if (IsPostBack == false)
{
Repeater1.DataSource = new CarData().select(TSQL(),hs);
Repeater1.DataBind();
Yecode.Text = "";
First.Enabled = false;
Up.Enabled = false; for (int i = ; i <= MaxNumber(); i++)
{
ListItem li = new ListItem(i.ToString(), i.ToString());
DropDownList1.Items.Add(li);
}
} } //下拉列表选中提交事件
void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
int xy = Convert.ToInt32(DropDownList1.SelectedItem.Value);
Repeater1.DataSource = new CarData().select(TSQL(xy),hs);
Repeater1.DataBind();
Yecode.Text = xy.ToString();
if (Yecode.Text == "")
{
First.Enabled = false;
Up.Enabled = false;
Over.Enabled = true;
Down.Enabled = true;
}
else if (Yecode.Text == MaxNumber().ToString())
{
First.Enabled = true;
Up.Enabled = true;
Over.Enabled = false;
Down.Enabled = false;
}
else
{
First.Enabled = true;
Up.Enabled = true;
Over.Enabled = true;
Down.Enabled = true;
}
} //末页按钮点击事件
void Over_Click(object sender, EventArgs e)
{
Repeater1.DataSource = new CarData().select(TSQL(MaxNumber()),hs);
Repeater1.DataBind();
Yecode.Text = MaxNumber().ToString();
DropDownList1.SelectedIndex = Convert.ToInt32(Yecode.Text) - ;
First.Enabled = true;
Up.Enabled = true;
Down.Enabled = false;
Over.Enabled = false;
} //首页按钮点击事件
void First_Click(object sender, EventArgs e)
{
Repeater1.DataSource = new CarData().select(TSQL(),hs);
Repeater1.DataBind();
Yecode.Text = "";
DropDownList1.SelectedIndex = ;
First.Enabled = false;
Up.Enabled = false;
Down.Enabled = true;
Over.Enabled = true;
} //上一页按钮点击事件
void Up_Click(object sender, EventArgs e)
{
int Yeshu = Convert.ToInt32(Yecode.Text) - ;
Yecode.Text = Yeshu.ToString();
DropDownList1.SelectedIndex = Convert.ToInt32(Yecode.Text) - ;
Repeater1.DataSource = new CarData().select(TSQL(Yeshu),hs);
Repeater1.DataBind();
if (Yecode.Text == "")
{
First.Enabled = false;
Up.Enabled = false;
}
Down.Enabled = true;
Over.Enabled = true;
} //下一页按钮点击事件
void Down_Click(object sender, EventArgs e)
{
int Yeshu = Convert.ToInt32(Yecode.Text) + ;
Yecode.Text = Yeshu.ToString();
DropDownList1.SelectedIndex = Convert.ToInt32(Yecode.Text) - ;
Repeater1.DataSource = new CarData().select(TSQL(Yeshu),hs);
Repeater1.DataBind();
if (Yecode.Text == MaxNumber().ToString())
{
Down.Enabled = false;
Over.Enabled = false;
}
First.Enabled = true;
Up.Enabled = true;
} //条件查询的确定按钮
void Select_Click(object sender, EventArgs e)
{
Repeater1.DataSource = new CarData().select(TSQL(),hs);
Repeater1.DataBind();
Yecode.Text = ""; //调用组合查询没有进行分页的拼接SQL语句的方法去数据库进行查询
List<Car> data = new CarData().select(Tsql(),hs);
if (data.Count <= count)//如果查询到的数据的数量少于每页显示的条数
{
First.Enabled = false;//则这些翻页按钮就不可用
Up.Enabled = false;
Down.Enabled = false;
Over.Enabled = false;
DropDownList1.Items.Clear();//将选择页面的下拉列表清空
ListItem li = new ListItem("", "");//只显示1
DropDownList1.Items.Add(li);
}
else //如果大于每页显示的条数
{
//则下一页和末页可用。因为一上来首页和上一页是不可用的,当点击下一页或者末页,就会触发点击事件,则其他按钮就会可用
Down.Enabled = true;
Over.Enabled = true;
DropDownList1.Items.Clear();//将选择页面的下拉列表清空
for (int i = ; i <= MaxNumber(); i++) //根据查出来的最大页数,重新赋值
{
ListItem li = new ListItem(i.ToString(), i.ToString());
DropDownList1.Items.Add(li);
}
}
} //根据条件拼接一个SQL语句方法
public string TSQL(int Yeshu)
{
hs.Clear();
int ccc = ;
string f1="";
string f2="";
string f3="";
string sql = "select top "+count+" * from car";
if (TextBox1.Text != "")
{
sql += " where name like @name ";
ccc++;
f1=" where name like @name ";
hs.Add("@name", "%" + TextBox1.Text + "%");
}
if (TextBox2.Text != "")
{
if (ccc > )
{
sql += " and oil like @oil ";
f2 = " and oil like @oil ";
}
else
{
sql += " where oil like @oil ";
f2 = " where oil like @oil ";
}
hs.Add("@oil", "%" + TextBox2.Text + "%");
ccc++;
}
if (TextBox3.Text != "")
{
if (ccc > )
{
sql += " and powers " + Tjf.SelectedItem.Value + " @powers";
f3=" and powers " + Tjf.SelectedItem.Value + " @powers";
}
else
{
sql += " where powers" + Tjf.SelectedItem.Value + " @powers";
f3=" where powers" + Tjf.SelectedItem.Value + " @powers";
}
hs.Add("@powers", TextBox3.Text);
ccc++;
} //分页语句
if (ccc > )
{
sql += " and code not in (select top " + count * (Yeshu - ) + " code from car " + f1 + f2 + f3 + ")";
}
else
{
sql += " where code not in (select top " + count * (Yeshu - ) + " code from car " + f1 + f2 + f3 + ")";
}
return sql;
} //查询所有符合条件的数据
public string Tsql()
{
hs.Clear();
int cc = ;
string sql = "select * from car";
if (TextBox1.Text != "")
{
sql += " where name like @name ";
cc++;
hs.Add("@name", "%" + TextBox1.Text + "%");
}
if (TextBox2.Text != "")
{
if (cc > )
{
sql += " and oil like @oil ";
}
else
{
sql += " where oil like @oil ";
}
hs.Add("@oil", "%" + TextBox2.Text + "%");
cc++;
}
if (TextBox3.Text != "")
{
if (cc > )
{
sql += " and powers " + Tjf.SelectedItem.Value + " @powers";
}
else
{
sql += " where powers" + Tjf.SelectedItem.Value + " @powers";
}
hs.Add("@powers", TextBox3.Text);
cc++;
}
return sql;
}
//根据上面查找数据的数量,除以每页显示的,得到的结果取上限值,得到的是最大页数
public int MaxNumber()
{
List<Car> ccc = new CarData().select(Tsql(),hs); double bbb = ccc.Count / (count * 1.0); //总页数,但是是浮点型
return Convert.ToInt32(Math.Ceiling(bbb));
}
}