Friday, December 10, 2010

Working with SPQuery

As a sharepoint developer most of the people might have used SPQuery and CAML atleast once.

Writing a Basic Query

SPContext ctx = SPContext.Current;
using (SPSite site = new SPSite(ctx.Site.ID))
{
      using (SPWeb web = site.OpenWeb(webid))
      {
         SPList list = web.Lists["MyTestList];
         SPQuery query = new SPQuery();
         //You have to call GetItems function of SPlist to execute the CAML query
         SPListItemCollection items = list.GetItems(query);      
      }
}

Here it will return All the Items.But in real time we need to specify the conditions and attributes to fine tune the Results. We need to have basic understanding of CAML syntax inorder to take full advantage of SPQuery Object.If you have a CAMLBuilder tool then it will be always handy in writing CAML queries.You could douwnload CAML builder from here

Understanding SPQuery Properties

1. SPQuery.RowLimit
   This Property allows you to set how many rows you need in your result.Its quiet common that your Query could return more than 500 rows so its better to limit the number of rows in a result and use paging to retrive the rest of the result.I will explain paging below.

2. spquery.ViewAttributes

View attribute is one of the important attribute which is veryusefull to set how you are going to retrieve the Data.
  Scenario 1: You want retrieve all the items including items in folder and subfolders then set it like the following

 //If you want to return only file
          SPQuery.ViewAttributes = "Scope='Recursive'";   //If you want to return All items
          SPQuery.ViewAttributes = "Scope='RecursiveAll'";

  Scenerio 2 : You want retrive only files
          spquery.ViewAttributes = "Scope=\"FilesOnly\"";

  Scenario 3: Only Approved Items
         spquery.ViewAttributes = "ModerationType='HideUnapproved'"

3. SPQuery.Folder
   This property is usefull when you want to retrieve the data only from specific folder.In real world scenario you need to retrive the items from specific folder rather than retrieving all items in the list.
    SPQuery.Folder = folder; //It should be a SPFolder Object

 4.SPQuery.ViewFields
This property helps to set which are the fields /columns you want in the result.There is no point in returning all the coloumns.So its better practice to specify the fields you want to return.

     //Name is the coloumn name / internal name of the field
     SPQuery.ViewFields = "";

5. SPQuery.Query

This property used to set the real CAML query.Build your CAML string and assign to this property.

SPQuery.Query = String.Format("<Where>
                                                           <Eq>
                                                              <FieldRef Name=\'FileLeafRef\'/>
                                                              <Value Type=\"Text\"></Value>
                                                        </Eq>
                                                  </Where>", searchText);

//searchText is name of the file you want to search

Note : One thing here we have to remember is no need to add query tag in your XML.

If you are using Date time in your query then set includetimevalue = true. See Below

Query = "<Where><Eq><FieldRef Name='Modified'/><Value Type='DateTime' IncludeTimeValue='TRUE'>"+ LastCheckedDate + "</Value></Eq>
</Where>"

CAML Notations


•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


Paging In SPQuery

Using paging with SPQuery is good practice.Its better to write code that will perform efficient way than giving bad user experience.

      using (SPSite site = new SPSite(SiteCollectionURL))
         {
                 
           using (SPWeb web = site.OpenWeb(WebName))
             {

              
               SPQuery query = new SPQuery();
               query.ViewAttributes = "Scope=\"Recursive\"";
               query.Folder = list.RootFolder;


               do
               {

                   SPListItemCollection listItems = list.GetItems(query);
                  
                 
//Perform the operations you want
                   query.ListItemCollectionPosition = listItems.ListItemCollectionPosition;
                 
//If you are showing in a grid view then stor the listItems.ListItemCollectionPosition in
                  viewstate or something like that
               } while (query.ListItemCollectionPosition != null);
             }
         }


Limitation of SPQuery Object
You cannot use the SPquery object to query across multiple site collection instead use SPSiteDataQuery

7 comments:

CmdrKeene said...

So, I've done a lot of looking around and have learned how to write these queries, but where do I put them? Do I start a new blank .aspx page in Designer, then paste in my query? It's honestly a bit frustrating to not understand how to get past what is probably a very basic first step.

sultana said...

Really wonderful blog! Thanks for taking your valuable time to share this with us. Keep us updated with more such blogs.
UiPath Training in Chennai
UiPath Training
Data Science Training in Chennai
Machine Learning Training in Chennai
Blue Prism Training in Chennai
UiPath Training in Anna Nagar
UiPath Training in T Nagar
UiPath Training in OMR

silakarim said...

Thanks for your valuable content, it is easy to understand and follow.keep update more information.
Tally Course in Velachery
Tally Course in Tambaram
Tally Course in Anna nagar
Tally Course in OMR
Tally Course in Adyar
Tally Course in Thiruvanmiyur
Tally Course in Porur
Tally Course in T Nagar
Tally Course in Vadapalani

Rahul Aniket said...

9x flicks

menon said...

Awesome post, This post shares some valuable information.
Spoken English Classes in Bangalore
Spoken English Classes in Chennai
English Speaking Course in Bangalore
Best Spoken English Classes in Bangalore
Spoken English in Bangalore
English Speaking Classes in Bangalore
AWS Training in Bangalore
Data Science Courses in Bangalore
DOT NET Training in Bangalore
DevOps Training in Bangalore

Jaswal Gupta said...

An interesting discussion is worth comment. I think that you ought to write more about this subject matter, it might not be a taboo matter but usually people do not talk about these issues. To the next! Best wishes!!
https://kbcofficialwinner.com/kbc-lottery-winner-show/
https://kbcofficialwinner.com/jio-lottery-winner-2020-jio-kbc-lottery-lucky-draw/
https://kbcofficialwinner.com/kbc-winner-list/
https://kbcofficialwinner.com/kbc-head-office-number-mumbai-kbc-helpline/

varsha said...

Great Article… I love to read your articles because your writing style is too good,Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries

AWS training in chennai | AWS training in annanagar | AWS training in omr | AWS training in porur | AWS training in tambaram | AWS training in velachery