Creare una query CAML

Un esempio di come costruire dinamicamente delle query CAML per SharePoint 2007 (WSS3 - MOSS).
In pratica posso costruire delle query in AND oppure OR tra i vari campi. Si istanzia la classe SPQueryCAML definendo la lista su cui si vuole fare la query e il criterio (AND oppure OR). Successivamente si aggiungo, tramite il metodo AddWhere i campi, il metodo di confronto e il valore. In ultimo, tramite il metodo GetCamlQuery, ottengo l'xml della query CAML.

C#

static void Main(string[] args)
{
    string url = "http://sharepoint2007/sgart/Lists/Budget";
    using (SPSite site = new SPSite(url))
    {
        using (SPWeb web = site.OpenWeb())
        {
            SPList list = web.GetList(url);

            SPQueryCAML qw = new SPQueryCAML(list, SPQueryCAML.QueryCompareType.And);
            qw.AddWhere("Title", "c", SPQueryCAMLWhere.Comparators.Contains);
            qw.AddWhere("ContentType", "Item", SPQueryCAMLWhere.Comparators.Eq);
            qw.AddWhere("Modified", new DateTime(2008,1,1), SPQueryCAMLWhere.Comparators.Gt);

            qw.AddOrderBy("Modified", SPQueryCAMLOrderBy.OrderByType.Desc);
            qw.AddOrderBy("Title");

            string q = qw.GetCamlQuery(true);

            Console.WriteLine(q);

            SPQuery query = new SPQuery();
            query.Query =  qw.GetCamlQuery(false); //nota il parametro false
            SPListItemCollection items = list.GetItems(query);
        }
    }
}

La query risultante:

XML

<Query>
  <Where>
    <And>
      <And>
        <Contains>
          <FieldRef Name="Title"/>
          <Value Type="Text">c</Value>
        </Contains>
        <Eq>
          <FieldRef Name="ContentType"/>
          <Value Type="Text">Item</Value>
        </Eq>
      </And>
      <Gt>
        <FieldRef Name="Modified"/>
        <Value Type="DateTime">2008-01-01T00:00:00Z</Value>
      </Gt>
    </And>
  </Where>
  <OrderBy>
    <FieldRef Name="Modified" Ascending="FALSE"/>
    <FieldRef Name="Title" />
  </OrderBy>
</Query>

Le classi di supporto:

C#

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SharePoint;

class SPQueryCAML
{
  public enum QueryCompareType
  {
    And,
    Or
  }

  System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo(1033);

  public SPQueryCAML(SPList list, QueryCompareType type)
  {
    this._currentList = list;
    this._compareType = type;
    this._whereList = new List<SPQueryCAMLWhere>();
    this._orderByList = new List<SPQueryCAMLOrderBy>();
  }

  private SPList _currentList;

  public SPList CurrentList
  {
    get { return _currentList; }
  }

  private QueryCompareType _compareType;

  public QueryCompareType CompareType
  {
    get { return _compareType;}
  }

  private List<SPQueryCAMLWhere> _whereList;

  public List<SPQueryCAMLWhere> WhereList
  {
    get { return _whereList; }
  }

  private List<SPQueryCAMLOrderBy> _orderByList;

  public List<SPQueryCAMLOrderBy> OrderByList
  {
    get { return _orderByList; }
  }

  public void AddWhere(string internalName, object value, SPQueryCAMLWhere.Comparators cmp)
  {
    SPField fld = CurrentList.Fields.GetFieldByInternalName(internalName);
    SPFieldType fieldType = fld.Type;
    string typeString = fld.TypeAsString;
    string valueString = string.Empty;
    if (fieldType == SPFieldType.Counter || fieldType == SPFieldType.Integer)
    {
      if (value != null)
      {
        valueString = ((int)value).ToString(ci);
      }
    }
    else if (fieldType == SPFieldType.Currency)
    {
      if (value != null)
      {
        valueString = ((double)value).ToString(ci);
      }
    }
    else if (fieldType == SPFieldType.Number)
    {
      if (value != null)
      {
        valueString = value.ToString();
      }
    }
    else if (fieldType == SPFieldType.DateTime)
    {
      if (value != null)
      {
        valueString = Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime((DateTime)value);
      }
    }
    else
    {
      valueString = value.ToString();
    }
    SPQueryCAMLWhere s = new SPQueryCAMLWhere();
    s.InternalName = fld.InternalName;
    s.TypeAsString = fld.TypeAsString;
    s.Value = valueString;
    s.Comparator = cmp;
    WhereList.Add(s);
  }

