两三个月前曾写过《重构ConditionHelper》的随笔，但不知是因为写得不够好还是没有什么新意，我发表至博客园首页时被屏蔽了，本着好的知识应该分享给更多人，加之新项目已交付用户使用所以现在时间也还算充足，所以就打算重新写一下这篇文章。
废话就不多说了，先上代码，然后就其中一些自己觉得写得还好的地方说一下自己的思路。
#region public enum Comparison
public enum Comparison
{
/// <summary>
/// 等于号 =
/// </summary>
Equal,
/// <summary>
/// 不等于号 <>
/// </summary>
NotEqual,
/// <summary>
/// 大于号 >
/// </summary>
GreaterThan,
/// <summary>
/// 大于或等于 >=
/// </summary>
GreaterOrEqual,
/// <summary>
/// 小于 <
/// </summary>
LessThan,
/// <summary>
/// 小于或等于 <=
/// </summary>
LessOrEqual,
/// <summary>
/// 模糊查询 Like
/// </summary>
Like,
/// <summary>
/// 模糊查询 Not Like
/// </summary>
NotLike,
/// <summary>
/// is null
/// </summary>
IsNull,
/// <summary>
/// is not null
/// </summary>
IsNotNull,
/// <summary>
/// in
/// </summary>
In,
/// <summary>
/// not in
/// </summary>
NotIn,
/// <summary>
/// 左括号 （
/// </summary>
OpenParenthese,
/// <summary>
/// 右括号 )
/// </summary>
CloseParenthese,
Between,
StartsWith,
EndsWith
}
#endregion #region public enum RelationType
/// <summary>
/// 关联类型：And、Or
/// </summary>
public enum RelationType
{
AND,
OR,
}
#endregion public class ConditionHelper
{
#region 变量、属性定义
string parameterPrefix = "@";
string parameterKey = "P";
/// <summary>
/// 用来拼接SQL语句
/// </summary>
StringBuilder conditionBuilder = new StringBuilder();
/// <summary>
/// 为True时表示字段为空或者Null时则不作为查询条件
/// </summary>
bool isExcludeEmpty = true;
/// <summary>
/// 是否生成带参数的sql
/// </summary>
bool isBuildParameterSql = true; #region public int Length
private int length=;
public int Length
{
get { return length; }
}
#endregion #region public List<SqlParameter> ParameterList
private List<SqlParameter> parameterList = new List<SqlParameter>();
/// <summary>
/// 参数列表
/// </summary>
public List<SqlParameter> ParameterList
{
get { return this.parameterList; }
}
#endregion int index = ; #endregion #region 构造函数
/// <summary>
/// 创建ConditionHelper对象
/// </summary>
/// <param name="isExcludeEmpty">为True时表示字段为空或者Null时则不作为查询条件</param>
/// <param name="isBuildParameterSql">是否生成带参数的sql</param>
/// <param name="parameterKey">参数的前辍</param>
public ConditionHelper(bool isExcludeEmpty = true, string parameterKey = "P", bool isBuildParameterSql = true)
{
this.isExcludeEmpty = isExcludeEmpty;
this.isBuildParameterSql = isBuildParameterSql;
this.parameterKey = parameterKey;
this.conditionBuilder.Append(" Where ");
}
#endregion #region 公共方法
#region public ConditionHelper Clear() 清除已添加的条件
/// <summary>
/// 清除已添加的条件
/// </summary>
/// <returns></returns>
public ConditionHelper Clear()
{
this.length = ;
parameterList.Clear();
conditionBuilder.Clear().Append(" Where ");
return this;
}
#endregion #region public ConditionHelper AppendCondition<T>(RelationType relationType, string fieldName, Comparison comparison, params T[] fieldValue) 添加条件
/// <summary>
/// 添加条件
/// </summary>
/// <param name="relationType">关联运算符</param>
/// <param name="comparison">比较符类型</param>
/// <param name="fieldName">字段名称</param>
/// <param name="fieldValue">字段值，注：1、不可为数组;2、Between时，此字段必须填两个值; </param>
/// <returns>返回ConditionHelper</returns>
public ConditionHelper AppendCondition<T>(RelationType relationType, string fieldName, Comparison comparison, params T[] fieldValue)
{
if (IsContinue(fieldValue)) return this; if (!this.conditionBuilder.ToString().Trim().EndsWith("WHERE", StringComparison.InvariantCultureIgnoreCase))
{
this.conditionBuilder.Append(GetRelationString(relationType));
}
this.AppendCondition(fieldName, comparison, fieldValue);
return this;
}
#endregion #region public ConditionHelper AppendCondition<T>(string fieldName, Comparison comparison, params T[] fieldValue)
/// <summary>
/// 添加条件
/// </summary>
/// <param name="comparison">比较符类型</param>
/// <param name="fieldName">字段名称</param>
/// <param name="fieldValue">字段值，注：1、不可为数组;2、Between时，此字段必须填两个值; </param>
/// <returns>返回ConditionHelper</returns>
public ConditionHelper AppendCondition<T>(string fieldName, Comparison comparison, params T[] fieldValue)
{
if (IsContinue(fieldValue)) return this; switch (comparison)
{
case Comparison.Equal:
case Comparison.NotEqual:
case Comparison.GreaterThan:
case Comparison.GreaterOrEqual:
case Comparison.LessThan:
case Comparison.LessOrEqual:
this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(fieldValue[]));
break;
case Comparison.IsNull:
case Comparison.IsNotNull:
this.conditionBuilder.AppendFormat("{0}{1}", GetFieldName(fieldName), GetComparisonOperator(comparison));
break;
case Comparison.Like:
case Comparison.NotLike:
this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(string.Format("%{0}%", fieldValue[])));
break;
case Comparison.In:
case Comparison.NotIn:
this.conditionBuilder.AppendFormat("{0}{1}({2})", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(fieldValue));
break;
case Comparison.StartsWith:
this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(string.Format("{0}%", fieldValue[])));
break;
case Comparison.EndsWith:
this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(string.Format("%{0}", fieldValue[])));
break;
case Comparison.Between:
this.conditionBuilder.AppendFormat("{0}{1}{2} AND {3}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(fieldValue[]), GetFieldValue(fieldValue[]));
break;
default:
throw new Exception("条件未定义");
}
this.length++;
return this;
}
#endregion #region public ConditionHelper AppendParenthese(RelationType relationType, ConditionHelper condition)
/// <summary>
/// 添加含有括号的条件
/// </summary>
/// <param name="relationType">关联运算符</param>
/// <param name="condition">条件</param>
/// <returns></returns>
public ConditionHelper AppendParenthese(RelationType relationType, ConditionHelper condition)
{
if (condition == null || condition.conditionBuilder.ToString().Trim().Length < )
return this;
if (!conditionBuilder.ToString().Trim().EndsWith("WHERE", StringComparison.InvariantCultureIgnoreCase))
{
this.conditionBuilder.Append(GetRelationString(relationType));
}
this.conditionBuilder.AppendFormat(" ({0}) ", condition.conditionBuilder.ToString().Remove(, ));
if (condition.parameterList.Count > )
{
this.parameterList.AddRange(condition.parameterList);
}
this.length++;
return this;
}
#endregion #region public override string ToString()
/// <summary>
/// 转化成Sql条件语句：（包含Where关键字）
/// </summary>
/// <returns></returns>
public override string ToString()
{
if (this.conditionBuilder.Length < )
return string.Empty;
return this.conditionBuilder.ToString();
}
#endregion #region 静态方法
/// <summary>
/// 取得condition中Sql条件语句：（包含Where关键字）
/// </summary>
/// <param name="condition"></param>
/// <returns>若condition为null时返回空字符串</returns>
public static string ToString(ConditionHelper condition)
{
if (condition != null)
return condition.ToString();
return string.Empty;
} /// <summary>
/// 取得condition中sql参数列表
/// </summary>
/// <param name="condition"></param>
/// <returns></returns>
public static List<SqlParameter> GetParamList(ConditionHelper condition)
{
if (condition == null || condition.ParameterList.Count < )
return null;
return condition.ParameterList;
}
#endregion #endregion #region 私有方法
/// <summary>
/// 取得字段值
/// </summary>
/// <param name="fieldValue"></param>
/// <returns></returns>
private string GetFieldValue<T>(params T[] fieldValue)
{
if (isBuildParameterSql == true)
{
List<string> parameterNameList = new List<string>();
foreach (var value in fieldValue)
{
parameterNameList.Add(AddParameter(value));
}
return string.Join(",", parameterNameList);
}
else
{
return string.Format("'{0}'", string.Join("','", fieldValue));
}
} /// <summary>
/// 添加参数
/// </summary>
/// <param name="fieldValue"></param>
/// <returns></returns>
private string AddParameter(object fieldValue)
{
index++;
string parameterName = string.Format("{0}{1}{2}", parameterPrefix, parameterKey, index);
this.parameterList.Add(new SqlParameter
{
ParameterName = parameterName,
Value = fieldValue ?? DBNull.Value
});
return parameterName;
} private string GetFieldName(string fieldName)
{
return string.Format("{0}", fieldName);
} /// <summary>
/// 用来判断是否跳过此拼接条件
/// </summary>
/// <param name="fieldValue">字段值</param>
/// <returns>返回True表示跳过，否则不跳过</returns>
private bool IsContinue<T>(params T[] fieldValue)
{
bool result = false;
//如果选择IsExcludeEmpty为True,并且该字段为空值的话则跳过
if (isExcludeEmpty && fieldValue != null && fieldValue.Length > && string.IsNullOrEmpty(fieldValue[] + ""))
result = true;
return result;
} /// <summary>
/// 取得关联类型字符串
/// </summary>
/// <param name="relationType"></param>
/// <returns></returns>
private static string GetRelationString(RelationType relationType)
{
string result = string.Empty;
switch (relationType)
{
case RelationType.AND:
result = " AND ";
break;
case RelationType.OR:
result = " OR ";
break;
}
return result;
} /// <summary>
/// 取得关系运算符
/// </summary>
/// <param name="comparison"></param>
/// <returns></returns>
private static string GetComparisonOperator(Comparison comparison)
{
string result = string.Empty;
switch (comparison)
{
case Comparison.Equal:
result = " = ";
break;
case Comparison.NotEqual:
result = " <> ";
break;
case Comparison.GreaterThan:
result = " > ";
break;
case Comparison.GreaterOrEqual:
result = " >= ";
break;
case Comparison.LessThan:
result = " < ";
break;
case Comparison.LessOrEqual:
result = " <= ";
break;
case Comparison.Like:
case Comparison.StartsWith:
case Comparison.EndsWith:
result = " LIKE ";
break;
case Comparison.NotLike:
result = " NOT LIKE ";
break;
case Comparison.IsNull:
result = " IS NULL ";
break;
case Comparison.IsNotNull:
result = " IS NOT NULL ";
break;
case Comparison.In:
result = " IN ";
break;
case Comparison.NotIn:
result = " NOT IN ";
break;
case Comparison.OpenParenthese:
result = " (";
break;
case Comparison.CloseParenthese:
result = ") ";
break;
case Comparison.Between:
result = " BETWEEN ";
break;
default:
throw new Exception("条件未定义!");
}
return result;
}
#endregion }
变量isExcludeEmpty：是标识是否排除条件值为空的条件，这个在第一篇《ConditionHelper》里已经说过，在这里就不废话了。
变量isBuildParameterSql：这是用来标识拼接的sql语句是否使用参数化。比如我们一般情况下为了防止SQL注入，我们都是使用参数化的查询语句，但毕竟使用参数化的语句没有直接使用普通的sql语句来的方便；另外这也是为了更好的兼容性，因为有时别的框架里就需要非参数化的条件语句。
Clear方法：之前构想这个类时没有想到要写这个方法，但后来使用时却发现有必要添加。比如在同一个方法里要使用Condition两次的话，如果没有这个方法就则必需要声明两个ConditionHelper变量，另外我们写代码最不喜欢的就是复制粘贴，而现在只要在之前那变量上直接调用Clear方法是不是使用起来更爽呢。
拼接条件的方法是由两个名为AppendCondition的重载方法和一个AppendParenthese方法组成。之前方法的命名是使用Add，但自己觉得这方法名总是感觉怪怪的，所以就改成了Append。AppendCondition之所以使用泛型方法，是因为我们在拼接条件时，其使用的值不一定总是字符串。
public ConditionHelper AppendCondition<T>(RelationType relationType, string fieldName, Comparison comparison, params T[] fieldValue)
{
if (IsContinue(fieldValue)) return this; if (!this.conditionBuilder.ToString().Trim().EndsWith("WHERE", StringComparison.InvariantCultureIgnoreCase))
{
this.conditionBuilder.Append(GetRelationString(relationType));
}
this.AppendCondition(fieldName, comparison, fieldValue);
return this;
}
上面代码第5行添加了一个条件判断，是为了防止使用该方法之前已经拼接了条件，但那些条件值为空而没有拼成语句，这样最后就会拼成where and name="张三"之类的语句。像一些刚学c#的人，可能在使用拼接SQL时先时直接在条件语句where后先加一名“1=1"再去拼接，这样虽然解决了问题，但是这样会造成sql查询时进行全表扫描，这样是很不好的一种解决方法。
public ConditionHelper AppendParenthese(RelationType relationType, ConditionHelper condition)
{
if (condition == null || condition.conditionBuilder.ToString().Trim().Length < )
return this;
if (!conditionBuilder.ToString().Trim().EndsWith("WHERE", StringComparison.InvariantCultureIgnoreCase))
{
this.conditionBuilder.Append(GetRelationString(relationType));
}
this.conditionBuilder.AppendFormat(" ({0}) ", condition.conditionBuilder.ToString().Remove(, ));
if (condition.parameterList.Count > )
{
this.parameterList.AddRange(condition.parameterList);
}
return this;
}
AppendParenthese方法是在拼接需要使用括号括起来的条件时使用，虽然使用这个方法不够简洁，但是不用担心拼接出来的语句会出错。在之前那个版本的ConditionHelper中，拼接含括号的条件比这简洁，但有时写代码时不注意的话就会出现拼接出来的语句中会出现只有左括号而没有右括号的情况，有时又会出现有括号而括号里没有任何条件的语句，这是在使用时才发现的问题。
其中两个静态方法ToString和GetParamList，是为了防止Condition为空时出错而添加的。