一个简单的分页组合查询页面
/// <summary>
/// 查询方法
/// </summary>
/// <param name="tsql">SQL语句</param>
/// <param name="hh">哈希表</param>
/// <returns></returns>
public List<Goods> Select(string uname, string sql, Hashtable hs)
{
List<Goods> glist = new List<Goods>();
cmd.CommandText = sql;
cmd.Parameters.Clear();
foreach (string aa in hs.Keys)
{
cmd.Parameters.AddWithValue(aa, hs[aa]);
}
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
Goods g = new Goods();
g.Ids = Convert.ToInt32(dr["Ids"]);
g.Sname = dr["Sname"].ToString();
g.Number = dr["Number"].ToString();
g.Date = Convert.ToDateTime(dr["Date"]);
g.Shelf = Convert.ToInt32(dr["Shelf"]);
g.Contacts = dr["Contacts"].ToString();
g.Tel = dr["Tel"].ToString();
g.UserName = uname;
glist.Add(g);
}
}
conn.Close();
return glist;
} /// <summary>
/// 查询方法（页数）
/// </summary>
/// <param name="tsql"></param>
/// <param name="hh"></param>
/// <returns></returns>
public List<Goods> Select(string uname, int count, int page, string sql, Hashtable hs)
{
List<Goods> glist = new List<Goods>();
string sql1 = sql.Replace("*", "top " + count + " * ");
string sql2 = sql.Replace("*", "top " + (page - ) * count + " Ids ");
cmd.CommandText = sql1 + " and Ids not in(" + sql2 + ")";
cmd.Parameters.Clear();
foreach (string aa in hs.Keys)
{
cmd.Parameters.AddWithValue(aa, hs[aa]);
}
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
Goods g = new Goods();
g.Ids = Convert.ToInt32(dr["Ids"]);
g.Sname = dr["Sname"].ToString();
g.Number = dr["Number"].ToString();
g.Date = Convert.ToDateTime(dr["Date"]);
g.Shelf = Convert.ToInt32(dr["Shelf"]);
g.Contacts = dr["Contacts"].ToString();
g.Tel = dr["Tel"].ToString();
g.UserName = uname;
glist.Add(g);
}
}
conn.Close();
return glist; }
数据访问类
public class Goods
{
public Goods()
{
//
// TODO: 在此处添加构造函数逻辑
//
} public int Ids { get; set; }
public string Sname { get; set; }
public string Number { get; set; }
public DateTime Date { get; set; }
public int Shelf { get; set; }
public string Contacts { get; set; }
public string Tel { get; set; }
public string UserName { get; set; } /// <summary>
/// 判断有效期，以颜色区分
/// </summary>
public string color
{
get
{
string re = "";
int a = Shelf - (DateTime.Now - Date).Days;
if(a<=)
re = "background-color:#393535; color:red;";
else if (a <= )
re = "background-color:red;";
else if (a <= &a>)
re = "background-color:yellow;";
return re;
}
}
}
封装实体类
public partial class Show : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
if (Request.Cookies["username"] != null)
{
string n = Request.Cookies["username"].Value;
Label_NowPage.Text = "";//第一页
Label_MaxPage.Text = MaxPageNumber().ToString();//获取最大页
btn_prev.Enabled = false;
btn_first.Enabled = false; Repeater1.DataSource = new GoodsData().Select(Count, );
Repeater1.DataBind();
int max = MaxPageNumber();
DropDownList1.Items.Clear();
//给可快速跳转列表框赋值
for (int i = ; i <= max; i++)
{
DropDownList1.Items.Add(new ListItem(i.ToString(), i.ToString()));
}
string uname = new UsersData().Select(n).NickName;
Literal1.Text = "欢迎你，" + uname + "";
}
else
{
Response.Redirect("Login.aspx");
} } DropDownList1.SelectedIndexChanged += Button1_Click;
btn_first.Click += btn_first_Click;//首页
btn_prev.Click += btn_prev_Click;//上一页
btn_next.Click += btn_next_Click;//下一页
btn_last.Click += btn_last_Click;//末页
Button1.Click += Button1_Click;//跳转按钮
Button2.Click += Button2_Click;
LinkButton1.Click += LinkButton1_Click;
} string sql = "";
//使用哈希表
Hashtable hs = new Hashtable();
private void groupselect()
{
sql = "select * from Warehouse";
hs.Clear();
//判断文本框中是否有内容需要查询
if (TextBox1.Text.Trim().Length > )
{
//如果有内容，那么就拼接到Tsql语句中去
sql += " where Sname like @a";
hs.Add("@a", "%" + TextBox1.Text.Trim() + "%");
hs.Add("@d", "%" + TextBox1.Text.Trim() + "%");
}
else
{
sql += " where 1=1";
}
if (TextBox2.Text.Trim().Length > )
{
sql += " and Shelf" + DropDownList2.SelectedValue + "@b";
hs.Add("@b", TextBox2.Text.Trim());
hs.Add("@e", TextBox2.Text.Trim());
}
else
{
sql += " and 1=1";
}
if (TextBox3.Text.Length > )
{
sql += " and Contacts like @c";
hs.Add("@c", "%" + TextBox3.Text.Trim() + "%");
hs.Add("@f", "%" + TextBox3.Text.Trim() + "%");
}
else
{
sql += " and 1=1";
}
Repeater1.DataSource = new GoodsData().Select(N, sql.Replace("*", "top " + Count + " *"), hs);
Repeater1.DataBind();
} void Button2_Click(object sender, EventArgs e)
{
groupselect();
int max = MaxPageNumber();
DropDownList1.Items.Clear();
for (int i = ; i <= max; i++)
{
DropDownList1.Items.Add(new ListItem(i.ToString(), i.ToString()));
}
Label_NowPage.Text = "";
Label_MaxPage.Text = MaxPageNumber().ToString();
if (max == )
{
btn_first.Enabled = false;
btn_prev.Enabled = false;
btn_next.Enabled = false;
btn_last.Enabled = false;
}
else
{
btn_first.Enabled = false;
btn_prev.Enabled = false;
btn_next.Enabled = true;
btn_last.Enabled = true;
}
} void btn_prev_Click(object sender, EventArgs e)
{
groupselect();
int max = MaxPageNumber();
DropDownList1.Items.Clear();
for (int i = ; i <= max; i++)
{
DropDownList1.Items.Add(new ListItem(i.ToString(), i.ToString()));
} int nextPage = Convert.ToInt32(Label_NowPage.Text) - ; Repeater1.DataSource = new GoodsData().Select(N, Count, nextPage, sql, hs);
Repeater1.DataBind(); Label_NowPage.Text = nextPage.ToString();
panduan(nextPage);
} void btn_first_Click(object sender, EventArgs e)
{
groupselect();
int max = MaxPageNumber();
DropDownList1.Items.Clear();
for (int i = ; i <= max; i++)
{
DropDownList1.Items.Add(new ListItem(i.ToString(), i.ToString()));
}
int nextPage = ; Repeater1.DataSource = new GoodsData().Select(N,Count, nextPage, sql, hs);
Repeater1.DataBind(); Label_NowPage.Text = nextPage.ToString();
panduan(nextPage);
} void btn_next_Click(object sender, EventArgs e)
{
groupselect();
int max = MaxPageNumber();
DropDownList1.Items.Clear();
for (int i = ; i <= max; i++)
{
DropDownList1.Items.Add(new ListItem(i.ToString(), i.ToString()));
}
//获取当前页，计算要看的下一页的页号
int nextPage = Convert.ToInt32(Label_NowPage.Text) + ;
//按照获取的页数绑定相应的数据
Repeater1.DataSource = new GoodsData().Select(N,Count, nextPage, sql, hs);
Repeater1.DataBind();
//修改显示页数
Label_NowPage.Text = nextPage.ToString();
panduan(nextPage);
} void btn_last_Click(object sender, EventArgs e)
{
groupselect();
int max = MaxPageNumber();
DropDownList1.Items.Clear();
for (int i = ; i <= max; i++)
{
DropDownList1.Items.Add(new ListItem(i.ToString(), i.ToString()));
}
int nextPage = MaxPageNumber();
//按照获取的页数绑定相应的数据
Repeater1.DataSource = new GoodsData().Select(N, Count, nextPage, sql, hs);
Repeater1.DataBind(); //修改显示页数
Label_NowPage.Text = nextPage.ToString();
panduan(nextPage); } void Button1_Click(object sender, EventArgs e)
{
//获取当前页，计算要看的下一页的页号
int nextPage = Convert.ToInt32(DropDownList1.SelectedValue); //按照获取的页数绑定相应的数据
Repeater1.DataSource = new GoodsData().Select(Count, nextPage);
Repeater1.DataBind(); //修改显示页数
Label_NowPage.Text = nextPage.ToString(); if (nextPage <= )
{
btn_prev.Enabled = false;
}
btn_next.Enabled = true;
} void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
} void LinkButton1_Click(object sender, EventArgs e)
{
Response.Redirect("Login.aspx");
}
string N = null;
int Count = ;
public int MaxPageNumber()
{
//查询所有的数据
List<Goods> glist = new List<Goods>();
if (sql == "")
{
glist = new GoodsData().Select(N);
}
else
{
glist = new GoodsData().Select(N, sql, hs);
}
//取上限
double end2 = Math.Ceiling(glist.Count / (Count * 1.0)); int end = Convert.ToInt32(end2); return end;
}
public void panduan(int nextPage)
{
if (nextPage == )
{
btn_first.Enabled = false;
btn_prev.Enabled = false;
btn_next.Enabled = true;
btn_last.Enabled = true;
}
else if (nextPage == MaxPageNumber())
{
btn_first.Enabled = true;
btn_prev.Enabled = true;
btn_next.Enabled = false;
btn_last.Enabled = false;
}
else
{
btn_first.Enabled = true;
btn_prev.Enabled = true;
btn_next.Enabled = true;
btn_last.Enabled = true;
}
}
}
展示界面后台代码
效果展示
1分页：
2单独条件查询
3多条件查询
4上下点击翻页（可直接点击下拉菜单进行跳转）