SPGridView WebPart with Multiple Filter and Sort Columns

The SPGridView is one of the most useful SharePoint controls. For viewing data it's tough to beat. It provides a very nice interface for sorting, filtering, paging and grouping. It gives custom data sources the look and feel of SharePoint list views, providing a consistent look and feel across your site

Unfortunately, it can be quite a handful and good examples of using the features in combination are hard to come by. This example doesn't combine all of the features, notably grouping has not been included. Wouldn't want to spoil all of your fun.

This webpart contains an ObjectDataSource that feeds an SPGridView. The sort and filter properties are maintained in viewstate. The ObjectDataSource does most of the work with a little bit of help with sorting. For the sorting to work, the SortParameterName needs to be set to the value of the persisted sort property. This will then be passed into the SelectMethod of the ObjectDataSource where we will need to implement a custom sort. This solution has been built with WSPBuilder and Visual Studio 2008.

A couple of screen shots of the finished webpart, looks familiar ?

 

 Download the code here

Here are the filter and sort properties being saved to and retrieved from viewstate. The format of the strings is important. It seems that filters work best with this format ((col1 = 'val1') AND (col2 = 'val2')). The sort works best in the format of "col1 DESC, col2 ASC".

        string FilterExpression
        {
            get
            {
                if (ViewState["FilterExpression"] == null)
                { ViewState["FilterExpression"] = ""; }

                return (string)ViewState["FilterExpression"];
            }
            set
            {
                string thisFilterExpression = "(" + value.ToString() + ")";
                List<string> fullFilterExpression = new List<string>();
             
                if (ViewState["FilterExpression"] != null)
                {
                    string[] fullFilterExp = ViewState["FilterExpression"].ToString().Split(_ssep, StringSplitOptions.RemoveEmptyEntries);
                    fullFilterExpression.AddRange(fullFilterExp);
                 
                    //add the filter if not present
                    int index = fullFilterExpression.FindIndex(s => s.Contains(thisFilterExpression));
                    if (index == -1)
                    { fullFilterExpression.Add(thisFilterExpression); }
                }
                else
                {
                    fullFilterExpression.Add(thisFilterExpression);
                }
                //loop through the list<T> and serialize to string
                string filterExp = string.Empty;
                fullFilterExpression.ForEach(s => filterExp += s + " AND ");
                filterExp = filterExp.Remove(filterExp.LastIndexOf(" AND "));
                if (!filterExp.EndsWith("))") && filterExp.Contains("AND"))
                { filterExp = "(" + filterExp + ")"; }
                ViewState["FilterExpression"] = filterExp;
            }
        }


        string SortExpression
        {
            get
            {
                if (ViewState["SortExpression"] == null)
                { ViewState["SortExpression"] = ""; }

                return (string)ViewState["SortExpression"];
            }
            set
            {
                string[] thisSE = value.ToString().Split(' ');
                string thisSortExpression = thisSE[0];
                List<string> fullSortExpression = new List<string>();

                if (ViewState["SortExpression"] != null)
                {
                    string[] fullSortExp = ViewState["SortExpression"].ToString().Split(_sep);
                    fullSortExpression.AddRange(fullSortExp);

                    //does the sort expression already exist?
                    int index = fullSortExpression.FindIndex(s => s.Contains(thisSortExpression));
                    if (index >= 0)
                    {
                        string s = string.Empty;
                        if (value.ToString().Contains("DESC"))
                        { s = value.ToString(); }
                        else
                        {
                            s = fullSortExpression[index];
                            if (s.Contains("ASC"))
                            { s = s.Replace("ASC", "DESC"); }
                            else
                            { s = s.Replace("DESC", "ASC"); }
                        }
                        //reset the sort direction
                        fullSortExpression[index] = s;
                    }
                    else
                    {
                        if (value.ToString().Contains("DESC"))
                        { fullSortExpression.Add(value.ToString()); }
                        else
                        { fullSortExpression.Add(thisSortExpression + " ASC"); }
                    }
                }
                else
                {
                    if (value.ToString().Contains("DESC"))
                    { fullSortExpression.Add(value.ToString()); }
                    else
                    { fullSortExpression.Add(thisSortExpression + " ASC"); }
                }
                //loop through the list<T> and serialize to string
                string sortExp = string.Empty;
                fullSortExpression.ForEach(s => sortExp += s);
                sortExp = sortExp.Replace(" ASC", " ASC,");
                sortExp = sortExp.Replace(" DESC", " DESC,");
                ViewState["SortExpression"] = sortExp.Remove(sortExp.LastIndexOf(','));
            }
        }

