【半原创】SharePoint CAML 查询生成器[SharePoint 2010 CAML Query Builder]

这个查询生成器是从SharePoint扒出来的，非完全原创，当然我在它的基础上修改了一下，原来的不支持Text类型的In查询。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using Microsoft.SharePoint;
using System.Globalization;
using Microsoft.SharePoint.Utilities;

namespace Only.SPLibraryExtend.Utils
{
    /// <summary>
    /// 查询生成器
    /// </summary>
    public class QueryBuilder
    {
        /// <summary>
        /// 将ID列表转成In操作符的字符串
        /// </summary>
        /// <param name="lstInIds"></param>
        /// <returns></returns>
        public static string GetIdsToString(IList<int> lstInIds)
        {
            string filterIds = "";
            foreach (var i in lstInIds)
            {
                if (filterIds != "")
                {
                    filterIds += ",";
                }
                filterIds += i.ToString();
            }
            return filterIds;
        }

        private static string[] VariantTypeToFieldType = new string[] { SPFieldType.Text.ToString(), SPFieldType.Text.ToString(), SPFieldType.Text.ToString(), SPFieldType.Text.ToString(), SPFieldType.Text.ToString(), SPFieldType.Number.ToString(), SPFieldType.Text.ToString(), SPFieldType.DateTime.ToString(), SPFieldType.Text.ToString(), SPFieldType.Text.ToString(), SPFieldType.Error.ToString(), SPFieldType.Boolean.ToString() };

        public QueryBuilder(SPList list)
        {
            m_splist = list;
            WithQueryRootElement = false;
        }
        
        private SPList m_splist = null;
        public SPList SPList
        {
            get
            {
                return m_splist;
            }
        }

        private List<QueryFilterOperation> m_filterOperations = null;

        public List<QueryFilterOperation> FilterOperations
        {
            get
            {
                if (m_filterOperations == null)
                {
                    m_filterOperations = new List<QueryFilterOperation>();
                }
                return m_filterOperations;
            }
        }
        
        /// <summary>
        /// 是否需要Query根结点
        /// </summary>        
        public Boolean WithQueryRootElement
        {
            get;
            set;
        }

