在网上其实已经有很多类似这种拼接sql条件的类，但是没有看到一个让我感觉完全满意的这样的类。最近看到 http://www.cnblogs.com/xtdhb/p/3811956.html 这博客，觉得这思路很好，但是个人觉得这样用起来比较麻烦，所以借鉴了这位兄弟的思路自己改进了一下，这样可以很方便地实现任何的组合条件。
以下是ConditionHelper类的代码：
1 #region public enum Comparison 2 public enum Comparison 3 { 4 /// <summary> 5 /// 等于号 = 6 /// </summary> 7 Equal, 8 /// <summary> 9 /// 不等于号 <> 10 /// </summary> 11 NotEqual, 12 /// <summary> 13 /// 大于号 > 14 /// </summary> 15 GreaterThan, 16 /// <summary> 17 /// 大于或等于 >= 18 /// </summary> 19 GreaterOrEqual, 20 /// <summary> 21 /// 小于 < 22 /// </summary> 23 LessThan, 24 /// <summary> 25 /// 小于或等于 <= 26 /// </summary> 27 LessOrEqual, 28 /// <summary> 29 /// 模糊查询 Like 30 /// </summary> 31 Like, 32 /// <summary> 33 /// 模糊查询 Not Like 34 /// </summary> 35 NotLike, 36 /// <summary> 37 /// is null 38 /// </summary> 39 IsNull, 40 /// <summary> 41 /// is not null 42 /// </summary> 43 IsNotNull, 44 /// <summary> 45 /// in 46 /// </summary> 47 In, 48 /// <summary> 49 /// not in 50 /// </summary> 51 NotIn, 52 /// <summary> 53 /// 左括号 （ 54 /// </summary> 55 OpenParenthese, 56 /// <summary> 57 /// 右括号 ) 58 /// </summary> 59 CloseParenthese, 60 Between, 61 StartsWith, 62 EndsWith 63 } 64 #endregion 65 66 public class ConditionHelper 67 { 68 #region 变量定义 69 string parameterPrefix = "@"; 70 string parameterKey = "P"; 71 /// <summary> 72 /// 用来拼接SQL语句 73 /// </summary> 74 StringBuilder conditionBuilder = new StringBuilder(); 75 /// <summary> 76 /// 为True时表示字段为空或者Null时则不作为查询条件 77 /// </summary> 78 bool isExcludeEmpty = true; 79 /// <summary> 80 /// 是否生成带参数的sql 81 /// </summary> 82 bool isBuildParameterSql = true; 83 /// <summary> 84 /// 参数列表 85 /// </summary> 86 public List<SqlParameter> parameterList = new List<SqlParameter>(); 87 int index = 0; 88 89 const string and = " AND "; 90 const string or = " OR "; 91 #endregion 92 93 #region 构造函数 94 95 /// <summary> 96 /// 创建ConditionHelper对象 97 /// </summary> 98 /// <param name="isBuildParameterSql">是否生成带参数的sql</param> 99 /// <param name="isExcludeEmpty">为True时表示字段为空或者Null时则不作为查询条件</param> 100 public ConditionHelper(bool isBuildParameterSql = true, bool isExcludeEmpty = true) 101 { 102 this.isBuildParameterSql = isBuildParameterSql; 103 this.isExcludeEmpty = isExcludeEmpty; 104 } 105 #endregion 106 107 #region 公共方法 108 /// <summary> 109 /// 添加and 条件 110 /// </summary> 111 /// <param name="fieldName">字段名称</param> 112 /// <param name="comparison">比较符类型</param> 113 /// <param name="fieldValue">字段值</param> 114 /// <returns>返回ConditionHelper</returns> 115 public ConditionHelper AddAndCondition(string fieldName, Comparison comparison, params object[] fieldValue) 116 { 117 conditionBuilder.Append(and); 118 this.AddCondition(fieldName, comparison, fieldValue); 119 return this; 120 } 121 122 /// <summary> 123 /// 添加or条件 124 /// </summary> 125 /// <param name="fieldName">字段名称</param> 126 /// <param name="comparison">比较符类型</param> 127 /// <param name="fieldValue">字段值</param> 128 /// <returns>返回ConditionHelper</returns> 129 public ConditionHelper AddOrCondition(string fieldName, Comparison comparison, params object[] fieldValue) 130 { 131 conditionBuilder.Append(or); 132 this.AddCondition(fieldName, comparison, fieldValue); 133 return this; 134 } 135 136 /// <summary> 137 /// 添加and+左括号+条件 138 /// </summary> 139 /// <param name="comparison">比较符类型</param> 140 /// <param name="fieldName">字段名称</param> 141 /// <param name="fieldValue">字段值，注：Between时，此字段必须填两个值</param> 142 /// <returns>返回ConditionHelper</returns> 143 public ConditionHelper AddAndOpenParenthese(string fieldName, Comparison comparison, params object[] fieldValue) 144 { 145 this.conditionBuilder.AppendFormat("{0}{1}", and, GetComparisonOperator(Comparison.OpenParenthese)); 146 this.AddCondition(fieldName, comparison, fieldValue); 147 return this; 148 } 149 150 /// <summary> 151 /// 添加or+左括号+条件 152 /// </summary> 153 /// <returns></returns> 154 /// <param name="comparison">比较符类型</param> 155 /// <param name="fieldName">字段名称</param> 156 /// <param name="fieldValue">字段值，注：Between时，此字段必须填两个值</param> 157 /// <returns>返回ConditionHelper</returns> 158 public ConditionHelper AddOrOpenParenthese(string fieldName, Comparison comparison, params object[] fieldValue) 159 { 160 this.conditionBuilder.AppendFormat("{0}{1}", or, GetComparisonOperator(Comparison.OpenParenthese)); 161 this.AddCondition(fieldName, comparison, fieldValue); 162 return this; 163 } 164 165 /// <summary> 166 /// 添加右括号 167 /// </summary> 168 /// <returns></returns> 169 public ConditionHelper AddCloseParenthese() 170 { 171 this.conditionBuilder.Append(GetComparisonOperator(Comparison.CloseParenthese)); 172 return this; 173 } 174 175 176 /// <summary> 177 /// 添加条件 178 /// </summary> 179 /// <param name="comparison">比较符类型</param> 180 /// <param name="fieldName">字段名称</param> 181 /// <param name="fieldValue">字段值，注：Between时，此字段必须填两个值</param> 182 /// <returns>返回ConditionHelper</returns> 183 public ConditionHelper AddCondition(string fieldName, Comparison comparison, params object[] fieldValue) 184 { 185 //如果选择IsExcludeEmpty为True,并且该字段为空值的话则跳过 186 if (isExcludeEmpty && string.IsNullOrEmpty(fieldValue.ToString())) 187 return this; 188 189 switch (comparison) 190 { 191 case Comparison.Equal: 192 case Comparison.NotEqual: 193 case Comparison.GreaterThan: 194 case Comparison.GreaterOrEqual: 195 case Comparison.LessThan: 196 case Comparison.LessOrEqual: 197 this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(fieldValue[0])); 198 break; 199 case Comparison.IsNull: 200 case Comparison.IsNotNull: 201 this.conditionBuilder.AppendFormat("{0}{1}", GetFieldName(fieldName), GetComparisonOperator(comparison)); 202 break; 203 case Comparison.Like: 204 case Comparison.NotLike: 205 this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(string.Format("%{0}%", fieldValue[0]))); 206 break; 207 case Comparison.In: 208 case Comparison.NotIn: 209 this.conditionBuilder.AppendFormat("{0}{1}({2})", GetFieldName(fieldName), GetComparisonOperator(comparison), string.Join(",", GetFieldValue(fieldValue))); 210 break; 211 case Comparison.StartsWith: 212 this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(string.Format("{0}%", fieldValue[0]))); 213 break; 214 case Comparison.EndsWith: 215 this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(string.Format("%{0}", fieldValue[0]))); 216 break; 217 case Comparison.Between: 218 this.conditionBuilder.AppendFormat("{0}{1}{2} AND {3}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(fieldValue[0]), GetFieldValue(fieldValue[1])); 219 break; 220 default: 221 throw new Exception("条件为定义"); 222 } 223 return this; 224 } 225 226 227 public override string ToString() 228 { 229 return this.conditionBuilder.ToString(); 230 } 231 232 #endregion 233 234 #region 私有方法 235 /// <summary> 236 /// 取得字段值 237 /// </summary> 238 /// <param name="fieldValue"></param> 239 /// <returns></returns> 240 private string GetFieldValue(params object[] fieldValue) 241 { 242 if (isBuildParameterSql == false) 243 { 244 if (fieldValue.Length < 2) 245 { 246 return string.Format("'{0}'", fieldValue[0]); 247 } 248 else 249 { 250 return string.Format("'{0}'", string.Join("','", fieldValue)); 251 } 252 } 253 else 254 { 255 if (fieldValue.Length < 2) 256 { 257 return AddParameter(fieldValue[0]); 258 } 259 else 260 { 261 List<string> parameterNameList = new List<string>(); 262 foreach (var value in fieldValue) 263 { 264 parameterNameList.Add(AddParameter(value)); 265 } 266 return string.Join(",", parameterNameList); 267 } 268 } 269 } 270 271 /// <summary> 272 /// 添加参数 273 /// </summary> 274 /// <param name="fieldValue"></param> 275 /// <returns></returns> 276 private string AddParameter(object fieldValue) 277 { 278 index++; 279 string parameterName = string.Format("{0}{1}{2}", parameterPrefix, parameterKey, index); 280 parameterList.Add(new SqlParameter() 281 { 282 ParameterName = parameterName, 283 Value = fieldValue 284 }); 285 return parameterName; 286 } 287 288 private string GetFieldName(string fieldName) 289 { 290 return string.Format("[{0}]", fieldName); 291 } 292 private static string GetComparisonOperator(Comparison comparison) 293 { 294 string result = string.Empty; 295 switch (comparison) 296 { 297 case Comparison.Equal: 298 result = " = "; 299 break; 300 case Comparison.NotEqual: 301 result = " <> "; 302 break; 303 case Comparison.GreaterThan: 304 result = " > "; 305 break; 306 case Comparison.GreaterOrEqual: 307 result = " >= "; 308 break; 309 case Comparison.LessThan: 310 result = " < "; 311 break; 312 case Comparison.LessOrEqual: 313 result = " <= "; 314 break; 315 case Comparison.Like: 316 case Comparison.StartsWith: 317 case Comparison.EndsWith: 318 result = " LIKE "; 319 break; 320 case Comparison.NotLike: 321 result = " NOT LIKE "; 322 break; 323 case Comparison.IsNull: 324 result = " IS NULL "; 325 break; 326 case Comparison.IsNotNull: 327 result = " IS NOT NULL "; 328 break; 329 case Comparison.In: 330 result = " IN "; 331 break; 332 case Comparison.NotIn: 333 result = " NOT IN "; 334 break; 335 case Comparison.OpenParenthese: 336 result = " ("; 337 break; 338 case Comparison.CloseParenthese: 339 result = ") "; 340 break; 341 case Comparison.Between: 342 result = " BETWEEN "; 343 break; 344 } 345 return result; 346 } 347 #endregion 348 349 }
比如说要实现这样的一个例子：
UserName In ('张三','李四','王五') and Age between 1 and 17 and (Gender='Male' or Gender='Female')
实现代码：
1 ConditionHelper helper = new ConditionHelper(false); 2 helper.AddCondition("UserName", Comparison.In, "张三", "李四", "王五") 3 .AddAndCondition("Age",Comparison.Between,1,17) 4 .AddAndOpenParenthese("Gender",Comparison.Equal,"Male") 5 .AddOrCondition("Gender",Comparison.Equal,"Female") 6 .AddCloseParenthese(); 7 string condition=helper.ToString();
还有要提一下的是这个类中的isExcludeEmpty变量，这个是借鉴了园子里伍华聪的想法，由于是很早以前看的，具体是哪一篇文章就不太清楚了，有兴趣的可以去他博客http://www.cnblogs.com/wuhuacong/里找下看。这变量在这有什么用呢？不要小看这小小的变量，它让我们在实际中少了很多重复的代码。比如界面上有一个条件文本框txtUserName,那我们一般拼接条件如下：
1 if(!string.IsNullOrEmpty(txtUserName.Text.Trim()) 2 { 3 condition=string.Format("UserName like '%{0}%'",txtUserName.Text.Trim()） 4 }
简单说就是每次在拼接条件时都要判断文本框里的值是否为空，只有在不为空的情况才加入条件里去。
现在在ConditonHelper里加了isExcludeEmpty变量，我们在使用的时候就不要加判断了，在ConditionHelper中拼接条件时它会自动去判断，是不是这样让代码变得更简洁？
个人觉得这样用起来还是挺方便的。第一次写文章，写得不好，不过写这文章的主要目的是分享自己的想法，同时也希望能得到大家的指点，个人感觉这个类应该还有很多可以优化的地方，所以以后可能还会修改。