using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Collections;//引入哈希表命名空间 namespace Common { /// <summary> /// SqlStringConstructor 的摘要说明 /// 动态构造拼接sql语句,用于商品多项选择搜索时 /// </summary> public class SqlConstructor { public SqlConstructor() { } /// <summary> /// 公有静态方法,将文本转换成适合在SQL语句里使用的字符串(就是在值外边加上两个'') /// </summary> /// <param name="pStr">要转换的文本</param> /// <returns>转换后的文本</returns> public static string GetQutoedString(string pStr) { return ("'" + pStr.Replace("'", "''") + "'"); } /// <summary> /// 根据条件哈希表，构造SQL语句中的and条件句 /// </summary> /// <param name="queryItems">条件哈希表</param> /// <returns>组合好的and 关系条件子句</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 " + SqlConstructor.GetQutoedString("%" + item.Value.ToString() + "%"); } else if (item.Value.GetType().ToString() == "System.DateTime[]") { //string[] time = item.Value.ToString().Split(','); where += "[" + item.Key.ToString() + "]" + " Between " + SqlConstructor.GetQutoedString(((DateTime[])item.Value)[0].ToString("yyyy-MM-dd")) + " And " + SqlConstructor.GetQutoedString(((DateTime[])item.Value)[1].ToString("yyyy-MM-dd")); } else if (item.Value.GetType().ToString() == "System.Decimal[]") { decimal[] prices =(decimal [])item.Value; where += "[" + item.Key.ToString() + "]" + " Between " + prices[0].ToString() + " And " + prices[1].ToString(); } else { where += "[" + item.Key.ToString() + "]" + "= " + item.Value.ToString(); } count++; } return where; } } }
