# SPDataSource - every SharePoint developer's friend

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";        ddlPersonTitles.DataBind();    }}

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:

SPDataSource

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>"<SelectParameters><asp:Parameter Name="WebUrl" DefaultValue="/configuration/" /><asp:Parameter Name="ListName" DefaultValue="PersonTitles" /></SelectParameters></SPWebControls:SPDataSource> <asp:DropDownList runat="server" ID="ddlPersonTitles" CssClass="title" DataSourceID="dsPersonTitles" DataTextField="Title" DataValueField="ID"></asp:DropDownList>

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"> <SelectParameters><asp:Parameter Name="WebUrl" DefaultValue="/configuration/" /><asp:Parameter Name="ListID" DefaultValue="34F91B0C-FCF2-455A-ABBA-67724FB4024A" /><asp:Parameter Name="ListItemID" DefaultValue="1" /></SelectParameters></SPWebControls:SPDataSource> <asp:GridView ID="grdPeople" runat="server" DataSourceID="dsPeople" AutoGenerateColumns="False"><Columns><asp:BoundField DataField="FullName" HeaderText="Blogger name" /><asp:BoundField DataField="WorkCity" HeaderText="City" /><asp:BoundField DataField="Blog_x0020_URL" HeaderText="Blog URL" /></Columns></asp:GridView>

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 http://blogs.msdn.com/sharepointdesigner/archive/2007/04/24/spdatasource-and-rollups-with-the-data-view.aspx - the focus here is mainly on using SPDataSource with a DataView, but there's some great info and samples.

### SPDataSource - every developer's friend (part 2)

SPDataSource - a refresher

So last time in part 1 of this 2-part series, we saw how SPDataSource is a great option for fetching data from lists since not only does it do the actual work of fetching the items for you, it also does this without any C#/VB.Net code. We can retrieve all the items from the list, or optionally supply a CAML query along with the list details if we wish to filter/sort the items. So as I showed in part 1, we can easily display a dropdown containing the items from a list with the following markup:

<SPWebControls:SPDataSource runat="server" ID="dsPersonTitles" DataSourceMode="List" SelectCommand="<Query><OrderBy><FieldRef Name='SortOrder' Ascending='true' /></OrderBy></Query>"<SelectParameters><asp:Parameter Name="WebUrl" DefaultValue="/configuration/" /><asp:Parameter Name="ListName" DefaultValue="PersonTitles" /></SelectParameters></SPWebControls:SPDataSource> <asp:DropDownList runat="server" ID="ddlPersonTitles" CssClass="title" DataSourceID="dsPersonTitles" DataTextField="Title" DataValueField="ID"></asp:DropDownList>

We also saw how SPDataSource offers more than just retrieving items from a list, with the following other 'modes' (see part 1 for more details on these):

• CrossList - similar to doing a query with SPSiteDataQuery across all lists in a site collection
• ListItem - show field values from a single list item
• Webs - lists all webs in a site collection
• ListOfLists - lists all lists in a web

What I want to focus on this time is how to use parameters with SPDataSource, since when using the control for real you'll often want to do this.

Parameters with SPDataSource

