Best practices when querying SharePoint lists/libraries

1) Golden rule - Never iterate through the whole list to filter/check-on couple of items in the list. List operations are heavy. Filter the required items needed maybe through a CAML query.

2) Use the SPQuery class if your objective is to get items from a single list

3) In your CAML query set ViewFields to filter the fields which you need. In the same way you can use SPList.GetItemByIdSelectedFields() method to get selected fields. Setting ViewFields makes the query more efficient

4) Use the SPSiteDataQuery class to get results from multiple lists. Here you can specify the web and lists which you are targeting at.

Example:

SPSiteDataQuery myQuery = new SPSiteDataQuery();
myQuery.query = "Your CAML query goes here";
myQuery.Webs = @"<Webs Scope=""SiteCollection"" />"; //Returns data from all webs in the current site collection
//myQuery.Webs = @"<Webs Scope=""Recursive"" />"; // will return from current web and its child webs

myQuery.Lists = @"<Lists ServerTemplate=""101"" />"; //101 refers to document libraries
myQuery.ViewFields =  @"
                                            <FieldRef Name=""Field One"" />
                                            <FieldRef Name=""Field Two"" />
                                           ";

Query SharePoint Libraries using CAML query and Powershell

$web = Get-SPWeb $siteUrl
$list = $web.Lists[$listName]

1) Query items in list

if($list -ne $null)
{

$caml = '<OrderBy><FieldRef Name="ID" Ascending="True" /></OrderBy><Where><And> <Geq> <FieldRef Name="ID" /><Value Type="Number">{0}</Value> </Geq><Leq><FieldRef Name="ID" /><Value Type="Number">{1}</Value></Leq> </And> </Where> ' -f $startValue,$endValue

$myQuery = new-object Microsoft.SharePoint.SPQuery
$myQuery.Query = $caml

$filteredItems = $list.GetItems($myQuery)

$filteredItems | ForEach-Object {

      #Assign meta data to variables
      $variableOne = $_["MetaDataOne_InternalName"]
}

}

2) Remove specific items from list

if($list -ne $null)
{

$caml='<Where> <Or> <Or> <Or> <Eq> <FieldRef Name="Title" /><Value Type="Text">{0}</Value> </Eq> <Eq> <FieldRef Name="Title" /><Value Type="Text">{1}</Value> </Eq> </Or> <Eq> <FieldRef Name="Title" /><Value Type="Text">{2}</Value> </Eq> </Or> <Eq> <FieldRef Name="Title" /><Value Type="Text">{3}</Value> </Eq> </Or> </Where> ' -f $itemOne, $itemTwo, $itemThree, $itemFour

$query=new-object Microsoft.SharePoint.SPQuery
$query.Query=$caml
$col=$list.GetItems($query)

Write-Host 'Number of items removed: ' $col.Count

$col | % {$list.GetItemById($_.Id).Delete()}

}

$web.Dispose()

for more info about CAML query syntax visit here

CAML Query - 3 AND 's in 1

It's pretty easy to deal with two conditions in CAML query. A newbie would find it to be a bit tricky when it comes to more than two. CAML has it's own way of grouping conditions. Below is a sample showing how to do it.

<Where>

<And>

<And>

<Eq>

          <FieldRef Name='_ModerationStatus'/>

          <Value Type='String'>Approved</Value>

</Eq>

<Neq>

<FieldRef Name='ContentType' />

<Value Type='Text'>Folder</Value>

</Neq>

</And>

<Neq>

<FieldRef Name='******' />

<Value Type='Text'>*****</Value>

</Neq>

</And>

</Where>

Allow CAML Query to get items inside Library Folders

Usually a CAML query would query a SharePoint library and get all available items matching the where clause, except the ones within folders of the Library. There is a property which needs to be explicitly set, in order for the query to return all items pertaining to the where clause, including the ones inside folders and nested folders. In the view attribute, the scope needs to be set to Recursive All. A sample CAML query would be:


SPListItemCollection itemCollection = null;
...
...
SPQuery myQuery = new SPQuery();

myQuery.ViewFields = @"<FieldRef Name='UniqueId'/>
                                            <FieldRef Name='ContentType'/>
                                            <FieldRef Name='ID'/><FieldRef Name='FileLeafRef'/>";

                string whereClause = @"<Where>
                                    <Eq>
                                        <FieldRef Name='" + myVariableOne + @"' />
                                            <Value Type='Text'>" + myVariableTwo + @"</Value>
                                    </Eq>
                                  </Where>";

myQuery.Query = whereClause;
myQuery.ViewAttributes += " Scope='RecursiveAll'";


Now use the SPLists GetItems() method to execute the query
Eg: testLibrary.GetItems(myQuery);

Custom Field Property Display using CAML - SharePoint 2007

SharePoint 2007 Custom field rendering at List Item View is done through the CAML code. In the custom field definition XML file, there is a section where display is defined... and that is within the  <RenderPattern>. Usually you can access the filed properties in the filed through simple CAML code. For an instance:

<RenderPattern Name="DisplayPattern">
  <HTML><![CDATA[</div><br/><br/><div>Name Value:</div><br/>]]></HTML>
  <ScriptQuote>
    <Property Select="Name" HTMLEncode="True" />
  </ScriptQuote>

  <HTML><![CDATA[<div>Custom Property 1 Value</div><br/>]]></HTML>
  <ScriptQuote>  
      <Property Select="MyCustomProperty1" HTMLEncode="True" />
  </ScriptQuote>
  <HTML><![CDATA[<div>Custom Property 2 Value</div><br/>]]></HTML>
  <ScriptQuote>
      <Property Select="MyCustomProperty2" HTMLEncode="True" />
  </ScriptQuote>
  <HTML><![CDATA[<div>Description : </div><br/>]]></HTML>
  <ScriptQuote>
    <Property Select="Description" HTMLEncode="True" />
  </ScriptQuote>
  <HTML><![CDATA[<div>Required : </div><br/>]]></HTML>
  <ScriptQuote>
    <Property Select="Required" HTMLEncode="True" />
  </ScriptQuote>
  <Column HTMLEncode="TRUE"/>

</RenderPattern>

The code would give you the property values of the Description, the required field value and Name value. But.....not the values of the custom properties in your field......

It is possible to get the custom property values and display at DisplayPattern. For this it's required that the property schema's being updated during the field's onAdd() and onUpdate() methods. Solution s to override the two methods.

Then the custom property values become accessible.

A detailed explanation is available at this Link.