Thursday, January 12, 2012

Select Distinct or Unique values from SharePoint List



I was looking around for the simple way of getting unique values from SharePoint list without affecting performance. I have seen several ways of doing it. For example, we can get all the items and then check if item exists in the array or some other collection. Another example, you can load your data into DataTable and then select unique values using DataTable method, following is snippet of code below on how to use it

GetUniqueColumnData("List Name", "ColumnName");


private void GetUniqueColumnData(string ListName, string ColumnName)
{
try
{
using (SPSite site = SPContext.Current.Site)
{
using (SPWeb web = site.OpenWeb())
{
DataTable dtList = new DataTable("TableName");
SPList list = web.Lists[ListName];
if (list != null)
{
SPQuery query = new SPQuery();
query.Query = "<OrderBy><FieldRef Name='" + ColumnName + "'/></Order By><FieldRef Name='" + ColumnName + "'/>";
dtList = list.GetItems(query).GetDataTable();
dtList = new DataView(dtList).ToTable(true, new string[] { ColumnName });
}
}
}
}
catch (System.Exception ex)
{
LoggingService.LogErrorInULS(ex.Message + "Stack Trace: "+ ex.StackTrace,TraceSeverity.High);
}
}

but I discovered one very simple method for selecting Distinct Values in SharePoint called GetDistinctFieldValues. Please see snippet of code below on how to use it


using (SPSite site = SPContext.Current.Site)
{
using (SPWeb web = site.OpenWeb())
{
SPList objList = web.Lists["List Name"];
SPField field = objList.Fields.GetField("Field Name");

Object[,] values;
uint numberValues = objList.GetDistinctFieldValues(field, out values);

for (int i = 0; i < numberValues; i++)
comboBox1.Items.Add(values.GetValue(0, i).ToString());

}
}