This is the CreateChildControls where the ObjectDataSource and SPGridView controls are instantiated and properties are set.

        protected override void CreateChildControls()
        {
            base.CreateChildControls();

            try
            {
                //build the datasource
                gridDS = new ObjectDataSource();
                gridDS.ID = "gridDS";
                gridDS.SelectMethod = "SelectData";
                gridDS.TypeName = this.GetType().AssemblyQualifiedName;
                gridDS.EnableViewState = false;
              
                //pass the SortExpression to the select method
                gridDS.SortParameterName = "SortExpression";

                //this resets the dropdown options for other columns after a filter is selected
                gridDS.FilterExpression = FilterExpression;
                //add the data source
                Controls.Add(gridDS);

                //build the gridview
                gridView = new SPGridView();
                gridView.AutoGenerateColumns = false;
                gridView.EnableViewState = false;
                gridView.ID = "gridView";
              
                //sorting
                gridView.AllowSorting = true;
              
                //filtering
                gridView.AllowFiltering = true;
                gridView.FilterDataFields = ",FirstName,LastName,Department,Country,Salary";
                gridView.FilteredDataSourcePropertyName = "FilterExpression";
                gridView.FilteredDataSourcePropertyFormat = "{1} = '{0}'";
              
                //set header icons for sorting an filtering
                gridView.RowDataBound += new GridViewRowEventHandler(gridView_RowDataBound);
              
                //sorting
                gridView.Sorting += new GridViewSortEventHandler(gridView_Sorting);
              
                //paging
                gridView.AllowPaging = true;
                gridView.PageSize = 5;
              
                //create gridView columns
                BuildColumns();

                //set the id and add the control
                gridView.DataSourceID = gridDS.ID;
                Controls.Add(gridView);
     
                SPGridViewPager pager = new SPGridViewPager();
                pager.GridViewId = gridView.ID;
                Controls.Add(pager);

            }
            catch (Exception ex)
            {
                //To Do Log it
            }
        }

Here is the SelectMethod of the ObjectDataSource. Note the signature, a DataTable is being returned, necessary for filtering. The SortParameterName, SortExpression, is passed to enable custom sorting. The custom sort is necessary due to a bug? with the sort ascending / descending menu options.

        public DataTable SelectData(string SortExpression)
        {
            DataTable dataSource = new DataTable();

            dataSource.Columns.Add("ID");
            dataSource.Columns.Add("LastName");
            dataSource.Columns.Add("FirstName");
            dataSource.Columns.Add("Department");
            dataSource.Columns.Add("Country");
            dataSource.Columns.Add("Salary", typeof(double));

            dataSource.Rows.Add(1, "Smith", "Laura", "Sales", "IreLand", 150000);
            dataSource.Rows.Add(2, "Jones", "Ed", "Marketing", "IreLand", 75000);
            dataSource.Rows.Add(3, "Jefferson", "Bill", "Security", "Britian", 87000);
            dataSource.Rows.Add(4, "Washington", "George", "PMO", "France", 110000);
            dataSource.Rows.Add(5, "Bush", "Laura", "Accounting", "USA", 44000);
            dataSource.Rows.Add(6, "Clinton", "Hillory", "Human Resources", "USA", 121000);
            dataSource.Rows.Add(7, "Ford", "Jack", "IT", "France", 150000);
            dataSource.Rows.Add(8, "Hailey", "Tom", "Networking", "Canada", 72000);
            dataSource.Rows.Add(9, "Raul", "Mike", "Accounting", "Canada", 97000);
            dataSource.Rows.Add(10, "Shyu", "Danny", "Sales", "Britian", 89000);
            dataSource.Rows.Add(11, "Hanny", "Susan", "Sales", "USA", 275000);

            //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 = newTable;
            }

            return dataSource;
        }

