/// <summary>
/// add where condition with list
/// </summary>
/// <param name="Relation"></param>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="Value"></param>
public void AddWhere(WhereRelation Relation, String ColumnName, CommandComparison Comparison, object[] Value)
{
String Condition;
if (String.IsNullOrEmpty(ColumnName))
{
throw new Exception("Values is null.");
}
if (!(Comparison == CommandComparison.In || Comparison == CommandComparison.NotIn))
{
throw new Exception("only support In comparison.");
}
Condition = "(";
foreach (object value in Value)
{
if (CommonService.CheckValueType(value))
{
Condition = Condition + "'" + value + "',";
}
else
{
Condition = Condition + value + ",";
}
}
Condition += ")";
Condition = Condition.Replace(",)", ")");
condition.Add(String.Format(" {0} {1} {2} {3}",
CommonService.GetWhereRelation(Relation), ColumnName, CommonService.ConvertComparison(Comparison), Condition));
}
/// <summary>
/// Where(WhereRelation) 构造函数
/// </summary>
/// <param name="_whereRelation">与前条件关系</param>
/// <param name="_whereString">条件语句</param>
public Where(WhereRelation _whereRelation, string _whereString)
{
this.WhereRelation = _whereRelation;
this.HasSubWhere = false;
this.ExpressionRelation = ExpressionRelation.Custom;
this.Field1 = _whereString;
this.Wheres = new WhereCollection();
}
public WhereClause(string column, WhereRelation relation, object value) : this()
{
string pName = Utils.GetParameterName("where_" + column);
whereClauseString = string.Format("{0} {1} {2}",
column, GetSymbol(relation), pName
);
parameters.Add(pName, value);
}
/// <summary>
/// add where condition with select query
/// </summary>
/// <param name="Relation"></param>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="SelectCommand"></param>
public void AddWhereBySelect(WhereRelation Relation, String ColumnName, CommandComparison Comparison, String SelectCommand)
{
if (String.IsNullOrEmpty(ColumnName))
{
throw new Exception("ColumnName is null.");
}
condition.Add(String.Format(" {0} {1} {2} ({3})",
CommonService.GetWhereRelation(Relation), ColumnName, CommonService.ConvertComparison(Comparison), SelectCommand));
}
/// <summary>
/// Where(WhereRelation,ExpressionMode,ExpressionRelation,object,object,object) 构造函数
/// </summary>
/// <param name="_whereRelation">与前条件关系</param>
/// <param name="_expressionMode">表达式模式</param>
/// <param name="_expressionRelation">表达式关系</param>
/// <param name="_field1">字段1的内容</param>
/// <param name="_field2">字段2的内容</param>
/// <param name="_field3">字段3的内容</param>
public Where(WhereRelation _whereRelation, ExpressionMode _expressionMode, ExpressionRelation _expressionRelation, object _field1, object _field2, object _field3)
{
this.WhereRelation = _whereRelation;
this.ExpressionMode = _expressionMode;
this.ExpressionRelation = _expressionRelation;
this.Field1 = _field1;
this.Field2 = _field2;
this.Field3 = _field3;
this.HasSubWhere = false;
this.Wheres = new WhereCollection();
}
/// <summary>
/// add where condition with RelationShip
/// </summary>
/// <param name="Relation"></param>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="ColumnName"></param>
public void AddWhereByRelationShip(WhereRelation Relation, String LeftColumnName, CommandComparison Comparison, String RightColumnName)
{
if (String.IsNullOrEmpty(LeftColumnName) || String.IsNullOrEmpty(RightColumnName))
{
throw new Exception("Cloumn name is null.");
}
condition.Add(String.Format(" {0} {1} {2} {3}",
CommonService.GetWhereRelation(Relation), LeftColumnName, CommonService.ConvertComparison(Comparison), RightColumnName));
}
/// <summary>
/// A where condition with a sub-query.
/// The sub-query needs to be completed and fully parametrized when this constructor is called.
/// </summary>
/// <param name="column"></param>
/// <param name="relation"></param>
/// <param name="subQuery"></param>
public WhereClause(string column, WhereRelation relation, Query subQuery) : this()
{
whereClauseString = string.Format("{0} {1} ({2})",
column, GetSymbol(relation), subQuery.QueryString
);
foreach (KeyValuePair <string, object> param in subQuery.Parameters())
{
parameters.Add(param.Key, param.Value);
}
}
public static String GetWhereRelation(WhereRelation where)
{
if (where == WhereRelation.And)
{
return("and");
}
else if (where == WhereRelation.Or)
{
return("or");
}
else
{
return("");
}
}
/// <summary>
/// add where condition
/// </summary>
/// <param name="Relation"></param>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="value"></param>
public void AddWhere(WhereRelation Relation, String ColumnName, CommandComparison Comparison, object value)
{
if (String.IsNullOrEmpty(ColumnName))
{
throw new Exception("Values is null.");
}
if (CommonService.CheckValueType(value))
{
condition.Add(String.Format(" {0} {1} {2} {3}",
CommonService.GetWhereRelation(Relation), ColumnName, CommonService.ConvertComparison(Comparison), "'" + value + "'"));
}
else
{
condition.Add(String.Format(" {0} {1} {2} {3}",
CommonService.GetWhereRelation(Relation), ColumnName, CommonService.ConvertComparison(Comparison), value));
}
}
static string GetSymbol(WhereRelation relation)
{
switch (relation)
{
case WhereRelation.Equal:
return("=");
case WhereRelation.NotEqual:
return("<>");
case WhereRelation.LesserThan:
return("<");
case WhereRelation.UpTo:
return("<=");
case WhereRelation.BiggerThan:
return(">");
case WhereRelation.AtLeast:
return(">=");
case WhereRelation.Like:
return("LIKE");
case WhereRelation.Exists:
return("EXISTS");
case WhereRelation.NotExists:
return("NOT EXISTS");
case WhereRelation.In:
return("IN");
case WhereRelation.NotIn:
return("NOT IN");
default:
throw new ArgumentException("Unknown WHERE operator: " + relation);
}
}
/// <summary>
/// add where condition with between
/// </summary>
/// <param name="Relation"></param>
/// <param name="ColumnName"></param>
/// <param name="Comparison"></param>
/// <param name="value1"></param>
/// <param name="value2"></param>
public void AddWhere(WhereRelation Relation, String ColumnName, CommandComparison Comparison, object value1, object value2)
{
String Condition;
if (String.IsNullOrEmpty(ColumnName))
{
throw new Exception("Values is null.");
}
if (!(Comparison == CommandComparison.Between || Comparison == CommandComparison.NotBetween))
{
throw new Exception("only support Between comparison.");
}
if (CommonService.CheckValueType(value1))
{
Condition = String.Format(" {0} {1} {2} {3}",
CommonService.GetWhereRelation(Relation), ColumnName, CommonService.ConvertComparison(Comparison), "'" + value1 + "'");
}
else
{
Condition = String.Format(" {0} {1} {2} {3}",
CommonService.GetWhereRelation(Relation), ColumnName, CommonService.ConvertComparison(Comparison), value1);
}
if (CommonService.CheckValueType(value2))
{
Condition += " and '" + value2 + "'";
}
else
{
Condition += " and " + value2;
}
condition.Add(Condition);
}
/// <summary>
/// Where(WhereRelation,bool) 构造函数
/// </summary>
/// <param name="_whereRelation">与前条件关系</param>
/// <param name="_hasSubCondition">是否有子条件</param>
public Where(WhereRelation _whereRelation, bool _hasSubCondition)
{
this.WhereRelation = _whereRelation;
this.HasSubWhere = true;
this.Wheres = new WhereCollection();
}
private void Add(WhereRelation _relation, object[] _values)
{
bool[] _fields = new bool[_values.Length];
for (int i = 0; i < _fields.Length; i++)
{
_fields[i] = _values[i].GetType() == typeof(string) && Regex.IsMatch(_values[i].ToString(), @"\[.+\]");
if (_fields[i])
{
_values[i] = _values[i].ToString().Substring(1, _values[i].ToString().Length - 2);
}
}
switch (_values.Length)
{
case 1:
#region 1 自定义条件(string) or 是否有自条件(bool) or 条件对象(Condition)
{
if (_values[0].GetType() == typeof(Where))
{
base.Add((Where)_values[0]);
}
else if (_values[0].GetType() == typeof(bool))
{
base.Add(new Where(_relation, (bool)_values[0]));
}
else
{
base.Add(new Where(_relation, _values[0].ToString()));
}
break;
}
#endregion
case 2:
#region 2 单一字段条件 NULL,NOTNULL
{
switch (_values[1].ToString())
{
case "NULL": base.Add(new Where(_relation, ExpressionMode.FieldVsField, ExpressionRelation.Null, _values[0])); break;
case "NOTNULL": base.Add(new Where(_relation, ExpressionMode.FieldVsField, ExpressionRelation.NotNull, _values[0])); break;
}
break;
}
#endregion
case 3:
#region 3 两个字段条件 =,<>,>,>=,<,<=,LIKE,NOTLIKE,IN,NOTIN
{
ExpressionMode _mode = _fields[2] ? ExpressionMode.FieldVsField : ExpressionMode.FieldVsValue;
switch (_values[1].ToString())
{
case "=": base.Add(new Where(_relation, _mode, ExpressionRelation.Equal, _values[0], _values[2])); break;
case "<>": base.Add(new Where(_relation, _mode, ExpressionRelation.NotEqual, _values[0], _values[2])); break;
case ">": base.Add(new Where(_relation, _mode, ExpressionRelation.MoreThen, _values[0], _values[2])); break;
case ">=": base.Add(new Where(_relation, _mode, ExpressionRelation.MoreEqualThen, _values[0], _values[2])); break;
case "<": base.Add(new Where(_relation, _mode, ExpressionRelation.LessThen, _values[0], _values[2])); break;
case "<=": base.Add(new Where(_relation, _mode, ExpressionRelation.LessEqualThen, _values[0], _values[2])); break;
case "LIKE": base.Add(new Where(_relation, _mode, ExpressionRelation.Like, _values[0], _values[2])); break;
case "NOTLIKE": base.Add(new Where(_relation, _mode, ExpressionRelation.NotLike, _values[0], _values[2])); break;
case "IN":
#region IN
{
object[] _valueList;
if (_values[2].GetType() == typeof(Array) || _values[2].GetType().BaseType == typeof(Array))
{
List <object> _objects = new List <object>();
Array _array = (Array)_values[2];
for (int i = 0; i < _array.Length; i++)
{
_objects.Add(_array.GetValue(i));
}
_valueList = _objects.ToArray();
}
else
{
_valueList = new object[] { _values[2] };
}
base.Add(new Where(_relation, ExpressionMode.FieldVsValue, ExpressionRelation.In, _values[0], _valueList));
break;
}
#endregion
case "NOTIN":
#region NOTIN
{
object[] _valueList;
if (_values[2].GetType() == typeof(Array) || _values[2].GetType().BaseType == typeof(Array))
{
_valueList = (object[])_values[2];
}
else
{
_valueList = new object[] { _values[2] };
}
base.Add(new Where(_relation, ExpressionMode.FieldVsValue, ExpressionRelation.NotIn, _values[0], _valueList));
break;
}
#endregion
}
break;
}
#endregion
default:
#region 个以上字段条件 BETWEEN,NOTBETWEEN,IN,NOTIN
{
if ((_values[1].ToString() == "BETWEEN" || _values[1].ToString() == "NOTBETWEEN") && _fields.Length == 4)
{
ExpressionRelation _expressRelation = _values[1].ToString() == "BETWEEN" ? ExpressionRelation.Between : ExpressionRelation.NotBetween;
ExpressionMode _mode = ExpressionMode.FieldVsValueVsValue;
if (_fields[2] && _fields[3])
{
_mode = ExpressionMode.FieldVsFieldVsField;
}
if (_fields[2] && !_fields[3])
{
_mode = ExpressionMode.FieldVsFieldVsValue;
}
if (!_fields[2] && _fields[3])
{
_mode = ExpressionMode.FieldVsValueVsField;
}
if (!_fields[2] && !_fields[3])
{
_mode = ExpressionMode.FieldVsValueVsValue;
}
base.Add(new Where(_relation, _mode, _expressRelation, _values[0], _values[2], _values[3]));
}
else if (_values[1].ToString() == "IN" || _values[1].ToString() == "NOTIN")
{
ExpressionRelation _expressRelation = _values[1].ToString() == "IN" ? ExpressionRelation.In : ExpressionRelation.NotIn;
if (_values[2] is Array)
{
base.Add(new Where(_relation, ExpressionMode.FieldVsValue, _expressRelation, _values[0], _values[2]));
}
else
{
object[] _valueList = new object[_values.Length - 2];
Array.Copy(_values, 2, _valueList, 0, _valueList.Length);
base.Add(new Where(_relation, ExpressionMode.FieldVsValue, _expressRelation, _values[0], _valueList));
}
}
break;
}
#endregion
}
}
/// <summary>
/// Where(WhereRelation) 构造函数
/// </summary>
/// <param name="_whereRelation">与前条件关系</param>
public Where(WhereRelation _whereRelation)
{
this.WhereRelation = _whereRelation;
this.HasSubWhere = false;
this.Wheres = new WhereCollection();
}
/// <summary>
/// 设置查询时的条件关系，默认为AND关系
/// </summary>
/// <param name="whereRelation"></param>
public DbEntity SetWhereRelation(WhereRelation whereRelation)
{
this.whereRelation = whereRelation;
return(this);
}
/// <summary>
/// Append a new conition with a logical AND
/// <para>To append with OR, use new WhereClause(...)</para>
/// </summary>
/// <param name="column"></param>
/// <param name="relation"></param>
/// <param name="value"></param>
/// <returns></returns>
public ConditionalQuery Where(string column, WhereRelation relation, object value)
{
return(Where(new WhereClause(column, relation, value)));
}
/// <summary>
/// Append a new sub-query conition with a logical AND and cast to another query type
/// </summary>
/// <typeparam name="Q"></typeparam>
/// <param name="column"></param>
/// <param name="relation"></param>
/// <param name="subQuery"></param>
/// <returns></returns>
public Q Where <Q>(string column, WhereRelation relation, Query subQuery) where Q : ConditionalQuery
{
return((Q)Where(new WhereClause(column, relation, subQuery)));
}
/// <summary>
/// Append a new sub-query conition with a logical AND
/// </summary>
/// <param name="column"></param>
/// <param name="relation"></param>
/// <param name="subQuery"></param>
/// <returns></returns>
public ConditionalQuery Where(string column, WhereRelation relation, Query subQuery)
{
return(Where(new WhereClause(column, relation, subQuery)));
}
/// <summary>
/// Append a new conition with a logical AND and cast to another query type
/// </summary>
/// <typeparam name="Q"></typeparam>
/// <param name="column"></param>
/// <param name="relation"></param>
/// <param name="value"></param>
/// <returns></returns>
public Q Where <Q>(string column, WhereRelation relation, object value) where Q : ConditionalQuery
{
return((Q)Where(new WhereClause(column, relation, value)));
}
