Webform（分页、组合查询）

一、分页

1.写查询方法：

public List<Student> Select(int PageCount, int PageNumber)
    {//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 = 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<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(new ListItem(i.ToString(), i.ToString()));
    }
}

Page_Load

public int MaxPageNumber()
    {
        List<Student> 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<Student> list = new StudentData().Select(PageCount, MaxPageNumber());
        Repeater1.DataSource = list;
        Repeater1.DataBind();
        Label2.Text = MaxPageNumber().ToString();
    }

跳转到最后一页

void btn_first_Click(object sender, EventArgs e)
    {
        List<Student> 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<Student> 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 List<Student> Select(string tsql,Hashtable hh)
 2     {
 3         List<Student> list = new List<Student>();
 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 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;
    }

View Code

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                  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 = new SubjectData().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 in li)
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 = new StudentData().Select(tsql, hs);
94         Repeater1.DataBind();
95     }

查询按钮赋功能