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);
        }