        public string QueryString
        {
            get
            {
                XmlDocument document = new XmlDocument();
                document.LoadXml("<Query></Query>");
                XmlNamespaceManager nsmgr = new XmlNamespaceManager(document.NameTable);
                XmlElement query = document.DocumentElement;
                System.Xml.XmlNode whereNode = query.SelectSingleNode("Where", nsmgr);
                if (whereNode == null)
                {
                    whereNode = document.CreateNode(XmlNodeType.Element, "", "Where", "");
                    whereNode = query.AppendChild(whereNode);
                }
                foreach (var operation in this.FilterOperations)
                {
                    string queryField = operation.FieldName;
                    if ((!string.IsNullOrEmpty(queryField)))
                    {                        
                        string filterValue = operation.FilterValue;
                        bool isLookupId = operation.IsFieldLookupId;
                        string type = operation.IsFieldLookupId ? "Integer" : null;
                        string operationTypeName = string.IsNullOrEmpty(operation.OperationType) ? "Eq" : operation.OperationType;
                        if ((queryField.Length > 0) && (queryField[0] == '@'))
                        {
                            queryField = queryField.Substring(1);
                        }

                        if (this.SPList != null)
                        {
                            SPField internalName = null;
                            try
                            {
                                internalName = this.SPList.Fields.GetFieldByInternalName(queryField);
                            }
                            catch (ArgumentException ex)
                            {
                                //
                            }                            
                            SPFieldCalculated calculated = internalName as SPFieldCalculated;
                            if (calculated != null)
                            {
                                int idx = filterValue.IndexOf('_');
                                if (idx > 0)
                                {
                                    ushort typeIdx;
                                    if (ushort.TryParse(filterValue.Substring(0, idx), NumberStyles.None, CultureInfo.InvariantCulture, out typeIdx) && (typeIdx < VariantTypeToFieldType.Length))
                                    {
                                        type = VariantTypeToFieldType[typeIdx];
                                    }
                                    filterValue = filterValue.Substring(idx + 1);
                                }
                            }
                            if (type == null)
                            {
                                SPFieldDecimal spDecimal = internalName as SPFieldDecimal;
                                if ((internalName != null) && ((internalName.Type != SPFieldType.Invalid) || (spDecimal != null)))
                                {
                                    if (calculated != null)
                                    {
                                        type = calculated.OutputType.ToString();
                                    }
                                    else
                                    {
                                        type = internalName.TypeAsString;
                                    }
                                    if (ReflectorUtils.getPerperty<bool>("IsDateTimeField", internalName))
                                    {
                                        DateTime time;
                                        if (DateTime.TryParse(filterValue, ReflectorUtils.getPerperty<bool>("IsFromConnection", operation) ? CultureInfo.CurrentCulture : CultureInfo.InvariantCulture, DateTimeStyles.None, out time))
                                        {
                                            filterValue = time.ToString("yyyy-MM-ddTHH:mm:ssZ", DateTimeFormatInfo.InvariantInfo);
                                        }
                                    }
                                    else if (spDecimal != null)
                                    {
                                        decimal num;
                                        if (decimal.TryParse(filterValue, NumberStyles.Number, ReflectorUtils.getPerperty<bool>("IsFromConnection", operation) ? CultureInfo.InvariantCulture : CultureInfo.CurrentCulture, out num))
                                        {
                                            filterValue = num.ToString(CultureInfo.InvariantCulture);
                                        }
                                        type = "Text";
                                    }
                                }
                                else
                                {
                                    type = "Text";
                                }
                            }

                            System.Xml.XmlNode firstNode = null;
                            if (whereNode.FirstChild != null)
                            {
                                firstNode = document.CreateNode(XmlNodeType.Element, "", "And", "");
                                firstNode = whereNode.AppendChild(firstNode);
                                firstNode.AppendChild(whereNode.FirstChild);
                            }
                            else
                            {
                                firstNode = whereNode;
                            }
                            if (string.IsNullOrEmpty(filterValue))
                            {
                                System.Xml.XmlNode nullNode = document.CreateNode(XmlNodeType.Element, "", "IsNull", "");
                                nullNode = firstNode.AppendChild(nullNode);
                                System.Xml.XmlNode refNode = document.CreateNode(XmlNodeType.Element, "", "FieldRef", "");
                                refNode = nullNode.AppendChild(refNode);
                                XmlAttribute nameAttr = document.CreateAttribute("Name");
                                nameAttr.Value = queryField;
                                refNode.Attributes.SetNamedItem(nameAttr);
                            }
                            else
                            {
                                System.Xml.XmlNode optNode = document.CreateNode(XmlNodeType.Element, "", operationTypeName, "");
                                optNode = firstNode.AppendChild(optNode);
                                System.Xml.XmlNode refNode = document.CreateNode(XmlNodeType.Element, "", "FieldRef", "");
                                refNode = optNode.AppendChild(refNode);
                                XmlAttribute nameAttr = document.CreateAttribute("Name");
                                nameAttr.Value = queryField;
                                refNode.Attributes.SetNamedItem(nameAttr);
                                if (isLookupId)
                                {
                                    System.Xml.XmlAttribute lookupAttr = document.CreateAttribute("LookupId");
                                    lookupAttr.Value = "TRUE";
                                    refNode.Attributes.SetNamedItem(lookupAttr);
                                }
                                if (operationTypeName == "In")
                                {
                                    System.Xml.XmlNode valuesNode = document.CreateNode(XmlNodeType.Element, "", "Values", "");
                                    valuesNode = optNode.AppendChild(valuesNode);
                                    if (isLookupId)
                                    {
                                        foreach (string InItem in filterValue.Split(new char[] { ',' }))
                                        {
                                            System.Xml.XmlNode valueNode = document.CreateNode(XmlNodeType.Element, "", "Value", "");
                                            valueNode = valuesNode.AppendChild(valueNode);
                                            nameAttr = document.CreateAttribute("Type");
                                            nameAttr.Value = type;
                                            valueNode.Attributes.SetNamedItem(nameAttr);
                                            filterValue = filterValue.Replace("&5c", @"\");
                                            XmlNode valueText = document.CreateTextNode(InItem);
                                            valueNode.AppendChild(valueText);
                                        }
                                    }
                                    else
                                    {
                                        foreach (string InItem in filterValue.Split(new char[] { ',' }))
                                        {
                                            XmlNode valueNode = document.CreateNode(XmlNodeType.Element, "", "Value", "");
                                            valueNode = valuesNode.AppendChild(valueNode);
                                            nameAttr = document.CreateAttribute("Type");
                                            nameAttr.Value = type;
                                            valueNode.Attributes.SetNamedItem(nameAttr);
                                            filterValue = filterValue.Replace("&5c", @"\");
                                            XmlNode valueText = document.CreateTextNode(filterValue);
                                            valueNode.AppendChild(valueText);
                                        }
                                    }
                                }
                                else
                                {
                                    XmlNode valueNode = document.CreateNode(XmlNodeType.Element, "", "Value", "");
                                    valueNode = optNode.AppendChild(valueNode);
                                    nameAttr = document.CreateAttribute("Type");
                                    nameAttr.Value = type;
                                    valueNode.Attributes.SetNamedItem(nameAttr);
                                    filterValue = filterValue.Replace("&5c", @"\");
                                    XmlNode valueText = document.CreateTextNode(filterValue);
                                    valueNode.AppendChild(valueText);
                                }
                            }
                        }
                    }
                }
                if (this.WithQueryRootElement)
                {
                    return document.OuterXml;
                }
                else
                {
                    return document.DocumentElement.InnerXml;
                }
            }
        }
    }



    public class QueryFilterOperation
    {
        private bool m_bIsFromQueryString;
        private string m_FilterData;
        private string m_FilterValue;
        private bool m_IsFieldLookupId;
        private bool m_IsFromConnection;
        private string m_OperationType;        

        public string FieldName
        {
            get;
            set;
        }

        public string FilterData
        {
            get
            {
                return this.m_FilterData;
            }
            set
            {
                this.m_FilterData = value;
            }
        }

        public string FilterValue
        {
            get
            {
                return this.m_FilterValue;
            }
            set
            {
                this.m_FilterValue = value;
            }
        }

        public bool IsFieldLookupId
        {
            get
            {
                return this.m_IsFieldLookupId;
            }
            set
            {
                this.m_IsFieldLookupId = value;
            }
        }

        internal bool IsFromConnection
        {
            get
            {
                return this.m_IsFromConnection;
            }
            set
            {
                this.m_IsFromConnection = value;
            }
        }

        internal bool IsFromQueryString
        {
            get
            {
                return this.m_bIsFromQueryString;
            }
            set
            {
                this.m_bIsFromQueryString = value;
            }
        }

        public string OperationType
        {
            get
            {
                return this.m_OperationType;
            }
            set
            {
                this.m_OperationType = value;
            }
        }
    }
}

 

调用示例：

//查询单值等于
SPList lst = SPContext.Current.Web.Lists["栏目"];
SPQuery query = new SPQuery();
QueryFilterOperation op = null;
op = new QueryFilterOperation();
op.OperationType = "Eq";
op.FilterValue ="1"
op.FieldName = "NavigationType";
builder.FilterOperations.Add(op);

//In查询

op = new QueryFilterOperation ();
op.IsFieldLookupId = true;
op.OperationType = "In";
op.FilterValue ="1,2,3,4"
op.FieldName="NavigationType"
builder.FilterOperations.Add(op);

//输出查询语句

SPQuery query=new SPQuery();
query.Query = builder.QueryString;
var items = lst.GetItems(query);