Use CAML Progress data query for the SharePoint server-side object model

The SharePoint server-side object model uses CAML for data queries

I. Overview

In a SharePoint development application, a query is a very common means of obtaining a collection of corresponding list entries in a list or some list based on certain filtering and sorting criteria.

In addition to the queries on the list, there are a large number of different queries in SharePoint, such as Sprecyclebinquery for the Recycle Bin, spauditquery for auditing, spchangequery for changes, and so on. However, the frequency with which these queries are used in actual projects is not very high. This section also focuses on the list query feature.

Before SharePoint 2010, query statements for list queries were written in an XML format such as CAML. It's not easy for developers to write more complex CAML queries accurately and quickly, but using CAML for data queries is still an essential skill in SharePoint development. This section describes how to use CAML for list queries and queries across multiple lists, and the next section describes the new list query method introduced by SharePoint 2010--LINQ.

Two, CAML query string

In a list query, all the query criteria are described using an XML-like syntax called CAML (Collaborative application Markup Language), which is a specific XML syntax in SharePoint. In fact, in SharePoint, not only are list queries using CAML, but there is also a large use of CAML-formatted XML in the definition of various objects such as sites, lists, fields, content types, site features, and so on.

The CAML query string can generally be divided into 3 parts: filter criteria, sorting and grouping criteria, and return fields.

When writing CAML strings, it is important to note that XML tags are case sensitive.

1. Filter conditions

The filtering criteria in CAML format can be interpreted as standardizing the SELECT statements in the traditional database in the form of XML to facilitate computer parsing and reading.

The filter condition of the CAML format uses <Where></Where> as the outermost node, the range of the flag filter criteria. The filter criteria can consist of several sub-filter conditions, each of which is formatted like this:

