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.