清华大佬耗费三个月吐血整理的几百G的资源，免费分享！....>>>
#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
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;
/// <summary>
/// 参数列表
/// </summary>
public List<SqlParameter> parameterList = new List<SqlParameter>();
int index = 0;
const string and = " AND ";
const string or = " OR ";
#endregion
#region 构造函数
/// <summary>
/// 创建ConditionHelper对象
/// </summary>
/// <param name="isBuildParameterSql">是否生成带参数的sql</param>
/// <param name="isExcludeEmpty">为True时表示字段为空或者Null时则不作为查询条件</param>
public ConditionHelper(bool isBuildParameterSql = true, bool isExcludeEmpty = true)
{
this.isBuildParameterSql = isBuildParameterSql;
this.isExcludeEmpty = isExcludeEmpty;
}
#endregion
#region 公共方法
/// <summary>
/// 添加and 条件
/// </summary>
/// <param name="fieldName">字段名称</param>
/// <param name="comparison">比较符类型</param>
/// <param name="fieldValue">字段值</param>
/// <returns>返回ConditionHelper</returns>
public ConditionHelper AddAndCondition(string fieldName, Comparison comparison, params object[] fieldValue)
{
conditionBuilder.Append(and);
this.AddCondition(fieldName, comparison, fieldValue);
return this;
}
/// <summary>
/// 添加or条件
/// </summary>
/// <param name="fieldName">字段名称</param>
/// <param name="comparison">比较符类型</param>
/// <param name="fieldValue">字段值</param>
/// <returns>返回ConditionHelper</returns>
public ConditionHelper AddOrCondition(string fieldName, Comparison comparison, params object[] fieldValue)
{
conditionBuilder.Append(or);
this.AddCondition(fieldName, comparison, fieldValue);
return this;
}
/// <summary>
/// 添加and+左括号+条件
/// </summary>
/// <param name="comparison">比较符类型</param>
/// <param name="fieldName">字段名称</param>
/// <param name="fieldValue">字段值，注：Between时，此字段必须填两个值</param>
/// <returns>返回ConditionHelper</returns>
public ConditionHelper AddAndOpenParenthese(string fieldName, Comparison comparison, params object[] fieldValue)
{
this.conditionBuilder.AppendFormat("{0}{1}", and, GetComparisonOperator(Comparison.OpenParenthese));
this.AddCondition(fieldName, comparison, fieldValue);
return this;
}
/// <summary>
/// 添加or+左括号+条件
/// </summary>
/// <returns></returns>
/// <param name="comparison">比较符类型</param>
/// <param name="fieldName">字段名称</param>
/// <param name="fieldValue">字段值，注：Between时，此字段必须填两个值</param>
/// <returns>返回ConditionHelper</returns>
public ConditionHelper AddOrOpenParenthese(string fieldName, Comparison comparison, params object[] fieldValue)
{
this.conditionBuilder.AppendFormat("{0}{1}", or, GetComparisonOperator(Comparison.OpenParenthese));
this.AddCondition(fieldName, comparison, fieldValue);
return this;
}
/// <summary>
/// 添加右括号
/// </summary>
/// <returns></returns>
public ConditionHelper AddCloseParenthese()
{
this.conditionBuilder.Append(GetComparisonOperator(Comparison.CloseParenthese));
return this;
}
/// <summary>
/// 添加条件
/// </summary>
/// <param name="comparison">比较符类型</param>
/// <param name="fieldName">字段名称</param>
/// <param name="fieldValue">字段值，注：Between时，此字段必须填两个值</param>
/// <returns>返回ConditionHelper</returns>
public ConditionHelper AddCondition(string fieldName, Comparison comparison, params object[] fieldValue)
{
//如果选择IsExcludeEmpty为True,并且该字段为空值的话则跳过
if (isExcludeEmpty && string.IsNullOrEmpty(fieldValue.ToString()))
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[0]));
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[0])));
break;
case Comparison.In:
case Comparison.NotIn:
this.conditionBuilder.AppendFormat("{0}{1}({2})", GetFieldName(fieldName), GetComparisonOperator(comparison), string.Join(",", GetFieldValue(fieldValue)));
break;
case Comparison.StartsWith:
this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(string.Format("{0}%", fieldValue[0])));
break;
case Comparison.EndsWith:
this.conditionBuilder.AppendFormat("{0}{1}{2}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(string.Format("%{0}", fieldValue[0])));
break;
case Comparison.Between:
this.conditionBuilder.AppendFormat("{0}{1}{2} AND {3}", GetFieldName(fieldName), GetComparisonOperator(comparison), GetFieldValue(fieldValue[0]), GetFieldValue(fieldValue[1]));
break;
default:
throw new Exception("条件为定义");
}
return this;
}
public override string ToString()
{
return this.conditionBuilder.ToString();
}
#endregion
#region 私有方法
/// <summary>
/// 取得字段值
/// </summary>
/// <param name="fieldValue"></param>
/// <returns></returns>
private string GetFieldValue(params object[] fieldValue)
{
if (isBuildParameterSql == false)
{
if (fieldValue.Length < 2)
{
return string.Format("'{0}'", fieldValue[0]);
}
else
{
return string.Format("'{0}'", string.Join("','", fieldValue));
}
}
else
{
if (fieldValue.Length < 2)
{
return AddParameter(fieldValue[0]);
}
else
{
List<string> parameterNameList = new List<string>();
foreach (var value in fieldValue)
{
parameterNameList.Add(AddParameter(value));
}
return string.Join(",", parameterNameList);
}
}
}
/// <summary>
/// 添加参数
/// </summary>
/// <param name="fieldValue"></param>
/// <returns></returns>
private string AddParameter(object fieldValue)
{
index++;
string parameterName = string.Format("{0}{1}{2}", parameterPrefix, parameterKey, index);
parameterList.Add(new SqlParameter()
{
ParameterName = parameterName,
Value = fieldValue
});
return parameterName;
}
private string GetFieldName(string fieldName)
{
return string.Format("[{0}]", fieldName);
}
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;
}
return result;
}
#endregion
}
比如说要实现这样的一个例子：
UserName In ('张三','李四','王五') and Age between 1 and 17 and (Gender='Male' or Gender='Female')
实现代码：
ConditionHelper helper = new ConditionHelper(false);
helper.AddCondition("UserName", Comparison.In, "张三", "李四", "王五")
.AddAndCondition("Age",Comparison.Between,1,17)
.AddAndOpenParenthese("Gender",Comparison.Equal,"Male")
.AddOrCondition("Gender",Comparison.Equal,"Female")
.AddCloseParenthese();
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中拼接条件时它会自动去判断，是不是这样让代码变得更简洁？