首页 > 代码库 > Webform（分页与组合查询配合使用）
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部分，最大页方法
1 int PageCount = 5; //每页显示条数 2 Hashtable hs = new Hashtable(); 3 protected void Page_Load(object sender, EventArgs e) 4 { 5 if(!IsPostBack) 6 { 7 string tsql = "select top "+PageCount+" *from Student";//查询前PageCount条数据 8 //Repeater1数据源指向 9 List<Student> list = new StudentData().Select(tsql,hs); 10 Repeater1.DataSource = list; 11 Repeater1.DataBind(); 12 Label2.Text = "1";//第一页 13 //获取最大页 14 string sql = "select *from Student"; 15 Label3.Text = MaxPageNumber(sql,hs).ToString(); 16 for (int i = 1; i <= MaxPageNumber(sql,hs); i++)//给可快速跳转列表框赋值 17 { 18 DropDownList2.Items.Add(new ListItem(i.ToString(), i.ToString())); 19 } 20 } 21 }
1 public int MaxPageNumber(string sql, Hashtable hs) 2 { 3 List<Student> list = new StudentData().Select(sql, hs);//查询所有数据 4 5 double de = list.Count / (PageCount * 1.0); 6 7 int aa = Convert.ToInt32(Math.Ceiling(de));//取上限 8 return aa; 9 }
4.根据组合查询拼接语句方法
1 /// <summary> 2 /// 3 /// </summary> 4 /// <param name="sql">拼接查询前PageCount条数据的语句</param> 5 /// <param name="sql2">查询所有的语句</param> 6 /// <param name="tj">用于分页查询与sql等拼接</param> 7 /// <param name="count">判断前几项是否为空</param> 8 private void Tsql(out string sql, out string sql2,out string tj,out int count) 9 { 10 count = 0; 11 sql = "select top " + PageCount + " *from Student"; 12 sql2 = "select *from Student"; 13 tj = ""; 14 //性别不为空 15 if (!string.IsNullOrEmpty(tb_sex.Text.Trim())) 16 {//判断输入的是男是女，其它输入默认为未输入内容 17 if (tb_sex.Text.Trim() == "男") 18 { 19 sql += " where Sex = @a"; 20 sql2 += " where Sex = @a"; 21 tj += " where Sex = @a"; 22 hs.Add("@a", "true"); 23 count++; 24 } 25 else if (tb_sex.Text.Trim() == "女") 26 { 27 sql += " where Sex = @a"; 28 sql2 += " where Sex = @a"; 29 tj += " where Sex = @a"; 30 hs.Add("@a", "false"); 31 count++; 32 } 33 } 34 //年龄不为空 35 if (!string.IsNullOrEmpty(tb_age.Text.Trim())) 36 { 37 int a = DateTime.Now.Year;//获取当前时间的年 38 try//确保输入的是数字 39 { 40 int ag = Convert.ToInt32(tb_age.Text.Trim()); 41 int g = a - ag; 42 DateTime d = Convert.ToDateTime(g.ToString() + "-1-1"); 43 if (DropDownList3.SelectedValue =http://www.mamicode.com/= ">=")//小于或等于您输入的年龄,即大于或等于某个时间 44 { 45 if (count == 0)//前面的一项未输入（性别） 46 { 47 sql += " where Birthday " + DropDownList3.SelectedValue + "@b"; 48 sql2 += " where Birthday " + DropDownList3.SelectedValue + "@b"; 49 tj += " where Birthday " + DropDownList3.SelectedValue + "@b"; 50 } 51 else 52 { 53 sql += " and Birthday " + DropDownList3.SelectedValue + "@b"; 54 sql2 += " and Birthday " + DropDownList3.SelectedValue + "@b"; 55 tj += " and Birthday " + DropDownList3.SelectedValue + "@b"; 56 } 57 hs.Add("@b", d); 58 } 59 else//大于或等于您输入的年龄,即小于或等于某个时间 60 { 61 DateTime dd = Convert.ToDateTime(g.ToString() + "-12-31"); 62 if (count == 0) 63 { 64 sql += " where Birthday " + DropDownList3.SelectedValue + "@b"; 65 sql2 += " where Birthday " + DropDownList3.SelectedValue + "@b"; 66 tj += " where Birthday " + DropDownList3.SelectedValue + "@b"; 67 } 68 else 69 { 70 sql += " and Birthday " + DropDownList3.SelectedValue + "@b"; 71 sql2 += " and Birthday " + DropDownList3.SelectedValue + "@b"; 72 tj += " and Birthday " + DropDownList3.SelectedValue + "@b"; 73 } 74 hs.Add("@b", dd); 75 } 76 count++; 77 } 78 catch 79 { 80 } 81 } 82 if (!string.IsNullOrEmpty(tb_s.Text.Trim()))//判断专业是否为空 83 { 84 List<Subject> li = new SubjectData().Select(tb_s.Text.Trim());//调用查询方法模糊查询专业 85 if (li.Count <= 0)//未查到数据 86 { 87 } 88 else//查到数据 89 { 90 int cou = 0;//用于查到的为多条数据 91 foreach (Subject ub in li) 92 { 93 if (li.Count == 1)//只查到一条数据 94 { 95 if (count == 0)//性别与年龄输入框都未输入内容 96 { 97 sql += " where SubjectCode =@c"; 98 sql2 += " where SubjectCode =@c"; 99 tj += " where SubjectCode =@c"; 100 } 101 else 102 { 103 sql += " and SubjectCode =@c"; 104 sql2 += " and SubjectCode =@c"; 105 tj += " and SubjectCode =@c"; 106 } 107 hs.Add("@c", ub.SubjectCode); 108 cou++; 109 count++; 110 } 111 else//查到多条数据 112 { 113 if (cou == 0)//第一次遍历 114 { 115 if (count == 0) 116 { 117 sql += " where (SubjectCode =@c"; 118 sql2 += " where (SubjectCode =@c"; 119 tj += " where (SubjectCode =@c"; 120 } 121 else//性别与年龄输入框都未输入内容 122 { 123 sql += " and (SubjectCode =@c"; 124 sql2 += " and (SubjectCode =@c"; 125 tj += " and (SubjectCode =@c"; 126 } 127 hs.Add("@c", ub.SubjectCode); 128 cou++; 129 } 130 else 131 { 132 sql += " or SubjectCode =@d)"; 133 sql2 += " or SubjectCode =@d)"; 134 tj += " or SubjectCode =@d)"; 135 hs.Add("@d", ub.SubjectCode); 136 } 137 } 138 139 } 140 } 141 } 142 }
5.组合查询 按钮功能赋予
1 void Button2_Click(object sender, EventArgs e) 2 { 3 string sql;//拼接查询前PageCount条数据的语句 4 string sql2;//查询所有的语句 5 string tj; 6 int count; 7 Tsql(out sql, out sql2,out tj,out count); 8 Repeater1.DataSource = new StudentData().Select(sql, hs);//数据指向 9 Repeater1.DataBind(); 10 Label2.Text = "1"; 11 Label3.Text = MaxPageNumber(sql2,hs).ToString();//获取当前的最大页 12 DropDownList2.Items.Clear(); 13 for (int i = 1; i <= MaxPageNumber(sql2,hs); i++)//更新快捷跳转列表框 14 { 15 DropDownList2.Items.Add(new ListItem(i.ToString(), i.ToString())); 16 } 17 }
6.分页代码
1 void btn_next_Click(object sender, EventArgs e) 2 { 3 int pagec = Convert.ToInt32(Label2.Text) + 1;//获取下一页为第几页 4 string sql;//拼接查询前PageCount条数据的语句 5 string sql2;//查询所有的语句 6 string tj; 7 int count; 8 Tsql(out sql, out sql2, out tj, out count); 9 if (pagec > MaxPageNumber(sql2,hs))//当前为最大页 10 { 11 return; 12 } 13 else 14 { 15 if(count>0)//进行的是组合查询的下一页跳转 16 { 17 sql += " and Code not in(select top " + (PageCount * (pagec - 1)) + " Code from Student " + tj + ")"; 18 } 19 else 20 { 21 sql += " where Code not in(select top " + (PageCount * (pagec - 1)) + " Code from Student " + tj + ")"; 22 } 23 } 24 Repeater1.DataSource = new StudentData().Select(sql, hs);//数据指向 25 Repeater1.DataBind(); 26 Label2.Text = pagec.ToString();//更新当前页面 27 DropDownList2.SelectedValue =http://www.mamicode.com/ pagec.ToString(); 28 }
1 void btn_prev_Click(object sender, EventArgs e) 2 { 3 int pagec = Convert.ToInt32(Label2.Text) - 1;//获取上一页为第几页 4 string sql;//拼接查询前PageCount条数据的语句 5 string sql2; 6 string tj; 7 int count; 8 Tsql(out sql, out sql2, out tj, out count); 9 if (pagec <= 0)//当前为第一页 10 { 11 return; 12 } 13 if (count > 0)//进行的是组合查询的上一页跳转 14 { 15 sql += " and Code not in(select top " + (PageCount * (pagec - 1)) + " Code from Student " + tj + ")"; 16 } 17 else 18 { 19 sql += " where Code not in(select top " + (PageCount * (pagec - 1)) + " Code from Student " + tj + ")"; 20 } 21 List<Student> list = new StudentData().Select(sql, hs);//数据指向 22 Repeater1.DataSource = list; 23 Repeater1.DataBind(); 24 Label2.Text = pagec.ToString();//更新当前页面 25 DropDownList2.SelectedValue =http://www.mamicode.com/ pagec.ToString(); 26 }
1 void btn_first_Click(object sender, EventArgs e) 2 { 3 string sql; 4 string sql2; 5 string tj; 6 int count; 7 Tsql(out sql, out sql2, out tj, out count); 8 List<Student> list = new StudentData().Select(sql, hs);//数据指向 9 Repeater1.DataSource = list; 10 Repeater1.DataBind(); 11 Label2.Text = "1"; 12 DropDownList2.SelectedValue = http://www.mamicode.com/"1"; 13 }
1 void btn_end_Click(object sender, EventArgs e) 2 { 3 string sql; 4 string sql2; 5 string tj; 6 int count; 7 Tsql(out sql, out sql2, out tj, out count); 8 if (count > 0)//进行的是组合查询的末页跳转 9 { 10 sql += " and Code not in(select top " + (PageCount * (MaxPageNumber(sql2,hs) - 1)) + " Code from Student " + tj + ")"; 11 } 12 else 13 { 14 sql += " where Code not in(select top " + (PageCount * (MaxPageNumber(sql2, hs) - 1)) + " Code from Student " + tj + ")"; 15 } 16 List<Student> list = new StudentData().Select(sql, hs);//数据指向 17 Repeater1.DataSource = list; 18 Repeater1.DataBind(); 19 Label2.Text = MaxPageNumber(sql2,hs).ToString(); 20 DropDownList2.SelectedValue =http://www.mamicode.com/ MaxPageNumber(sql2,hs).ToString(); 21 }
1 void DropDownList2_SelectedIndexChanged(object sender, EventArgs e) 2 { 3 string sql; 4 string sql2; 5 string tj; 6 int count; 7 Tsql(out sql, out sql2, out tj, out count); 8 if (count > 0)//进行的是组合查询的快捷跳转 9 { 10 sql += " and Code not in(select top " + (PageCount * (Convert.ToInt32(DropDownList2.SelectedValue) - 1)) + " Code from Student " + tj + ")"; 11 } 12 else 13 { 14 sql += " where Code not in(select top " + (PageCount * (Convert.ToInt32(DropDownList2.SelectedValue) - 1)) + " Code from Student " + tj + ")"; 15 } 16 Repeater1.DataSource = new StudentData().Select(sql, hs);//数据指向 17 Repeater1.DataBind(); 18 Label2.Text = DropDownList2.SelectedValue; 19 }
预览图：
Webform（分页与组合查询配合使用）
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。