SharePoint 2010 CAML 查询语句构建器(修正了字段类型为Lookup的情况)
前段时间,因为某些特殊的需要,需要动态构建CAML语句,用于查询List中的数据。通过工具CAML Builder工具的使用,大致了解了CAML的一些操作符的概念之后,就开始着手自己写一个可以动态拼CAML语句的类:CAMLBuilder。
在构建查询语句时,我区分为WhereBuilder和OrderByBuilder,这样也帮助理解。
使用WhereBuilder和OrderByBuilder前,需要先实例化,之后的操作都遵循管道模式,可以一句话写到头(虽然这个写法不是一个好建议,可以在打点的时候换行)。以下是调用例子:
SPQuery query =new SPQuery
{
ViewFields = CAMLBuilder.BuildViewFields(newstring[] { "URL" }),
Query = CAMLBuilder.BuildQuery(new CAMLBuilder.WhereBuilder("Author", "User", web.CurrentUser.Name, CAMLBuilder.OperationSymbol.Eq)
.And("ContentType", "Computed", "Folder", CAMLBuilder.OperationSymbol.Neq), null),
RowLimit = displayRecordCount,
ViewAttributes ="Scope=\"RecursiveAll\""
};
初始构造WhereBuilder时,可以传入一组条件,即FieldName、FieldType(考虑到自定义字段类型,所以没有采用枚举类型)、Value、操作符(大于、小于、等于等等,可参考CAML语法;在此采用枚举类型,方便使用)。
每一组条件,像之前提到的,一般为FieldName、FieldType、Value、操作符;如果运算符为IsNull或IsNotNull时,就不需要提供Value(这个稍微想一下就可以理解)。我把这组条件,定义成内部类型ConditionUnit(即条件单元),如果加入一个条件,在运算时需要把前边的运算结果与新加入的条件进行逻辑运算(And、Or等)。我把带有逻辑运算符的条件,定义成内部类型DoubleConditonUnit(即双条件)。在之后为了引入类似Sql中的In和Not In操作符,对这个类型进行了简单扩展,即通过构造函数传入DoubleConditonUnit集合来实现对其封装成DoubleConditonUnit类型,这样对于WhereBuilder来说,添加的条件类型是一致的(都是DoubleConditonUnit类型,这也是我感觉不是很好的地方,希望有一个好的解决方法)。
对于新引入操作符In和Not In的理解:
操作符In:将一组操作符为Eq的条件集合以逻辑运算符Or进行关联。
操作符Not In:将一组操作符为Neq的条件集合以逻辑运算符And进行关联。
对于调用着来说,当然调用越简单越好了,所以在实际调用时,已经对内部类型ConditionUnit、DoubleConditonUnit进行了透明化处理。对调用者来说,只需要理解FieldName、FieldType、Value、操作符、逻辑运算符即可。
对此的讲述可能比较简单,如果大家有什么好的改进办法,请回复我,谢谢!
以下是源码:
/* * Author: Jerry Bai * Create Date: 2011/6/23 * Description: Easy to build CAML expression. * Licence: BSD */ using System; using System.Xml; using System.Collections.Generic; using System.Text; using System.Text.RegularExpressions; namespace Wicresoft.ITSG.Components.Utilities { /// <summary> /// CAML构建器 /// </summary> public class CAMLBuilder { /// <summary> /// 构建查询语句 /// </summary> /// <param name="whereBuilder"></param> /// <param name="orderByBuilder"></param> /// <returns></returns> public static string BuildQuery(WhereBuilder whereBuilder, OrderByBuilder orderByBuilder) { string result = String.Format("<Query>{0}{1}</Query>", new object[] { whereBuilder, orderByBuilder }); XmlDocument document = new XmlDocument(); document.LoadXml(result); StringBuilder sb = new StringBuilder(); XmlWriterSettings writerSettings = new XmlWriterSettings(); writerSettings.Indent = true; writerSettings.ConformanceLevel = ConformanceLevel.Fragment; writerSettings.OmitXmlDeclaration = false; XmlWriter writer = XmlWriter.Create(sb, writerSettings); document.ChildNodes[0].WriteContentTo(writer); writer.Flush(); writer.Close(); result = sb.ToString(); Console.WriteLine("CAML Query:\r\n" + result); LogUtility.CreateLogManager().Debug("CAML Query:\r\n" + result); return result; } /// <summary> /// 构建查询字段列表 /// </summary> /// <param name="fieldNames"></param> /// <returns></returns> public static string BuildViewFields(string[] fieldNames) { if (fieldNames == null || fieldNames.Length == 0) return ""; string result = ""; foreach (string fieldName in fieldNames) { result = String.Format("{0}<FieldRef Name=\"{1}\" />", result, fieldName); } Console.WriteLine("CAML ViewFields:\r\n" + result); LogUtility.CreateLogManager().Debug("CAML ViewFields:\r\n" + result); return result; } #region OrderByBuilder /// <summary> /// OrderBy构建器 /// </summary> public class OrderByBuilder { private List<SortUnit> _SortUnitList = new List<SortUnit>(); public OrderByBuilder() { } public OrderByBuilder(string fieldName, bool ascending) { _SortUnitList.Add(new SortUnit(fieldName, ascending)); } /// <summary> /// 将SortExpression转换为CAML的OrderBy子句 /// </summary> /// <param name="sortExpression"></param> /// <returns></returns> public static OrderByBuilder LoadFromSortExpression(string sortExpression) { OrderByBuilder builder = new OrderByBuilder(); if (!String.IsNullOrEmpty(sortExpression)) { foreach (string singleFieldSort in sortExpression.Split(',')) { string noSpaceSingleFieldSort = singleFieldSort.Trim(); if (Regex.IsMatch(noSpaceSingleFieldSort, @"\w+((\s+asc)|(\s+desc))?", RegexOptions.IgnoreCase)) { string[] noSingleFieldSortArray = noSpaceSingleFieldSort.Split(' '); if (noSingleFieldSortArray.Length == 1 || noSingleFieldSortArray[1].Equals("asc", StringComparison.OrdinalIgnoreCase)) { builder.Add(noSingleFieldSortArray[0], true); } else { builder.Add(noSingleFieldSortArray[0], false); } } } } return builder; } /// <summary> /// 添加排序字段 /// </summary> /// <param name="fieldName"></param> /// <param name="ascending"></param> /// <returns></returns> public OrderByBuilder Add(string fieldName, bool ascending) { _SortUnitList.Add(new SortUnit(fieldName, ascending)); return this; } public override string ToString() { string result = ""; foreach (SortUnit sortUnit in _SortUnitList) { result += sortUnit.ToString(); } return String.IsNullOrEmpty(result) ? "" : String.Format("<OrderBy>{0}</OrderBy>", result); } internal class SortUnit { private readonly string FieldName; private readonly bool Ascending; public SortUnit(string fieldName, bool ascending) { FieldName = fieldName; Ascending = ascending; } public override string ToString() { return String.Format("<FieldRef Name=\"{0}\" Ascending=\"{1}\" />", new object[] { FieldName, (Ascending ? "True" : "False") }); } } } #endregion #region WhereBuilder /// <summary> /// Where构建器 /// </summary> public class WhereBuilder { private readonly ConditionUnit _ConditionUnit; private List<DoubleConditonUnit> _DoubleConditonUnitList = new List<DoubleConditonUnit>(); public WhereBuilder() { } public WhereBuilder(string fieldName, OperationSymbol operationSymbol) { _ConditionUnit = new ConditionUnit(fieldName, operationSymbol); } public WhereBuilder(string fieldName, string valueType, object value, OperationSymbol operationSymbol) { _ConditionUnit = new ConditionUnit(fieldName, valueType, value, operationSymbol); } #region Connect condition with And /// <summary> /// 添加And条件 /// </summary> /// <param name="fieldName"></param> /// <param name="operationSymbol"></param> /// <returns></returns> public WhereBuilder And(string fieldName, OperationSymbol operationSymbol) { _DoubleConditonUnitList.Add(new DoubleConditonUnit(LogicSymbol.And, new ConditionUnit(fieldName, operationSymbol))); return this; } /// <summary> /// 添加And条件 /// </summary> /// <param name="fieldName"></param> /// <param name="valueType"></param> /// <param name="value"></param> /// <param name="operationSymbol"></param> /// <returns></returns> public WhereBuilder And(string fieldName, string valueType, object value, OperationSymbol operationSymbol) { _DoubleConditonUnitList.Add(new DoubleConditonUnit(LogicSymbol.And, new ConditionUnit(fieldName, valueType, value, operationSymbol))); return this; } #endregion #region Connect condition with Or /// <summary> /// 添加Or条件 /// </summary> /// <param name="fieldName"></param> /// <param name="operationSymbol"></param> /// <returns></returns> public WhereBuilder Or(string fieldName, OperationSymbol operationSymbol) { _DoubleConditonUnitList.Add(new DoubleConditonUnit(LogicSymbol.Or, new ConditionUnit(fieldName, operationSymbol))); return this; } /// <summary> /// 添加Or条件 /// </summary> /// <param name="fieldName"></param> /// <param name="valueType"></param> /// <param name="value"></param> /// <param name="operationSymbol"></param> /// <returns></returns> public WhereBuilder Or(string fieldName, string valueType, object value, OperationSymbol operationSymbol) { _DoubleConditonUnitList.Add(new DoubleConditonUnit(LogicSymbol.Or, new ConditionUnit(fieldName, valueType, value, operationSymbol))); return this; } #endregion /// <summary> /// 添加条件 /// </summary> /// <param name="logicSymbol"></param> /// <param name="fieldName"></param> /// <param name="operationSymbol"></param> /// <returns></returns> public WhereBuilder AddCondition(LogicSymbol logicSymbol, string fieldName, OperationSymbol operationSymbol) { _DoubleConditonUnitList.Add(new DoubleConditonUnit(logicSymbol, new ConditionUnit(fieldName, operationSymbol))); return this; } /// <summary> /// 添加条件 /// </summary> /// <param name="logicSymbol"></param> /// <param name="fieldName"></param> /// <param name="valueType"></param> /// <param name="value"></param> /// <param name="operationSymbol"></param> /// <returns></returns> public WhereBuilder AddCondition(LogicSymbol logicSymbol, string fieldName, string valueType, object value, OperationSymbol operationSymbol) { _DoubleConditonUnitList.Add(new DoubleConditonUnit(logicSymbol, new ConditionUnit(fieldName, valueType, value, operationSymbol))); return this; } /// <summary> /// 添加In条件 /// </summary> /// <param name="logicSymbol"></param> /// <param name="fieldName"></param> /// <param name="valueType"></param> /// <param name="values"></param> /// <returns></returns> public WhereBuilder AddInCondition(LogicSymbol logicSymbol, string fieldName, string valueType, object[] values) { if (values != null && values.Length > 0) { if (values.Length == 1) { this.AddCondition(logicSymbol, fieldName, valueType, values[0], OperationSymbol.Eq); } else { List<DoubleConditonUnit> subDoubleConditionUnitList = new List<DoubleConditonUnit>(); for (int i = 1; i < values.Length; i++) { subDoubleConditionUnitList.Add(new DoubleConditonUnit(LogicSymbol.Or, new ConditionUnit(fieldName, valueType, values[i], OperationSymbol.Eq))); } _DoubleConditonUnitList.Add(new DoubleConditonUnit(logicSymbol, new ConditionUnit(fieldName, valueType, values[0], OperationSymbol.Eq), subDoubleConditionUnitList.ToArray())); } } return this; } /// <summary> /// 添加NotIn条件 /// </summary> /// <param name="logicSymbol"></param> /// <param name="fieldName"></param> /// <param name="valueType"></param> /// <param name="values"></param> /// <returns></returns> public WhereBuilder AddNotInCondition(LogicSymbol logicSymbol, string fieldName, string valueType, object[] values) { if (values != null && values.Length > 0) { if (values.Length == 1) { this.AddCondition(logicSymbol, fieldName, valueType, values[0], OperationSymbol.Neq); } else { List<DoubleConditonUnit> subDoubleConditionUnitList = new List<DoubleConditonUnit>(); for (int i = 1; i < values.Length; i++) { subDoubleConditionUnitList.Add(new DoubleConditonUnit(LogicSymbol.Or, new ConditionUnit(fieldName, valueType, values[i], OperationSymbol.Neq))); } _DoubleConditonUnitList.Add(new DoubleConditonUnit(logicSymbol, new ConditionUnit(fieldName, valueType, values[0], OperationSymbol.Neq), subDoubleConditionUnitList.ToArray())); } } return this; } public override string ToString() { string result = ""; if (_ConditionUnit != null) { result = _ConditionUnit.ToString(); } if (_DoubleConditonUnitList.Count != 0) { foreach (DoubleConditonUnit doubleConditionUnit in _DoubleConditonUnitList) { if (String.IsNullOrEmpty(result)) { result = doubleConditionUnit.ToConditionString(); } else { result = String.Format("<{0}>{1}{2}</{0}>", new object[] { doubleConditionUnit.LogicSymbol, result, doubleConditionUnit.ToConditionString() }); } } } return String.IsNullOrEmpty(result) ? "" : String.Format("<Where>{0}</Where>", result); } #region internal class internal class DoubleConditonUnit { internal readonly LogicSymbol LogicSymbol; private readonly ConditionUnit ConditionUnit; private readonly DoubleConditonUnit[] DoubleConditionUnitList; public DoubleConditonUnit(LogicSymbol logicSymbol, ConditionUnit conditionUnit) { LogicSymbol = logicSymbol; ConditionUnit = conditionUnit; } public DoubleConditonUnit(LogicSymbol logicSymbol, ConditionUnit conditionUnit, DoubleConditonUnit[] doubleConditionUnitList) { LogicSymbol = logicSymbol; ConditionUnit = conditionUnit; DoubleConditionUnitList = doubleConditionUnitList; } public string ToConditionString() { string result = ConditionUnit.ToString(); if (DoubleConditionUnitList != null && DoubleConditionUnitList.Length > 0) { foreach (DoubleConditonUnit doubleConditionUnit in DoubleConditionUnitList) { if (String.IsNullOrEmpty(result)) { result = doubleConditionUnit.ToConditionString(); } else { result = String.Format("<{0}>{1}{2}</{0}>", new object[] { doubleConditionUnit.LogicSymbol, result, doubleConditionUnit.ToConditionString() }); } } } return result; } } internal class ConditionUnit { internal readonly string FieldName; internal readonly string ValueType; internal readonly object Value; internal readonly OperationSymbol OperationSymbol; public ConditionUnit(string fieldName, string valueType, object value, OperationSymbol operationSymbol) { FieldName = fieldName; ValueType = valueType; Value = value; OperationSymbol = operationSymbol; } public ConditionUnit(string fieldName, OperationSymbol operationSymbol) { FieldName = fieldName; OperationSymbol = operationSymbol; } public override string ToString() { if (String.IsNullOrEmpty(FieldName)) { throw new Exception("ConditionUnit->FieldName couldn't be null!"); } if (OperationSymbol == CAMLBuilder.OperationSymbol.IsNull || OperationSymbol == CAMLBuilder.OperationSymbol.IsNotNull) { return String.Format("<{1}><FieldRef Name=\"{0}\" /></{1}>", new object[] { FieldName, OperationSymbol }); } else if ("Lookup".Equals(ValueType, StringComparison.CurrentCultureIgnoreCase)) { return String.Format("<{3}><FieldRef Name=\"{0}\" LookupId=\"True\" /><Value Type=\"{1}\">{2}</Value></{3}>", new object[] { FieldName, ValueType, Value, OperationSymbol }); } else { return String.Format("<{3}><FieldRef Name=\"{0}\" /><Value Type=\"{1}\">{2}</Value></{3}>", new object[] { FieldName, ValueType, Value, OperationSymbol }); } } } #endregion } #endregion /// <summary> /// 操作符号 /// </summary> public enum OperationSymbol { /// <summary> /// Equal /// </summary> Eq, /// <summary> /// NotEqual /// </summary> Neq, /// <summary> /// GreatThen /// </summary> Gt, /// <summary> /// GreatThenOrEqual /// </summary> Geq, /// <summary> /// LessThen /// </summary> Lt, /// <summary> /// LessThenOrEqual /// </summary> Leq, IsNull, IsNotNull, BeginsWith, Contains, DateRangesOverlap } /// <summary> /// 逻辑符号 /// </summary> public enum LogicSymbol { And, Or } } }