Thursday, February 16, 2012

Reusable SPGridView with Multiple Filter and Sort Columns

The venerable SPGridView still has its use in SharePoint 2010 when your data is not stored in a list or accessible as an external content type through BCS. A typical example is when using a KeywordQuery to build a search-driven web-part feeding on results as DataTable as show in How to: Use the SharePoint 2010 Enterprise Search KeywordQuery Class by Corey Roth. Another example is cross-site queries using SPSiteDataQuery.

The SPGridView can use a DataTable as its data source, but several things from sorting arrows to filtering don't work as expected when not using an ObjectDataSource. As Shawn Kirby shows in SPGridView WebPart with Multiple Filter and Sort Columns it is quite easy to implement support for such features.

In this post, I show how to generalize Shawn's web-part code into a SPGridView derived class and a data source class wrapping a DataTable, isolating this functionality from the web-part code itself, for both better reusability and separation of concerns.

First the simple abstract data source class that you must implement to populate your data set:

namespace Puzzlepart.SharePoint.Core
{
    public abstract class SPGridViewDataSource
    {
        public abstract DataTable SelectData(string sortExpression);
 
        protected void Sort(DataTable dataSource, string sortExpression)
        {
            //clean up the sort expression if needed - the sort descending 
            //menu item causes the double in some cases 
            if (sortExpression.ToLowerInvariant().EndsWith("desc desc"))
                sortExpression = sortExpression.Substring(0, sortExpression.Length - 5);
 
            //need to handle the actual sorting of the data
            if (!string.IsNullOrEmpty(sortExpression))
            {
                DataView view = new DataView(dataSource);
                view.Sort = sortExpression;
                DataTable newTable = view.ToTable();
                dataSource.Clear();
                dataSource.Merge(newTable);
            }
        }
    }
}

The SPGridViewDataSource class provides the SelectData method that you must override, and a completed Sort method that allows the SPGridView to sort your DataTable. Note that this class must be stateless as required by any class used as an ObjectDataSource. Its logic cannot be combined with the grid view class, as it will get instantiated new every time the ObjectDataSource calls SelectData.

Then the derived grid view with support for filtering and sorting, including the arrows and filter images:

namespace Puzzlepart.SharePoint.Core
{
    public class SPGridViewMultiSortFilter : SPGridView
    {
        public SPGridViewMultiSortFilter()
        {
            this.FilteredDataSourcePropertyName = "FilterExpression";
            this.FilteredDataSourcePropertyFormat = "{1} = '{0}'";            
        }
 
        private ObjectDataSource _gridDS;
        private char[] _sortingSeparator = { ',' };
        private string[] _filterSeparator = { "AND" };
 
        public ObjectDataSource GridDataSource
        {
            get { return _gridDS; }
            private set
            {
                _gridDS = value;
                this.DataSourceID = _gridDS.ID;
            }
        }
 
        public bool AllowMultiSorting { get; set; }
        public bool AllowMultiFiltering { get; set; }
 
        string FilterExpression
        {
. . .
        }
 
        string SortExpression
        {
. . .
        }
 
        protected override void CreateChildControls()
        {
            base.CreateChildControls();
 
            this.Sorting += new GridViewSortEventHandler(GridView_Sorting);
            this.RowDataBound += new GridViewRowEventHandler(GridView_RowDataBound);
        }
 
        protected void GridView_Sorting(object sender, GridViewSortEventArgs e)
        {
            EnsureChildControls();
            string direction = e.SortDirection.ToString();
            direction = (direction == "Descending") ? " DESC" : "";
 
            SortExpression = e.SortExpression + direction;
            e.SortExpression = SortExpression;
 
            //keep the object dataset filter
            if (!string.IsNullOrEmpty(FilterExpression))
            {
                _gridDS.FilterExpression = FilterExpression;
            }
        }
 
        protected void GridView_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            EnsureChildControls();
            if (sender == null || e.Row.RowType != DataControlRowType.Header)
            {
                return;
            }
 
            BuildFilterView(_gridDS.FilterExpression);
            SPGridView grid = sender as SPGridView;
 
            // Show icon on filtered and sorted columns 
            for (int i = 0; i < grid.Columns.Count; i++)
            {
. . .
            }
        }
 
        void BuildFilterView(string filterExp)
        {
. . .
 
            //update the filter
            if (!string.IsNullOrEmpty(lastExp))
            {
                FilterExpression = lastExp;
            }
 
            //reset object dataset filter
            if (!string.IsNullOrEmpty(FilterExpression))
            {
                _gridDS.FilterExpression = FilterExpression;
            }
        }
 
        public ObjectDataSource SetObjectDataSource(string dataSourceId, SPGridViewDataSource dataSource)
        {
            ObjectDataSource gridDS = new ObjectDataSource();
            gridDS.ID = dataSourceId;
            gridDS.SelectMethod = "SelectData";
            gridDS.TypeName = dataSource.GetType().AssemblyQualifiedName;
            gridDS.EnableViewState = false;
            gridDS.SortParameterName = "SortExpression";
            gridDS.FilterExpression = FilterExpression;
            this.GridDataSource = gridDS;
 
            return gridDS;
        }
    }
}

Only parts of the SPGridViewMultiSortFilter code is shown here, see download link below for the complete code. Note that I have added two properties that controls whether multi-column sorting and multi-column filtering are allowed or not.

This is an excerpt from a web-part that shows search results using the grid:

namespace Puzzlepart.SharePoint.Presentation
{
    [ToolboxItemAttribute(false)]
    public class JobPostingRollupWebPart : WebPart
    {
        protected SPGridViewMultiSortFilter GridView = null;
 
