Bind data to DropDownList using a DataTable

Posted on November 7, 2008
Filed Under Development, Programming | Leave a Comment

If you've enjoyed reading this post then please subscribe to my Full Text RSS Feed.

DropDownLists are frequently used in forms in ASP.NET pages. Some DropDownLists can contain static data in the drop-down but there are times we want the drop-down data to be dynamic. One way is to bind data from a database to the DropDownList. The DropDownList data is bounded when the ASP.NET page is loaded by querying a SQL database. Here is an example of how the dynamic DropDownList. It is not any different from a static DropDownList. The difference is in the code-behind which we will be looking at shortly.

Below is the function BindActivity() which is coded in the ASP.NET code-behind page. In Page_Load() of the aspx page, you call BindActivity(). In BindActivity(), we have:

1) An instance of the Connector class
2) A Dataset
3) A Datatable
4) A DropDownList

The Connector class contains the properties and methods that connects to a SQL database and retrieves the data, binds it to a DataSet and eventually binds to a DropDownList. I have coded the data connectivity layer in the Connector class to facilitate reusability.

cn.ActivityList is a property in Connector that returns the data fetched from the SQL Database to a DataSet. We then assign the Table in the DataSet to a DataTable so that we can assign specific column from the DataTable to the DropDownList.DataTextField and DropDownList.DataTextValue. Finally binding the data to the DropDownList through DropDownList2.DataBind().

In the GetActList() method, we instantiate a SQLConnection called sqlconn. The connectionstring is predefined in the method called ConnectionString() shown below. We use a SqlDataAdapter instead of a SqlCommand because we want to bind the data to a DataSet. The CommandType that I have used in this example is a StoredProcedure. You don’t have to use a Stored Procedure. You can code the sql query in a string as an alternative to creating a Stored Procedure.

da.Fill(d);
return d;

The two lines of code above fills the DataSet with the data retrieved from SQL and the DataSet is returned to method ActivityList().

There are other ways that you can code binding dynamic data to a DropDownList but I have chosen to show you this example because I want to show you how to separately code the data layer.