How to dynamically bind Asp.Net MVC Dropdownlist from Sql Server Database using entity framework

Introduction: In this article I am going to explain with example on How to generate DropDownList using HTML helper and dynamically populate it from Sql Server database using entity framework in Asp.Net MVC.

In previous articles i explained Multiple ways to generate DropDownList in MVC using HTML helpers and hard coded items and Multiple ways to pass data from controller to view in MVC with example and example to Create,Read,Update,Delete operation using Asp.Net MVC and Entity Framework

Implementation: Let’s create a sample MVC application to dynamically fill dropdownlist from sql server database.

But first of all create a Sql Server Database and name it "MySampleDataBase" and in this database create a  table and name it  "Department".

You can create the above mentioned Database, Table and insert data in table by pasting the following script in sql server query editor:

CREATE DATABASE MySampleDataBase

GO

USE [MySampleDataBase]

GO

CREATE TABLE Department

(

                DepartmentId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

                DepartmentName VARCHAR(50) NOT NULL

)

Now insert some data in table using the following queries:

INSERT INTO Department(DepartmentName) VALUES('HR')

INSERT INTO Department(DepartmentName) VALUES('SALES')

INSERT INTO Department(DepartmentName) VALUES('ACCOUNTS')

INSERT INTO Department(DepartmentName) VALUES('IT')

Now our database and table is ready. Let's create the demo application:

Step 1: Open Visual Studio. I am using Visual studio 12 for this applications.  File Menu -> New project.

Select you preferred language either Visual C# or Visual Basic. from the left pane For this tutorial we will use Visual C#.  Select ASP.NET MVC 4 Web Application. 

Name the project "MvcBindDropDownList".  Specify the location where you want to save this project as shown in image below. Click on Ok button. 

Click on image to enlarge

Step 2: A "New ASP.NET MVC 4" project dialog box having various Project Templates will open. Select Internet template from available templates. Select Razor as view engine. Click on Ok button.

It will add required files and folder automatically in the solution .So a default running MVC applications is ready. But our aim is to bind dropdownlist from database using entity framework.

Step 3: Now right click on the project name (MvcBindDropDownList) in solution explorer. Select Add New Item. Select ADO.NET Entity Data Model and name it “MySampleDataModel.edmx” as shown in image below:

Click on image to enlarge

Step 4:  A New Entity Data Model Wizard dialog box will open. Select Generate Model from Database and click next:

Step 5:  Select your database from server as shown in image below. It will automatically create connection string in web.config file with the name “MySampleDataBaseEntities” . Just check your web,config file place in the root folder.You can also change the name of the connection string. But leave it as it is.

Click on image to enlarge

Step 6:  Select Database object that you wish to add in the Model as shown in image below. In our case it is “Department” Table. Leave the Model namespace “MySampleDataBaseModel” as it is. Click Finish Button.

Click on image to enlarge

Step 7: EDMX and Model files are added in the solution as shown in image below (HIGHLIGHTED):

Click on image to enlarge

Step 8: Now in Home Controller (Controllers folder\ HomeController.cs) remove all the auto generated code and paste the code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using MvcBindDropDownList.Models;

namespace MvcBindDropDownList.Controllers

{

    public class HomeController : Controller

    {

        public ActionResult Index()

        {

            MySampleDataBaseEntities db = new MySampleDataBaseEntities();

            ViewBag.Departments = new SelectList(db.Departments, "DepartmentId", "DepartmentName");

            return View();

        }       

    }

}

Explaination: 

--> In first line of the above index action I first created the object of MySampleDataBaseEntities class.

e.g. MySampleDataBaseEntities db = new MySampleDataBaseEntities();

Notice that Entity framework has automatically added a class with the name “MySampleDataBaseEntities “. To locate the class you need to open Model folder -> expand MySampleDataModel.edmx -> expand MySampleDataModel.Context.tt -> double click MySampleDataModel.Context.cs file. So this “MySampleDataBaseEntities” class will help us to connect to our database.

Then through this object we can get the data from the department table.

e.g. db.Departments will return all the departments contained in Department table 

--> Then in second line I specified the “DepartmentId” as DataValueField  and “DepartmentName” as  DataTextField  as we specify in asp.net while binding dropdownlist.  And stored that in ViewBag’s dynamic property Departments (property name can be anything but here I named it Departments)

--> Then in third line I returned the view.

Step 9: In your Index.cshtml view (Views folder\Home folder\Index.cshtml) remove all the auto generated stuff and paste the following:

@{

    ViewBag.Title = "Home Page";

}

Select Department: @Html.DropDownList("Departments", "Select")

Explaination: In the above code, the DropDownList helper accepts two parameters. The first parameter named DropDownList ("Departments") is compulsory parameter and it must be same as  ViewBag name (ViewBag.Departments)and the second parameter, optionLabel, as the name says is optional. The optionLabel is generally used for first option in DropDownList. E.g. In our case it is “Select”. 

Now run the application and check it.

 Now over to you:

"A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates." 

• Tweet
• Share
• Share
• Share

Related Post