SPQuery syntax

SPQuery is the best way to write queries on MOSS.

MOSS is using CAML to get data in the best and quickest way from sites,lists and folder. using SPQuery is in most cases the "best practice" way to find information in MOSS and also used in Sharepoint web services.

We have gather a few impotent information about SPQuery syntax,

Properties:

• SPQuery.Query:
  

• SPQuery.RowLimit: An int that indicate the row limit.

• SPQuery.Folder: Get a SPFolder object.  
  This is the best way to get items that are in folder.

• SPQuery.ViewField: The same is a "SELECT" sentence.

Operators:

Eq = equal to

Neq = not equal to

BeginsWith = begins with

Contains = contains

Lt = less than

Leq = less than or equal to

Gt = greater than

Geq = greater than or equal to

IsNull = is null

IsNotNull = is not null

AND & OR CONDITIONS

Using and & or is very similar to using regular parentheses, between each to operation we need to add AND or OR for example:

• AND:

We are using AND between 2 operators and add another tab when we add a new operator.

• OR:

The same as AND.

• AND & OR:

With AND & OR we are creating brackets, we create each one of the conditions and wrap them with AND/OR.

How to write CAML queries ?

A very useful tool allow you to build yours CAML queries: U2U Caml Query Builder.

You can download it at : http://www.u2u.be/res/Tools/CamlQueryBuilder.aspx

Performance consideration:

If your farm is well indexed, retiewing datas using SPquey in the best method to improve performance. See also our post about working with large list in Sharepoint

Document library internal fields

Recently i was needed to use OpenXml in Moss (hopefully i will write on this soon), one of the issue i needed to handle was the fact that it will only work of office 2007 files and not on pdf/office 2003/txt files and more.

After  a short google seach i found all the relevant suffix (http://msdn.microsoft.com/en-us/library/aa338205.aspx , http://office.microsoft.com/en-us/word/HA100069351033.aspx), so all i needed is the file suffix. However, i was very surprised to find out that non of the SPFile properties or the SPListItem properties contains the document suffix (the last one make sense).

Then i remembered that sharepoint document library (as so list) have hidden/frombase/readonly fields that contains all kind of parameters, like:

BaseName - Document prefix.

FileLeafRef - Prefix.Suffix.

EncodedAbsUrl - Full document url.

DocIcon - Document suffix.

Author - Created by.

Editor - Modified by.

FileDirRef - Directory id and directory name (seperated by  ;#).

ContentTypeId - Content type id.

FileSizeDisplay - File size.

GUID - File guid.

So i used the DocIcon field to get the suffix and to check if the document support OpenXml.