SPDataSource works like other .Net data source controls, so the key to passing parameters is using one of the .Net 2.0 parameter classes, either declaratively (in markup) or in code. The example above shows using the basic Parameter class by setting the DefaultValue property to a known string, but the following parameter types can also be used (though note I haven't tried them all and the SPD Team Blog article I mentioned says somewhat vaguely that "most" of them can be used!):

Effectively using one of these saves you writing code to read the value from the respective location and passing it to SPDataSource yourself. I think that ControlParameter and QueryStringParameter are possibly of the most value, though all could be interesting depending on your requirements. As an example, to get the value a user selected earlier from a dropdown containing a list of lists and pass it to a SPDataSource control I would need:

<SelectParameters><asp:Parameter Name="WebUrl" DefaultValue="/configuration/" />    <asp:ControlParameter Name="ListName" ControlID="ddlLists"PropertyName="SelectedValue"/></SelectParameters>

I seemed to have some control execution lifecycle fun with ControlParameter but it did work in the end. To pull the value from a 'userID' querystring parameter I would need:

<SelectParameters><asp:QueryStringParameter Name="userId" QueryStringField="userId" /></SelectParameters>

And that's not all - I can also drop a parameter value into a string such as a CAML query used in the earlier example of 'List' mode:

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

Notice here I'm dynamically specifying the sort field and direction from querystring parameters. Cool stuff.

Even cooler is the idea of building your own parameter control - one that strikes me is one for retrieving values from a user's SharePoint profile (since the ProfileParameter listed above refers to ASP.Net profiles). Scott Mitchell has a great guide to this at Accessing and Updating Data in ASP.NET 2.0: Creating Custom Parameter Controls.

Summary

So we've covered a lot of ground there, so here's a recap of why the SPDataSource is your friend:

• Can bind to any control which can do data-binding - DropdownList, ListBox, CheckBoxList are some obvious candidates, but consider also Repeater, DataGrid and SharePoint DataView/SPGridView controls
• Can easily get parameters from other controls, querystring, session, form values etc. (or write some code to fetch from another location)
• Can use a variety of modes to make different queries e.g. items in a list, properties of a list item, webs in a site etc.

If you want to see more I recommend reading SPDataSource and Rollups with the Data View - this emphasises using SPDataSource with SharePoint DataViews but also has some good examples I haven't covered, such as using the 'CrossList' mode.

• 本文已收录于以下专栏：

## 902A-Visiting a Friend

• Cai_Haiq
• 2017年12月20日 19:22
• 246

## 【LeetCode】Palindrome Partitioning II 解题报告

【题目】 Given a string s, partition s such that every substring of the partition is a palindrome. ...
• ljiabin
• 2014年11月16日 16:00
• 2882

## 小议：如何监控SharePoint 2013 Web Page的Performance？

• ShelleyLiu0415
• 2015年09月01日 11:41
• 924

## 【LeetCode】Palindrome Partitioning 解题报告

【题目】 Given a string s, partition s such that every substring of the partition is a palindrome. ...
• ljiabin
• 2014年11月16日 16:05
• 2008

## Welcome!Every Friend^_^

新博客开张了!我一定要好好学啊，不能浪费美好的时光：》     今天是进实验室的第二周的第三天......估计这个暑假就没有假放了：《，毕竟要二年级了啊，时间很紧迫啊     在大四的时候我...
• stamina88
• 2006年07月26日 17:44
• 639

## Ten Must-Have Tools Every Developer Should Download

Ten Must-Have Tools Every Developer Should Download Nowhttp://msdn.microsoft.com/msdnmag/issues/04/0...
• black_ben
• 2006年02月16日 15:27
• 652

## 《牛客网leetcode20题》Given a string s, partition s such that every substring of the partition is a palind

• li374456041
• 2017年02月23日 22:13
• 455

## A. Visiting a Friend（模拟）

A. Visiting a Friend time limit per test 1 second memory limit per test 256 megabytes ...
• sinat_37668729
• 2017年12月28日 14:34
• 22

## volatile: The Multithreaded Programmer's Best Friend

http://www.drdobbs.com/cpp/volatile-the-multithreaded-programmers-b/184403766 Comment The volatile ...
• u011127094
• 2016年06月30日 16:48
• 64

## leetCode解题报告之Palindrome Partitioning I,II(DFS,DP)

Palindrome Partitioning I, II DFS 和 DP动态规划问题 首先我们先定义几个变量，并对这几个量做一定的说明！为了方便理解，下面这些为伪码!!! len = s...
• u011133213
• 2014年03月31日 01:02
• 20345

举报原因： 您举报文章：SPDataSource - every SharePoint developer's friend 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)