摘要：
一、分页1.写查询方法：publicListSelect(intPageCount,intPageNumber){//PageCount为每页显示条数，PageNumber为当前第几页Listlist=newList();cmd.CommandText="selecttop"+PageCount+"*fromStudentwhereCodeno
一、分页
1.写查询方法：
public List<Student> Select(int PageCount, intPageNumber) {//PageCount为每页显示条数，PageNumber为当前第几页 List<Student> list = new List<Student>(); cmd.CommandText = "select top " + PageCount + "*from Student where Code not in (select top "+(PageCount * (PageNumber - 1))+"Code from Student)"; conn.Open(); SqlDataReader dr =cmd.ExecuteReader(); if(dr.HasRows) { while(dr.Read()) { Student s = newStudent(); 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(); returnlist; }
2.C#代码
int PageCount = 5; //每页显示条数 protected void Page_Load(objectsender, EventArgs e) { if(!IsPostBack) { List<Student> list = new StudentData().Select(PageCount,1); Repeater1.DataSource =list; Repeater1.DataBind(); Label2.Text = "1";//第一页 Label3.Text = MaxPageNumber().ToString();//总页数 } for (int i = 1; i <= MaxPageNumber(); i++) { DropDownList2.Items.Add(newListItem(i.ToString(), i.ToString())); } }
public intMaxPageNumber() { List<Student> list = new StudentData().select(); double de = list.Count / (PageCount * 1.0); int aa = Convert.ToInt32(Math.Ceiling(de));//取上限 returnaa; }
void DropDownList2_SelectedIndexChanged(objectsender, EventArgs e) { Repeater1.DataSource = newStudentData().Select(PageCount, Convert.ToInt32(DropDownList2.SelectedValue)); Repeater1.DataBind(); Label2.Text =DropDownList2.SelectedValue; }
void btn_end_Click(objectsender, EventArgs e) { List<Student> list = newStudentData().Select(PageCount, MaxPageNumber()); Repeater1.DataSource =list; Repeater1.DataBind(); Label2.Text =MaxPageNumber().ToString(); }
void btn_first_Click(objectsender, EventArgs e) { List<Student> list = new StudentData().Select(PageCount, 1); Repeater1.DataSource =list; Repeater1.DataBind(); Label2.Text ="1"; }
void btn_prev_Click(objectsender, EventArgs e) { int pagec = Convert.ToInt32(Label2.Text) - 1; if (pagec <=0)//判断是不是第一页，是的话什么也不干 { return; } List<Student> list = newStudentData().Select(PageCount, pagec); Repeater1.DataSource =list; Repeater1.DataBind(); Label2.Text =pagec.ToString(); }
void btn_next_Click(objectsender, EventArgs e) { int pagec = Convert.ToInt32(Label2.Text) + 1; if (pagec > MaxPageNumber())//判断是不是最后一页，是的话什么也不干 { return; } Repeater1.DataSource = newStudentData().Select(PageCount,pagec); Repeater1.DataBind(); Label2.Text =pagec.ToString(); }
二、组合查询
1.查询方法
1 public List<Student> Select(stringtsql,Hashtable hh) 2 { 3 List<Student> list = new List<Student>(); 4 cmd.CommandText =tsql; 5 cmd.Parameters.Clear(); 6 foreach( string s inhh.Keys) 7 { 8 cmd.Parameters.Add(s,hh[s]); 9 } 10 conn.Open(); 11 SqlDataReader dr =cmd.ExecuteReader(); 12 if(dr.HasRows) 13 { 14 while(dr.Read()) 15 { 16 Student s = newStudent(); 17 s.Code = dr[0].ToString(); 18 s.Name = dr[1].ToString(); 19 s.Sex = Convert.ToBoolean(dr[2]); 20 s.Birthday = Convert.ToDateTime(dr[3]); 21 s.SubjectCode = dr[4].ToString(); 22 s.Nation = dr[5].ToString(); 23 list.Add(s); 24 } 25 } 26 conn.Close(); 27 returnlist; 28 }
public List<Subject> Select(stringname) { 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 = newSubject(); s.SubjectCode = dr[0].ToString(); s.SubjectName = dr[1].ToString(); list.Add(s); } } conn.Close(); returnlist; }
2.c#代码
1 void Button2_Click(objectsender, EventArgs e) 2 { 3 int count = 0;//判断tsql是否含有where 4 Hashtable hs = new Hashtable();//哈希表 5 string tsql = "select * from Student"; 6 //性别 7 if(!string.IsNullOrEmpty(tb_sex.Text.Trim()))//输入的性别非空 8 { 9 if (tb_sex.Text.Trim() == "男") 10 { 11 tsql += "where Sex = @a"; 12 hs.Add("@a", "true"); 13 count++; 14 } 15 else if (tb_sex.Text.Trim() == "女") 16 { 17 tsql += "where Sex = @a"; 18 hs.Add("@a", "false"); 19 count++; 20 } 21 } 22 //年龄 23 if (!string.IsNullOrEmpty(tb_age.Text.Trim()))//判断年龄输入框是否为空 24 { 25 int a =DateTime.Now.Year; 26 try {//异常保护，输入框只能是数字 27 int ag=Convert.ToInt32(tb_age.Text.Trim()); 28 int g = a -ag; 29 DateTime d = Convert.ToDateTime(g.ToString()+"-1-1"); 30 if (DropDownList3.SelectedValue == ">=")//列表框显示值为<=,小于某个年龄 31 { 32 if (count == 0) 33 { 34 tsql += "where Birthday " + DropDownList3.SelectedValue + "@b"; 35 } 36 else 37 { 38 tsql += "and Birthday " + DropDownList3.SelectedValue + "@b"; 39 } 40 hs.Add("@b", d); 41 } 42 else//大于某个年龄 43 { 44 DateTime dd = Convert.ToDateTime(g.ToString() + "-12-31"); 45 if (count == 0) 46 { 47 tsql += "where Birthday " + DropDownList3.SelectedValue + "@b"; 48 } 49 else 50 { 51 tsql += "and Birthday " + DropDownList3.SelectedValue + "@b"; 52 } 53 hs.Add("@b", dd); 54 } 55 count++; 56 } 57 catch{ 58 } 59 } 60 if (!string.IsNullOrEmpty(tb_s.Text.Trim()))//专业非空 61 { 62 List<Subject> li = newSubjectData().Select(tb_s.Text.Trim()); 63 if (li.Count <= 0) 64 { 65 } 66 else 67 { string u =""; 68 int cou = 0; 69 foreach(Subject ub inli) 70 { 71 u =ub.SubjectCode; 72 if (cou == 0)//第一条数据 73 { 74 if (count == 0) 75 { 76 tsql += "where SubjectCode =@c"; 77 } 78 else 79 { 80 tsql += "and SubjectCode =@c"; 81 } 82 hs.Add("@c", u); 83 cou++; 84 } 85 else 86 { 87 tsql += "or SubjectCode =@d"; 88 hs.Add("@d", u); 89 } 90 } 91 } 92 } 93 Repeater1.DataSource = newStudentData().Select(tsql, hs); 94 Repeater1.DataBind(); 95 }