[Solved] Custom Filter for custom columns

[Christophe Vialatte]

Hi,

I have a radgrid which is programmatically created. The user can create its own query and select the columns he would like to display.
I have to object (A et B) with a many to many relationship. A List<A> is the grid datasource and for each row, I have to display all B objects related to the DataItem. That's why I have created a custom column with a dropdownlist control (MyCustomFilteringDDLColumnCS).

The user should also be able to filter the source using a "google like" filter on my custom column. It means that the filter DDL is binded with a list<B>. If the user select a value in the filter DDL , the grid must display all objects A related to the the selected B object.

When the grid is loaded, all columns are well formatted. When the user clicks on the filter DDL, possible values are displayed. The first thing is that if the user selects one of these values, the radgrid is rebind and the content of my custom column disappear. The second thing is that I don't know how to filter the datasource.

One of my problems is related to the ResultRadGrid_ColumnCreating method. I need to pass arguments to the custom column constructor but I don't know how to retrieve them.

I wasn't able to find a complete example of this implementation.

regards

Here is my code : i tried to extract the main methods  but that's not easy because I also have a custom pager ... , I'm still working on it so some lines are there for testing purpose:

aspx page :

private void buildGrid()
        {
            this.ResultRadGrid.MasterTableView.Columns.Clear();
            if (SelectedRadGrigColList.Items.Count != 0)
            {
                this.ResultRadGrid.MasterTableView.Columns.Clear();
                Hashtable ddlColumnTable = new Hashtable();
                Hashtable lblColumnTable = new Hashtable();
                foreach (RadListBoxItem rlbi in SelectedRadGrigColList.Items)
                {
                    switch (rlbi.Value)
                    {
                        case "Pk_Contribution":
                            {
                                DataTable dt = ContributionFactory.GetAllContributionDataTable();
                                MyCustomFilteringDDLColumnCS templateColumn = new MyCustomFilteringDDLColumnCS(rlbi.Value, "Pk_Contribution", "Title", dt);
                                templateColumn.HeaderText = "Contribution Relationship";
                                templateColumn.DataField = rlbi.Value;
                                templateColumn.UniqueName = rlbi.Value;
                                ResultRadGrid.MasterTableView.Columns.Add(templateColumn);
                                ddlColumnTable.Add(ResultRadGrid.MasterTableView.Columns.Count - 1, rlbi.Value);

                                break;
                            }
                        case "Fk_Enum_ContributionStatus":
                            {
                                MyCustomFilteringLabelColumnCS templateColumn = new MyCustomFilteringLabelColumnCS(rlbi.Value, rlbi.Value);
                                ResultRadGrid.MasterTableView.Columns.Add(templateColumn);
                                templateColumn.HeaderText = "Contribution Status";
                                templateColumn.DataField = rlbi.Value;
                                templateColumn.UniqueName = rlbi.Value;
                                lblColumnTable.Add(ResultRadGrid.MasterTableView.Columns.Count - 1, rlbi.Value);
                                break;
                            }
                        default:
                            {
                                MyCustomFilteringColumnCS gridColumn = new MyCustomFilteringColumnCS();
                                this.ResultRadGrid.MasterTableView.Columns.Add(gridColumn);
                                gridColumn.DataField = rlbi.Value;
                                gridColumn.HeaderText = rlbi.Text;
                                break;
                            }

                    }

                }
                Session.Add("DDLGRIDCOLUMNSINDEX", ddlColumnTable);
                Session.Add("LBLGRIDCOLUMNSINDEX", lblColumnTable);
            }
        }

