Populate Dropdown from Database in MVC

A commonly required functionality is populating dropdown with values fetched from the database.Many times data is contained in a database table from where it needs to be fetched and displayed in a dropdown in the UI.Here we will see how to populate dropdown from database in MVC application.

Unlike other html controls a dropdown has both name and id values to uniquely identifying an item in the collection.Please refer the following for more about DropdownList in MVC 

Following are the steps to populate the dropdown with values from the database.

1. Declare a class which represents the items in the dropdown.In the following example we are declaring a Category class.

public class Category
{
public string Name { get; set; }
public int Id { get; set; }
}

2. Define  a data access method to fetch the values from the database table .We will populate the dropdown with these values.

In the Data access method

• Create connection and command objects
• Iterate through the rows returned from the database table.
• Add the items corresponding to the rows to a generic list collection.Here we are adding the fetched values to the Category list collection.

private List<Category> GetCategories()
{
string connection = ConfigurationManager.ConnectionStrings["Connection"].ConnectionString;
var categories = new List<Category>();
var con=new SqlConnection(connection);
con.Open();
var command = new SqlCommand("SELECT * FROM Category", con);

SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
categories.Add(new Category { Name =Convert.ToString(reader["Name"]), Id = Convert.ToInt32(reader["Id"]) });

}
con.Close();
}

3.In the Action method

• Call the data access method declared above   This data access method  fetches the records from the database and returns a collection.This collection contains the records from the database table which we want to bind to the DropDownList.
• Filter the returned values if required     If we want to display only a subset of the returned values we can filter the collection.One common way is to use LINQ query operators for filtering the data.
• Create SelectListItem collection   As we can directly bind a List of SelectListItems to the DropDownList so we need to create a SelectListItem list.This list represents the items returned by the data access method.Here we are creating a List from the Category collection.
• Assign the SelectListItem collection to a ViewBag property  We can use this ViewBag property in the view to bind the collection.

public ActionResult Details()
{

ViewBag.Categories=GetCategories().Where(x=>x.Id<10).Select(x=>new SelectListItem{Text=x.Name,Value=x.Id.ToString()});

return View();

}

4. Render the dropdown in the view by calling the DropDownList helper method and passing the ViewBag property which we had assigned in the Details action method:

@Html.DropDownList("Categories")

So by following the above four steps we can easily populate dropdown from database in MVC view.

Follow on: