首页 > 代码库 > webform组合查询和分页

webform组合查询和分页

1、组合查询
(1)数据访问类

 
//参数1:SQL语句 参数2:哈希表
public List<Users> chas(string s,Hashtable has) { List<Users> List = new List<Users>(); Users u = null; com.CommandText = s; com.Parameters.Clear(); foreach(string aa in has.Keys) { com.Parameters.Add(aa,has[aa]); } con.Open(); SqlDataReader dr = com.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { u = new Users() { UserName = dr[0].ToString(), PassWord = dr[1].ToString(), NickName = dr[2].ToString(), Sex = Convert.ToBoolean(dr[3]), Birthday = Convert.ToDateTime(dr[4]), Phone = dr[5].ToString(), Email = dr[6].ToString(), Natoin = dr[7].ToString(), Area = dr[8].ToString(), }; List.Add(u); } } con.Close(); return List; }

(2)查找按钮事件

 Hashtable has = new Hashtable();//实例化哈希表,作用防攻击

//拼sql语句
string sqls = "select *from Users "; if(TextBox1.Text!="") { sqls += " where UserName like @a"; has.Add("@a", "%" + TextBox1.Text.Trim() + "%");//将用户输入的内容放进哈希表,防攻击 count++; } if(Drop_nation.SelectedValue !="0") { if(count !=0)//如果上面有查询条件接and { sqls += " and Nation=@b"; } else//如果上面没有查询条件接where { sqls += " WHERE Nation=@b"; count++; } string sss=Drop_nation.SelectedValue.ToString(); has.Add("@b",sss ); count++; }

      //Repeater数据绑定
      Repeater1.DataSource = new Usersdata().chas(sqls,has);
      Repeater1.DataBind();
      count = 0;

2、分页
前台

           当前第<asp:Label ID="Label1" runat="server" Text=""></asp:Label>页 
            共<asp:Label ID="Label2" runat="server" Text=""></asp:Label><asp:LinkButton ID="LinkButton1" runat="server" >首页</asp:LinkButton>
            <asp:LinkButton ID="LinkButton2" runat="server">上一页</asp:LinkButton>
            <asp:LinkButton ID="LinkButton3" runat="server">下一页</asp:LinkButton>
            <asp:LinkButton ID="LinkButton4" runat="server">末页</asp:LinkButton>
            跳到
            <asp:DropDownList ID="DropDownList1" AutoPostBack="true" runat="server"></asp:DropDownList>

后台

(1)数据访问类

//参数1:第几页 参数2:每页显示的个数
public
List<Users> cha(int page,int pagecode) { List<Users> List = new List<Users>(); Users u = null;
//注意:sql语句 com.CommandText
= "select top " + pagecode + " *FROM Users where UserName not in (select top " + ((page - 1) * pagecode) + " UserName FROM Users)"; con.Open(); SqlDataReader dr = com.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { u = new Users() { UserName = dr[0].ToString(), PassWord = dr[1].ToString(), NickName = dr[2].ToString(), Sex = Convert.ToBoolean(dr[3]), Birthday = Convert.ToDateTime(dr[4]), Phone = dr[5].ToString(), Email = dr[6].ToString(), Natoin = dr[7].ToString(), Area = dr[8].ToString(), }; List.Add(u); } } con.Close(); return List; }

(2)

//全局变量,每页显示的个数
int
pagecode = 5; //跳到指定页数 void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) { int a=Convert.ToInt32(DropDownList1.SelectedValue);//获取页数
//显示数据 Repeater1.DataSource
= new Usersdata().cha(a, pagecode); Repeater1.DataBind(); Label1.Text = a.ToString();//显示当前页 } //下一页 void LinkButton3_Click(object sender, EventArgs e) { Label1.Text = (Convert.ToInt32(Label1.Text) + 1).ToString();//显示下一页 if (Convert.ToInt32(Label1.Text) <= MAXPAGE(pagecode))//判断是否小于最大页 { Repeater1.DataSource = new Usersdata().cha(Convert.ToInt32(Label1.Text), pagecode); Repeater1.DataBind(); } else { Label1.Text = MAXPAGE(pagecode).ToString(); return; } } //上一页 void LinkButton2_Click(object sender, EventArgs e) { Label1.Text =(Convert.ToInt32(Label1.Text) - 1).ToString();//显示上一页 if (Convert.ToInt32(Label1.Text)<=0)//判断是否大于0页 { Label1.Text = "1"; return; } Repeater1.DataSource = new Usersdata().cha(Convert.ToInt32(Label1.Text), pagecode); Repeater1.DataBind(); } //末页 void LinkButton4_Click(object sender, EventArgs e) { Repeater1.DataSource = new Usersdata().cha(MAXPAGE(pagecode), pagecode); Repeater1.DataBind(); Label1.Text = MAXPAGE(pagecode).ToString(); } //首页 void LinkButton1_Click(object sender, EventArgs e) { Repeater1.DataSource = new Usersdata().cha(1, pagecode); Repeater1.DataBind(); Label1.Text = "1"; } //最大页数 private static int MAXPAGE(int pagecode) { double ye = (new Usersdata().cha().Count) / (pagecode *1.0); int maxye = Convert.ToInt32(Math.Ceiling(ye)); return maxye; }

3 组合查询和分页结合使用
先查询后分页(拼SQL语句)

数据访问类:同上面的组合查询

        string sqls = "select top " + pagecode + " *from Users ";//带分页和查询条件的SQL语句
        string sql2 = "select *from Users";//带查询条件的SQL的语句
        string sqls1 = "";//查询条件sql语句
        if (TextBox1.Text != "")
        {
            sqls += " where UserName like @a";
            sqls1 += " where UserName like @a";
            has.Add("@a", "%" + TextBox1.Text.Trim() + "%");
            count++;
        }
        if (Drop_nation.SelectedValue != "0")
        {
            if (count != 0)
            {
                sqls += " and Nation=@b";
                sqls1 += " and Nation=@b";
            }
            else
            {
                sqls += " WHERE Nation=@b";
                sqls1 += " WHERE Nation=@b";
                count++;
            }
            string sss = Drop_nation.SelectedValue.ToString();
            has.Add("@b", sss);
            count++;
        }

 if (count > 0)
        {
            sqls += " and UserName not in (select top " + ((Convert.ToInt32(Label1.Text) - 1) * pagecode) + " UserName from Users" + sqls1 + ")";
        }
        else 
        {
            sqls += " where UserName not in (select top " + ((Convert.ToInt32(Label1.Text) - 1) * pagecode) + " UserName from Users)";
        }
//用label记录sql语句并隐藏,当上一页、下一页等可以从label中取出用

Label3.Text = sqls;
Label4.Text = sqls1;
Label5.Text = sql2 + sqls1;

//Repeater数据绑定
       Repeater1.DataSource = new Usersdata().chas(Label3.Text, has);
        Repeater1.DataBind();

 

webform组合查询和分页