Generic DropDown Control Using LINQ to SQL and Entities using Reflection

Introduction

This article describes accessing LINQ to SQL and Entities using Reflection, and using Reflection, we retrieve entities through the data context.
I have been trying to create a generic dropdown list for my current project.

In my current project, I have to deal with so many dropdown list boxes and get a list of values from different tables, and it could also contain values from different columns. I thought of coming up with a novel solution for my project and I created a custom dropdown list control to provide the DataTextColumn, DataValueColumn, and TableName properties. I just have to assign the DataTextField, DataValueField, and TableName properties of the dropdown list and it will populate the dropdown list automatically.
The user just has to call the DataBind() function of the dropdown to bind the list of values to the dropdown control.

Using the Code

This article contains two sections: Firstly, it shows the custom dropdown list control, and secondly, it shows the function in which LINQ to SQL and
Entities is being accessed through Reflection.

Custom DropDown Control

The custom dropdown control class inherits from DropDownList to extend the functionality of the traditional dropdown control. In the new dropdown class, we have three main
fields: DataTextFeild, DataValueField, and TableName. We are also using the ListOfValue class to get the list of ListItems to bind to the dropdown control.

[ToolboxData("<{0}:CustomDropDownList runat=server></{0}:CustomDropDownList>")]
public class CustomDropDownList : DropDownList
{
    public CustomDropDownList()
    {
        _listOfValues = new ListOfValues();
    }
    private ListOfValues _listOfValues;
    public ListOfValues ListOfValues
    {
        get { return _listOfValues; }
        set { _listOfValues = value; }
    }
    public override string DataTextField
    {
        get
        {
            return "Text";
        }
        set
        {
            ;
        }
    }
    public override string DataValueField
    {
        get
        {
            return "Value";
        }
        set
        {
            ;
        }
    }
    [Browsable(true)]
    [DefaultValue("")]
    public string TableName
    {
        get
        {
            String s = (String)ViewState["TableName"];
            return ((s == null) ? String.Empty : s);
        }
        set
        {
            ViewState["TableName"] = value;
        }
    }
    [Browsable(true)]
    [DefaultValue("")]
    public string DataTextColumnName
    {
        get
        {
            String s = (String)ViewState["DataTextColumnName"];
            return ((s == null) ? String.Empty : s);
        }
        set
        {
            ViewState["DataTextColumnName"] = value;
        }
    }
    [Browsable(true)]
    [DefaultValue("")]
    public string DataValueColumnName
    {
        get
        {
            String s = (String)ViewState["DataValueColumnName"];
            return ((s == null) ? String.Empty : s);
        }
        set
        {
            ViewState["DataValueColumnName"] = value;
        }
    }
    public override void DataBind()
    {
        this.DataSource = ListOfValues.GetListOfValue(
             DataTextColumnName, DataValueColumnName, TableName);
        base.DataBind();
    }
    protected override void RenderContents(HtmlTextWriter output)
    {
        base.RenderContents(output);
    }
}

Custom dropdown control declaration: DataTextColumnName should have the column name that you want to show to the user, DataValueColumnName should have the column name that you want to get to uniquely identify the selected value, and TableName will contain the name of the table.

<cc1:CustomDropDownList ID="CustomDropDownList1" runat="server"
 DataTextColumnName="name" DataValueColumnName="id" TableName="State" >
</cc1:CustomDropDownList>
<cc1:CustomDropDownList ID="CustomDropDownList2" runat="server"
 DataTextColumnName="name" DataValueColumnName="id" TableName="Country" >
</cc1:CustomDropDownList>

[Browsable(True)], [DefaultValue("")]: [Browsable] means you can see TableName in intellisense, and [DefaultValue]
denotes the default value for TableName. We store the TableName in the ViewState to retain the value during postback.

TableName: This is the property of the custom dropdown control to hold the name of the table provided in the declaration.

[Browsable(true)]
[DefaultValue("")]
public string TableName
{
    get
    {
        String s = (String)ViewState["TableName"];
        return ((s == null) ? String.Empty : s);
    }
    set{
        ViewState["TableName"] = value;
    }
}

DataTextColumnName: This property of the custom dropdown control holds the name of the column visible to the user provided in the declaration.

