On a recent project, we needed to display some data from lists which spanned multiple sites within a site collection, but the client wanted the data in the “familiar” SharePoint list format, with sorting and filtering and edit and ECB action capabilities. We explained that collecting the data can easily be done using an SPSiteDataQuery, but displaying the data with the sorting and filtering and familiar “list view” look and feel the users were familiar with, would take a little effort.

The response was, “Well, can’t you just use the list view in SharePoint?”.

Sort of…

SharePoint gives you a great deal out of the box, but it’s sort of like the wizard’s line in the The Wizard of Oz, “Pay no attention to that man behind the curtain.” That “man” behind the curtain is really busy pulling levers and twisting knobs to make things look really simple.

The thought was that integrating the data and mimicking the look and feel of SharePoint should be easy, right?

The key was how to reuse the power of SharePoint’s out of box features to minimize the amount of coding we needed to do.

Let’s open the curtain and see how we got this accomplished.

Follow the Yellow Brick Road

Our client had a very clear idea of what data they wanted, and how they wanted to interact with that data. Here are the loose requirements in User Story format (with some of the details cloudy from the poppies):

As a team member I want to display list data for the current site only for top-level sites
As a team member I want to display all data from a non-root site and all child sites
As a team member I want to edit the displayed fields without leaving the view of all the items
As a team member I want to be able to edit a specific list item by clicking the “Edit” menu for the item
To implement these features, we needed to query data from multiple lists, assemble the data and enable the ECB menu style interaction with the returned data. Here’s the high level steps required to accomplish the previous features.

Using ObjectDataSource and SPSiteDataQuery to query list data
Creating a Custom People Editor Bound Field
Binding SPGridView to the ObjectDataSource
Providing an ECB Menu using MenuTemplate and SPMenuField
Dealing with the Flying Monkeys
Using ObjectDataSource and SPSiteDataQuery To Query List Data

Many articles and blog posts hint at using the SPDataSource as the datasource to bind to the SPGridView instance. However, for our purposes, SPDataSource had some shortcomings. Specifically, the search modes of SPDataSource were insufficient for our scenario. Using the ObjectDataSource gave us the most flexibility, enabling configuration of Select and Update (we did not implement Insert and Delete) commands to execute on our data, as well as the integration we wanted to keep all of the SharePoint goodness.

One challenge of using the ObjectDataSource combined with SPSiteDataQuery was providing an SPContext in order to query the correct site and web. The ObjectDataSource enables this by providing the OnDataSourceCreating event, and wiring this up enables us to provide the SPContext information to an overloaded constructor of our ObjectDataSource. The following are snippets of the code that initializes the instance of our CustomDataSource and then the handler that lets us use the SPSite and SPWeb we need for querying data.

Within the GetDataTable method of our CustomDataSource, we dynamically build a CAML query for all Lists of a specific type (by list ID) and use an instance of the SPSiteDataQuery class and SPContext.Site instance to get the DataTable back from our CAML Query.

We then iterate over the rows in the table and add or modify columns and data to get specific information. One specific piece of data required was the SPUser that an item was Assigned To (the lists were based on the Task List). Getting the AssignedTo translated value into the table led into another key feature – binding the row to a list item and enabling the use of the custom PeoplePickerBoundField to bind and update the AssignedTo field.

To enable the Update of a given list item as a postback, the UpdateDataTable method of our CustomDataSource used the SPSite, SPWeb, ListID and ListItemID from our query contained in the DataTable to access the list and update the list item for any SPWeb within the site collection.

Creating a Custom PeopleEditor Bound Field

Since one of the columns we wanted to display for the user to edit was an AssignedTo field, we wanted to enable the field to be bound to a PeopleEditor part. Well, it turns out that there is no out of the box implementation of a BoundField that will let you bind to an SPUser (at least we couldn’t find one…please comment on this post if there is). Here’s one of those places to help the wizard and create a custom BoundField implementation. The following is the most important bit of code in creating the PeopleEditorBoundField:

For details on creating a custom BoundField, see the Notes and Links below.

Binding SPGridView to the ObjectDataSource

Now that we have the data in a DataTable by using our CustomDataSource, and our custom PeopleEditorBoundField that we bind user information to, we need to display the data. The SPGridView inherits from GridView, and so gives you all of the crunchy goodness of the GridView with the standard rendering of the SharePoint styled grid in the browser. We just configured the SPGridView and enabled the options needed, like the data source, sorting and filtering and our columns to display. Below are some of the more interesting bits of initializing the SPGridView instance in our case.

One interesting aspect of using the SPGridView with the ObjectDataSource is that you must specify the DataSourceID when using the ObjectDataSource. This is required since the underlying calls find the DataSourceControl by the parent NamingContainer and the DataSourceID properties.

//must set DataSourceID in order to use the ObjectDataSource binding
_spGridView.DataSourceID = "CustomDataSourceID";

Providing an ECB Menu Using MenuTemplate and SPMenuField

Finally, we needed the ability for the user to view or edit a list item directly by the use of ECB menu style actions. To do this, while building the columns for our grid view, we simply use the MenuTemplate and SPMenuField classes to build a menu that has links and custom actions for our list items (code modified to cleanse some specifics).

This gives us a link to the list item like the standard SharePoint List with the Title column as a link, a menu with a link to the EditForm.aspx page for the specific list item, and our custom PeopleEditor bound field.

Dealing With the Flying Monkeys

One of the special requirements was to enable a RootWeb to retrieve all of the data for all webs recursively, but to initially filter the data to the RootWeb list items. So far everything was going smoothly, a webpart, a custom ObjectDataSource and a custom BoundField, easy-peasy.

Well, you knew the flying monkeys had to show up at some point to make slow us down, right?

The most difficult issue in this process was attempting to get the SPGridView to be filtered automatically on page load. Turns out that there are properties that the SPGridView uses to determine and pass the FilterExpression to the ObjectDataSourceView.CreateFilteredDataView() instance in order to filter the data to render to the user.

Well, you might think this should be exposed as a property or method, but the SetFilterField and SetFilterValue properties are private. This time the “curtain” was lifted by .NET Reflector. Turns out, the private methods in SPGriedView set the ViewState entries for the FilterFieldName and FilterFieldValue keys respectively. Using this knowledge, getting the filtering to be applied immediately on page load was a matter of ensuring that the “filterFieldName” and “filterFieldValue” attributes were applied before the grid was rendered by the following:

Once the pieces were all in place, wiring this all together was amazingly simple. To recap, we created a custom web part wrapping the SPGridView, created a custom ObjectDataSource and used the SPSiteDataQuery to query across webs and lists, and bound the SPGridView to the ObjectDataSource. Finally we configured our SPGridView to use a standard and custom BoundFields to display, edit and persist list item changes. Here is the web parts CreateChildControls method:

protected override void CreateChildControls()
{
InitGridView(); &nbsp;//set up the grid view for filtering and sorting and events
InitBoundColumns(); &nbsp;//initialize the bound columns
InitDataSource(); &nbsp;//initialize the custom ObjectDataSource
//must set DataSourceID in order to use the ObjectDataSource binding
_spGridView.DataSourceID = "CustomDataSourceID";
Controls.Add(_spGridView);
}
//It is best to databind in the Render method since this
//is after all other sorting and filtering flags have been set
protected override void Render(System.Web.UI.HtmlTextWriter writer)
{
base.Render(writer);
_spGridView.DataBind();
}

This gave our users the familiar look and feel of the lists in SharePoint, let the users sort and filter the list, and even bound that data in the rows to enable in-place updates of list items that spanned multiple sites (webs) and lists.

Notes and Links

While determining how to solve this issue, we used the following links and resources. Thanks to all of these authors for pointing us in the right direction and easing the time to implement. Hopefully someone can use this post’s information the same way.

What if SPGridView is created on the fly? For example, a SPGridView inside a Repeater control. What are changes to be made to make it work.

In my case, I am getting SPGridView_FilterCallbackErrorHandler() when clicking on the sort column. I could not find any documentation for my scenario where I am creating SPGridView control on the fly.

Thanks much in advance.

http://www.threewill.com/pskelly/ Pete Skelly

Pruhdvi,

Thanks for the question. I have never tried creating the SPGridView instance on the fly, but my first guess would be to ensure that the timing (sequence of adding the controls in page life cycle) is correct. I found that all of the setup for the ObjectDataSource, BoundColumns and SPGridView had to be done in CreateChildControls override of my wrapper webpart.

Hope that helps.

Blake Skinner

Hey Pete,

Thank you for this great article!! On the filter part.
if (Page.IsPostBack && !string.IsNullOrEmpty(_filterExpression))
{
_spGridView.Attributes[“filterFieldName”] = _filterExpression.Split(‘=’)[0].Trim();
_spGridView.Attributes[“filterFieldValue”] = _filterExpression.Split(‘=’)[1].Trim();
}

Did you add this in the Page_Load event? I was only able to get it to work when I changed Page.IsPostBack to !Page.IsPostBack. Is this correct?

http://www.threewill.com/pskelly/ Pete Skelly

Blake,

I had the call in a private method which was called from CreateChildControls(). Essentially, in CreateChildControls, I had a method which initialized the GridView and once the basic properties for the GridView were set up, I called the helper method which set the default filtering values. First, set the following values to enable Filtering:

Then here is the SetDefaultFilterValues() method.
///
/// Determines if the page is a post back and if not, assigns attributes values used in the
/// SPGridView.DoFilterPostBackEventProcessing(); and other load methods to trick the gridview
/// into a state that has a filter pre-applied for the current web path (from helper method). ///
///

Do you know how to set multiple values for FilterFieldName, and FilterFieldValue? I am able to filter the grid with multiple columns using viewstate and building the filterexpression in the OnFiltering method. I modified the OnGridViewDataBound method to ensure the filter icon shows for each filtered column. The problem is the clear filter option is only available for the last column that was filtered. I see that the spGridView JavaScript uses the FilterFieldName to determine if the clear filter option should be available. Would it be best to modify the JavaScript?

gentledepp

Could you please provide an example solution? Seems I am too noobish to understand everything without it… :/

http://www.threewill.com dannyryan

Pete – had a question from someone about this post:

“Got a problem with SPGridView. I use filtering and paging and after clicking on the pager control the SPGridView looses its Values for FilterFieldName and FilterFieldValue. Both have no setter – can’t set them. Try now to use SPGridView.Attributes …”

Any ideas?

http://www.threewill.com pskelly

@dannyryan Danny, have person with the issue re-read the “Dealing With the Flying Monkeys” section of the post. Sounds like the values are not being set, but they can be using the code provided.

vnpv

Is there any option to Export this View on SPGridView to Excel. I tried many options and nothing worked as expected.

http://www.threewill.com pskelly

@vnpv There is nothing from SPGridView specifically that would enable export to Excel. However, you could “roll your own” export to Excel using OpenXML SDK. See Eric White’s blog and http://www.threewill.com/2010/08/document-generation-in-sharepoint-part-1/ for some primer information.

About ThreeWill

ThreeWill is a consulting firm based in Alpharetta, Georgia focused on building collaborative solutions on SharePoint 2013, Office 365 and Microsoft Azure.