protected void ResultRadGrid_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
        {
            List<CustomParam> parameterList = new List<CustomParam>();
            List<Contribution> ContribList = new List<Contribution>();

            ////TODO apply filter
            DbParameterManager dph = new DbParameterManager(DbParameterManager.ProviderType.SqlClient);
            CustomParam param1 = new CustomParam(dph.CreateDataParameter("@Pk_Contribution", DbType.Int32));
            param1.Value = 11;
            CustomParam param2 = new CustomParam(dph.CreateDataParameter("@MeetingNotes", DbType.String));
            param2.Value = "comment";
            parameterList.Add(param1);
            parameterList.Add(param2);


            MyContribClient = new ChannelFactory<IContribution>("IContribution");
            IContribution MyServices = MyContribClient.CreateChannel();

            if (MyContribClient.State == CommunicationState.Opened)
            {
                ((IContextChannel)MyServices).OperationTimeout = new TimeSpan(0, 0, 240);
                ContribList = MyServices.GetContributionUsingSQLQuery("select * from Contribution where Pk_Contribution >= @Pk_Contribution and MeetingNotes = @MeetingNotes", parameterList);

            }
            else
            {
                MyContribClient.Open();
                ((IContextChannel)MyServices).OperationTimeout = new TimeSpan(0, 0, 240);
                ContribList = MyServices.GetContributionUsingSQLQuery("select * from Contribution where Pk_Contribution >= @Pk_Contribution and MeetingNotes = @MeetingNotes", parameterList);

            }

            MyContribClient.Close();


            this.ResultRadGrid.DataSource = ContribList;

        }

protected void ResultRadGrid_ColumnCreating(object sender, GridColumnCreatingEventArgs e)
        {
            if ((e.ColumnType == typeof(MyCustomFilteringColumnCS).Name))
            {
                e.Column = new MyCustomFilteringColumnCS();
            }
            if ((e.ColumnType == typeof(MyCustomFilteringDDLColumnCS).Name))
            {
                GridDataItem dataItem = (GridDataItem)((Control)sender).NamingContainer;

                e.Column = new MyCustomFilteringDDLColumnCS();
            }
            if ((e.ColumnType == typeof(MyCustomFilteringLabelColumnCS).Name))
            {
                e.Column = new MyCustomFilteringLabelColumnCS();
            }

        }

protected void ResultRadGrid_ItemCommand(object source, GridCommandEventArgs e)
        {
            if ((e.CommandName == "Filter"))
            {
                foreach (GridColumn column in e.Item.OwnerTableView.Columns)
                {
                    column.CurrentFilterValue = string.Empty;
                    column.CurrentFilterFunction = GridKnownFunction.NoFilter;
                }
            }
        }

protected void ResultRadGrid_ItemDataBound(object sender, GridItemEventArgs e) // similar to RowDataBound event in GridView
        {

            if ((e.Item.ItemType == GridItemType.Item) || (e.Item.ItemType == GridItemType.AlternatingItem))// gets the row collection
            {
                RadGrid dg = (RadGrid)sender;
                int cnt = e.Item.ItemIndex + (dg.PageSize * dg.CurrentPageIndex);
                List<Contribution> dtBound = (List<Contribution>)dg.DataSource;
                GridDataItem dataitem = (GridDataItem)e.Item;
                try
                {

                    if (Session["DDLGRIDCOLUMNSINDEX"] != null)
                    {
                        Hashtable colIndexTable = (Hashtable)Session["DDLGRIDCOLUMNSINDEX"];
                        IEnumerator iter = colIndexTable.Keys.GetEnumerator();
                        while (iter.MoveNext())
                        {
                            int i = (int)iter.Current;
                            string colName = colIndexTable[i].ToString();
                            DropDownList ddl = (DropDownList)e.Item.Cells[i].FindControl(colName);
                            if (ddl != null)
                            {
                                int index = 0;
                                if (dtBound[cnt] != null)
                                {
                                    ddl.SelectedValue = dtBound[cnt].Pk_Contribution.ToString();
                                }
                            }
                        }
                    }
                }
                catch (Exception ee) { }
            }
        }

CustomColumns definition Class :

