Monday, 14 July 2008

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.

20 comments:

mike said...

Hi Chris,

Thankyou for all your useful blogs.

I am new to SharePoint Designer. I need to create a very simple workflow in designer which should be initiated on adding/updating an item in a list and work something like:

1. check column(choice yes/no) value.

2. if "yes" then before publishing this item, it should go for approval to approver group.

3. approver should be presented with a form with approved, rejected buttons.

4. If approver clicks 'approved' button then item will be published with "yes" as value for choice column.

5. If rejected then item will be published with "No" as value for the choice column and email the author of this item.

Sorry for my bad english!

mike

Chris O'Brien said...

Hi Mike,

Afraid I've not done too much with SPD workflows so can't really help. Suggest checking some of the posts on the SPD Team blog..

Best of luck,

Chris.

Karine Bosch said...

Hi Mike,
I found these articles on SPDataSource very interesting!
Kind regards,
Karine

Anonymous said...

Hi Chris

How did you get around teh control execution lifecycle problems. I am trying to use the ControlParemeter to render one dropdown from another. Whilst everything seems ok, the second dropdown does not change when the first is changed. Thanks

Richard

Chris O'Brien said...

Hi Richard,

I'm afraid I'd need to see what you're doing to try and work out the problem. As I say, I actually got it working fine in the end - it's just I think I initially was trying to do something too late in the cycle.

Cheers,

Chris.

Anonymous said...

Hi I'm having the same problem of Richard, it looks like the ControlParameter get the value the first time the browser load the page but not in the subsequent postback, the code is formerly the same as yours.
maybe can be any cache of sharepoint?

Chris O'Brien said...

@Anonymous,

Again, I think it's more likely to be a page execution lifecycle thing rather than caching - unless you are doing some form of caching obviously.

Unfortunately I can't remember if I had to change anything to re-bind on postback, but the markup above is definitely what I had working. Sorry I can't be more helpful.

Cheers,

Chris.

evilgenius said...

Where does this code actually go? I understand the code, but I do not know what file it goes in. At first I thought it would go in a custom newform.aspx page

Chris O'Brien said...

@evilgenius,

The code kind of goes where you need it - for me, it was in a MOSS page layout (WCM) which was hosting my dropdown control.

It sounds in your case like you're wanting to use it on a SharePoint list page. In that case yes, I think editing the appropriate form (e.g. newform.aspx) in SharePoint Designer is what you should be doing.

HTH,

Chris.

Robert Martinez said...

To get around the control execution lifecycle problem change the EnableViewState behavior to False for the dropdown you're trying to filter. With it set to true you're telling the drop-down to keep its values even when it round-trips.

Chris O'Brien said...

Hi Robert,

Thanks, but I don't think that was the execution lifecycle problem I ran into at the time.

Appreciate the pointer anyway though :-)

Chris.

Anonymous said...

Hi Chris,
I am using the SPDatasource in Crosslist mode to query data from Tasks list and I am binding it to SPGridview. But I do get an error saying "A field or property with the name 'Title' was not found on the selected data source.
If I use the SPDatasource in List mode everything works fine.
Any help in this regard is greatly appretiated.
Thanks,
Granada

Chris O'Brien said...

@Granada,

Sorry, I didn't do much with SPDataSource in cross-list mode. At a guess, it could be because one of the lists involved in the query doesn't contain a column called 'Title'.

Best of luck,

Chris.

Alan said...

Hi Chris,

How would you return the ID part of a Lookup Field using an SPDataSource.

I've tried using

<ViewFields>
<FieldRef LookupId='TRUE' name='MyField1 />
</ViewFields>

within the selectcommand of the SPDatasource, however the text content of the lookup ID is always returned.

Chris O'Brien said...

@Alan,

Not sure I'm afraid, I don't think I've used SPDataSource with lookup fields.

Best of luck..

Chris.

Kbert said...

Hey Alan,

I'm not sure you can return just the ID of a lookup field in the select portion of an SPDataSource. By definition, you are requesting the field, regardless of type.

In case you don't know, you can use an SPQuery to query your SharePoint list, get the SPListItemCollection using SPList.GetItems(SPQuery) and then assign a SPFieldLookupValue to your lookup field. From there, you would use SPFieldLookupValue.LookupID to get the ID of your lookup field.

I'm still learning SharePoint so there might be a better way, but this is what worked for me.

HTH,

Keith

@iOnline247 said...

Thanks for posting this insight into binding SPDatasources to controls. This has big wins for users that only have Designer available.

Cheers,
Matt

Alex Dove said...

Excellent article. How would you filter the data based on input from a textbox instead of a dropdown? I am looking for a solution which using filter-as-you-type to filter my SPDataSource and asp:GridView targeting a specific column.

Thanks.

Anonymous said...

Thank You for being one of the very few to provide any guidance on SPDataSource filtering with parameters... other blogs on it are not 'findable'... Thanks for great work again!

Garima said...

Hi Chris,
Thanks for the amazing blog. I learn a lot from it.
One question: have you ever used a managed metadata column in SPDataSource?
In my case, I bind my SPGridView with SPDataSource but in filtering it is giving me "TermName|TermGUID". Could you help?