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();