May 9, 2012

CAML Query to Get Data from SharePoint List

To know about the Basics of the CAML query read this article.
To know about using CAML query builder to create CAML queries, read this article.
In this article, we will learn how to use CAML query in Microsoft Visual Studio
Scenario:
I have a custom list named “Product List” with the columns named Title, Price, Quantity and Is Available.
I want to retrieve the products with price greater than 1000 and lesser than 10000. Then I have to order the results depending upon the quantity in descending order.
Using the CAML query builder, I have created a CAML query for the above scenario and the query is as follows:
<Query><Where><And><Gt><FieldRef Name="Price" /><Value Type="Number">1000</Value></Gt><Leq><FieldRef Name="Price" /><Value Type="Number">10000</Value></Leq></And></Where><OrderBy><FieldRef Name="Price" Ascending="False" /></OrderBy></Query>

Implementing CAML Query in Microsoft Visual Studio
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace UsingCAMLQuery
{
    class Program
    {
        static void Main(string[] args)
        {
            const string camlQuery = @"100010000";
            try
            {
                using (SPSite objSite = new SPSite("sitename"))
                {
                    using (SPWeb objWeb = objSite.OpenWeb())
                    {
                        // Building a query
                        SPQuery query=new SPQuery();
                        query.Query=camlQuery;
                     
                        // Retrieving the data from the List
                        SPList objList=objWeb.Lists["Product List"];
                        SPListItemCollection items= objList.GetItems(query);

                        // Prinitng the header
                        Console.WriteLine("{0,-25} {1,-20} {2,-15} {3}", "Title", "Price", "Qunatity", "Is Available");

                        foreach (SPListItem objListItem in items)
                        {
                            Console.WriteLine("{0,-25} {1,-20} {2,-25} {3}", objListItem["Title"], objListItem["Price"], 
                                                                             objListItem["Quantity"], objListItem["Is Available"]);
                        }

                    }
                    Console.ReadLine(); 
                }
 
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error Occured:", ex.Message);
            }
            Console.ReadLine();
        }
    }
}
The full solution can be downloaded from the following link
Please free to comment and share this post if it helps you!

3 comments:

Dear Readers, I LOVE to hear from you! Your feedback is always appreciated. I will try to reply to your query as soon as possible.

1. Make sure to click the "Notify me" check box at the right side to be notified of follow up comments and replies.
2. Please Do Not Spam - Spam comments will be deleted immediately upon review.