.net 组合查询
首先写HTML布局：
<style type="text/css"> #tab1 { width: 100%; text-align: center; background-color: navy; border-spacing: 1px; /*边框之间的空隙变成1像素*/ } #tab1 thead tr { color: white; } #tab1 tbody tr { background-color: white; } #tab1 td { padding: 7px 5px; } #tab1 tbody tr:hover { background-color: #f00; /*//鼠标移入时候的背景颜色*/ } </style> </head> <body> <form id="form1" runat="server"> <div> <table id="tab1"> <br /> 昵称：<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> 性别：<asp:DropDownList ID="DropDownList1" runat="server"> <asp:ListItem Value="true">全部</asp:ListItem> <asp:ListItem >男</asp:ListItem> <asp:ListItem>女</asp:ListItem> </asp:DropDownList> 生日：<asp:DropDownList ID="DropDownList2" runat="server"> <asp:ListItem Value="true">全部</asp:ListItem> <asp:ListItem Value=">=">大于等于</asp:ListItem> <asp:ListItem Value="<=">小于等于</asp:ListItem> </asp:DropDownList> <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="查询" /> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> <br /> <br /> <br /> <thead> <tr> <td>ID</td> <td>编号</td> <td>用户名</td> <td>密码</td> <td>昵称</td> <td>性别</td> <td>生日</td> <td>民族</td> </tr> </thead> <tbody> <asp:Repeater ID="Repeater1" runat="server"> <%-- 在内容里加Repeater和TtemTemplate--%> <ItemTemplate> <tr> <td><%#Eval("ID") %></td> <td><%#Eval("ucode") %></td> <td><%#Eval("username") %></td> <td><%#Eval("password") %></td> <td><%#Eval("nickname") %></td> <td><%#Eval("sexstr") %> <%-- <img src="<%#Eval("sexImg") %>" />--%> <%--使用图片当性别区分--%> </td> <td><%#Eval("birthday","{0:yyyy年MM月dd日}") %></td> <%-- 日期的转换--%> <td><%#Eval("NationName") %></td> </tr> </ItemTemplate> </asp:Repeater> </tbody> </table> </div> </form> </body> </html>
然后后台代码：
protected void Page_Load(object sender, EventArgs e) { Button1.Click += Button1_Click; if(!IsPostBack) { Repeater1.DataSource = new UsersData().SelectAll(); Repeater1.DataBind(); } } void Button1_Click(object sender, EventArgs e) { //获取要查询的条件,拼接sql语句 int count = 0; string sql = "select *from Users"; Hashtable hs = new Hashtable(); if(TextBox1.Text.Trim().Length>0) { sql += " where nickname like @a"; hs.Add("@a","%"+TextBox1.Text.Trim()+"%"); count++; } if (DropDownList1.SelectedIndex!=0 ) //索引为0就是 不选全部 { if (count > 0) { sql += " and sex =" + (DropDownList1.SelectedValue == "男" ? "1" : "0"); } else { sql += " where sex =" + (DropDownList1.SelectedValue == "男" ? "1" : "0"); } count++; } if ( TextBox2.Text.Trim().Length > 0) { if (count > 0) { sql += " and Year(birthday)" + DropDownList2.SelectedValue + TextBox2.Text; } else { sql += " where Year(birthday)" + DropDownList2.SelectedValue + TextBox2.Text; } count++; } //Label1.Text = sql; //测试用的 //执行语句并接收返回值绑定,展示数据； Repeater1.DataSource = new UsersData().Selectsql(sql, hs); Repeater1.DataBind(); }
用到的方法：
public List<Users> SelectAll() { List<Users> ulist = new List<Users>(); cmd.CommandText = "select *from Users"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Users u = new Users(); u.ID = Convert.ToInt32(dr[0]); u.ucode = dr[1].ToString(); u.username = dr[2].ToString(); u.password = dr[3].ToString(); u.nickname = dr[4].ToString(); u.sex = Convert.ToBoolean(dr[5]); u.birthday = Convert.ToDateTime(dr[6]); u.nation = dr[7].ToString(); ulist.Add(u); } conn.Close(); return ulist; } public List<Users> Selectcount(int NowNumber, int Count) //NowNumber当前要看的页数，Count当前页的条数 { List<Users> ulist = new List<Users>(); cmd.CommandText = "select top " + Count + " *from Users where ID not in(select top " + ((NowNumber - 1) * Count) + " ID from Users)"; //分页方法 ，把count放进去，查询前几条。后面也页数条数，就是前去前面的页数的条数。 conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Users u = new Users(); u.ID = Convert.ToInt32(dr[0]); u.ucode = dr[1].ToString(); u.username = dr[2].ToString(); u.password = dr[3].ToString(); u.nickname = dr[4].ToString(); u.sex = Convert.ToBoolean(dr[5]); u.birthday = Convert.ToDateTime(dr[6]); u.nation = dr[7].ToString(); ulist.Add(u); } conn.Close(); return ulist; } public List<Users> Selectsql(string sql,Hashtable hs) { List<Users> ulist = new List<Users>(); cmd.CommandText = sql; cmd.Parameters.Clear(); foreach(string s in hs.Keys) { cmd.Parameters.AddWithValue(s,hs[s]); } conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Users u = new Users(); u.ID = Convert.ToInt32(dr["ID"]); u.ucode = dr["ucode"].ToString(); u.username = dr["username"].ToString(); u.password = dr["password"].ToString(); u.nickname = dr["nickname"].ToString(); u.sex = Convert.ToBoolean(dr["sex"]); u.birthday = Convert.ToDateTime(dr["birthday"]); u.nation = dr["nation"].ToString(); ulist.Add(u); } conn.Close(); return ulist; }
所有的的理解都写在了代码里面，方便下次看的时候理解。
