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

Dynamically bind Asp.Net MVC Dropdownlist from Sql Server DatabaseIntroduction: 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.


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. 

Dynamically bind Asp.Net MVC Dropdownlist from Sql Server Database Pic-1
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:

Dynamically bind Asp.Net MVC Dropdownlist from Sql Server Database Pic-2
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.

Dynamically bind Asp.Net MVC Dropdownlist from Sql Server Database Pic-3
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.

Dynamically bind Asp.Net MVC Dropdownlist from Sql Server Database Pic-4
Click on image to enlarge


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

Dynamically bind Asp.Net MVC Dropdownlist from Sql Server Database Pic-5
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." 
Previous
Next Post »

8 comments

Click here for comments
Unknown
admin
August 26, 2015 ×

Nice Explained... But can you explain about selected Index ??

Reply
avatar
Unknown
admin
October 30, 2015 ×

thanks that was so helpful ^_^

Reply
avatar
November 01, 2015 ×

Ur welcome Marloo Stuart..Stay connected and keep reading for more useful updates like this..

Reply
avatar
Unknown
admin
December 16, 2015 ×

Nice article very clearly explained Thank you

Reply
avatar
Ishan
admin
January 12, 2016 ×

Thank u it's helpful 😊

Reply
avatar
February 08, 2016 ×

Thanks for you feedback..I am glad you liked this article..stay connected and keep reading...

Reply
avatar
August 05, 2016 ×

Thank u so much.... very nice...

Reply
avatar
August 18, 2016 ×

Thanks for your comment..Stay connected and keep reading

Reply
avatar

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..