        protected override void CreateChildControls()
        {
            try
            {
                CreateJobPostingGrid();
            }
            catch (Exception ex)
            {
                Label error = new Label();
                error.Text = String.Format("An unexpected error occurred: {0}", ex.Message);
                error.ToolTip = ex.StackTrace;
                Controls.Add(error);
            }
        }
 
        private void CreateJobPostingGrid()
        {
            //add to control tree first is important for view state handling  
            Panel panel = new Panel();
            Controls.Add(panel);

            GridView = new SPGridViewMultiSortFilter();
 
  . . .
 
            GridView.AllowSorting = true;
            GridView.AllowMultiSorting = false;
            GridView.AllowFiltering = true;
            GridView.FilterDataFields = "Title,Author,Write,";
 
  . . .
 
            panel.Controls.Add(GridView) 

            //set PagerTemplate after adding grid to control tree

 
            PopulateGridDataSource();
 
            //must bind in OnPreRender
            //GridView.DataBind();  
        }
 
        protected override void OnPreRender(EventArgs e)
        {
            GridView.DataBind();
        }
 
        private void PopulateGridDataSource()
        {
            var dataSource = new ApprovedJobPostingDataSource();
            var gridDS = GridView.SetObjectDataSource("gridDS", dataSource);
            //add the data source
            Controls.Add(gridDS);
        }
    }
}

Note how the data source is created and assigned to the grid view, but also added to the control set of the web-part itself. This is required for the grid's DataSourceId binding to find the ObjectDataSource at run-time. Also note that data binding cannot be triggered from CreateChildControls as it is too early in the control's life cycle. The DataBind method must be called from OnPreRender to allow for view state and child controls to load before the sorting and filtering postback events

Finally, this is an example of how to implement a search-driven SPGridViewDataSource:

namespace Puzzlepart.SharePoint.Presentation
{
    public class ApprovedJobPostingDataSource : SPGridViewDataSource
    {
        private string _cacheKey = "Puzzlepart_Godkjente_Jobbannonser";
 
        public override DataTable SelectData(string sortExpression)
        {
            DataTable dataTable = (DataTable)HttpRuntime.Cache[_cacheKey];
            if (dataTable == null)
            {
                dataTable = GetJobPostingData();
                HttpRuntime.Cache.Insert(_cacheKey, dataTable, null
DateTime.Now.AddMinutes(1), Cache.NoSlidingExpiration);
            }
 
            this.Sort(dataTable, sortExpression);
 
            return dataTable;
        }
 
        private DataTable GetJobPostingData()
        {
            DataTable results = new DataTable();
            string jobPostingManager = "Puzzlepart Jobbannonse arbeidsleder";
            string jobPostingAssistant = "Puzzlepart Jobbannonse assistent";
            string approvedStatus = "0";
 
            SPSite site = SPContext.Current.Site;
            KeywordQuery query = new KeywordQuery(site);
            query.QueryText = String.Format(
"ContentType:\"{0}\" ContentType:\"{1}\" ModerationStatus:\"{2}\""
jobPostingManager, jobPostingAssistant, approvedStatus);
            query.ResultsProvider = SearchProvider.Default;
            query.ResultTypes = ResultType.RelevantResults;
 
            ResultTableCollection resultTables = query.Execute();
            if (resultTables.Count > 0)
            {
                ResultTable searchResults = resultTables[ResultType.RelevantResults];
                results.Load(searchResults, LoadOption.OverwriteChanges);
            }
 
            return results;
        }
    }
}

That was not too hard, was it? Note that SearchProvider Default work for both FAST (FS4SP) and a standard SharePoint 2010 Search Service Application (SSA).

All the code can be downloaded from here.

10 comments:

GatorCompSciAdmin said...

Hello, Code looks interesting and very useful. However, the download link doesn't seem to be working.

Thank you.
Matt, MengelIT.com

Kjell-Sverre Jerijærvi said...

The download link works fine for others.

Roopa said...

Hi

I am trying to implement multiple column filters in SPGridview.

Encountered the exception below when i click on the downarrow (to open context menu)

exception on - CreateGrid method

The DataSourceID of 'GridView_Result' must be the ID of a control of type IDataSource. A control with ID 'gridDS' could not be found.

can you please help me out with this?

Kjell-Sverre Jerijærvi said...

You need to ensure that the data source control is recreated on postbacks, before the binding occurs.

Roopa said...
This comment has been removed by the author.
Roopa said...

Hi,

Thank you very much for the quick help. It is working fine now.

multiple filter: In case i select multiple columns to filter, only one of the columns is having 'Clear filter' enabled in the context menu and on click the grid removes filter from all the columns.

Also paging is not getting displayed for the grid even when there is more than one page.

Roopa said...

Multiple sort and filter is working extremely good..but i want to change the date format in filter (context menu) which is not happening.

Kjell-Sverre Jerijærvi said...

I did some research on that a while back, and found no simple solution to modify how the filter dropdown data is retrieved and formatted.

Anonymous said...

About the error "The DataSourceID of 'GridView_Result' must be the ID of a control of type IDataSource. A control with ID 'gridDS' could not be found."
I had the same error. I had 3 Control-objects with 3 SPGridView.
A Control-object by default has no new ID-structure. You should be able to fix it by also implementing INamingContainer, but the only way to solve it was by overriding WebPart instead of Control.

Really great job!!!

Anonymous said...

Can you have multiple controls on one page?

The sorting applies on the wrong grid when I add two controls on one page.