ASP.NET中小型软件系统中复合条件的查询

复合条件的查询在于如何根据多种不同条件，正确的拼接出SQL字符串

1，单表复合条件查询，如图所示：

1，在系统Common类库中创建公共类SqlStringConstructor类用来构造sql语句。

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace shoppingcity.Common
{
    public class SqlStringConstuctor
    {
        /// <summary>
        /// 在字符串的开头中间和结尾添加'
        /// </summary>
        /// <param name="pStr"></param>
        /// <returns></returns>
        public static String GetQuotedString(string pStr)
        {
            return ("'" + pStr.Replace("'", "''") + "'");
        }

        /// <summary>
        /// 组成Where 条件语句
        /// </summary>
        /// <param name="queryItems">Hashtable 条件的集合，类型和值</param>
        /// <returns>where 语句</returns>
        public static String GetConditionClause(Hashtable queryItems)
        {
            int Count = 0;
            string Where = "";
            foreach (DictionaryEntry item in queryItems)
            {
                if (Count == 0)
                {
                    Where = " where ";
                }
                else
                {
                    Where += " and ";
                }
                if (item.Value.GetType().ToString() == "System.String")
                {
                    Where += item.Key.ToString()
                        + " like " + SqlStringConstuctor.GetQuotedString("%" + item.Value.ToString() + "%");

                }
                else
                    if (item.Value.GetType().ToString() == "System.DateTime[]")
                    {
                        string[] time = item.Value.ToString().Split(',');
                        Where += item.Key.ToString() + " between " +
                            SqlStringConstuctor.GetQuotedString(((DateTime[])item.Value)[0].ToString()) + " and " +
                            SqlStringConstuctor.GetQuotedString(((DateTime[])item.Value)[1].ToString());
                    }
                    else
                    {
                        Where += item.Key.ToString() + "=" + item.Value.ToString();
                    }
                Count++;
            }
            return Where;
        }
    }
}
2，在DAL类库中创建数据访问类：ItemAccess类：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using shoppingcity.Common;
using shoppingcity.DAL; 

namespace shoppingcity.DAL
{
    public class ItemAccess
    { 

        public DataSet Search(string sql)
        {
            DataSet ds = new DataSet();
            SqlDataAdapter da = null;
            DataBase db = new DataBase();
            da = db.CreateDataAdapter(CommandType.Text, sql, null, 1);
            da.Fill(ds);
            return ds;
        }
    }
}

3，在BLL类库中创建业务逻辑类ItemManager类。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using shoppingcity.DAL;
using shoppingcity.Model; 

namespace shoppingcity.BLL
{
    [Serializable]
    public class ItemManager
    {
        //私有成员图书详情数据访问类对象
        private static readonly ItemAccess dal = new ItemAccess(); 

        //图书检索方法
        public DataSet Search(string sql)
        {
            return dal.Search(sql);
        }
    }
}

4，在页面中调用使用相关类：

private void BindGrid(string gsql)
{
    ItemManager im = new ItemManager();
    DataSet ds = null;
    ds = im.Search(gsql);
    gvSearch.DataSource = ds;
    gvSearch.DataBind();
    lblShow.Text = "共找到" + ds.Tables[0].Rows.Count.ToString() + "条符合条件的记录";
    lblShow.ForeColor = Color.Red;
} 

protected void btnOK_Click(object sender, EventArgs e)
{
    gvSearch.Visible = true;
    string gsql = "select * from item ";
    Hashtable ht = new Hashtable();                             //构造存储哈希表 键值对：列名+值(有类型)
    if(cbTitle.Checked)
        ht.Add("itemname",txtTitle.Text.Trim());
    if(cbAuthor.Checked)
        ht.Add("bookauthor",txtAuthor.Text.Trim());
    if(cbPress.Checked)
        ht.Add("publisher",txtPress.Text.Trim());
    gsql = gsql + SqlStringConstuctor.GetConditionClause(ht) + " order by publishdate Desc";
    ViewState["gsql"] = gsql;
    BindGrid(gsql);
}