SPGridView Part 2 - Custom Filters

The SharePoint 2007 SPGridView control allows easy filtering by setting the AllowFiltering property to true, but it generates the list of possible filter options by selecting all of the distinct values for the column when the column header is clicked. In a DB table I'm using with an SPGridView I store integer values of 0 to represent unset, 1 for low, 2 for medium and 3 for high and so the available filter options are 0, 1, 2 or 3.

To allow filtering by the associated text value, I check for a callback in my WebPart's override of CreateChildControls and if the column header in question has been clicked, bind the SPGridView control to a temporary data table which has a column with the same name as the one clicked containing all of the required values:

if (Page.IsCallback && !string.IsNullOrEmpty(Page.Request.Form["__CALLBACKPARAM"]))
                {
                    string[] param = Page.Request.Form["__CALLBACKPARAM"].Split(';');
                    if (param[1] == myColumn)
                    {
                        DataTable dt = new DataTable();
                        dt.Columns.Add(param[1]);
                        dt.Rows.Add("Unset");
                        dt.Rows.Add("Low");
                        dt.Rows.Add("Medium");
                        dt.Rows.Add("High");

                        m_SpGridView.DataSourceID = null;
                        m_SpGridView.DataSource = dt;
                        m_SpGridView.DataBind();
                    }
                } 

Also in CreateChildControls, if it isn't a callback to get the available filter options, I check for an actual filter call or a clear filter call and flip the text value back to the associated id if required (storing the filter in the ViewState) using a call to the following:

protected virtual void CheckFilter()
        {
            // If there is a call back with an event argument and event target and the target is our gridview...
            if (Context.Request.Form["__EVENTARGUMENT"] != null && Context.Request.Form["__EVENTTARGET"] != null &&
                Context.Request.Form["__EVENTTARGET"].EndsWith(m_SpGridView.ID))
            {
                string search = "__SPGridView__;__Filter__;";
                if (Context.Request.Form["__EVENTARGUMENT"].Equals("__SPGridView__;__Filter__;__ClearFilter__"))
                {
                    ViewState.Remove("FilterExpression");
                }
                else if (Context.Request.Form["__EVENTARGUMENT"].StartsWith(search))
                {
                    string[] newFilter = Context.Request.Form["__EVENTARGUMENT"].Replace(search, "").Split(';');

                    // If this is a custom filter field, switch the selected value for the actual value
                    if (newFilter[0] == myColumn)
                    {
                        if (newFilter[1] == "Unset")
                            newFilter[1] = "0";
                        else if (newFilter[1] == "Low")
                            newFilter[1] = "1";
                        else if (newFilter[1] == "Medium")
                            newFilter[1] = "2";
                        else if (newFilter[1] == "High")
                            newFilter[1] = "3";
                    }

                    // Set the filter in the viewstate
                    ViewState["FilterExpression"] = string.Format("{0}='{1}'", newFilter[0], newFilter[1]);
                }
            }
        }

Finally, override OnPreRender and set the FilterExpression of the SqlDataSource control associated with the SPGridView to the query stored in the ViewState:

protected override void OnPreRender(EventArgs e)
        {
            m_SqlDataSource.FilterExpression = string.Empty;
            if (ViewState["FilterExpression"] != null)
                m_SqlDataSource.FilterExpression = ViewState["FilterExpression"].ToString();

            base.OnPreRender(e);
        }

SPGridView Part 1 - Multiple SPGridViews

There appears to be a bug clearing filters when using multiple instances of the SharePoint 2007 GridView (SPGridView) control on the same page. In my case I had two very similar custom WebParts on the same page using SPGridView controls to display data pulled from a database.

If filtering is enabled on the controls, when the user clears the filter on the second SPGridView on the screen, the filter on the first SPGridView is cleared instead.

After some searching, I found a nice solution to this problem here.

I copied the suggested solution into a class that inherits from SPGridView and used that in my WebParts to resolve the problem:

public class MySPGridView : SPGridView
    {
        // Override the OnPreRender to databind and then fix each headerrow control
        protected override void OnPreRender(EventArgs e)
        {
            DataBind();
            if (this.HeaderRow != null)
            {
                foreach (WebControl control in this.HeaderRow.Controls)
                {
                    UpdateTemplateClientID(control);
                }
            }
            base.OnPreRender(e);
        }

        // Fix the ClientOnClickPreMenuOpen property of a menu control
        private void UpdateTemplateClientID(Control control)
        {
            if (control is Microsoft.SharePoint.WebControls.Menu)
            {
                Microsoft.SharePoint.WebControls.Menu menuControl = control as Microsoft.SharePoint.WebControls.Menu;
                string jsFunctionCall = menuControl.ClientOnClickPreMenuOpen;
                menuControl.ClientOnClickPreMenuOpen = jsFunctionCall.Replace("%TEMPLATECLIENTID%", this.ClientID + "_SPGridViewFilterMenuTemplate");
            }
            else if (control.HasControls())
            {
                foreach (WebControl c in control.Controls)
                    UpdateTemplateClientID(c);
            }
        }
    }