Monday 29 December 2014

HOW TO USE CAML QUERY IN SHAREPOINT TO FETCH LIST ITEMS




CAML represents Collaborative Markup Language which is an XML based language for querying and updating SharePoint objects.

CAML Query Comparison Operators:

Tag Name     Meaning
ContainsContains a given text value
EqEqual to
GeqGreater than or equal to
GtGreater than
LeqLess than or equal to
LtLess than
NeqNot equal to
DateRanges
Overlap
Compares dates in recurring events to determine if they overlap
IsNotNullIs not null
IsNullIs null

Examples:-

 FETCH CURRENT USER ITEMS FROM SHAREPOINT  LIST USING CAML QUERY

 Dim userName  As SPUser = SPContext.Current.Site.Web.Currentuser.name
  Using site As SPSite = SPContext.Current.Site
  Using _web As SPWeb = site.OpenWeb()
    Dim _waitinglist As SPList = _web.Lists("List Name")
    Dim oSb As New System.Text.StringBuilder
           oSb.Append("         <OrderBy>")
            oSb.Append("              <FieldRef Name=""Priority""  Ascending='FALSE'/>")
            oSb.Append("         </OrderBy>")
            oSb.Append("         <Where>")
            oSb.Append("                       <Eq>")
            oSb.Append("                             <FieldRef Name=""Booked_x0020_By"" />")
            oSb.Append("                             <Value Type=""User"">" & userName  & "</Value>")
            oSb.Append("                        </Eq>")
            oSb.Append("         </Where>")
    Dim sResult As String = oSb.ToString()
    Dim query As New SPQuery()
    query.Query = sResult
    Dim _waitingcoll As SPListItemCollection = _waitinglist.GetItems(query)           
 End Using
 End Using


In the above code Booked by is people picker column in sharepoint list

 FETCH  SHAREPOINT  LIST  ITEM'S BETWEEN TO TWO  DATES

  Using site As SPSite = SPContext.Current.Site
  Using _web As SPWeb = site.OpenWeb()
  Dim _waitinglist As SPList = _web.Lists("List Name")
    Dim oSb As New System.Text.StringBuilder
    oSb.Append("  <Where>")
    oSb.Append("     <And>")
    oSb.Append("       <Lt>")
    oSb.Append("          <FieldRef Name=""Start_x0020_Date_x0020_Time"" />")
    oSb.Append("           <Value Type=""DateTime"" IncludeTimeValue=""TRUE"">" & enddatetime & "</Value>")
    oSb.Append("       </Lt>")
    oSb.Append("        <Gt>")
    oSb.Append("            <FieldRef Name=""End_x0020_Date_x0020_Time"" />")
    oSb.Append("            <Value Type=""DateTime"" IncludeTimeValue=""TRUE"">" & startdatetime & "</Value>")
    oSb.Append("        </Gt>")
    oSb.Append("     </And>")
    oSb.Append("   </Where>")
    Dim sResult As String = oSb.ToString()
    Dim query As New SPQuery()
    query.Query = sResult
    Dim _waitingcoll As SPListItemCollection = _waitinglist.GetItems(query)           
 End Using
 End Using

 FETCH THE SHAREPOINT LIST ITEMS BY COMPARING BOOLEAN VALUE
      
        Dim web As SPWeb = Common.getWeb
        web.AllowUnsafeUpdates = True
        Dim _list As SPList = web.Lists("RESOURCE")
        Dim query As New SPQuery
        Dim oSb As New System.Text.StringBuilder
        oSb.Append("         <Where>")
        oSb.Append("                   <Eq>")
        oSb.Append("                        <FieldRef Name=""Displayed"" />")
        oSb.Append("                        <Value Type=""Boolean"">1</Value>")
        oSb.Append("                   </Eq>")
        oSb.Append("         </Where>")
        Dim sResult As String = oSb.ToString()
        query.Query = sResult
        Dim Items As SPListItemCollection = _list.GetItems(query)
        Return Items

USING CAML TO QUERY SHAREPOINT LISTS OVER LOOKUP FIELDS