public class MyCustomFilteringDDLColumnCS : GridTemplateColumn
    {

        #region Item Template for DropDownList

        public class CreateItemTemplateDDL : ITemplate
        {
            DataTable dtBind;
            string strddlName;
            string strDataValueField;
            string strDataTextField;

            public MyCustomFilteringDDLColumnCS Column = null;

            public CreateItemTemplateDDL(string DDLName, string DataValueField, string DataTextField, DataTable DDLSource)
            {
                this.dtBind = DDLSource;
                this.strDataValueField = DataValueField;
                this.strDataTextField = DataTextField;
                this.strddlName = DDLName;
            }

            public CreateItemTemplateDDL()
            {

            }
            public void InstantiateIn(Control objContainer)
            {
                DropDownList ddl = new DropDownList();
                ddl.DataBinding += new EventHandler(ddl_DataBinding);
                objContainer.Controls.Add(ddl);
            }

            private void ddl_DataBinding(object sender, EventArgs e)
            {
                DropDownList ddl = (DropDownList)sender;
                GridDataItem dataItem = (GridDataItem)((Control)sender).NamingContainer;
                if(dataItem!=null){
                    try {
                        Contribution ctb = (Contribution)dataItem.DataItem;

                        if (ctb != null)
                        {
                            string test = this.Column.DataField;
                            //todo use reflection
                            if (test != null) {
                                ddl.ID = test;
                                DataTable dt = ContributionFactory.GetAllContributionDataTable();
                                ddl.DataSource = dt;
                                ddl.DataValueField = test;
                                ddl.DataTextField = "Title";
                            }
                        }
                        else {
                            ddl.ID = strddlName;
                            ddl.DataSource = dtBind;
                            ddl.DataValueField = strDataValueField;
                            ddl.DataTextField = strDataTextField;
                        }

                    }
                    catch (Exception ee) {

                    }
                }


                //ddl.DataBind();
            }
        }

        #endregion Item Template for DropDownList

        public MyCustomFilteringDDLColumnCS() {

            CreateItemTemplateDDL template = new CreateItemTemplateDDL();
            template.Column = this;
            this.ItemTemplate = template;
        }

        public MyCustomFilteringDDLColumnCS(string DDLName, string DataValueField, string DataTextField, DataTable DDLSource)
        {
            CreateItemTemplateDDL template = new CreateItemTemplateDDL(DDLName,DataValueField,DataTextField,DDLSource);
            template.Column = this;
            this.ItemTemplate = template;
        }

        public string DataField
        {
            get
            {
                object res = this.ViewState["_df"];
                if (res != null)
                {
                    return (string)res;
                }

                return String.Empty;
            }
            set
            {
                this.ViewState["_df"] = value;
            }
        }

        protected override string GetFilterDataField()
        {
            return this.DataField;
        }

        //RadGrid will call this method when it initializes the controls inside the filtering item cells
        protected override void SetupFilterControls(TableCell cell)
        {
            base.SetupFilterControls(cell);
            cell.Controls.RemoveAt(0);
            RadComboBox combo = new RadComboBox();
            combo.ID = ("RadComboBox1" + this.UniqueName);
            combo.ShowToggleImage = false;
            combo.Skin = "Office2007";
            combo.EnableLoadOnDemand = true;
            combo.AutoPostBack = true;
            combo.MarkFirstMatch = true;
            combo.Height = Unit.Pixel(100);
            combo.ItemsRequested += this.list_ItemsRequested;
            combo.SelectedIndexChanged += this.list_SelectedIndexChanged;
            cell.Controls.AddAt(0, combo);
            cell.Controls.RemoveAt(1);
        }

        //RadGrid will cal this method when the value should be set to the filtering input control(s)
        protected override void SetCurrentFilterValueToControl(TableCell cell)
        {
            base.SetCurrentFilterValueToControl(cell);
            RadComboBox combo = (RadComboBox)cell.Controls[0];
            if ((this.CurrentFilterValue != string.Empty))
            {
                combo.Text = this.CurrentFilterValue;
            }
        }

        //RadGrid will cal this method when the filtering value should be extracted from the filtering input control(s)
        protected override string GetCurrentFilterValueFromControl(TableCell cell)
        {
            RadComboBox combo = (RadComboBox)cell.Controls[0];
            return combo.Text;
        }

        private void list_ItemsRequested(object o, RadComboBoxItemsRequestedEventArgs e)
        {
            //TODo ; use reflection and custom attributes
            switch (this.UniqueName) {
                case "Fk_Enum_ContributionStatus":
                    {
                        ((RadComboBox)o).DataSource = GetDataTable(this.UniqueName, e.Text);
                        ((RadComboBox)o).DataTextField = "Enum_Value";
                        ((RadComboBox)o).DataValueField = "Pk_Enum_ContributionStatus";
                        break;
                }

                default: {
                    ((RadComboBox)o).DataSource = GetContributionTable(this.UniqueName, e.Text);
                    ((RadComboBox)o).DataTextField = this.UniqueName;
                    ((RadComboBox)o).DataValueField = this.UniqueName;
                    break;
                }
            }

            ((RadComboBox)o).DataBind();
        }

        private void list_SelectedIndexChanged(object o, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            GridFilteringItem filterItem = (GridFilteringItem)((RadComboBox)o).NamingContainer;
            if ((this.UniqueName == "Index"))
            {
                //this is filtering for integer column type
                filterItem.FireCommandEvent("Filter", new Pair("EqualTo", this.UniqueName));
            }
            //filtering for string column type
            filterItem.FireCommandEvent("Filter", new Pair("Contains", this.UniqueName));
        }

        public static List<Contribution> GetContributionTable(string col, string value)
        {

            List<Contribution> ContribList = new List<Contribution>();
            List<CustomParam> parameterList = new List<CustomParam>();
            String tablename = string.Empty;
            String column = string.Empty;



            ////TODO apply filter
            DbParameterManager dph = new DbParameterManager(DbParameterManager.ProviderType.SqlClient);
            CustomParam param1 = new CustomParam(dph.CreateDataParameter("@" + col, DbType.String));
            param1.Value = "%" + value + "%";
            parameterList.Add(param1);


            ContribList = ContributionFactory.getCustomQueryList("select * from Contribution where " + col + " like @" + col, parameterList);

            return ContribList;
        }

        public static DataTable GetDataTable(string col, string value) {
            return Enum_ContributionStatusFactory.GetAllEnum_ContributionStatusDataTable();
        }

        public DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable(TableName);
            dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

            object LastValue = null;
            foreach (DataRow dr in SourceTable.Select("", FieldName))
            {
                if (LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])))
                {
                    LastValue = dr[FieldName];
                    dt.Rows.Add(new object[] { LastValue });
                }
            }
            if (ds != null)
                ds.Tables.Add(dt);
            return dt;
        }

        private bool ColumnEqual(object A, object B)
        {

            // Compares two values to see if they are equal. Also compares DBNULL.Value.
            // Note: If your DataTable contains object fields, then you must extend this
            // function to handle them in a meaningful way if you intend to group on them.

            if (A == DBNull.Value && B == DBNull.Value) //  both are DBNull.Value
                return true;
            if (A == DBNull.Value || B == DBNull.Value) //  only one is DBNull.Value
                return false;
            return (A.Equals(B));  // value type standard comparison
        }

        public override bool SupportsFiltering()
        {
            return true;
        }


    }