[Browsable(true)]
[DefaultValue("")]
public string DataTextColumnName
{
    get
    {
        String s = (String)ViewState["DataTextColumnName"];
        return ((s == null) ? String.Empty : s);
    }
    set
    {
        ViewState["DataTextColumnName"] = value;
    }
}

DataValueColumnName: This property holds the name of the column to identify the unique row in the table provided in the declaration..

[Browsable(true)]
[DefaultValue("")]
public string DataValueColumnName
{
    get
    {
        String s = (String)ViewState["DataValueColumnName"];
        return ((s == null) ? String.Empty : s);
    }
    set
    {
        ViewState["DataValueColumnName"] = value;
    }
}

Why Not DataTextField and DataValueField

DataTextField and DataValueField are used by the dropdown control to bind those columns with the dropdown control as we are using ListItem, so by default, the dropdown list would get DataTextField as the text of ListItem and DataValueField would get the value of ListItem. It is important to remember the custom dropdown control DataTextColumnName and DataValueColumnName gets the column name for a different purpose (extract out the value from the table column not to bind) as opposed to DataTextField and DataValueField.

DataBind

In the DataBind method of the custom dropdown control, I call the DataBind event to bind data to the dropdown list. In the DataBind event, I also call the GetListOfValue function to get the DataText column and the DataValue column from the given table.

public override void DataBind()
{
    this.DataSource = ListOfValues.GetListOfValue(
         DataTextColumnName, DataValueColumnName, TableName);
    base.DataBind();
}

How to DataBind

The user can call the dropdown DataBind function just like in the traditional dropdown control but the good thing is that the user does not have to provide the list to the dropdown control; it will automatically get the list by using DataTextName, DataValueName, and TableName, and LINQ to SQL and Reflection.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        CustomDropDownList1.DataBind();
        CustomDropDownList2.DataBind();
    }
}

LINQ to SQL and Entities Using Reflection

This ListOfValues class is responsible to get the list of values from database through Reflection. This function accesses the data context
of LINQ to SQL. First, it gets the table using a lambda expression where the table name is equal to the given table, and then iterates through its columns to find the matching data text column and the data value column and saves it in the ListItem list.

How Reflection Works

There is a GetTables() function in the mapping object on the DC. Once you have the metatable, you can presumably get the corresponding .NET Type from the meta table (metaTable.RowType.Type?). Get all the metatables, loop through the list of metatables, and for each one, get the corresponding .NET Type and call the GetTable() function.

After getting the selected table, find the column name through the GetProperties method and populate the respective ListItem Text and Value properties of the ListItem.

public List<ListItem> GetListOfValue(string dataFieldName, 
       string dataFieldValue, string tableName)
{
    List<ListItem> listItemList = new List<ListItem>();
    ListItem listItem = null;

    MetaTable table = _dataContext.Mapping.GetTables().Where(
      t => t.TableName.ToUpper().Equals("DBO." + 
      tableName.ToUpper())).FirstOrDefault();

    if (table == null)
        throw new NullReferenceException("table");

    ITable iTable = _dataContext.GetTable(table.RowType.Type);
    if (iTable == null)
       throw new NullReferenceException("iTable");

    List<object> objectList = iTable.OfType<object>().ToList();
    foreach (object obj in objectList)
    {
        listItem = new ListItem();
        foreach (PropertyInfo property in obj.GetType().GetProperties())
        {
            if (property.Name.ToUpper().Equals(dataFieldName.ToUpper()))
            // see if dataFieldName(DataTextColumnName) is equal to table column name {
                listItem.Text = property.GetValue(obj, null).ToString();
            }
            if (property.Name.ToUpper().Equals(dataFieldValue.ToUpper()))
            // see if dataFieldValue(DataValueColumnName) is equal to table column name{
                listItem.Value = property.GetValue(obj, null).ToString();
        }
    }
    //this loop will make the complete list of listitem for the given text and value column
    listItemList.Add(listItem);
}
return listItemList;

This function uses Reflection so there may be problems in performance and it re-initializes the data context for each different custom dropdown control; perhaps the user can modify this to initialize the data context once for each custom dropdown control. This was just one of many tries and I continuously tried to learn something.
Thanks, and hope this may help someone in learning.