复合条件的查询在于如何根据多种不同条件,正确的拼接出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);
}