Using site As SPSite = SPContext.Current.Site
  Using _web As SPWeb = site.OpenWeb()

    Dim _waitinglist As SPList = _web.Lists("List Name")

    Dim oSb As New System.Text.StringBuilder
    oSb.Append("  <Where>")

    oSb.Append("      <Eq> ")

    oSb.Append("          <FieldRef Name=""ProjectID"" />")

    oSb.Append("           <Value Type="Lookup">My Test Project 1</Value> 

    oSb.Append("       </Eq> ")

    oSb.Append("   </Where>")

    Dim sResult As String = oSb.ToString()

    Dim query As New SPQuery()
    query.Query = sResult
    Dim _waitingcoll As SPListItemCollection = _waitinglist.GetItems(query)           
 End Using
 End Using

CAML QUERY TO MATCH TEXT FIELD IN SHAREPOINT LSIT

 Using site As SPSite = SPContext.Current.Site

  Using _web As SPWeb = site.OpenWeb()

    Dim _waitinglist As SPList = _web.Lists("List Name")

    Dim oSb As New System.Text.StringBuilder

    oSb.Append("  <Where>")

    oSb.Append("      <Eq> ")

    oSb.Append("          <FieldRef Name=""Designation"" />")
    oSb.Append("           <Value Type="Text">Enginee</Value> 
    oSb.Append("       </Eq> ")
    oSb.Append("   </Where>")
    Dim sResult As String = oSb.ToString()
    Dim query As New SPQuery()
    query.Query = sResult
    Dim _waitingcoll As SPListItemCollection = _waitinglist.GetItems(query)           
 End Using
 End Using

HOW TO USE INCLUDE OPERATOR IN CAML QUERY


INCLUDES:

MSDN Def: If the specified field is a Lookup field that allows multiple values,

specifies that the Value element is included in the list item for the field that is specified by the FieldRef element.



  Using site As SPSite = SPContext.Current.Site
  Using _web As SPWeb = site.OpenWeb()
    Dim _waitinglist As SPList = _web.Lists("List Name")
    Dim oSb As New System.Text.StringBuilder
    oSb.Append("  <Where>")
    oSb.Append("      <Includes> ")
    oSb.Append("          <FieldRef Name=""Organization"" />")
    oSb.Append("           <Value Type="Lookup">microsoft</Value> 
    oSb.Append("       </Includes> ")
    oSb.Append("      <Includes> ")
    oSb.Append("          <FieldRef Name=""Designation"" />")
    oSb.Append("           <Value Type="Lookup">.NetDeveloper</Value> 
    oSb.Append("       </Includes> ")
    oSb.Append("   </Where>")
    Dim sResult As String = oSb.ToString()
    Dim query As New SPQuery()
    query.Query = sResult
    Dim _waitingcoll As SPListItemCollection = _waitinglist.GetItems(query)           
 End Using
 End Using


HOW TO USE NOT-INCLUDE OPERATOR IN CAML QUERY

Not-Include quite oppsite to Include operator

 Using site As SPSite = SPContext.Current.Site
  Using _web As SPWeb = site.OpenWeb()
    Dim _waitinglist As SPList = _web.Lists("List Name")
    Dim oSb As New System.Text.StringBuilder
    oSb.Append("  <Where>")
    oSb.Append("      <Includes> ")
    oSb.Append("          <FieldRef Name=""Organization"" />")
    oSb.Append("           <Value Type="Lookup">microsoft</Value> 
    oSb.Append("       </Includes> ")
    oSb.Append("      <NotIncludes> ")
    oSb.Append("          <FieldRef Name=""Designation"" />")
    oSb.Append("           <Value Type="Lookup">.NetDeveloper</Value> 
    oSb.Append("       </NotIncludes> ")
    oSb.Append("   </Where>")
    Dim sResult As String = oSb.ToString()
    Dim query As New SPQuery()
    query.Query = sResult
    Dim _waitingcoll As SPListItemCollection = _waitinglist.GetItems(query)           
 End Using
 End Using

Share:

No comments:

Post a Comment

© TBGsharepointforum All rights reserved | Designed by Blogger Templates