组合查询
实体类:
using System; using System.Collections.Generic; using System.Linq; using System.Web; /// <summary> /// goods 的摘要说明 /// </summary> public class goods { public goods() { // // TODO: 在此处添加构造函数逻辑 // } public int ids { get; set; } public string goodsname { get; set; } public string number { get; set; } public string sprice { get; set; } public DateTime intime { get; set; } public string gtel { get; set; } public string goodsbase { get; set; } }
数据访问类:
public List<goods> select(string tsql,Hashtable hhs) { List<goods> glist = new List<goods>(); cmd.CommandText = tsql; cmd.Parameters.Clear(); foreach (string s in hhs.Keys) { cmd.Parameters.AddWithValue(s,hhs[s]); } conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.HasRows) { while (dr.Read()) { goods g = new goods(); g.ids = Convert.ToInt32(dr[0]); g.goodsname = dr[1].ToString(); g.number = dr[2].ToString(); g.sprice = dr[3].ToString(); g.intime = Convert.ToDateTime(dr[4]); g.gtel = dr[5].ToString(); g.goodsbase = dr[6].ToString(); glist.Add(g); } } conn.Close(); return glist; }
aspx界面:
<%@ 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> .tb1 { width:100%; text-align:center; } .a { background-color:navy; color:white; } .b { background-color:orange; color:white; } .b:hover { background-color:gray; } </style> </head> <body> <form id="form1" runat="server"> <div> 货品名称:<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> 数量: <asp:DropDownList ID="DropDownList1" runat="server"> <asp:ListItem>=</asp:ListItem> <asp:ListItem>>=</asp:ListItem> <asp:ListItem><=</asp:ListItem> </asp:DropDownList> <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox> 单价: <asp:DropDownList ID="DropDownList2" runat="server"> <asp:ListItem>=</asp:ListItem> <asp:ListItem>>=</asp:ListItem> <asp:ListItem><=</asp:ListItem> </asp:DropDownList> <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="查询" /> <asp:Button ID="Button2" runat="server" Text="查询2" /> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> <asp:Repeater ID="Repeater1" runat="server"> <HeaderTemplate> <table class="tb1"> <tr class="a"> <td>编号</td> <td>货品名称</td> <td>数量</td> <td>单价</td> <td>进货时间</td> <td>联系人电话</td> <td>仓库号</td> <td>操作</td> </tr> </HeaderTemplate> <ItemTemplate> <tr class="b"> <td><%#Eval("ids") %></td> <td><%#Eval("goodsname") %></td> <td><%#Eval("number") %></td> <td><%#Eval("sprice") %></td> <td><%#Eval("intime","{0:yyyy年MM月dd日}") %></td> <td><%#Eval("gtel") %></td> <td><%#Eval("goodsbase") %></td> <td> <a href="update.aspx?id=<%#Eval("ids")%>" target="_blank">修改</a> <a href="delete.aspx?id=<%#Eval("ids")%>" target="_blank">删除</a> </td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> </div> </form> </body> </html>
cs里:
两种方法,每个按钮一种
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) { Repeater1.DataSource = new goodsdata().select(); Repeater1.DataBind(); } Button2.Click += Button2_Click; Button1.Click += Button1_Click; } void Button2_Click(object sender, EventArgs e) { Hashtable hs = new Hashtable(); string sql = "select*from goods"; if (TextBox1.Text.Trim().Length > 0) { sql += " where goodsname like @goodsname"; hs.Add("@goodsname", "%" + TextBox1.Text + "%"); } else { sql += " where 1=1"; } if (TextBox2.Text.Trim().Length > 0) { sql += " and number" + DropDownList1.SelectedValue + " @number"; hs.Add("@number", TextBox2.Text); } else { sql += " and 1=1"; } if (TextBox3.Text.Trim().Length > 0) { sql += " and sprice= @sprice"; hs.Add("@sprice", TextBox3.Text); } else { sql += " and 1=1"; } Repeater1.DataSource = new goodsdata().select(sql, hs); Repeater1.DataBind(); } void Button1_Click(object sender, EventArgs e) { Hashtable hs = new Hashtable(); int count = 0; string sql = "select*from goods"; if (TextBox1.Text.Trim().Length > 0) { sql += " where goodsname like @goodsname"; hs.Add("@goodsname","%"+TextBox1.Text+"%"); count++; } if (TextBox2.Text.Trim().Length > 0) { if (count > 0) { sql += " and number"+DropDownList1.SelectedValue+" @number" ; } else { sql += " where number"+DropDownList1.SelectedValue+" @number"; } hs.Add("@number",TextBox2.Text); count++; } if (TextBox3.Text.Trim().Length > 0) { if (count > 0) { sql += " and sprice" + DropDownList2.SelectedValue + " @sprice"; } else { sql += " where sprice" + DropDownList2.SelectedValue + "@sprice"; } hs.Add("@sprice",TextBox3.Text); count++; } Label1.Text = sql; Repeater1.DataSource = new goodsdata().select(sql, hs); Repeater1.DataBind(); } }