Sunday, 6 July 2008

SPDataSource - every SharePoint developer's friend (part 1)

Sooner or later, nearly every SharePoint developer needs to write code to retrieve all the items from a list and display them - either on the page, or often in a control like a dropdown list control. An example could be retrieving a list of countries, or perhaps person titles for a form:


Clearly there are benefits from storing such values in a list, since we (or the client) can then add/edit/delete items easily. To implement this, the developer's first thought might be to write code like this:

private void bindPersonTitles()
// the name of the DropDownList control we are populating is ddlPersonTitles..
using (SPWeb configWeb = SPContext.Current.Site.AllWebs["configuration"])
SPList titlesList = configWeb.Lists["PersonTitles"];

foreach (SPListItem titleItem in titlesList.Items)
ddlPersonTitles.Items.Add(new ListItem(titleItem.Title, titleItem.ID.ToString()));

Which is fine. Except if we want to filter or sort the list items, we really should use a CAML query instead of iterating through all the items - the code below shows this (sorting on a column called 'SortOrder'), and also has a slight twist on the previous example in that I'm using data-binding rather than looping through the items 'manually':

private void bindPersonTitlesByCamlQuery()
using (SPWeb configWeb = SPContext.Current.Site.AllWebs["configuration"])
SPList titlesList = configWeb.Lists["PersonTitles"];
SPQuery query = new SPQuery();
query.Query = "<OrderBy><FieldRef Name=\"SortOrder\" /></OrderBy>";
SPListItemCollection titlesItems = titlesList.GetItems(query);

// showing alternative of using data-binding rather iterating through items..
ddlPersonTitles.DataSource = titlesItems;
ddlPersonTitles.DataTextField = "Title";
ddlPersonTitles.DataValueField = "ID";

Fine again. Except I can't help thinking "all this just to take the items from a list and put them in a dropdown??" Surely there must be a better way. And what did Microsoft do every time they needed to do this, surely they didn't repeat the above code in every place in SharePoint? The answer is no, they used this:


In short, SPDataSource is a web control which implements IDataSource and saves you writing code like the above. The great thing is that it is extremely flexible, and as we'll see, can be used for more than you might think. The best thing though, is that being a control it can be used declaratively, so I can bind my dropdown to the list without writing a single line of C# or VB.Net code - all I have to do is set properties correctly. We'll go through the detail in a second, but the markup to replace the last code sample would look like:

<SPWebControls:SPDataSource runat="server" ID="dsPersonTitles" DataSourceMode="List" 
SelectCommand="<Query><OrderBy><FieldRef Name='SortOrder' Ascending='true' /></OrderBy></Query>"
<asp:Parameter Name="WebUrl" DefaultValue="/configuration/" />
<asp:Parameter Name="ListName" DefaultValue="PersonTitles" />

<asp:DropDownList runat="server" ID="ddlPersonTitles" CssClass="title" DataSourceID="dsPersonTitles" DataTextField="Title" DataValueField="ID">

So we're doing the following:
  • setting the 'DataSourceMode' for the SPDataSource is set to 'List' - we'll examine other possible values shortly

  • setting the 'SelectCommand' to the CAML query we want to use - here we're just sorting on the 'SortOrder' field once more

  • telling the SPDataSource which list we want to use by supplying ASP.Net 2.0 parameter objects named 'WebUrl' and 'ListName' - we'll dive more into this later

  • finally we bind the dropdown to the data by specifying the DataSourceID to the ID we gave our SPDataSource, and also say which fields in the resultset we want to use for the 'Text' and 'Value' of the dropdown. Incidentally I recommend using the ID for the value and the Title for the text (as shown above) so that it's easy to create an SPLookupValue to update a list item - more on this in part 2

We're only just starting to see the power of SPDataSource, but I love this approach for a couple of reasons:
  • Details of my query aren't specified in compiled code, so if anything about the list changes (e.g. we restructure our site) I don't have to recompile and redeploy assemblies

  • Less custom code, less bugs!

  • Setting properties is arguably simpler than writing code

So let's dive deeper into what we can do - we'll cover 'modes' of SPDataSource in this article and how to dynamically pass parameters to control the query in part 2.

The different 'modes' of SPDataSource

SPDataSource isn't just limited to fetching the items from a list (DataSourceMode = 'List'). Other possibilities are:

  • CrossList - similar to doing a query with SPSiteDataQuery across all lists in a site collection (for a sample of this see the SharePoint Designer Team blog post linked at the end of this article)

  • ListItem - show field values from a single list item

  • Webs - lists all webs in a site collection

  • ListOfLists - lists all lists in a web

For the last 2 modes I was able to bind but had some difficulty working out values to use for the 'DataTextField'/'DataValueField' of my control. I was trying to simply get the web or list name, but none of the obvious values such as 'Title', 'ListName' etc. were in the resultset. I was unable to find any other information on this but I'm sure some more trial and error would solve it. The 'ListItem' mode can be interesting - in the following sample I'm binding a single list item to a DataGrid to show selected fields from the item, which itself is selected by using the 'ListItemID' property:

<SPWebControls:SPDataSource runat="server" ID="dsPeople" DataSourceMode="ListItem" UseInternalName="true"> 
<asp:Parameter Name="WebUrl" DefaultValue="/configuration/" />
<asp:Parameter Name="ListID" DefaultValue="34F91B0C-FCF2-455A-ABBA-67724FB4024A" />
<asp:Parameter Name="ListItemID" DefaultValue="1" />

<asp:GridView ID="grdPeople" runat="server" DataSourceID="dsPeople"
<asp:BoundField DataField="FullName" HeaderText="Blogger name" />
<asp:BoundField DataField="WorkCity" HeaderText="City" />
<asp:BoundField DataField="Blog_x0020_URL" HeaderText="Blog URL" />

This would give something like this, based on an underlying list item which has these fields (no formatting yet applied):


I found I had to specify UseInternalName = "true" and use the ListID rather than ListName parameter in this mode.

Next time - passing parameters to SPDataSource

So far we've looked at supplying parameters by using a standard ASP.Net parameter control and specifying the value in the DefaultValue' property. In fact, ASP.Net has a whole range of parameter controls which can do the work of retrieving a parameter from somewhere and passing it to the SPDataSource, so that's what we'll look at next time. Additionally, since we're often using SPDataSource to bind data to form controls, we'll look at the common scenario of getting the selected item out of the form control to save back to a lookup field in SharePoint.

<updated>That link to the SPD blog article I mentioned but forgot to link to is - the focus here is mainly on using SPDataSource with a DataView, but there's some great info and samples.</updated>


Renaud Comte said...

I think you speak about this link ?

Ahmed Eltawil said...

Great article Chris. I always thought that it took a lot of code just to retrieve a list of items from a SharePoint list. It's a shame that SPDataSource wasn't introduced in some of the SharePoint application development books I haven read so far.

Chris O'Brien said...

Renaud - oops, yes thanks for that. Article now updated :-)

VV said...

Hi Chris,
I have searched all over but could not find answer to this. This is my last attempt towards the problem.
I wish to use updatecommand/insertcommand in SPDatasource, but I do not see any example.
Appreciate if you can help. Thanks.

Chris O'Brien said...


I'm 99% certain SPDataSource doesn't support insert/update operations I'm afraid. If you need to insert/update you'll need to code these operations using SPList.Items.Add() etc.

If you hear any different I'd love to know.



VV said...

Thanks for your response Chris!

May be you are right, but then why does SPDatasource has properties named insertcommand/updatecommand? These are misleading. I have spent so much time trying to make this work, as I have to use lot of bulk editable gridviews in my project. Now I have to look at other options.
I have read both of your articles, they are very helpful. Keep up the Good work!

Chris O'Brien said...


I was just about to respond that the reason for this is that the InsertCommand and UpdateCommand properties are inherited from System.Web.UI.DataSourceControl (but there is no implementation), but upon checking this isn't the case!

So it now seems quite strange that SPDataSource would define these properties but not implement them. Like you, I haven't seen any samples of their use. Hence, I still believe SPDataSource doesn't use them - and especially so when you consider that CAML (used by SelectCommand property) has no syntax for performing insert/update operations.

As I say, if you hear different I'd be very interested!



Kirk Liemohn said...

I'm not sure if this completely solves the riddle, but SPDataSource implements IDataSource whose GetView method returns a DataSourceView which defines the ExeuteDelete, ExecuteInsert, ExecuteSelect, and ExecuteUpdate methods.

So, by ipmlementing IDataSource, SPDataSource must have at least some knowledge of the update/insert/delete operations even if they are just stubbed out.

Albert said...

I tried your code and got a warning saying that "OrderBy" isn't a public property of spdatasource. Is that normal?

Chris O'Brien said...


At a guess, that would say to me that your markup isn't quite in the right format (I'm assuming you're using the SPDataSource sample rather than the CAML query). Can you paste in what you're using?



silas2 said...

What guidlines would you suggest for deciding whether to use familiar (and probably well wrapped) Asp.Net databound controls, and this new stuff, executing xml/xsl/caml/html hybrid? It seems very verbose.

Chris O'Brien said...


That's a good question. XML + XSLT can be very performant, I used this approach a lot years ago with Content Management Server :)

Often it depends on what you get from the API you're using - if it gives you XML, don't serialize it into objects, just transform it. If it gives you objects, don't deserialize them, data-bind them!

Otherwise, you'll be able to find good perf comparisons with the right internet searches - there are a few variables so I won't comment too much here.



sharepoint said...

Hello Chris,
Thank you for your article. We came across it because we think we messed with the binding somehow.
Here is the markup (Sharepoint 2010) :


However on the page, the dropdownlist does not show the contry name ("Germany") but a link to it (inside the drop list!), like this:

<a href="http://servername/my/personal/Lab04/_layouts/listform.aspx?PageType=4&ListId={FA572B1D-F8A4-4A7B-BC3D-E1EAE92F20DE}&ID=4&RootFolder=*">Germany</a>

Do you have any idea how it could be?
Thank you,

Chris O'Brien said...


Sorry I'm not quite sure what you've done there. Hope you get sorted.