/// <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();
}
private string GetExpresstionString(ExpressionRelation _expressionRelation, string _field1, string _field2, string _field3)
{
string _return = "";
switch (_expressionRelation)
{
case ExpressionRelation.Between:
_return = _field1 + " BETWEEN " + _field2 + " AND " + _field3;
break;
case ExpressionRelation.Custom:
_return = _field1;
break;
case ExpressionRelation.Equal:
_return = _field1 + " = " + _field2;
break;
case ExpressionRelation.In:
_return = _field1 + " IN (" + _field2 + ")";
break;
case ExpressionRelation.LessEqualThen:
_return = _field1 + " <= " + _field2;
break;
case ExpressionRelation.LessThen:
_return = _field1 + " < " + _field2;
break;
case ExpressionRelation.Like:
_return = _field1 + " LIKE " + _field2 + "";
break;
case ExpressionRelation.MoreEqualThen:
_return = _field1 + " >= " + _field2;
break;
case ExpressionRelation.MoreThen:
_return = _field1 + " > " + _field2;
break;
case ExpressionRelation.NotBetween:
_return = _field1 + " NOT BETWEEN " + _field2 + " AND " + _field3;
break;
case ExpressionRelation.NotEqual:
_return = _field1 + " <> " + _field2;
break;
case ExpressionRelation.NotIn:
_return = _field1 + " NOT IN (" + _field2 + ")";
break;
case ExpressionRelation.NotLike:
_return = _field1 + " NOT LIKE '" + _field2 + "'";
break;
case ExpressionRelation.NotNull:
_return = _field1 + " IS NOT NULL";
break;
case ExpressionRelation.Null:
_return = _field1 + " IS NULL";
break;
}
return(_return);
}
private string GetExpression(ExpressionMode _expressionMode, ExpressionRelation _expressionRelation, object _fieldObject1, object _fieldObject2, object _fieldObject3, ref SqlCommand _sqlCommand, Random _random)
{
string _return = "";
string _field1 = _fieldObject1 == null ? "" : _fieldObject1.ToString();
string _field2 = _fieldObject2 == null ? "" : _fieldObject2.ToString();
string _field3 = _fieldObject3 == null ? "" : _fieldObject3.ToString();
if (_expressionRelation != ExpressionRelation.Custom)
{
switch (_expressionMode)
{
case ExpressionMode.FieldVsField:
_field1 = "[" + _field1.Replace(".", "].[") + "]";
_field2 = "[" + _field2.Replace(".", "].[") + "]";
_field3 = "[" + _field3.Replace(".", "].[") + "]";
_return = GetExpresstionString(_expressionRelation, _field1, _field2, _field3);
break;
case ExpressionMode.FieldVsFieldVsField:
_field1 = "[" + _field1.Replace(".", "].[") + "]";
_field2 = "[" + _field2.Replace(".", "].[") + "]";
_field3 = "[" + _field3.Replace(".", "].[") + "]";
_return = GetExpresstionString(_expressionRelation, _field1, _field2, _field3);
break;
case ExpressionMode.FieldVsFieldVsValue:
_field3 = "@" + _field1.Replace(".", "_") + _random.Next().ToString();
_field1 = "[" + _field1.Replace(".", "].[") + "]";
_field2 = "[" + _field2.Replace(".", "].[") + "]";
_sqlCommand.Parameters.AddWithValue(_field3, _fieldObject3);
_return = GetExpresstionString(_expressionRelation, _field1, _field2, _field3);
break;
case ExpressionMode.FieldVsValue:
_field2 = "@" + _field1.Replace(".", "_") + _random.Next().ToString();
_field1 = "[" + _field1.Replace(".", "].[") + "]";
_field3 = "[" + _field1.Replace(".", "].[") + "]";
if (_expressionRelation == ExpressionRelation.Like)
{
SqlParameter _sqlParameter = new SqlParameter(_field2, SqlDbType.NVarChar);
_sqlParameter.Value = _fieldObject2;
_sqlCommand.Parameters.Add(_sqlParameter);
}
else if (_expressionRelation == ExpressionRelation.In || _expressionRelation == ExpressionRelation.NotIn)
{
object[] _array = (object[])_fieldObject2;
string _field2Value = "";
foreach (object _value in _array)
{
_field2Value += _field2Value == "" ? "" : ",";
string _field2Name = _field2 + _random.Next().ToString();
_field2Value += _field2Name;
_sqlCommand.Parameters.AddWithValue(_field2Name, _value);
}
_field2 = _field2Value;
}
else if (_fieldObject2.GetType() == Guid.NewGuid().GetType())
{
SqlParameter _sqlParameter = new SqlParameter(_field2, SqlDbType.UniqueIdentifier);
_sqlParameter.Value = _fieldObject2;
_sqlCommand.Parameters.Add(_sqlParameter);
}
else
{
_sqlCommand.Parameters.AddWithValue(_field2, _fieldObject2);
}
_return = GetExpresstionString(_expressionRelation, _field1, _field2, _field3);
break;
case ExpressionMode.FieldVsValueVsField:
_field2 = "@" + _field1.Replace(".", "_") + _random.Next().ToString();
_field1 = "[" + _field1.Replace(".", "].[") + "]";
_field3 = "[" + _field1.Replace(".", "].[") + "]";
_sqlCommand.Parameters.AddWithValue(_field2, _fieldObject2);
_return = GetExpresstionString(_expressionRelation, _field1, _field2, _field3);
break;
case ExpressionMode.FieldVsValueVsValue:
_field2 = "@" + _field1.Replace(".", "_") + "1" + _random.Next().ToString();
_field3 = "@" + _field1.Replace(".", "_") + "2" + _random.Next().ToString();
_field1 = "[" + _field1.Replace(".", "].[") + "]";
_sqlCommand.Parameters.AddWithValue(_field2, _fieldObject2);
_sqlCommand.Parameters.AddWithValue(_field3, _fieldObject3);
_return = GetExpresstionString(_expressionRelation, _field1, _field2, _field3);
break;
case ExpressionMode.ValueVsValueVsValue:
_field1 = "@" + _field1.Replace(".", "_") + "1" + _random.Next().ToString();
_field2 = "@" + _field1.Replace(".", "_") + "2" + _random.Next().ToString();
_field3 = "@" + _field1.Replace(".", "_") + "3" + _random.Next().ToString();
_sqlCommand.Parameters.AddWithValue(_field1, _fieldObject1);
_sqlCommand.Parameters.AddWithValue(_field2, _fieldObject2);
_sqlCommand.Parameters.AddWithValue(_field3, _fieldObject3);
_return = GetExpresstionString(_expressionRelation, _field1, _field2, _field3);
break;
case ExpressionMode.Custom:
_field1 = _fieldObject1.ToString();
_field2 = "";
_field3 = "";
_return = " " + _field1;
break;
}
}
else
{
_return = _fieldObject1.ToString();
}
return(_return);
}
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
}
}
