SharePoint Queries

We all know that SharePoint lists can provide a simple, user friendly and straight forward way of storing & retrieving user editable data. Once you start developing anything using SharePoint it would be very hard to escape SharePoint lists. Other than the fact that most SharePoint features rely on them (like blogs, discussion boards, galleries, etc.) they will save you a lot of time when you want data stored in a table format and the end user needs to edit that data, the administrator needs to secure the data, you want workflow and approval features, change history and all the other little features that SharePoint provides on list items.

But the problem arises when you as the developer need to use that data in other parts of your program and would like to access it just as you would a regular table in the database.

To be more specific let's assume we have decided to create a public (Internet) facing site which is going to among other features display a list of our products in different ways on separate pages. Let's say we want to show the latest products, the cheapest priced products, etc.

Now let's make a few assumptions: (1) we don't have this information in a standard DBMS (2) we want our team of "product administrators" to be able to introduce new products, modify previous specs etc. through a simple web UI and (3) the lists displayed on the public site will be formatted with the public sites branding.

OK let's see how we can solve this using SharePoint features and minimal coding effort:

I would create a SharePoint list and call it 'Products' (what a surprise!). This list will be setup to have all the necessary fields such as product name, description, price, …

Now if I was going to provide a view of this list showing the cheapest priced products, the first thing would be to create a view on the list which has the right ordering and filtering to achieve the criteria for cheapest priced product. Creating this view is very simple and can be easily achieved through the standard SharePoint UI. But we are looking for a way to fetch this list of products so we can display them in our own special web part/user control with our own special formatting, rules, etc.

OK how to get to this data using the SharePoint API:

A couple of different approaches can be taken to get to this data and I'm going to touch on two of them here:

1. You can access the same view that we described above using the backend API and fetch the items from the view.
   
2. You can create a query on the fly and execute it against the list to fetch the list of items you need and then work on them as needed.
   

Before I get into the details of each approach let's do a little comparison of each approach:

The first approach is much easier to program. You create a view through the standard SharePoint UI and give it a specific name and then start using it in your code.

The second approach requires more code but it is more flexible since you can decide about what you fetch at runtime (as opposed to working on a fixed view). I also like the second approach a lot better since all the things that I need are encapsulated in my code and I'm not relying on any external elements that for any reason might not exist (or might get deleted) and cause problems for my code.

So let's look at some code.

1^st approach: "You can access the same view that we described above using the backend API and fetch the items from the view"

SPSite site = new
SPSite("http://mysite");

SPWeb web = site.OpenWeb();

SPList productList = web.Lists["Products"];

SPView cheapestView = productList.Views["CheapestView"];

foreach (SPListItem item in productList.GetItems(cheapestView))

{

//do what ever you want to do with the item

}

2^nd approach: "You can create a query on the fly and execute it against the list to fetch the list of items you need and then work on them as needed"

SPSite site = new
SPSite("http://mysite");

SPWeb web = site.OpenWeb();

SPList productList = web.Lists["Products"];

SPQuery qry = new
SPQuery();

int maximumCheapPrice = 100; //what ever the maximum price criteria is

qry.Query = "<Where><Leq>" +

"<FieldRef Name=\"Price\" /><Value Type=\"Number\">" +

maximumCheapPrice.ToString() +

"</Value>" +

"</Leq></Where>" +

"<OrderBy>" +

"<FieldRef Name=\"Price\" Ascending=\"TRUE\" />" +

"</OrderBy>";

qry.RowLimit = 10; //only fetch the top 10 cheapest products

foreach (SPListItem item in productList.GetItems(qry))

{

//do what ever you want to do with the item

}

The weird way of defining a filter and order by clause for our query is called CAML. It's a XML based language that you can use to define whatever filter you need to fetch the data. There are some good free editors (try this) that you can use to define the filter and then copy paste the result into your code.