/// <summary>
///
/// </summary>
/// <param name="fieldName"></param>
/// <param name="fieldType"></param>
/// <param name="conditionType"></param>
/// <param name="paramValue"></param>
/// <returns></returns>
public DBConditions Or(string fieldName, EnumSqlType fieldType,
EnumCondition conditionType, object paramValue)
{
#region
return(this.add(fieldName, fieldType, conditionType, paramValue, EnumConditionsRelation.Or));
#endregion
}
/*
* /// <summary>
* /// 输出字符串未实现（？？？）
* /// </summary>
* /// <returns></returns>
* public string ToString()
* {
#region
* StringBuilder str = new StringBuilder();
* object[] param = new object[5];
* foreach (DBCondition temp in _DBConditions)
* {
*
* param[0] = temp._UId;
* param[1] = temp.IPAddress;
* param[2] = temp.Port;
* param[3] = temp._LogonTime;
* param[4] = temp._UpdateTime;
*
* str.AppendLine(string.Format("UID:{0},IPAddress:{1},ClientPort:{2},LoginTime:{3},UpdateTime:{4}",
* param));
*
* }
* return str.ToString();
#endregion
* }
*/
/// <summary>
///
/// </summary>
/// <param name="fieldName"></param>
/// <param name="conditionType"></param>
/// <param name="paramValue"></param>
/// <param name="fieldType"></param>
public void AddAndCondition(string fieldName,
EnumCondition conditionType
, object paramValue,
EnumSqlType fieldType)
{
#region
AddAndCondition(fieldName,
conditionType
, paramValue,
fieldType, null);
#endregion
}
public void AddAndCondition(string fieldName,
EnumCondition conditionType
, object paramValue,
EnumSqlType fieldType, string aliasOfTable)
{
this.Add(new DBCondition()
{
_FieldName = fieldName,
_ParamsName = fieldName,
_ParamValue = paramValue,
_ConditionsRelation = EnumConditionsRelation.And,
_EnumCondition = conditionType,
_FieldType = fieldType,
_AlisaOfTable = aliasOfTable
});
}
/// <summary>
///
/// </summary>
/// <param name="fieldName"></param>
/// <param name="conditionType"></param>
/// <param name="paramValue"></param>
/// <param name="fieldType"></param>
public void AddOrCondition(string fieldName,
EnumCondition conditionType,
object paramValue,
EnumSqlType fieldType)
{
#region
this.Add(new DBCondition()
{
_FieldName = fieldName,
_ParamsName = fieldName,
_ParamValue = paramValue,
_ConditionsRelation = EnumConditionsRelation.Or,
_EnumCondition = conditionType,
_FieldType = fieldType
});
#endregion
}
/// <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>
/// 从前台获取的字符串，转各类型forSql
/// </summary>
/// <param name="src"></param>
/// <param name="type"></param>
/// <returns></returns>
public static object ConvertForSQL(string src, EnumSqlType type)
{
#region
string source = "";
if (src != null)
{
source = src.Trim();
return(System.DBNull.Value);
}
object rtValue = null;
bool issuccess = false;
switch (type)
{
case EnumSqlType.bigint:
{
long temp;
issuccess = Int64.TryParse(source, out temp);
rtValue = temp;
break;
}
case EnumSqlType.bit:
{
bool temp;
issuccess = bool.TryParse(source, out temp);
rtValue = temp;
break;
}
case EnumSqlType.datetime:
{
DateTime temp;
issuccess = DateTime.TryParse(source, out temp);
rtValue = temp;
break;
}
case EnumSqlType.sqldecimal:
{
decimal temp;
issuccess = decimal.TryParse(source, out temp);
rtValue = temp;
break;
}
case EnumSqlType.ntext:
{
if (source != string.Empty)
{
issuccess = true;
rtValue = source;
}
break;
}
case EnumSqlType.nvarchar:
{
if (source != string.Empty)
{
issuccess = true;
rtValue = source;
}
break;
}
case EnumSqlType.varchar:
{
if (source != string.Empty)
{
issuccess = true;
rtValue = source;
}
break;
}
case EnumSqlType.tinyint:
{
byte temp;
issuccess = byte.TryParse(source, out temp);
rtValue = temp;
break;
}
case EnumSqlType.uniqguid:
{
Guid temp;
issuccess = Guid.TryParse(source, out temp);
rtValue = temp;
break;
}
case EnumSqlType.text:
{
if (source != string.Empty)
{
issuccess = true;
rtValue = source;
}
break;
}
case EnumSqlType.sqlnumeric:
{
decimal temp;
issuccess = decimal.TryParse(source, out temp);
rtValue = temp;
break;
}
case EnumSqlType.sqlint:
{
int temp;
issuccess = int.TryParse(source, out temp);
rtValue = temp;
break;
}
default:
{
break;
}
}
if (!issuccess)
{
return(System.DBNull.Value);
}
else
{
return(rtValue);
}
#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
}