Binding a ListControl with Values from the Database

The Concept


In ASP.Net, we have number of List Controls such as DropDownList, ListBox, CheckBoxLists, RadioButtonList and BulletedList which contains a list of items in key value pair. Sometimes, it is required to bind these types of controls with values from any Database table.

The following code exaplains how to bind a DropDownList with values from any Database table.


Code

protected void Page_Load(object sender, EventArgs e)
{
        if (!Page.IsPostBack)
        {
            DataTable dt = GetKeyValuePair("TblStudents", "sname", "sid", "-SELECT-");
            DropDownList1.DataSource = dt;
            DropDownList1.DataTextField = "sname";
            DropDownList1.DataValueField = "sid";
            DropDownList1.DataBind();
        }
}

public DataTable GetKeyValuePair(string TableName, string KeyColumn, string ValueColumn, string DefaultValue)
{
        string query;
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcon"].ToString());
        SqlDataAdapter sqlda;
        DataTable dt;
        query = "SELECT '" + DefaultValue + "' AS " + KeyColumn + " , '" + DefaultValue + "' AS " + ValueColumn + " FROM " + TableName + " UNION ";
        query += "SELECT " + KeyColumn + ", " + ValueColumn + " FROM " + TableName;
        sqlda = new SqlDataAdapter(query, con);
        dt = new DataTable();
        sqlda.Fill(dt);
        return dt;
}

1. TableName parameter is the name of the Database Table from which we want to retrieve the records.


2. KeyColumn parameter is the name of the Column, whose values would be used to display in the DropDownList.


3. ValueColumn parameter is the name of the Column, whose values would be used as the DataValueField in the DropDownList.


4. DefaultValue parameter is used to display the default text in the DropDownList.