Filter Items in the Dropdown control after DataBinding

SKRaj78

2.20/5 (4 votes)

May 7, 2006

2 min read

75450

1471

By default all items in the datasource are binded to the dropdownlist control;Using this control,we can filter datasource items.

Introduction

Sometimes requirement may come to bind some of the items present in the datasource (like dataset) to the dropdown control. By default all items in the datasource will be shown.

Assume we have one web form; with mutliple dropdown controls (dropdownlist1,dropdownlist2,dropdownlist3..) and 2 database tables category and values; One to Many relationship exists between category and values tables.

Category table

CREATE TABLE [dbo].[dropdownCategory]

( [CategoryID] [int] NOT NULL,

[Name] [varchar](50) NOT NULL,

[Description] [varchar](255) NULL, CONSTRAINT [Category_PK] PRIMARY KEY CLUSTERED ( [CategoryID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[DropdownValues]

( [dropdownValuesID] [int] IDENTITY(1,1) NOT NULL,

[CategoryID] [int] NOT NULL,

[Value] [varchar](50) NOT NULL,

[Code] [varchar](50) NOT NULL ,

CONSTRAINT [DropdownValues_PK] PRIMARY KEY CLUSTERED ( [dropdownValuesID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE [dbo].[dropdownValues] WITH CHECK ADD CONSTRAINT [dropdownCategory_dropdownValues_FK1] FOREIGN KEY([CategoryID]) REFERENCES [dbo].[dropdownCategory] (CategoryID)

Dropdown category Table

categoryId	Name
1	India
2	USA
3	Australia

Dropdown values Table

dropdownvaluesId	categoryId	value	code
1	1	AndhraPradesh	AP
2	1	Tamilnadu	TN
3	2	South Carolina	SC
4	2	Tennesse	TN
5	3	Victoria	Vic
6	3	New South Wales	NSW

Say we have 3 dropdowns on the form;dropdownlist1 should be populated with states in India,dropdownlist2 with states in USA and dropdownlist3 with states in australia. Using a single stored procedure fetch all dropdown values in to a dataset and bind it to the dropdown controls.

ddlIndia.DataTextField = "value"; ddlIndia.DataValueField = "code";

ddlIndia.datasource = dsDropdownValues; ddlIndia.databind();

ddlUSA.DataTextField = "value"; ddlUSA.DataValueField = "code";

ddlUSA.datasource = dsDropdownValues; ddlUSA.databind();

ddlAustralia.DataTextField = "value"; ddlAustralia.DataValueField = "code";

ddlAustralia.datasource = dsDropdownValues; ddlAustralia.databind();

Now we need to filter the items in the dataset;to populate the items in the dropdownlists accordingly.

Using the code

I added 2 properties(FilterBy and ColumnName) to the asp.net dropdownlist control and overided the "PerformDataBinding" method to filter the items in the datasource.

Few more properties EmptyDataText and UIFriendlyText are added to the custom dropdown list.The "emptydatatext" property is same as the "emptydatatext' property of gridview.

To make dropdownlist user friendly, normally we insert user friendly text as the top item in the dropdownlist.UserFriendlyText property value is displayed as the first item in the dropdownlist.

[ToolboxData("<{0}:CustomDropDownList runat="server"></{0}:CustomDropDownList>
")]
public class CustomDropDownList : DropDownList,INamingContainer
{
    [Category("Data")]    
    [DefaultValue("")]
    [Localizable(true)]    
    public string FilterValue
    { 
        get 
        {
            object o = ViewState["FilterValue"]; return ((o == null) ? String.Empty : (string)o); 
        }
        set 
        { 
            ViewState["FilterValue"] = value; 
        } 
    }

    true)> 
    [Category("Data")]
    [DefaultValue("")]
    [Localizable(true)]    
    public string    ColumnName
    {       
        get 
        { 
            object o = ViewState["columnName"]; return ((o == null) ? String.Empty :
            (string)o);    
        }
        set    
        { 
            ViewState["columnName"] = value; 
        } 
    }

     protected override void <CODE>PerformDataBinding(IEnumerable dataSource)
        {
            DataView dvSource = null;
            string sDataValueField = ((System.Web.UI.WebControls.ListControl)(this)).DataValueField;
            string sDataTextField = ((System.Web.UI.WebControls.ListControl)(this)).DataTextField;
            string sFilterExpression = string.Empty;
            if (dataSource != null)
            {
                dvSource = (DataView)dataSource;
                if (dvSource.Table.Columns.Contains(sDataTextField) && dvSource.Table.Columns.Contains(sDataValueField))
                {
                    DataRow dr = dvSource.Table.NewRow();
                    dr[sDataValueField] = "-1";
                    if (dvSource.Table.Rows.Count == 0)
                    {
                        dr[sDataTextField] = EmptyDataText;
                        dvSource.Table.Rows.InsertAt(dr, 0);//as first item  

                        sFilterExpression = sDataValueField + " = '" + -1 + "'";
                    }
                    else if (UIFriendlyText != string.Empty)
                    {
                        dr[sDataTextField] = UIFriendlyText;
                        dvSource.Table.Rows.InsertAt(dr, 0);//as first item  

                        sFilterExpression = sDataValueField + " = '" + -1 + "'";
                    }                   
                }

                if (ColumnName != string.Empty && FilterValue!= string.Empty)
                {
                    if (sFilterExpression.Length > 0)
                    {
                        sFilterExpression += " OR ";
                    }
                    sFilterExpression += ColumnName + " = '" + FilterValue+ "'";
                }
                dvSource.RowFilter = sFilterExpression;
            }
            base.PerformDataBinding(dvSource);
        }

Usage of the control:

    ddlIndia.EmptyDataText = "No Data Items";
    ddlIndia.DataSource = dsDropdownValues;
    ddlIndia.ColumnName = "CategoryID"; 
    ddlIndia.FilterValue= "1"; 
    ddlIndia.DataBind(); 
    
    ddlUSA.UIFriendlyText = "Select State";
    ddlUSA.DataSource = dsDropdownValues;
    ddlUSA.ColumnName = "CategoryID"; 
    ddlUSA.FilterValue= "2"; 
    ddlUSA.DataBind();

Points of Interest

In the same way we can customize the other databound controls.