WebForm 分页与组合查询
1.封装实体类
2.写查询方法
//SubjectData类 public List<Subject> Select(string name) { List<Subject> list = new List<Subject>(); cmd.CommandText = "select *from Subject where SubjectName like @a "; cmd.Parameters.Clear(); cmd.Parameters.Add("@a","%"+name+"%"); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { Subject s = new Subject(); s.SubjectCode = dr[0].ToString(); s.SubjectName = dr[1].ToString(); list.Add(s); } } conn.Close(); return list; } //StudentData类 /// <summary> /// 查询方法 /// </summary> /// <param name="tsql">SQL语句</param> /// <param name="hh">哈希表</param> /// <returns></returns> public List<Student> Select(string tsql,Hashtable hh) { List<Student> list = new List<Student>(); cmd.CommandText = tsql; cmd.Parameters.Clear(); foreach( string s in hh.Keys) { cmd.Parameters.Add(s,hh[s]); } conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { Student s = new Student(); s.Code = dr[0].ToString(); s.Name = dr[1].ToString(); s.Sex = Convert.ToBoolean(dr[2]); s.Birthday = Convert.ToDateTime(dr[3]); s.SubjectCode = dr[4].ToString(); s.Nation = dr[5].ToString(); list.Add(s); } } conn.Close(); return list; } 查询方法
3.Page_Load部分，最大页方法
int PageCount = 5; //每页显示条数 Hashtable hs = new Hashtable(); protected void Page_Load(object sender, EventArgs e) { if(!IsPostBack) { string tsql = "select top "+PageCount+" *from Student";//查询前PageCount条数据 //Repeater1数据源指向 List<Student> list = new StudentData().Select(tsql,hs); Repeater1.DataSource = list; Repeater1.DataBind(); Label2.Text = "1";//第一页 //获取最大页 string sql = "select *from Student"; Label3.Text = MaxPageNumber(sql,hs).ToString(); for (int i = 1; i <= MaxPageNumber(sql,hs); i++)//给可快速跳转列表框赋值 { DropDownList2.Items.Add(new ListItem(i.ToString(), i.ToString())); } } } Page_Load
public int MaxPageNumber(string sql, Hashtable hs) { List<Student> list = new StudentData().Select(sql, hs);//查询所有数据 double de = list.Count / (PageCount * 1.0); int aa = Convert.ToInt32(Math.Ceiling(de));//取上限 return aa; } 获取最大页
4.根据组合查询拼接语句方法
/// <summary> /// /// </summary> /// <param name="sql">拼接查询前PageCount条数据的语句</param> /// <param name="sql2">查询所有的语句</param> /// <param name="tj">用于分页查询与sql等拼接</param> /// <param name="count">判断前几项是否为空</param> private void Tsql(out string sql, out string sql2,out string tj,out int count) { count = 0; sql = "select top " + PageCount + " *from Student"; sql2 = "select *from Student"; tj = ""; //性别不为空 if (!string.IsNullOrEmpty(tb_sex.Text.Trim())) {//判断输入的是男是女，其它输入默认为未输入内容 if (tb_sex.Text.Trim() == "男") { sql += " where Sex = @a"; sql2 += " where Sex = @a"; tj += " where Sex = @a"; hs.Add("@a", "true"); count++; } else if (tb_sex.Text.Trim() == "女") { sql += " where Sex = @a"; sql2 += " where Sex = @a"; tj += " where Sex = @a"; hs.Add("@a", "false"); count++; } } //年龄不为空 if (!string.IsNullOrEmpty(tb_age.Text.Trim())) { int a = DateTime.Now.Year;//获取当前时间的年 try//确保输入的是数字 { int ag = Convert.ToInt32(tb_age.Text.Trim()); int g = a - ag; DateTime d = Convert.ToDateTime(g.ToString() + "-1-1"); if (DropDownList3.SelectedValue == ">=")//小于或等于您输入的年龄,即大于或等于某个时间 { if (count == 0)//前面的一项未输入（性别） { sql += " where Birthday " + DropDownList3.SelectedValue + "@b"; sql2 += " where Birthday " + DropDownList3.SelectedValue + "@b"; tj += " where Birthday " + DropDownList3.SelectedValue + "@b"; } else { sql += " and Birthday " + DropDownList3.SelectedValue + "@b"; sql2 += " and Birthday " + DropDownList3.SelectedValue + "@b"; tj += " and Birthday " + DropDownList3.SelectedValue + "@b"; } hs.Add("@b", d); } else//大于或等于您输入的年龄,即小于或等于某个时间 { DateTime dd = Convert.ToDateTime(g.ToString() + "-12-31"); if (count == 0) { sql += " where Birthday " + DropDownList3.SelectedValue + "@b"; sql2 += " where Birthday " + DropDownList3.SelectedValue + "@b"; tj += " where Birthday " + DropDownList3.SelectedValue + "@b"; } else { sql += " and Birthday " + DropDownList3.SelectedValue + "@b"; sql2 += " and Birthday " + DropDownList3.SelectedValue + "@b"; tj += " and Birthday " + DropDownList3.SelectedValue + "@b"; } hs.Add("@b", dd); } count++; } catch { } } if (!string.IsNullOrEmpty(tb_s.Text.Trim()))//判断专业是否为空 { List<Subject> li = new SubjectData().Select(tb_s.Text.Trim());//调用查询方法模糊查询专业 if (li.Count <= 0)//未查到数据 { } else//查到数据 { int cou = 0;//用于查到的为多条数据 foreach (Subject ub in li) { if (li.Count == 1)//只查到一条数据 { if (count == 0)//性别与年龄输入框都未输入内容 { sql += " where SubjectCode =@c"; sql2 += " where SubjectCode =@c"; tj += " where SubjectCode =@c"; } else { sql += " and SubjectCode =@c"; sql2 += " and SubjectCode =@c"; tj += " and SubjectCode =@c"; } hs.Add("@c", ub.SubjectCode); cou++; count++; } else//查到多条数据 { if (cou == 0)//第一次遍历 { if (count == 0) { sql += " where (SubjectCode =@c"; sql2 += " where (SubjectCode =@c"; tj += " where (SubjectCode =@c"; } else//性别与年龄输入框都未输入内容 { sql += " and (SubjectCode =@c"; sql2 += " and (SubjectCode =@c"; tj += " and (SubjectCode =@c"; } hs.Add("@c", ub.SubjectCode); cou++; } else { sql += " or SubjectCode =@d)"; sql2 += " or SubjectCode =@d)"; tj += " or SubjectCode =@d)"; hs.Add("@d", ub.SubjectCode); } } } } } } Tsql方法
5.组合查询 按钮功能赋予
void Button2_Click(object sender, EventArgs e) { string sql;//拼接查询前PageCount条数据的语句 string sql2;//查询所有的语句 string tj; int count; Tsql(out sql, out sql2,out tj,out count); Repeater1.DataSource = new StudentData().Select(sql, hs);//数据指向 Repeater1.DataBind(); Label2.Text = "1"; Label3.Text = MaxPageNumber(sql2,hs).ToString();//获取当前的最大页 DropDownList2.Items.Clear(); for (int i = 1; i <= MaxPageNumber(sql2,hs); i++)//更新快捷跳转列表框 { DropDownList2.Items.Add(new ListItem(i.ToString(), i.ToString())); } } 组合查询
6.分页代码
void btn_next_Click(object sender, EventArgs e) { int pagec = Convert.ToInt32(Label2.Text) + 1;//获取下一页为第几页 string sql;//拼接查询前PageCount条数据的语句 string sql2;//查询所有的语句 string tj; int count; Tsql(out sql, out sql2, out tj, out count); if (pagec > MaxPageNumber(sql2,hs))//当前为最大页 { return; } else { if(count>0)//进行的是组合查询的下一页跳转 { sql += " and Code not in(select top " + (PageCount * (pagec - 1)) + " Code from Student " + tj + ")"; } else { sql += " where Code not in(select top " + (PageCount * (pagec - 1)) + " Code from Student " + tj + ")"; } } Repeater1.DataSource = new StudentData().Select(sql, hs);//数据指向 Repeater1.DataBind(); Label2.Text = pagec.ToString();//更新当前页面 DropDownList2.SelectedValue = pagec.ToString(); } 下一页
void btn_prev_Click(object sender, EventArgs e) { int pagec = Convert.ToInt32(Label2.Text) - 1;//获取上一页为第几页 string sql;//拼接查询前PageCount条数据的语句 string sql2; string tj; int count; Tsql(out sql, out sql2, out tj, out count); if (pagec <= 0)//当前为第一页 { return; } if (count > 0)//进行的是组合查询的上一页跳转 { sql += " and Code not in(select top " + (PageCount * (pagec - 1)) + " Code from Student " + tj + ")"; } else { sql += " where Code not in(select top " + (PageCount * (pagec - 1)) + " Code from Student " + tj + ")"; } List<Student> list = new StudentData().Select(sql, hs);//数据指向 Repeater1.DataSource = list; Repeater1.DataBind(); Label2.Text = pagec.ToString();//更新当前页面 DropDownList2.SelectedValue = pagec.ToString(); } 上一页
void btn_first_Click(object sender, EventArgs e) { string sql; string sql2; string tj; int count; Tsql(out sql, out sql2, out tj, out count); List<Student> list = new StudentData().Select(sql, hs);//数据指向 Repeater1.DataSource = list; Repeater1.DataBind(); Label2.Text = "1"; DropDownList2.SelectedValue = "1"; } 跳转到第一页
void btn_end_Click(object sender, EventArgs e) { string sql; string sql2; string tj; int count; Tsql(out sql, out sql2, out tj, out count); if (count > 0)//进行的是组合查询的末页跳转 { sql += " and Code not in(select top " + (PageCount * (MaxPageNumber(sql2,hs) - 1)) + " Code from Student " + tj + ")"; } else { sql += " where Code not in(select top " + (PageCount * (MaxPageNumber(sql2, hs) - 1)) + " Code from Student " + tj + ")"; } List<Student> list = new StudentData().Select(sql, hs);//数据指向 Repeater1.DataSource = list; Repeater1.DataBind(); Label2.Text = MaxPageNumber(sql2,hs).ToString(); DropDownList2.SelectedValue = MaxPageNumber(sql2,hs).ToString(); } 最后一页跳转
void DropDownList2_SelectedIndexChanged(object sender, EventArgs e) { string sql; string sql2; string tj; int count; Tsql(out sql, out sql2, out tj, out count); if (count > 0)//进行的是组合查询的快捷跳转 { sql += " and Code not in(select top " + (PageCount * (Convert.ToInt32(DropDownList2.SelectedValue) - 1)) + " Code from Student " + tj + ")"; } else { sql += " where Code not in(select top " + (PageCount * (Convert.ToInt32(DropDownList2.SelectedValue) - 1)) + " Code from Student " + tj + ")"; } Repeater1.DataSource = new StudentData().Select(sql, hs);//数据指向 Repeater1.DataBind(); Label2.Text = DropDownList2.SelectedValue; } 快捷跳转
