public OQLCompare Comparer(object field, CompareType type, OQL Value)
{
OQLCompare compare = new OQLCompare(this.LinkedOQL);
var tnf = compare.LinkedOQL.TakeOneStackFields();
compare.ComparedFieldName = tnf.SqlFieldName;
compare.ComparedType = type;
if (type == CompareType.IS || type == CompareType.IsNot)
{
throw new ArgumentOutOfRangeException("IS 操作符的不支持子查询！");
}
else
{
string childSql = Value.ToString();
if (Value.Parameters.Count > 0)
{
//先备份SQL语句中的参数名 ，感谢网友 null(yoli799480165) 发现此bug
foreach (string key in Value.Parameters.Keys)
{
childSql = childSql.Replace(key, key + "_C");
}
foreach (string key in Value.Parameters.Keys)
{
var vtnf = Value.Parameters[key];
string paraName = this.LinkedOQL.CreateParameter(vtnf);
childSql = childSql.Replace(key + "_C", paraName);
}
}
compare.ComparedParameterName = "\r\n(" + childSql + ")\r\n";
}
compare.SqlFunctionFormat = "";
return(compare);
}
/// <summary>
/// 判断指定字段条件为空 Is Not NULL
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
public OQLCompare IsNotNull(object field)
{
OQLCompare compare = this.IsNull(field);
compare.ComparedType = CompareType.IsNot;
return(compare);
}
/// <summary>
/// 判断指定字段条件为空 Is NULL
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
public OQLCompare IsNull(object field)
{
OQLCompare compare = new OQLCompare(this.LinkedOQL);
compare.ComparedFieldName = compare.LinkedOQL.TakeOneStackFields().Field;
compare.ComparedParameterName = "NULL";
compare.ComparedType = CompareType.IS;
compare.LinkedOQL.fieldStack.Clear();
return(compare);
}
private static bool IsEmptyCompare(OQLCompare cmp)
{
if (object.Equals(cmp, null))
{
return(true);
}
if (cmp.IsLeaf && string.IsNullOrEmpty(cmp.ComparedFieldName))
{
return(true);
}
return(false);
}
//public OQLCompare Comparer(object field, string cmpTypeString, DateTime[] Value, string sqlFunctionFormat)
//{
// return this.Comparer(field, CompareString2Type(cmpTypeString), Value, sqlFunctionFormat);
//}
//public OQLCompare Comparer(object field, string cmpTypeString, ValueType[] Value, string sqlFunctionFormat)
//{
// return this.Comparer(field, CompareString2Type(cmpTypeString), Value, sqlFunctionFormat);
//}
/// <summary>
/// 将当前实体属性的值作为比较的值，得到一个新的实体比较对象
/// </summary>
/// <param name="field">实体对象的属性字段</param>
/// <returns>比较表达式</returns>
public OQLCompare EqualValue(object field)
{
OQLCompare compare = new OQLCompare(this.LinkedOQL);
var tnf = compare.LinkedOQL.TakeOneStackFields();
compare.ComparedFieldName = tnf.SqlFieldName;
compare.ComparedParameterName = compare.LinkedOQL.CreateParameter(tnf, field);
compare.ComparedType = CompareType.Equal;
compare.LinkedOQL.fieldStack.Clear();
return(compare);
}
/// <summary>
/// 对一组OQLCompare 对象，执行CompareLogic 类型的比较，通常用于构造复杂的带括号的条件查询
/// <example>参见 http://www.cnblogs.com/bluedoctor/archive/2011/02/24/1963606.html </example>
/// </summary>
/// <param name="compares">OQL比较对象列表</param>
/// <param name="logic">各组比较条件的组合方式，And，Or，Not</param>
/// <returns>新的条件比较对象</returns>
public OQLCompare Comparer(List <OQLCompare> compares, CompareLogic logic)
{
if (compares == null || compares.Count == 0)
{
throw new Exception("OQL 条件比较对象集合不能为空或者空引用！");
}
if (compares.Count == 1)
{
return(compares[0]);
}
OQLCompare cmp = new OQLCompare(this.LinkedOQL);
//string typeString = logic == CompareLogic.AND ? " And " : logic == CompareLogic.OR ? " Or " : " Not ";
//foreach (OQLCompare item in compares)
//{
// cmp.CompareString += item.CompareString + typeString;
// if (item.ComparedParameters != null)
// foreach (string key in item.ComparedParameters.Keys)
// {
// cmp.ComparedParameters.Add(key, item.ComparedParameters[key]);
// }
//}
//cmp.CompareString = cmp.CompareString.Substring(0, cmp.CompareString.Length - typeString.Length);
//cmp.CompareString = " ( " + cmp.CompareString + " ) ";
//return cmp;
//
//将列表转换成树
foreach (OQLCompare item in compares)
{
if (object.Equals(cmp.LeftNode, null))
{
cmp.LeftNode = item;
cmp.Logic = logic;
}
else if (object.Equals(cmp.RightNode, null))
{
cmp.RightNode = item;
}
else
{
var newCmp = new OQLCompare(this.LinkedOQL);
newCmp.LeftNode = cmp;
newCmp.Logic = logic;
newCmp.RightNode = item;
cmp = newCmp;
}
}
return(cmp);
}
public OQLCompare Comparer <T>(T field, CompareType type, T[] Value, string sqlFunctionFormat)
{
if (Value == null && (type == CompareType.IN || type == CompareType.NotIn))
{
throw new ArgumentNullException("IN 条件的参数不能为空！");
}
OQLCompare compare = new OQLCompare(this.LinkedOQL);
var tnf = compare.LinkedOQL.TakeOneStackFields();
compare.ComparedFieldName = tnf.SqlFieldName;
compare.ComparedType = type;
compare.SqlFunctionFormat = sqlFunctionFormat;
if (type == CompareType.IN || type == CompareType.NotIn)
{
string[] paraNames = new string[Value.Length];
//如果 field== DBNull.value 下面的类型判断将不准。
//解决网友 Love๑ 的问题
Type t = field == null? typeof(string) : field.GetType();
//网友Super Show 发现GUID和枚举问题 2014.3.11
if (t == typeof(string) || t == typeof(DateTime) || t == typeof(Guid) || t.IsEnum)
{
for (int i = 0; i < Value.Length; i++)
{
paraNames[i] = compare.LinkedOQL.CreateParameter(tnf, Value[i]);
}
compare.ComparedParameterName = "(" + string.Join(",", paraNames) + ")";
}
else if (t.IsValueType)
{
for (int i = 0; i < Value.Length; i++)
{
paraNames[i] = Value[i].ToString();
}
compare.ComparedParameterName = "(" + string.Join(",", paraNames) + ")";
}
else
{
throw new ArgumentException("IN,Not In 操作只支持字符串数组或者值类型数组参数");
}
}
else if (type == CompareType.IS || type == CompareType.IsNot)
{
compare.ComparedParameterName = "NULL";
}
else
{
throw new ArgumentException("当前方法只允许使用IN 或者 NOT IN，否则请使用另外的重载方法");
}
return(compare);
}
/// <summary>
/// 指定条件的包含范围
/// </summary>
/// <typeparam name="T">属性字段的类型</typeparam>
/// <param name="field">属性字段</param>
/// <param name="beginValue">起始值</param>
/// <param name="endValue">结束值</param>
/// <returns>比较对象</returns>
public OQLCompare Between <T>(T field, T beginValue, T endValue)
{
OQLCompare compare = new OQLCompare(this.LinkedOQL);
var tnf = compare.LinkedOQL.TakeOneStackFields();
compare.ComparedFieldName = tnf.SqlFieldName;
compare.ComparedParameterName = string.Format(" {0} AND {1} "
, compare.LinkedOQL.CreateParameter(tnf, beginValue)
, compare.LinkedOQL.CreateParameter(tnf, endValue));
compare.ComparedType = CompareType.Between;
compare.LinkedOQL.fieldStack.Clear();
return(compare);
}
public OQLCompare Count(CompareType type, object Value)
{
OQLCompare compare = new OQLCompare(this.LinkedOQL);
if (type == CompareType.IS || type == CompareType.IN || type == CompareType.NotIn)
{
throw new ArgumentOutOfRangeException("IS,IN,NOT IN 操作符请使用Count 方法不受支持！");
}
compare.ComparedFieldName = "*";
compare.ComparedType = type;
compare.ComparedParameterName = compare.LinkedOQL.CreateParameter(null, Value);
compare.SqlFunctionFormat = "COUNT({0})";
return(compare);
}
/// <summary>
/// 采用两个实体比较对象按照某种比较逻辑进行处理，构造一个新的实体比较对象
/// </summary>
/// <seealso cref="http://www.cnblogs.com/bluedoctor/archive/2010/11/28/1870095.html"/>
/// <param name="compare1">比较逻辑符号左边的实体比较对象</param>
/// <param name="logic">比较逻辑</param>
/// <param name="compare2">比较逻辑符号左边的实体比较对象</param>
public OQLCompare(OQLCompare compare1, CompareLogic logic, OQLCompare compare2)
{
if (object.Equals(compare1, null))
{
throw new ArgumentNullException("参数compare1 不能为空！");
}
if (object.Equals(compare2, null) && logic != CompareLogic.NOT)
{
throw new ArgumentNullException("参数compare2 为空的时候，只能是NOT操作！");
}
this.LinkedOQL = compare1.LinkedOQL;
this.LeftNode = compare1;
this.RightNode = compare2;
this.Logic = logic;
}
private static OQLCompare BuildOperator(OQLCompare compare, object Value, CompareType cmpType)
{
//如果Value是字段属性，将出错
if (compare.LinkedOQL == null)
{
throw new ArgumentException("OQLCompare 关联的OQL对象为空！参数无效，参数名：compare");
}
var tnf = compare.LinkedOQL.TakeOneStackFields();
compare.ComparedFieldName = tnf.SqlFieldName;
compare.ComparedParameterName = compare.LinkedOQL.CreateParameter(tnf, Value);
compare.ComparedType = cmpType;
compare.LinkedOQL.fieldStack.Clear();
return(compare);
}
/// <summary>
/// 检查子节点的逻辑类型
/// </summary>
/// <param name="childCmp"></param>
/// <param name="currLogic"></param>
/// <returns></returns>
private bool CheckChildLogicEquals(OQLCompare childCmp, CompareLogic currLogic)
{
//currCmp 不能是叶子结点
//如果子节点的逻辑类型不同于当前逻辑类型，直接返回 非
if (childCmp.Logic != currLogic)
{
return(false);
}
//如果子节点的逻辑类型 同于当前逻辑类型，则需要检查子节点的左右子节点与当前逻辑类型的对比
if (childCmp.LeftNode.IsLeaf && childCmp.RightNode.IsLeaf)
{
return(childCmp.Logic == currLogic);
}
else
{
if (!childCmp.LeftNode.IsLeaf && !childCmp.RightNode.IsLeaf)
{
bool left_flag = false;
bool right_flag = false;
left_flag = CheckChildLogicEquals(childCmp.LeftNode, currLogic);
right_flag = CheckChildLogicEquals(childCmp.RightNode, currLogic);
return(left_flag && right_flag);
}
else if (!childCmp.LeftNode.IsLeaf && childCmp.RightNode.IsLeaf)
{
return(CheckChildLogicEquals(childCmp.LeftNode, currLogic));
}
else if (childCmp.LeftNode.IsLeaf && !childCmp.RightNode.IsLeaf)
{
return(CheckChildLogicEquals(childCmp.RightNode, currLogic));
}
else
{
return(false);
}
}
}
/// <summary>
/// 获取复杂查询条件(具有逻辑优先级的复杂比较条件）
/// </summary>
/// <param name="compare">实体对象比较类</param>
/// <returns>基本表达式</returns>
public OQL1 Where(OQLCompare compare)
{
whereString = " WHERE " + compare.CompareString;
paras = compare.ComparedParameters;
useWhereMethod = true;
//this.CurrEntity.PropertyGetting += new EventHandler<PropertyGettingEventArgs>(CurrEntity_PropertyGetting);
return this;
}
private static OQLCompare BuildOperator(OQLCompare compare, object Value, string operatorString)
{
string paraName = compare.GetNewParameterName();
compare.CompareString += operatorString + paraName;
compare.compareValueList.Add(paraName.Substring(1), Value);
return compare;
}
/// <summary>
/// 根据实体对象的属性，获取新的条件比较对象
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
public OQLCompare Property(object field)
{
OQLCompare cmp = new OQLCompare();
cmp.CompareString = this.currPropName;
return cmp;
}
/// <summary>
/// 将当前实体属性的值和要比较的值进行比较，得到一个新的实体比较对象
/// </summary>
/// <param name="field">实体对象属性</param>
/// <param name="Value">要比较的值</param>
/// <returns>比较表达式</returns>
public new OQLCompare Equals(object field, object Value)
{
this.compareIndex++;
OQLCompare cmp = new OQLCompare();
string paraName = "@CP" + this.compareIndex;
cmp.compareValueList.Add(paraName.Substring(1), Value);
cmp.CompareString = this.currPropName + "=" + paraName;
return cmp;
}
/// <summary>
/// 将当前实体属性的值和要比较的值进行比较，得到一个新的实体比较对象
/// </summary>
/// <param name="field">实体对象属性</param>
/// <param name="compareTypeString">数据库比较类型字符串</param>
/// <param name="Value">要比较的值</param>
/// <returns>比较表达式</returns>
public OQLCompare Comparer(object field, string compareTypeString, object Value)
{
string[] cmpStrs = { "=", ">", "<", "<>", ">=", "<=", "like", "is", "in", "not in" };
if (String.IsNullOrEmpty(compareTypeString))
compareTypeString = "=";
else
compareTypeString = compareTypeString.Trim().ToLower();
bool flag = false;
foreach (string str in cmpStrs)
{
if (compareTypeString == str)
{
flag = true;
break;
}
}
if (!flag)
throw new Exception("比较符号必须是 =,>,<,>=,<=,<>,like,is,in,not in 中的一种。");
this.compareIndex++;
OQLCompare cmp = new OQLCompare();
if (compareTypeString == "is")
{
cmp.CompareString = this.currPropName + " IS " + Value.ToString();//此处可能不安全，IS NULL，IS NOT NULL
}
else if (compareTypeString == "in")
{
cmp.CompareString = this.currPropName + " IN ( " + Value.ToString() + " )";//此处可能不安全
}
else if (compareTypeString == "not in")
{
cmp.CompareString = this.currPropName + " NOT IN ( " + Value.ToString() + " )";//此处可能不安全
}
else
{
string paraName = "@CP" + this.compareIndex;
cmp.compareValueList.Add(paraName.Substring(1), Value);
cmp.CompareString = this.currPropName + " " + compareTypeString + " " + paraName;
}
return cmp;
}
/// <summary>
/// 将当前实体属性的值和要比较的值进行比较，得到一个新的实体比较对象
/// </summary>
/// <param name="field">实体对象属性</param>
/// <param name="type">比较类型枚举</param>
/// <param name="Value">要比较的值</param>
/// <returns>比较表达式</returns>
public OQLCompare Comparer(object field, CompareType type, object Value)
{
//string typeStr = "";
//switch (type)
//{
// case CompareType.Equal: typeStr = "="; break;
// case CompareType.Greater: typeStr = ">"; break;
// case CompareType.Like: typeStr = "LIKE"; break;
// case CompareType.NoGreater: typeStr = "<="; break;
// case CompareType.NoSmaller: typeStr = ">="; break;
// case CompareType.NotEqual: typeStr = "<>"; break;
// case CompareType.Smaller: typeStr = "<"; break;
// default: typeStr = "="; break;
//}
//this._DbCompareTypeStr = typeStr;
//this._CompareValue = Value;
//
//
//this.CompareString = this.CompareFieldName + typeStr+" @" + this.CompareFieldName;
this.compareIndex++;
OQLCompare cmp = new OQLCompare();
if (type == CompareType.IS)
{
cmp.CompareString = this.currPropName + " IS " + Value.ToString();//此处可能不安全
}
else if (type == CompareType.IN)
{
cmp.CompareString = this.currPropName + " IN ( " + Value.ToString() + " )";//此处可能不安全
}
else if (type == CompareType.NotIn)
{
cmp.CompareString = this.currPropName + " NOT IN ( " + Value.ToString() + " )";//此处可能不安全
}
else
{
string paraName = "@CP" + this.compareIndex;
cmp.compareValueList.Add(paraName.Substring(1), Value);
//cmp.compareTypeList.Add(type);
//cmp.propertyList.Add(this.currPropName);
cmp.CompareString = this.currPropName + GetDbCompareTypeStr(type) + paraName;
}
return cmp;
}
/// <summary>
/// 采用两个实体比较对象按照某种比较逻辑进行处理，构造一个新的实体比较对象
/// </summary>
/// <seealso cref="http://www.cnblogs.com/bluedoctor/archive/2010/11/28/1870095.html"/>
/// <param name="compare1">比较逻辑符号左边的实体比较对象</param>
/// <param name="logic">比较逻辑</param>
/// <param name="compare2">比较逻辑符号左边的实体比较对象</param>
public OQLCompare(OQLCompare compare1, CompareLogic logic, OQLCompare compare2)
{
//propertyList.AddRange(compare1.propertyList);
//compareValueList.AddRange(compare1.compareValueList);
//compareTypeList.AddRange(compare1.compareTypeList);
//propertyList.AddRange(compare2.propertyList);
//compareValueList.AddRange(compare2.compareValueList);
//compareTypeList.AddRange(compare2.compareTypeList);
//compareLogicStrList.AddRange(compare1.compareLogicStrList);
dictionaryAddRange(compareValueList, compare1.compareValueList);
dictionaryAddRange(compareValueList, compare2.compareValueList);
switch (logic)
{
case CompareLogic.AND:
//this.compareLogicStrList.Add(" AND ");
this.CompareString = " (" + compare1.CompareString + " AND " + compare2.CompareString + ") ";
break;
case CompareLogic.OR:
//this.compareLogicStrList.Add(" OR ");
this.CompareString = " (" + compare1.CompareString + " OR " + compare2.CompareString + ") ";
break;
//case CompareLogic.NOT :
// this.compareLogicStrList.Add(" NOT ");
// this.CompareString = " NOT (" + compare1.CompareString + " AND " + compare2.CompareString + ") ";
// break;
}
}
/// <summary>
/// 对一组OQLCompare 对象，执行CompareLogic 类型的比较，通常用于构造复杂的带括号的条件查询
/// </summary>
/// <seealso cref="http://www.cnblogs.com/bluedoctor/archive/2011/02/24/1963606.html"/>
/// <param name="compares">OQL比较对象列表</param>
/// <param name="logic">各组比较条件的组合方式，And，Or，Not</param>
/// <returns>新的条件比较对象</returns>
public OQLCompare Comparer(List<OQLCompare> compares, CompareLogic logic)
{
if (compares == null || compares.Count == 0)
throw new Exception("OQL 条件比较对象集合不能为空或者空引用！");
if (compares.Count == 1)
return compares[0];
OQLCompare cmp = new OQLCompare();
string typeString = logic == CompareLogic.AND ? " And " : logic == CompareLogic.OR ? " Or " : " Not ";
foreach (OQLCompare item in compares)
{
cmp.CompareString += item.CompareString + typeString;
if (item.ComparedParameters != null)
foreach (string key in item.ComparedParameters.Keys)
{
cmp.ComparedParameters.Add(key, item.ComparedParameters[key]);
}
}
cmp.CompareString = cmp.CompareString.Substring(0, cmp.CompareString.Length - typeString.Length);
cmp.CompareString = " ( " + cmp.CompareString + " ) ";
return cmp;
}
/// <summary>
/// 对条件表达式取反
/// </summary>
/// <param name="cmp"></param>
/// <returns></returns>
public static OQLCompare Not(OQLCompare cmp)
{
return(new OQLCompare(cmp, CompareLogic.NOT, null));
}
/// <summary>
/// 根据实体对象的属性，获取新的条件比较对象，用于比较操作符重载
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
public OQLCompare Property(object field)
{
OQLCompare compare = new OQLCompare(this.LinkedOQL);
return(compare);
}
private OQLCompare ComparerInner <T>(T field, CompareType type, object oValue, string sqlFunctionFormat)
{
OQLCompare compare = new OQLCompare(this.LinkedOQL);
TableNameField leftField;
TableNameField rightField;
compare.LinkedOQL.TakeTwoStackFields(field, oValue, out leftField, out rightField);
if (leftField != null)
{
compare.ComparedFieldName = leftField.SqlFieldName;
}
else if (rightField != null)
{
compare.ComparedFieldName = rightField.SqlFieldName;
}
else
{
throw new Exception("前或者后的比较字段对像均为空！");
}
compare.ComparedType = type;
if (type == CompareType.IS || type == CompareType.IsNot)
{
if (oValue != null)
{
string strValue = oValue.ToString().ToUpper().Trim();
if (strValue == "NULL" || strValue == "NOT NULL")
{
compare.ComparedParameterName = strValue;
}
else
{
throw new ArgumentOutOfRangeException("IS 操作符的对象只能是NULL 或者 NOT NULL");
}
}
else
{
compare.ComparedParameterName = "NULL";
}
}
else if (type == CompareType.IN || type == CompareType.NotIn)
{
throw new ArgumentOutOfRangeException("IN,NOT IN 操作符请使用Comparer方法中带数组参数的重载方法");
}
else
{
if (leftField != null && rightField != null)
{
//可能直接用相同的字段来比较，感谢网友Sharp_C 发现此问题
if (leftField.SqlFieldName == rightField.SqlFieldName)
{
compare.ComparedParameterName = compare.LinkedOQL.CreateParameter(leftField, oValue);
}
else
{
compare.ComparedParameterName = rightField.SqlFieldName;
}
}
else if (leftField != null && rightField == null)
{
compare.ComparedParameterName = compare.LinkedOQL.CreateParameter(leftField, oValue);
}
else if (leftField == null && rightField != null)
{
compare.ComparedFieldName = compare.LinkedOQL.CreateParameter(rightField, field);
compare.ComparedParameterName = rightField.SqlFieldName;
}
else
{
throw new InvalidOperationException("当前OQLCompare 内部操作状态无效，条件比较未使用实体类的属性。");
}
}
compare.SqlFunctionFormat = sqlFunctionFormat;
//compare.LinkedOQL.fieldStack.Clear();//TakeTwoStackFields 方法已经清理过
return(compare);
}