[Christophe Vialatte]

Ok, I have found why my custom columns weren't rebound after a filter selection. I don't need a constructor with arguments designed to bind the DDL because it will be done when on ddl_DataBinding.

I used to set the DDL Id in the custom constructor, it is now set in theInstantiateIn method :

#region GridBoundDDLColumn Filter
    // Class MyCustomFilteringColumnCS
    public class MyCustomFilteringDDLColumnCS : GridTemplateColumn
    {

        #region Item Template for DropDownList

        public class CreateItemTemplateDDL : ITemplate
        {
            DataTable dtBind;
            string strddlName;
            string strDataValueField;
            string strDataTextField;

            public MyCustomFilteringDDLColumnCS Column = null;

            //public CreateItemTemplateDDL(string DataValueField, string DataTextField, DataTable DDLSource)
            //{
            //    this.dtBind = DDLSource;
            //    this.strDataValueField = DataValueField;
            //    this.strDataTextField = DataTextField;
            //}

            public CreateItemTemplateDDL()
            {

            }
            public void InstantiateIn(Control objContainer)
            {
                DropDownList ddl = new DropDownList();
                ddl.DataBinding += new EventHandler(ddl_DataBinding);
                ddl.ID = this.Column.UniqueName;
                objContainer.Controls.Add(ddl);
            }

            private void ddl_DataBinding(object sender, EventArgs e)
            {
                DropDownList ddl = (DropDownList)sender;
                GridDataItem dataItem = (GridDataItem)((Control)sender).NamingContainer;
                if(dataItem!=null){
                    try {
                        Contribution ctb = (Contribution)dataItem.DataItem;

                        if (ctb != null)
                        {
                            string test = this.Column.DataField;
                            //todo use reflection
                            if (test != null) {
                                DataTable dt = ContributionFactory.GetAllContributionDataTable();
                                ddl.DataSource = dt;
                                ddl.DataValueField = test;
                                ddl.DataTextField = test;
                            }
                        }
                    }
                    catch (Exception ee) {

                    }
                }


                //ddl.DataBind();
            }
        }

        #endregion Item Template for DropDownList

        public MyCustomFilteringDDLColumnCS() {

            CreateItemTemplateDDL template = new CreateItemTemplateDDL();
            template.Column = this;
            this.ItemTemplate = template;
        }

        //public MyCustomFilteringDDLColumnCS(string DataValueField, string DataTextField, DataTable DDLSource)
        //{
        //    CreateItemTemplateDDL template = new CreateItemTemplateDDL(DataValueField,DataTextField,DDLSource);
        //    template.Column = this;
        //    this.ItemTemplate = template;
        //}

        public string DataField
        {
            get
            {
                object res = this.ViewState["_df"];
                if (res != null)
                {
                    return (string)res;
                }

                return String.Empty;
            }
            set
            {
                this.ViewState["_df"] = value;
            }
        }

        protected override string GetFilterDataField()
        {
            return this.DataField;
        }

        //RadGrid will call this method when it initializes the controls inside the filtering item cells
        protected override void SetupFilterControls(TableCell cell)
        {
            base.SetupFilterControls(cell);
            cell.Controls.RemoveAt(0);
            RadComboBox combo = new RadComboBox();
            combo.ID = ("RadComboBox1" + this.UniqueName);
            combo.ShowToggleImage = false;
            combo.Skin = "Office2007";
            combo.EnableLoadOnDemand = true;
            combo.AutoPostBack = true;
            combo.MarkFirstMatch = true;
            combo.Height = Unit.Pixel(100);
            combo.ItemsRequested += this.list_ItemsRequested;
            combo.SelectedIndexChanged += this.list_SelectedIndexChanged;
            cell.Controls.AddAt(0, combo);
            cell.Controls.RemoveAt(1);
        }

        //RadGrid will cal this method when the value should be set to the filtering input control(s)
        protected override void SetCurrentFilterValueToControl(TableCell cell)
        {
            base.SetCurrentFilterValueToControl(cell);
            RadComboBox combo = (RadComboBox)cell.Controls[0];
            if ((this.CurrentFilterValue != string.Empty))
            {
                combo.Text = this.CurrentFilterValue;
            }
        }

        //RadGrid will cal this method when the filtering value should be extracted from the filtering input control(s)
        protected override string GetCurrentFilterValueFromControl(TableCell cell)
        {
            RadComboBox combo = (RadComboBox)cell.Controls[0];
            return combo.Text;
        }

        private void list_ItemsRequested(object o, RadComboBoxItemsRequestedEventArgs e)
        {
            //TODo ; use reflection and custom attributes
            switch (this.UniqueName) {
                case "Fk_Enum_ContributionStatus":
                    {
                        ((RadComboBox)o).DataSource = GetDataTable(this.UniqueName, e.Text);
                        ((RadComboBox)o).DataTextField = "Enum_Value";
                        ((RadComboBox)o).DataValueField = "Pk_Enum_ContributionStatus";
                        break;
                }

                default: {
                    ((RadComboBox)o).DataSource = GetContributionTable(this.UniqueName, e.Text);
                    ((RadComboBox)o).DataTextField = this.UniqueName;
                    ((RadComboBox)o).DataValueField = this.UniqueName;
                    break;
                }
            }

            ((RadComboBox)o).DataBind();
        }

        private void list_SelectedIndexChanged(object o, RadComboBoxSelectedIndexChangedEventArgs e)
        {
            GridFilteringItem filterItem = (GridFilteringItem)((RadComboBox)o).NamingContainer;
            if ((this.UniqueName == "Index"))
            {
                //this is filtering for integer column type
                filterItem.FireCommandEvent("Filter", new Pair("EqualTo", this.UniqueName));
            }
            //filtering for string column type
            filterItem.FireCommandEvent("Filter", new Pair("Contains", this.UniqueName));
        }

        public static List<Contribution> GetContributionTable(string col, string value)
        {

            List<Contribution> ContribList = new List<Contribution>();
            List<CustomParam> parameterList = new List<CustomParam>();
            String tablename = string.Empty;
            String column = string.Empty;



            ////TODO apply filter
            DbParameterManager dph = new DbParameterManager(DbParameterManager.ProviderType.SqlClient);
            CustomParam param1 = new CustomParam(dph.CreateDataParameter("@" + col, DbType.String));
            param1.Value = "%" + value + "%";
            parameterList.Add(param1);


            ContribList = ContributionFactory.getCustomQueryList("select * from Contribution where " + col + " like @" + col, parameterList);

            return ContribList;
        }

        public static DataTable GetDataTable(string col, string value) {
            return Enum_ContributionStatusFactory.GetAllEnum_ContributionStatusDataTable();
        }

        public DataTable SelectDistinct(string TableName, DataTable SourceTable, string FieldName)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable(TableName);
            dt.Columns.Add(FieldName, SourceTable.Columns[FieldName].DataType);

            object LastValue = null;
            foreach (DataRow dr in SourceTable.Select("", FieldName))
            {
                if (LastValue == null || !(ColumnEqual(LastValue, dr[FieldName])))
                {
                    LastValue = dr[FieldName];
                    dt.Rows.Add(new object[] { LastValue });
                }
            }
            if (ds != null)
                ds.Tables.Add(dt);
            return dt;
        }

        private bool ColumnEqual(object A, object B)
        {

            // Compares two values to see if they are equal. Also compares DBNULL.Value.
            // Note: If your DataTable contains object fields, then you must extend this
            // function to handle them in a meaningful way if you intend to group on them.

            if (A == DBNull.Value && B == DBNull.Value) //  both are DBNull.Value
                return true;
            if (A == DBNull.Value || B == DBNull.Value) //  only one is DBNull.Value
                return false;
            return (A.Equals(B));  // value type standard comparison
        }

        public override bool SupportsFiltering()
        {
            return true;
        }


    }
    // end Class MyCustomFilteringColumnCS
    #endregion

