Search This Blog

Monday, June 20, 2011

UNIQUE Query in Sharepoint, CAML

Sharepoint doesnot provides any API through which we can retrieve unique records, nor we can do this by CAML. Although we can use LINQ, but there is another way to make the records unique after retreiving from sharepoint.

We can use method of DataView to make the data unique, Then this dataView can be converted to DataTable with unique values. Below is the example to do this:


public System.Data.DataTable GetUniqueData()
       {
           SPQuery objSPQuery = null;
           SPListItemCollection objSPListItemCollection;
           SPList objSPList;
           System.Data.DataTable objDataTable;
           DataView objDataView;

           using (SPSite objSPSite = new SPSite("<Your Site URl>"))
            {
                using (SPWeb objSPWeb = objSPSite.OpenWeb())
                {

                    try
                    {
                        objSPQuery = new SPQuery();

                        //write your query here
                        objSPQuery.Query = @"<Where>
                                              <IsNotNull>
                                                 <FieldRef Name='ID' />
                                              </IsNotNull>
                                           </Where>";

                        objSPList = objSPWeb.Lists["<List Name>"];
                        //Fill the list item collection
                        objSPListItemCollection = objSPList.GetItems(objSPQuery);
                       
                        if (objSPListItemCollection.Count > 0)
                        {
                            //convert the SPListItemCollection to Datatable
                            objDataTable = objSPListItemCollection.GetDataTable();

                            //Create a Dataview for applying UNIQUE
                            objDataView = new DataView(objDataTable);

                            //Convert the datatview to Table again by
specifying paramater 'distict' to true.
                            // the second parameter should be the column to be
checked for unique.
objDataTable = objDataView.ToTable(true, "<column                                  name(s) that you want to check for unique>");
                        }
                    }
                    catch(Exception Ex)
                    {
                        throw Ex;
                    }
                }
           }
           return objDataTable;
       }

No comments:

Post a Comment