ConditonHelper

在网上其实已经有很多类似这种拼接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中拼接条件时它会自动去判断，是不是这样让代码变得更简洁？

个人觉得这样用起来还是挺方便的。第一次写文章，写得不好，不过写这文章的主要目的是分享自己的想法，同时也希望能得到大家的指点，个人感觉这个类应该还有很多可以优化的地方，所以以后可能还会修改。