/// <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> /// 根据传入的各字段参数及类型动态构造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 }