一、分页
1.写查询方法：
public ListSelect(int PageCount, int PageNumber) { //PageCount为每页显示条数，PageNumber为当前第几页 List list = new List (); 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 = 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; }
2.C#代码
int PageCount = 5; //每页显示条数 protected void Page_Load(object sender, EventArgs e) { if(!IsPostBack) { List 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(new ListItem(i.ToString(), i.ToString())); }} public int MaxPageNumber() { List list = new StudentData().select(); double de = list.Count / (PageCount * 1.0); int aa = Convert.ToInt32(Math.Ceiling(de));//取上限 return aa; } void DropDownList2_SelectedIndexChanged(object sender, EventArgs e) { Repeater1.DataSource = new StudentData().Select(PageCount, Convert.ToInt32(DropDownList2.SelectedValue)); Repeater1.DataBind(); Label2.Text = DropDownList2.SelectedValue; } void btn_end_Click(object sender, EventArgs e) { List list = new StudentData().Select(PageCount, MaxPageNumber()); Repeater1.DataSource = list; Repeater1.DataBind(); Label2.Text = MaxPageNumber().ToString(); } void btn_first_Click(object sender, EventArgs e) { List list = new StudentData().Select(PageCount, 1); Repeater1.DataSource = list; Repeater1.DataBind(); Label2.Text ="1"; } void btn_prev_Click(object sender, EventArgs e) { int pagec = Convert.ToInt32(Label2.Text) - 1; if (pagec <=0)//判断是不是第一页，是的话什么也不干 { return; } List list = new StudentData().Select(PageCount, pagec); Repeater1.DataSource = list; Repeater1.DataBind(); Label2.Text = pagec.ToString(); } void btn_next_Click(object sender, EventArgs e) { int pagec = Convert.ToInt32(Label2.Text) + 1; if (pagec > MaxPageNumber())// 判断是不是最后一页，是的话什么也不干 { return; } Repeater1.DataSource = new StudentData().Select(PageCount,pagec); Repeater1.DataBind(); Label2.Text = pagec.ToString(); } 二、组合查询
1.查询方法
1 public ListSelect(string tsql,Hashtable hh) 2 { 3 List list = new List (); 4 cmd.CommandText = tsql; 5 cmd.Parameters.Clear(); 6 foreach( string s in hh.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 = new Student();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 return list;28 }
public ListSelect(string name) { List list = new List (); 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; }
2.c#代码
1 void Button2_Click(object sender, 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 else37 {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 else50 {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 li = new SubjectData().Select(tb_s.Text.Trim());63 if (li.Count <= 0)64 {65 }66 else67 { string u ="";68 int cou = 0;69 foreach(Subject ub in li)70 {71 u = ub.SubjectCode;72 if (cou == 0)//第一条数据73 {74 if (count == 0)75 {76 tsql += " where SubjectCode =@c";77 }78 else79 {80 tsql += " and SubjectCode =@c";81 }82 hs.Add("@c", u);83 cou++;84 }85 else86 {87 tsql += " or SubjectCode =@d";88 hs.Add("@d", u);89 }90 }91 }92 }93 Repeater1.DataSource = new StudentData().Select(tsql, hs);94 Repeater1.DataBind();95 }