< operator >< field />< value /></ >   Equivalent: &NBSP; [ field [ operator " [ " Span style= "text-decoration:underline;" >]

Some of the general applications of the operators include: Eq (equals), Neq (Not Equal), Lt (less than), Leq (less than equals), Gt (greater than), GEQ (greater than or equal), Contains (inclusive), Beginswith (beginning with a string), in (within the collection range), IsNull (empty), isnotnull (not empty), membership (belongs to the user group). The last three operators are unary operators and do not have a "value" part of them.

The fields section is in the format:<fieldref name= " field name " other properties />, Pay special attention not to forget the last "/" Terminator, the field name must use the internal name .

The format of the value section is:<value type= " type " > value </Value>, The type section has different settings depending on the field, often including text (text), number (value), DateTime (date), lookup (lookup), and so on.

For example, we want to query the list for the "title" field (the internal name is title) for those list entries that contain the string "Object Model", and if it is a traditional T-SQL statement, it should look like this:

1 ' Object Model '

However, in SharePoint, when you use the CAML format description, it becomes:

1  <Where>2   <Contains>3     <fieldref name='Title' />4     <value type='Text'> Object Model </Value>5   </Contains>6 </Where>

As you can see from the format of the query criteria, when SharePoint is making a list query, it only supports comparisons between fields and values, and does not support comparisons between the two fields, which is a big limitation.

2, a number of special screening conditions

Here is a list of some of the more specific filter conditions (where tags are omitted):

(1) A query that is contained within the collection scope, which is a new type of query that SharePoint 2010 adds, using the "in" operator to query the collection that is contained within the specified scope. For example, the following example finds the section of writer that is contained in ["Erucy", "Windie"] collections (that is, writer equals erucy or Windie):

1 <In>2   <fieldref name='Writer'/>3   <Values>4     <value type='Text'>Erucy</Value>  5     <value type='Text'>Windie</Value>6   </Values>7 </In>

(2) "Yes/no" type of query, which is actually a bool type in the database, use "1" to indicate "yes" when querying, and "0" for "no", for example:

1 <Eq>2    <fieldref name='boolfield'/>3    <value type='Boolean'>1</Value>4  </eq >

(3) The field of a person and group type is equal to the current user, and if it is multi-selected, it contains the current user:

1 <Eq>2   <fieldref name='userfield'/>3   <value type='Integer'><UserID/></Value>4 </Eq>

(4) If a user or user group Type field is filled in with a user group, the filter user group contains the current user's:

1 <membership type='currentusergroups'>2   <fieldref Name ='userfield'/>3 </Membership>

(5) By default, queries for time and date types are accurate to the date only, if necessary to the exact time:

1 <Gt>2   <fieldref name='datetimefield' />3   <value type='DateTime' includetimevalue='TRUE' >4     - -08t17:37Z 5   </Value>6 </Gt>

(where the date format can be converted by using a static method Sputility.createiso8601datetimefromsystemdatetime)

(6) In general, when making a lookup or user filter, you can use the text type of value to determine the text of a lookup list entry or a string of the user's real name If you need to find exactly the ID of the item you are looking for or the ID of the user (you should use the EQ operator instead of the contains operator for multiple-choice lookup and multiple-choice users):

1 <Eq>2   <fieldref name='lookupfield' lookupid='  TRUE'/>3   <value type='Lookup'> Panax Notoginseng</Value>4 </Eq>

(7) In the list of calendar template types, when you need to query for events within a specified time range, if you are looking through Gt/geq and Lt/leq, you can only find non-repeating events, and the first event of a repeating event, in order to be able to accurately find a range of recurring events, You need to use the DATERANGESOVERLAP query operator and with some of the properties of SPQuery. (Duplicate events are not supported in cross-list queries)

 1  <daterangesoverlap>2  < FieldRef name= "  ' />/>

The DATERANGESOVERLAP operator is relatively fixed, where the eventdate is the internal name of the "Start Time"; EndDate is the internal name of the "End Time"; Recurrenceid is the internal name of a field that is related to the repeating event. The tags in value define the scope of the query, including the following 4 types of:<today/> (days), <Week/> (weeks), <Month/> (month), <Year/> (year).

You need to use the two properties of SPQuery when querying: expandrecurrence (bool type) needs to be set to true to indicate that a duplicate event is expanded when queried (even if there are no duplicate events, This property should also be set when using the operator Daterangesoverlap, calendardate (datetime type), setting the date of the query scope. For example, the following program queries all events for tomorrow (we can get an idea of the use of SPQuery in advance):

1 using(SPSite site =NewSPSite ("Http://sp2010/book"))2 {3   using(SPWeb Web =site. OpenWeb ())4   {5SPList callist = web. lists["Calendar"];6SPQuery query =NewSPQuery ();7Query. Expandrecurrence =true;8Query. Calendardate = DateTime.Today.AddDays (1);9Query. Query ="<DateRangesOverlap>"+Ten             "<fieldref name= ' eventdate '/>"+ One             "<fieldref name= ' EndDate '/>"+ A             "<fieldref name= ' Recurrenceid '/>"+ -             "<value type= ' DateTime ' ><today/></value>"+ -             "</DateRangesOverlap>"; theSPListItemCollection tomorrowevents =list. GetItems (query); -     foreach(SPListItemEvent inchtomorrowevents) -Console.WriteLine (Event. Title); -   } +}

For other types of query criteria, refer to the relevant chapters in the SDK (in the English version of the SDK, the location of the CAML query reference is in: SharePoint Foundation General Reference–services references– SharePoint schema References–caml Core schemas–query schema).

3. Logical operation in filter conditions

Logical combinations of multiple query conditions are also supported in CAML, supported with and and OR, but not supported. Use <And></And> and <Or></Or> tags (note capitalization) for logical operations.

However, it is necessary to specifically state thatonly two query criteria can be used within and tags and or tags . For example, the following query conditions are illegal (cond represents each subquery condition):

1 <Where>2   <And>3     <Cond1/><Cond2/><Cond3/>  4   </And>5 </Where>

Should be written as:

1 <Where>2   <And>3     <And>4       <Cond1/>< Cond2/>5     </And>6     <Cond3/>7   </And>8 </Where>

In this way, each logical operation tag is guaranteed to have only two sub-conditions.

Of course, and tags and or tags can be used interchangeably to form complex logical filter conditions.

4. Sorting conditions

In a CAML query, use <OrderBy></OrderBy> to specify 0-to-set sorting criteria.

Each of the fields in the query condition is ranked in the order of the first query condition, the secondary query condition, the third query condition, and so on, where each query condition is written as:

1 <fieldref name=' field name ' ascending='true| FALSE' />

The Name property must also use the internal name, the ascending property specifies whether it is a forward sort (from small to large), and if it is not, the default is a forward sort.

For example, the following CAML fragment specifies that the query is sorted by the author (the internal name is author) and then by the new to the old (the internal name is created), in the order of the creation time:

<OrderBy>  <fieldref name='Author'/>  <fieldref name='  Created' ascending='FALSE' /></orderby>

5. Return field (column)

When making a query, you do not return all fields of the entry every time, for performance reasons, you can return only the fields you need, by making a return field to do this, specifying the way to return the field using the form <fieldref name= ' field name '/> .

For example, if you need to return a title, author, and three fields for creation time, the notation is:

1 <fieldref name='Title' />2 <fieldref name='  Author' />3 <fieldref name='Created' />

It is also necessary to use the internal name of the field.

Reprint: http://www.myexception.cn/sharepoint/1857376.html

Use CAML Progress data query for the SharePoint server-side object model