/// <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 }