private void buildGrid()
        {
            this.ResultRadGrid.MasterTableView.Columns.Clear();
            if (SelectedRadGrigColList.Items.Count != 0)
            {
                this.ResultRadGrid.MasterTableView.Columns.Clear();
                Hashtable ddlColumnTable = new Hashtable();
                Hashtable lblColumnTable = new Hashtable();
                foreach (RadListBoxItem rlbi in SelectedRadGrigColList.Items)
                {
                    switch (rlbi.Value)
                    {
                        case "Pk_Contribution":
                            {
                                MyCustomFilteringDDLColumnCS templateColumn = new MyCustomFilteringDDLColumnCS();
                                templateColumn.HeaderText = "Contribution Relationship";
                                templateColumn.DataField = rlbi.Value;
                                templateColumn.UniqueName = rlbi.Value;
                                ResultRadGrid.MasterTableView.Columns.Add(templateColumn);
                                ddlColumnTable.Add(ResultRadGrid.MasterTableView.Columns.Count - 1, rlbi.Value);

                                break;
                            }

Now, I have to find a way to enable filtering using the datasource of my DDL.

[Pavlina]

Hello Christophe,

Filtering is automatically supported for Template columns.If you would like to support filtering for GridTemplateColumns or for your custom columns please review the following help article:
http://www.telerik.com/help/aspnet-ajax/grdimplementingfilteringfortemplatecolumns.html

Sincerely yours,
Pavlina
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.