Get distinct values in share point 2010 list using CAML query

Get distinct values in share point 2010 list using CAML query


Step 1


Create a list in Sharepoint 2010 site, here I have created a list called "CAMLTEST" and entered three items in to the list

Here the Name "Rajesh" is repeated.

Step 2


I have created a visual Web part with a label - Name, drop down - ddl_names and a button - "btn_binddropdown". It will show the distinct values from the list.

Step 3


Write in the btn_binddropdown button click event...


 protected void btn_binddropdown_Click(object sender, EventArgs e)
        {
            try
            {
                SPList oList = SPContext.Current.Web.Lists["CAMLTEST"];
                SPQuery query = new SPQuery();
                query.Query = "<OrderBy><FieldRef Name='Name' /></OrderBy>";
                DataTable dtcamltest = oList.GetItems(query).GetDataTable();
                DataView dtview = new DataView(dtcamltest);
                DataTable dtdistinct = dtview.ToTable(true, "Name");
                ddl_names.DataSource = dtdistinct; 
                ddl_names.DataTextField = "Name";
                ddl_names.DataValueField = "Name";
                ddl_names.DataBind();                           
                               


            }
            catch (Exception exe)
            {
                Response.Write("Exception at btn_binddropdown_Click :" + exe);
            }
       }

Step 4


Deploy the webpart to the sharepoint site required (the site should contain the "CAMLTEST" list).

Insert the wepart in to any page and click the binddropdown button, the dropdown will be populated with the distinct values of name column in CAMLTEST list.

Happy Coding :))))))))))))))

Get the list items using CAML from SharePoint 2010 List

Get the list items in SharePoint 2010 List

The caml query to get the list items from the SharePoint 2010 list.

This is the Sample List - List name is "Details"

ID    Title         Address    

1      B               Bangalore

2      C               Chennai

Scenario 1

SPList olist = web.Lists[“ Details ”];

SPQuery spQuery = new SPQuery();

spQuery.Query = “<Where><Eq><FieldRef Name="Title" />

                 <Value Type="Text">B</Value>

                 </Eq></Where>”

SPListItemCollection oitems = olist.GetItems(spQuery);

The Result is

ID    Title         Address    

1      B             Bangalore

Scenario 2

SPList olist = web.Lists[" Details ”];

SPQuery spQuery = new SPQuery();

spQuery.Query = “<Where><Eq><FieldRef Name="Title" />

                 <Value Type="Text"> C</Value>

                 </Eq></Where>” 

SPListItemCollection oitems = olist.GetItems(spQuery);

The Result is

ID    Title         Address    

2      C             Chennai

View Fields In CAML

The sample CAML Query using viewfields

SPQuery query = new SPQuery();
               query.Query = string.Concat(
                              "<Where><Eq>",                                 "<FieldRef Name='Status'/>",
                                 "<Value Type='CHOICE'>Not Started</Value>",
                              "</Eq></Where>",
                              "<OrderBy>",
                                 "<FieldRef Name='DueDate' Ascending='TRUE' />",
                                 "<FieldRef Name=’Priority’ Ascending='TRUE' />",
                              "</OrderBy>");                   

               query.ViewFields = string.Concat(
                                   "<FieldRef Name='AssignedTo' />",
                                   "<FieldRef Name='LinkTitle' />",
                                   "<FieldRef Name='DueDate' />",
                                   "<FieldRef Name='Priority' />");

               query.ViewFieldsOnly = true; // Fetch only the data that we need.

Determine Total Count Of Items Returned By SPQuery

 SPQuery query = new SPQuery();
   query.Query = string.Concat(
                  "<Where><Eq>",
                     "<FieldRef Name='Status'/>",
                     "<Value Type='CHOICE'>Not Started</Value>",
                  "</Eq></Where>",
                  "<OrderBy>",
                     "<FieldRef Name='DueDate' Ascending='TRUE' />",
                     "<FieldRef Name=’Priority’ Ascending='TRUE' />",
                  "</OrderBy>");                  


   SPListItemCollection items = list.GetItems(query);
   double totalCount = items.Count; 

Get Top 5 list items from Sharepoint List using CAML

Get Top 5 list items from Sharepoint List using CAML


This is a sample code for retrieving top 5 items from the SharePoint List.

SPQuery is the SharePoint class to initialize the CAML query.

This following Query will display the top 5 items OrderBy ID as Ascending is false.

SPQuery spQuery = new SPQuery();
spQuery.Query = "<Query>
   <OrderBy>
      <FieldRef Name='ID' Ascending='False' />
   </OrderBy> </Query> ";
spQuery.RowLimit = 5;