实现按照各种条件对数据库进行综合查询
基本功能：可以根据用户需要灵活查询
重难点：各种条件的可能、限制。
public List<users> selectA( string str,Hashtable h) { List<users> ulist = new List<users>(); cmd.CommandText = str; conn.Open(); foreach (string s in h.Keys) { cmd.Parameters.Add(s, h[s]); } SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { users u = new users(); u.Ids = Convert.ToInt32(dr[0]); u.Username = dr[1].ToString(); u.Password = dr[2].ToString(); u.Nickname = dr[3].ToString(); u.Sex = Convert.ToBoolean(dr[4]); u.Birthday = Convert.ToDateTime(dr[5]); u.Nation = dr[6].ToString(); ulist.Add(u); } } conn.Close(); return ulist; } //查询共有多少条信息————— 条件查询用 public int selectAllline(string str, Hashtable h) { int a = 0; cmd.CommandText = str; conn.Open(); foreach (string s in h.Keys) { cmd.Parameters.Add(s, h[s]); } SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { dr.Read(); a = Convert .ToInt32( dr[1]); } conn.Close(); return a; }
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title></title> <style type="text/css"> .div1 { 100%; height: 80px; text-align: center; line-height: 80px; font-size: 30px; } /*表格样式*/ .tab { 100%; background-color: blue; text-align: center; } </style> </head> <body> <form id="form1" runat="server"> <div class="div1">奇点0216班学生信息</div> <div> 用户名：<asp:TextBox ID="Text_name" runat="server"></asp:TextBox> 性别：<asp:DropDownList ID="Dr_sex" runat="server"> <asp:ListItem Value="null" Text =""> </asp:ListItem> <asp:ListItem Value="1">男</asp:ListItem> <asp:ListItem Value="0">女</asp:ListItem> </asp:DropDownList> 生日：<asp:DropDownList ID="Dr_bir" runat="server"> <asp:ListItem Value ="null" Text="" > </asp:ListItem> </asp:DropDownList> 民族：<asp:DropDownList ID="Dr_nation" runat="server"> <asp:ListItem Value="null">===所有===</asp:ListItem> </asp:DropDownList> <asp:Button ID="But_tj" runat="server" Text="查询" /> <asp:Button ID="But_qubu" runat="server" Text="查询全部" /> </div> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> <br /> <br /> <br /> <br /> <%--使用 Repeater 添加数据--%> <asp:Repeater ID="Repeater1" runat="server"> <HeaderTemplate> <%-- 头模板--%> <table class="tab"> <tr style="color: white; height: 30px;"> <td>编号</td> <td>用户名</td> <td>密码</td> <td>昵称</td> <td>性别</td> <td>生日</td> <td>年龄</td> <td>民族</td> <td>设置</td> </tr> </HeaderTemplate> <ItemTemplate> <%-- 项模板--%> <tr style="background-color: white;"> <td><%#Eval("Ids") %></td> <td><%#Eval("Username") %></td> <td><%#Eval("Password") %></td> <td><%#Eval("Nickname") %></td> <td><%#Eval("Sexstr") %></td> <td><%#Eval("Birthdaystr") %></td> <td><%#Eval("Age") %></td> <td><%#Eval("NationName") %></td> <td> <a href="xiugai.aspx?i=<%#Eval("Ids") %>">编辑 </a> <a onclick="return confirm('是否要删除<%#Eval("NickName") %>？');" href="shanchu.aspx?i=<%#Eval("Ids") %>">删除</a> </td> </tr> </ItemTemplate> <FooterTemplate> <%--脚模板--%> <tr style="color: white; height: 30px;"> <td>本次查询共有[ <asp:Literal ID="Literal1" runat="server"></asp:Literal>]条 </td> <%-- 在这里取不到控件--%> </tr> </table> </FooterTemplate> </asp:Repeater> 本次查询共有[ <asp:Literal ID="Literal2" runat="server"></asp:Literal> ]条记录 <a href="zhuce.aspx" target="_blank">添加新同学</a> </form> </body> </html>
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { //绑定生日 for (int i = DateTime.Now.Year; i >= 1960; i--) { ListItem li = new ListItem(); li.Text = i.ToString(); li.Value = i.ToString(); Dr_bir.Items.Add(li); } //绑定民族 List<usernation> ulist = new usernationData().selectAll(); foreach (usernation u in ulist) { ListItem li = new ListItem(); li.Text = u.NationName; li.Value = u.NationCode; Dr_nation.Items.Add(li); } } But_tj.Click += But_tj_Click; But_qubu.Click += But_qubu_Click; Repeater1.DataSource = new usersData().selectAll(); Repeater1.DataBind(); Literal2.Text = new usersData().selectAll().Count.ToString(); } //查全部 void But_qubu_Click(object sender, EventArgs e) { Repeater1.DataSource = new usersData().selectAll(); Repeater1.DataBind(); Literal2.Text = new usersData().selectAll().Count.ToString(); } //组合查 void But_tj_Click(object sender, EventArgs e) { Hashtable hs = new Hashtable(); //哈希表集合 string sql = " select * from users "; int count = 0; //匹配用户名 if (Text_name.Text.Length > 0) { sql += "where Username like @a"; hs.Add("@a", "%" + Text_name.Text.Trim() + "%"); //用哈希表集合装 @a count++; // 如果用户名填写了 记一下，为后面连接字符准备 } //匹配性别 if (Dr_sex.SelectedValue != "null") { if (count > 0) { sql += " and Sex=" + Dr_sex.SelectedValue; } else { sql += "where Sex=" + Dr_sex.SelectedValue; } count++; } //匹配生日 if (Dr_bir.SelectedValue !="null") { if (count > 0) { sql += " and YEAR( Birthday)= '" + Dr_bir.SelectedValue+"'"; } else { sql += "where YEAR( Birthday)=' " + Dr_bir.SelectedValue+"'"; } count++; } //匹配民族 if (Dr_nation.SelectedValue != "null") { if (count > 0) { sql += " and Nation= '" + Dr_nation.SelectedValue+"'"; } else { sql += "where Nation= '" + Dr_nation.SelectedValue+"'"; } count++; } List < users> u =new usersData().selectA(sql , hs); Repeater1.DataSource = u; Repeater1.DataBind(); //Literal1.Text = u.Count.ToString (); //无法对Literal1 进行定位 Literal2.Text = u.Count.ToString(); Label1.Text = sql; } }
效果图
