/// <summary>
/// 添加查询条件
/// </summary>
/// <param name="fieldName">字段名</param>
/// <param name="fieldType"></param>
/// <param name="conditionType">条件类型（>,=,in等枚举类型）</param>
/// <param name="paramValue">参数传来的值</param>
/// <param name="conditionsRelation"></param>
/// <returns></returns>
private DBConditions add(string fieldName, EnumSqlType fieldType,
EnumCondition conditionType, object paramValue,
EnumConditionsRelation conditionsRelation)
{
#region
DataRow dr = this.Tables[0].NewRow();
object maxconditionid = this.Tables[0].Compute("Max(conditionId)", "true");
dr[DBConditions.conditionId] = (maxconditionid == System.DBNull.Value) ? 1 : Convert.ToInt16(maxconditionid) + 1;
dr[DBConditions.paramsName] = fieldName;
dr[DBConditions.fieldName] = fieldName;
dr[DBConditions.fieldType] = fieldType;
dr[DBConditions.conditionsRelation] = conditionsRelation;
dr[DBConditions.enumCondation] = conditionType;
dr[DBConditions.paramValue] = paramValue;
this.Tables[0].Rows.Add(dr);
return(this);
#endregion
}
/// <summary>
/// 添加查询条件
/// </summary>
/// <param name="allConditions"></param>
/// <returns></returns>
private void addCondition(DBConditions allConditions)
{
#region
_condition = "";
DataRowCollection drcollect = allConditions.Tables[0].Rows;
for (int i = 0; i < drcollect.Count; i++)
{
DataRow dr = drcollect[i];
EnumCondition conditiontype = (EnumCondition)dr[DBConditions.enumCondation];
EnumSqlType fieldtype = (EnumSqlType)dr[DBConditions.fieldType];
EnumConditionsRelation conditionsrelation = (EnumConditionsRelation)dr[DBConditions.conditionsRelation];
getCondition(conditiontype, dr[DBConditions.fieldName],
dr[DBConditions.paramValue], fieldtype, conditionsrelation);
}
if (_condition != string.Empty)
{
_condition = _condition.Remove(_condition.Length - 4, 4);
}
//可在最后一个条件时，不予补And。
#endregion
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public override string ToString()
{
#region
string allconditions = "";
if (this._DBConditions != null)
{
for (int i = 0; i < this._DBConditions.Count; i++)
{
DBCondition condition = this._DBConditions[i];
EnumCondition conditiontype = condition._EnumCondition;
EnumSqlType fieldtype = condition._FieldType;
EnumConditionsRelation conditionsrelation = condition._ConditionsRelation;
allconditions += condition.ToString(conditiontype, condition._FieldName,
condition._ParamValue, fieldtype, conditionsrelation, condition._AlisaOfTable);
}
if (allconditions != string.Empty)
{
allconditions = allconditions.Remove(allconditions.Length - 4, 4);
}
}
return(allconditions);
#endregion
}
/// <summary>
/// 根据传入的各字段参数及类型动态构造SQL语句
/// </summary>
/// <param name="conditionType">条件类型</param>
/// <param name="fieldName">SQL字段名</param>
/// <param name="paramValue">查询值</param>
private void getCondition(EnumCondition conditionType,
object fieldName, object paramValue, EnumSqlType fieldtype,
EnumConditionsRelation conditionsRelation)
{
#region
string convertsqlvalue = "";
string relation = (conditionsRelation == EnumConditionsRelation.And) ? "and" : "or";
switch (conditionType)
{
case EnumCondition.IsNotNull:
if (paramValue.ToString() == string.Empty)
{
_condition += String.Format(conditionIsNotNullFormat,
fieldName, relation);
}
break;
case EnumCondition.EmptyIsNull:
if (paramValue.ToString() == string.Empty)
{
_condition += String.Format(conditionEmptyIsNullFormat,
fieldName, relation);
}
else
{
_condition += String.Format(conditionInitFormat,
fieldName, "=", paramValue, relation);
}
break;
case EnumCondition.Equal:
convertsqlvalue = paramValue.ToString();
if (convertsqlvalue != string.Empty)
{
if (fieldtype == EnumSqlType.bit)
{
convertsqlvalue = Convert.ToBoolean(paramValue) ? "1" : "0";
}
_condition += String.Format(conditionInitFormat,
fieldName, "=", convertsqlvalue, relation);
}
break;
case EnumCondition.LikeBoth:
if (paramValue.ToString() != string.Empty)
{
_condition += String.Format(conditionBothLikeFormat,
fieldName, "like", paramValue, relation);
}
break;
case EnumCondition.LikeLeft:
if (paramValue.ToString() != string.Empty)
{
_condition += String.Format(conditionLeftLikeFormat,
fieldName, "like", paramValue, relation);
}
break;
case EnumCondition.LikeRight:
if (paramValue.ToString() != string.Empty)
{
_condition += String.Format(conditionRightLikeFormat,
fieldName, "like", paramValue, relation);
}
break;
case EnumCondition.Greater:
if (paramValue.ToString() != string.Empty)
{
_condition += String.Format(conditionInitFormat,
fieldName, ">", paramValue, relation);
}
break;
case EnumCondition.GreaterOrEqual:
if (paramValue.ToString() != string.Empty)
{
_condition += String.Format(conditionInitFormat,
fieldName, ">=", paramValue, relation);
}
break;
case EnumCondition.LessOrEqual:
if (paramValue.ToString() != string.Empty)
{
_condition += String.Format(conditionInitFormat,
fieldName, "<=", paramValue, relation);
}
break;
case EnumCondition.Less:
if (paramValue.ToString() != string.Empty)
{
_condition += String.Format(conditionInitFormat,
fieldName, "<", paramValue, relation);
}
break;
case EnumCondition.InValues:
if (paramValue.ToString() != string.Empty)
{
_condition += String.Format(conditionInValuesFormat,
fieldName, paramValue, relation);
}
break;
default:
break;
}
#endregion
}
/// <summary>
///
/// </summary>
/// <param name="conditionType"></param>
/// <param name="fieldName"></param>
/// <param name="paramValue"></param>
/// <param name="fieldtype"></param>
/// <param name="conditionsRelation"></param>
/// <param name="aliasOfTable"></param>
/// <returns></returns>
public string ToString(
EnumCondition conditionType,
object fieldName, object paramValue,
EnumSqlType fieldtype,
EnumConditionsRelation conditionsRelation,
string aliasOfTable)
{
#region
string condition = "";
string relation = (conditionsRelation == EnumConditionsRelation.And) ? "and" : "or";
string fieldnamewithalias = string.IsNullOrEmpty(aliasOfTable)
? string.Format("[{0}]", fieldName)
: string.Format("{0}.[{1}]", aliasOfTable, fieldName);
switch (conditionType)
{
case EnumCondition.IsNotNull:
condition += String.Format(conditionIsNotNullFormat,
fieldnamewithalias, relation);
break;
case EnumCondition.EmptyIsNull:
condition += String.Format(conditionEmptyIsNullFormat,
fieldnamewithalias, relation);
break;
case EnumCondition.Equal:
this.checkParam(paramValue, delegate {
if (fieldtype == EnumSqlType.bit)
{
paramValue = Convert.ToBoolean(paramValue) ? "1" : "0";
}
condition += String.Format(conditionInitFormat,
fieldnamewithalias, "=", paramValue, relation);
});
break;
case EnumCondition.LikeBoth:
this.checkParam(paramValue, delegate {
condition += String.Format(conditionBothLikeFormat,
fieldnamewithalias, "like", paramValue, relation);
});
break;
case EnumCondition.LikeLeft:
this.checkParam(paramValue, delegate
{
condition += String.Format(conditionLeftLikeFormat,
fieldnamewithalias, "like", paramValue, relation);
});
break;
case EnumCondition.LikeRight:
this.checkParam(paramValue, delegate
{
condition += String.Format(conditionRightLikeFormat,
fieldnamewithalias, "like", paramValue, relation);
});
break;
case EnumCondition.Greater:
this.checkParam(paramValue, delegate
{
condition += String.Format(conditionInitFormat,
fieldnamewithalias, ">", paramValue, relation);
});
break;
case EnumCondition.GreaterOrEqual:
this.checkParam(paramValue, delegate
{
condition += String.Format(conditionInitFormat,
fieldnamewithalias, ">=", paramValue, relation);
});
break;
case EnumCondition.LessOrEqual:
this.checkParam(paramValue, delegate
{
condition += String.Format(conditionInitFormat,
fieldnamewithalias, "<=", paramValue, relation);
});
break;
case EnumCondition.Less:
this.checkParam(paramValue, delegate
{
condition += String.Format(conditionInitFormat,
fieldnamewithalias, "<", paramValue, relation);
});
break;
case EnumCondition.InValues:
this.checkParam(paramValue, delegate
{
condition += String.Format(conditionInValuesFormat,
fieldnamewithalias, paramValue, relation);
});
break;
default:
break;
}
return(condition);
#endregion
}