The gridView_RowDataBound event is used to add the sort and filter images to the header.

        private void gridView_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (sender == null || e.Row.RowType != DataControlRowType.Header)
            { return; }

            SPGridView grid = sender as SPGridView;

            // Show icon on filtered and sorted columns
            for (int i = 0; i < grid.Columns.Count; i++)
            {
                DataControlField field = grid.Columns[i];

                if (FilterExpression.Contains(field.SortExpression) &&
                    !string.IsNullOrEmpty(FilterExpression))
                {
                    PlaceHolder panel = HeaderImages(field, "/_layouts/images/filter.gif");
                    e.Row.Cells[i].Controls[0].Controls.Add(panel);
                }
                else if(SortExpression.Contains(field.SortExpression))
                {
                    string url = sortImage(field);
                    PlaceHolder panel = HeaderImages(field, url);
                    e.Row.Cells[i].Controls[0].Controls.Add(panel);
                }        
            }
        }


        private string sortImage(DataControlField field)
        {
            string url = string.Empty;
            string[] fullSortExp = SortExpression.Split(_sep);
            List<string> fullSortExpression = new List<string>();
            fullSortExpression.AddRange(fullSortExp);

            //does the sort expression already exist?
            int index = fullSortExpression.FindIndex(s => s.Contains(field.SortExpression));
            if (index >= 0)
            {
                string s = fullSortExpression[index];
                if (s.Contains("ASC"))
                { url = "_layouts/images/sortup.gif"; }
                else
                { url = "_layouts/images/sortdown.gif"; }
            }
            return url;
        }


        private PlaceHolder HeaderImages(DataControlField field, string imageUrl)
        {
            Image filterIcon = new Image();
            filterIcon.ImageUrl = imageUrl;
            filterIcon.Style[HtmlTextWriterStyle.MarginLeft] = "2px";

            Literal headerText = new Literal();
            headerText.Text = field.HeaderText;

            PlaceHolder panel = new PlaceHolder();
            panel.Controls.Add(headerText);

            //add the sort icon if needed
            if (FilterExpression.Contains(field.SortExpression) &&
                SortExpression.Contains(field.SortExpression))
            {
                string url = sortImage(field);
                Image sortIcon = new Image();
                sortIcon.ImageUrl = url;
                sortIcon.Style[HtmlTextWriterStyle.MarginLeft] = "1px";
                panel.Controls.Add(sortIcon);
                //change the left margin to 1
                filterIcon.Style[HtmlTextWriterStyle.MarginLeft] = "1px";
            }

            panel.Controls.Add(filterIcon);
            return panel;
        }

This is the sorting method of the SPGridView. The event handler is registered in CreateChildren and is where the sortexpression property is built. The filter needs to be reset here or it will be lost.

        void gridView_Sorting(object sender, GridViewSortEventArgs e)
        {
            string sDir = e.SortDirection.ToString();
            sDir = sDir == "Descending" ? " DESC" : "";

            SortExpression = e.SortExpression + sDir;
            e.SortExpression = SortExpression;

            //if the filter is not reset it will be cleared
            if (!string.IsNullOrEmpty(FilterExpression))
            { gridDS.FilterExpression = FilterExpression; }

        }
      

        void buildFilterView(string filterExp)
        {
            string lastExp = filterExp;
            if (lastExp.Contains("AND"))
            {
                if (lastExp.Length < lastExp.LastIndexOf("AND") + 4)
                { lastExp = lastExp.Substring(lastExp.LastIndexOf("AND") + 4); }
                else
                { lastExp = string.Empty; }
            }
          
            //update the filter
            if (!string.IsNullOrEmpty(lastExp))
            { FilterExpression = lastExp; }

            //reset object dataset filter
            if (!string.IsNullOrEmpty(FilterExpression))
            { gridDS.FilterExpression = FilterExpression; }
        }