Retrieving data from a SharePoint list - Part 2
Drop Down Box Items Sorting
In part 1, we saw how to retrieve data from a SharePoint list programmatically. In this part, we will modify our code and make it more efficient by using the SPQuery object. SPQuery will make the code efficient and fast. It will also sort the items and the drop down will show a sorted list of countries.

1. Open form in editor.

2. Open VSTA.

3. Modify the AddCountries() function by adding these lines:

SPQuery query = new SPQuery();

query.Query = "<OrderBy><FieldRef Name='Title' /></OrderBy>";


Then modify the statement in the same function that defined list item collection:

SPListItemCollection listitems = list.Items;

The new statement will be:

SPListItemCollection listitems = list.GetItems(query);



You will notice the query is missing the "<Where>" tag. This is because we are not filtering the data. "<Where>" tag is used to filter data. Right now we are just sorting the data. To sort data, we use "<OrderBy>" tag. The complete code will look like the following:

public void AddCountries()

{

try

{

SPSite site = SPContext.Current.Site;

SPWeb web = site.OpenWeb();

SPList list = web.Lists["Countries"];

SPQuery query = new SPQuery();

query.Query = "<OrderBy><FieldRef Name='Title' /></OrderBy>";

SPListItemCollection listitems = list.GetItems(query);

XPathNavigator nav = this.CreateNavigator().SelectSingleNode("/my:myFields/my:Countries", this.NamespaceManager);

foreach (SPListItem li in listitems)

{

XPathNavigator newNode = null;

newNode = nav.Clone();

newNode.SelectSingleNode("/my:myFields/my:Countries/my:Displayname", this.NamespaceManager).SetValue(li["Title"].ToString());

newNode.SelectSingleNode("/my:myFields/my:Countries /my:Value", this.NamespaceManager).SetValue(li["Title"].ToString());

nav.InsertAfter(newNode);

newNode = null;

}

nav.DeleteSelf();

nav = null;

}

catch

{

}

}



4. You don't have to make any changes in your form. Compile the code, republish the form, open it in SharePoint and you will see a sorted list of countries in the "Country" drop down.



You will notice that the list has been sorted in the reverse order. To sort it in correct order, change the query. Add attribute "Ascending='FALSE'" in the query.

query.Query = "<OrderBy><FieldRef Name='Title' Ascending='FALSE' /></OrderBy>";



Now, the list will be sorted with countries starting with A first.

Download completed InfoPath form and application code (ZIP format)