  public void AddWhere(string internalName, string value, SPQueryCAMLWhere.Comparators cmp)
  {
    SPField fld = CurrentList.Fields.GetFieldByInternalName(internalName);
    SPQueryCAMLWhere s = new SPQueryCAMLWhere();
    s.InternalName = fld.InternalName;
    s.TypeAsString = fld.TypeAsString;
    s.Value = value;
    s.Comparator = cmp;
    WhereList.Add(s);
  }

  public void AddOrderBy(string internalName, SPQueryCAMLOrderBy.OrderByType order)
  {
    SPQueryCAMLOrderBy q = new SPQueryCAMLOrderBy(internalName, order);
    OrderByList.Add(q);
  }

  public void AddOrderBy(string internalName)
  {
    SPQueryCAMLOrderBy q = new SPQueryCAMLOrderBy(internalName, SPQueryCAMLOrderBy.OrderByType.Asc);
    OrderByList.Add(q);
  }

  public string GetCamlQuery(bool addQuery)
  {
    if (addQuery)
    {
      return "<Query>\r\n" + GetCamlWhere() + GetCamlOrderBy() + "</Query>";
    }
    else
    {
      //return GetCamlWhere(); //22-10-2009
      return GetCamlWhere() + GetCamlOrderBy();
    }
  }
  
  public string GetCamlWhere()
  {
    StringBuilder sb = new StringBuilder(1000);
    string cmpBegin = string.Format("<{0}>\r\n", CompareType.ToString());
    string cmpEnd = string.Format("</{0}>\r\n", CompareType.ToString());
    foreach (SPQueryCAMLWhere q in WhereList)
    {

      if (sb.Length == 0)
      {
        sb.AppendFormat(@"<{3}><FieldRef Name=""{0}""/><Value Type=""{2}"">{1}</Value></{3}>"
          , q.InternalName
          , q.Value
          , q.TypeAsString
          , q.Comparator.ToString());
      }
      else
      {
        sb.Insert(0, cmpBegin);
        sb.AppendFormat(@"<{3}><FieldRef Name=""{0}""/><Value Type=""{2}"">{1}</Value></{3}>"
          , q.InternalName
          , q.Value
          , q.TypeAsString
          , q.Comparator.ToString());
        sb.Append(cmpEnd);
      }
      sb.Append("\r\n");
    }
    sb.Insert(0, "<Where>");
    sb.Append("</Where>");
    return sb.ToString();
  }

  public string GetCamlOrderBy()
  {
    StringBuilder sb = new StringBuilder(1000);
    sb.Append("<OrderBy>");
    foreach (SPQueryCAMLOrderBy q in OrderByList)
    {
      if (q.OrderBy == SPQueryCAMLOrderBy.OrderByType.Asc)
      {
        sb.AppendFormat(@"<FieldRef Name=""{0}"" />", q.InternalName);
      }
      else
      {
        sb.AppendFormat(@"<FieldRef Name=""{0}"" Ascending=""FALSE""/>", q.InternalName);
      }
    }
    sb.Append("</OrderBy>");
    return sb.ToString();
  }
}

C#

using System;
using System.Collections.Generic;
using System.Text;

public class SPQueryCAMLWhere
{
  public enum Comparators {
    Eq,
    Neq,
    Gt,
    Geq,
    Lt,
    Leq,
    IsNull,
    IsNotNull,
    BeginsWith,
    Contains
  }


  private string _internalName;

  public string InternalName
  {
    get { return _internalName; }
    set { _internalName = value; }
  }

  private string _value;

  public string Value
  {
    get { return _value; }
    set { _value = value; }
  }

  private string _typeAsString;

  public string TypeAsString
  {
    get { return _typeAsString; }
    set { _typeAsString = value; }
  }


  private Comparators _comparator;

  public Comparators Comparator
  {
    get { return _comparator; }
    set { _comparator = value; }
  }
}

C#

using System;
using System.Collections.Generic;
using System.Text;

class SPQueryCAMLOrderBy
{
  public enum OrderByType
  {
    Asc,
    Desc
  }

  public SPQueryCAMLOrderBy()
  {
    this.OrderBy = OrderByType.Asc;
  }

  public SPQueryCAMLOrderBy(string internalName, OrderByType order)
  {
    this.InternalName = internalName;
    this.OrderBy = order;
  }

  public SPQueryCAMLOrderBy(string internalName)
  {
    this.InternalName = internalName;
    this.OrderBy = OrderByType.Asc;
  }

  private string _internalName;

  public string InternalName
  {
    get { return _internalName; }
    set { _internalName = value; }
  }

  private OrderByType _orderBy;

  public OrderByType OrderBy
  {
    get { return _orderBy; }
    set { _orderBy = value; }
  }
}
Tags:
CAML17 SharePoint498 SharePoint 2007218
Potrebbe interessarti anche: