One of the most exciting new web controls that come with SharePoint 2007 is the SPGridView control. With it, you can present data in a grid view that closely resembles the view that SharePoint uses to display data from lists. You can sort and group rows, add drop-down menus to cells and filter rows using column header drop-down menus.
There is, however, a slight drawback with it; there is almost no official documentation on how to use it. But there is lots of information post on it around the 'net. If you're new to the SPGridView, I suggest you head over to Powlo's blog that has a great introduction of how to use the basic features of the control.
I recently chose to use this control in a scenario where filtering using the UI was required. I had heard about the SPGridView and knew it had the filtering capabilities I needed, but I had a very hard time figuring out how to use it. But after much (unsuccessful) Googling and a lot of trial-and-error on my own, I managed to get it right and thought it would be nice to share this information with others facing the same challenges I did.
The first hurdle I ran into was that filtering does not work if you bind the SPGridView using the DataSource property; you have to use the DataSourceID property to perform the binding. For this example, I wrote a small class that creates a DataTable object with some hardcoded data:
public static class ExampleObjectDataSource
{
public static DataTable GetDataTable()
{
DataTable tblData = new DataTable();
tblData.Columns.Add("Title");
tblData.Columns.Add("Author");
tblData.Columns.Add("Rating");
tblData.Rows.Add("Obsession", "Robards, Karen", 2);
tblData.Rows.Add("Vanished", "Robards, Karen", 3);
tblData.Rows.Add("Magician: Apprentice", "Feist, Raymong E.", 4);
tblData.Rows.Add("Magician: Master", "Feist, Raymong E.", 5);
tblData.Rows.Add("Silverthorn", "Feist, Raymong E.", 4);
tblData.Rows.Add("Lord Foul's Bane", "Donaldson, Stephen R.", 3);
tblData.Rows.Add("The Illearth War", "Donaldson, Stephen R.", 4);
tblData.AcceptChanges();
return tblData;
}
}
Then simply instantiate an ObjectDataSource object that points to the example data class in the CreateChildControl() method of the web part:
ObjectDataSource odsDataSource = new ObjectDataSource(
"Strand.Demos.FilteredGridExample.ExampleObjectDataSource, Strand.Demos.FilteredGridExample, Culture=neutral, Version=1.0.0.0, PublicKeyToken=9f4da00116c38ec5",
"GetDataTable");
odsDataSource.ID = "ExampleSource";
Controls.Add(odsDataSource);
Of course, we're going to need some columns to display the data. For good measure, I want to implement sorting as well, so let's set the SortExpression properties of the fields too:
sgvGrid = new SPGridView();
sgvGrid.ID = "ExampleGrid";
sgvGrid.AutoGenerateColumns = false;
BoundField col = new BoundField();
col.DataField = "Title";
col.SortExpression = "Title";
col.HeaderText = "Title";
sgvGrid.Columns.Add(col);
col = new BoundField();
col.DataField = "Author";
col.SortExpression = "Author";
col.HeaderText = "Author";
sgvGrid.Columns.Add(col);
col = new BoundField();
col.DataField = "Rating";
col.SortExpression = "Rating";
col.HeaderText = "Rating";
sgvGrid.Columns.Add(col);
sgvGrid.AllowSorting = true;
Next, let's enable the filtering!
sgvGrid.AllowFiltering = true;
Now, we need to tell the SPGridView which columns we want to enable filtering on, and which field each column should use in the filter expression by setting the FilterDataFields property. This is a comma-separated string containing which field each respective column in the grid should filter by. We want to be able to filter by Author or Rating, but not by Title, so leave the first field empty:
sgvGrid.FilterDataFields = ",Author,Rating";
We also need to tell the grid which property to set on the data source control when filtering. In our case, it is the FilterExpression property of the ObjectDataSource:
sgvGrid.FilteredDataSourcePropertyName = "FilterExpression";
The SPGridView control will use string.Format() to generate the string it will set the FilterExpression to, so we must specify the format string it will use. The first parameter is the value to filter on, and the second parameter is the name of the field being filtered.
sgvGrid.FilteredDataSourcePropertyFormat = "{1} LIKE '{0}'";
And lest we forget, let's set the DataSourceID property of the grid and add the grid to the Controls collection:
sgvGrid.DataSourceID = "ExampleSource";
Controls.Add(sgvGrid);
Do the databinding in the Render method:
protected override void Render(HtmlTextWriter writer)
{
sgvGrid.DataBind();
base.Render(writer);
}
Now you'll have filtering implemented and you can compile, deploy and filter the grid to your heart's content!
(Please excuse the Swedish UI!)
If you've come this far and played around with the filtering and sorting for awhile, you've probably noticed that there's something that's not working quite as you'd expect it; If you filter by one column and then sort by another, your filter will disappear! This is of course highly unsatisfactory and needs to be dealt with!
I think my solution to this is a bit on the ugly side, so if anyone out there happens to know the "right" way of doing this, I'd appreciate it if you let me know!
What I did was to first save the FilterExpression in the ViewState in the OnPreRender method:
protected override void OnPreRender(EventArgs e)
{
ViewState["FilterExpression"] = odsDataSource.FilterExpression;
base.OnPreRender(e);
}
Then I inserted code to set the FilterExpression to the previously saved expression in the ViewState. Since I never want the options in the filter menu drop-down to be filtered, I check the query string and then only set the FilterExpression if the current page request is not callback from the drop down menu. This code should be inserted just before adding the odsDataSource object to the Controls collection:
HttpRequest req = HttpContext.Current.Request;
if (req.Form["__CALLBACKID"] == null ||
req.Form["__CALLBACKPARAM"] == null ||
!req.Form["__CALLBACKID"].EndsWith("ExampleGrid"))
{
if (ViewState["FilterExpression"] != null)
odsDataSource.FilterExpression = (string)ViewState["FilterExpression"];
}
And that's it!