Bind Dropdownlist in ASP.NET MVC From Database Using Stored Procedure

Introduction

This article provides an explanation about how to populate a dropdown list from the database using stored procedure in Asp.Net MVC with example and also show you how you can get selected value from the dropdown list and use of @Html.Dropdownlist or @Html.DropdownlistFor properties to bind dropdown list based on the values from the database.

In my previous article, I explained Bootstrap JQuery Multiselect Dropdown List With Checkbox in ASP.NET Using C# And VB.NET and CRUD Operation Using Angular and WEB API 2 With Stored Procedure in Asp.Net MVC and AngularJS File Upload in ASP.NET using C# and VB.NET and Export JSON Data to Excel/CSV File using AngularJs With Bootstrap and also shared many other interesting articles on related technology that you might like to read.

Many of developers/programmers/people those who work with web applications will have at least heard talk about the dropdown list and maybe know something about the use of dropdown but still, in the developer's community, there is lot's of developers/students didn't know how to bind drop-down list from database in asp.net MVC application and especially those who are beginner or student So, in this article I am gonna show you how you can bind drop-down list from database dynamically.

In asp.net MVC we can implement drop-down list by using two properties either you can implement using the @Html.DropDownList or you can implement using @Html.DropDownListFor. Let's go with a simple example so you can understand clearly.

Requirement

1) Create a sample database.

2) Create a table for employees.

3) Write Stored Procedure to get information of an employee to bind dropdown.

4) Bind (populate) a dropdown list from the database based on the result set.

Implementation

So, Let's start with an example for demonstration purpose, but before that, we will create a database and then create a table and insert some dummy entry and write a stored procedure to get employee data for bind drop-down list.

Step 1: Create a Database in SQL Server.
Step 2: Create a table with the name "Employee_Master".

CREATE TABLE [dbo].[Employee_Master] (
    [EmpId]       INT            IDENTITY (1, 1) NOT NULL,
    [FirstName]   NVARCHAR (50)  NULL,
    [Department]  NVARCHAR (25)  NULL,
    [Designation] NVARCHAR (50)  NULL,
    [CompanyName] NVARCHAR (100) NULL,
    CONSTRAINT [PK_Employee_Master] PRIMARY KEY CLUSTERED ([EmpId] ASC)
);

Step 3: Insert some dummy records in the table.

INSERT INTO Employee_Master 
VALUES ('Nikunj Satasiya','Computer/IT','Sr.Software Engineer','D&K Technologies'),
       ('Hiren Dobariya','Computer/IT','Sr.Software Engineer','Version System Pvt Ltd'),
    ('Sruti Patel','Network and Sequrity','Sr.Software Engineer','D&K Technologies'),
    ('Vivek Ghadiya','Computer/IT','Sr.Software Engineer','D&K Technologies'),
    ('Nikunj Ladani','Computer/IT','Sr.Software Engineer','Version System Pvt Ltd')

Step 4: Write a Stored Procedure.

CREATE PROCEDURE Get_Employee
AS
BEGIN
SELECT EmpId, FirstName,Department, Designation, CompanyName FROM Employee_Master WITH(NOLOCK)
END

Now, Open your visual studio and go to File >> New >> Select Project.

Once you select Project new popup will open where you have to select "Web" from the left panel and choose "Asp.Net Web Application" and then give a name to your web application, select application path and click OK like as I shown below.

Now again you can see one another popup window will appear on your screen where you have to select template as "Empty" and checked the checkbox of MVC for folders and core references and then click on OK Button.

Now, after completing all the above steps the structure of your project will look like as I shown below.

Now, you need to add a model and controller to your web application and for that, you need to press right click on your "Models" folder and select "Add" >> "Class".

You can see one popup window will appear on your screen for add model class in your application, here You have to select "Class" and then give a name of your model class and simply click on "Add" button. Here I will give my model name as "EmployeeModel.cs". And I will also add one another model class with name "EmployeeDBHandle.cs" same as I show as below.

Now, same as the model you have to add controller class to your web application and for that, you have to press right-click on your "controller's" folder and select Add >> Controller.

Then after you can see one popup window will appear on your screen where you have to select "MVC 5 Controller with read/write actions" and click on Add button.

Now, add a name of the controller and simply add a controller to your application

Note: The name of the controller must end with controller i.g EmployeeController, StudentController, ProductController and etc.

Now you have to write following code in Model Class "EmployeeModel.cs" and "EmployeeDBHandle.cs".

EmployeeModel.cs

using System;

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
 
namespace Codingvila.Models
{
    public class EmployeeModel
    {
        public List<EmployeeModel> EmployeeInformation { get; set; }
        public int EmpId { get; set; }
        public string FirstName { get; set; }
        public string Department { get; set; }
        public string Designation { get; set; }
        public string CompanyName { get; set; }
    }
}

EmployeeDBHandle.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
 
namespace Codingvila.Models
{
    public class EmployeeDBHandle
    {
        private SqlConnection con;
        private void connection()
        {
            string constring = ConfigurationManager.ConnectionStrings["Conn"].ToString();
            con = new SqlConnection(constring);
        }
 
        // ********** VIEW EMPLOYEE DETAILS ********************
        public List<EmployeeModel> GetEmployee()
        {
            connection();
            List<EmployeeModel> EmployeeInformation = new List<EmployeeModel>();
 
            SqlCommand cmd = new SqlCommand("EXEC Get_Employee", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter sd = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            con.Open();
            sd.Fill(dt);
            con.Close();
            foreach (DataRow dr in dt.Rows)
            {
                EmployeeInformation.Add(
                    new EmployeeModel
                    {
                        EmpId = Convert.ToInt32(dr["EmpId"]),
                        FirstName = Convert.ToString(dr["FirstName"]),
                        Department = Convert.ToString(dr["Department"]),
                        Designation = Convert.ToString(dr["Designation"]),
                        CompanyName = Convert.ToString(dr["CompanyName"])
                    });
            }
            return EmployeeInformation;
        }
    }
}

Now we need database connection so you just follow some steps as I show below.

Step 1: Go to in your Server Explorer and click on the icon as I show on the screen.

Step 2: Now, you have to select your server so you need to enter the name of your server or static IP address of your server if you have. and you just to need your database from the dropdown and click on Ok.

Now, you need to add a connection string in Web.config file to get the database connection.

Web.Config

<connectionStrings>
<add name="Conn" connectionString="Data Source=DESKTOP-P1PHIU6\SQLEXPRESS;Initial Catalog=DB_Codingvila;Integrated Security=True"/>  
</connectionStrings>

Then after you need to write following code in your EmployeeController class.

EmployeeController

using Codingvila.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
 
namespace Codingvila.Controllers
{
    public class EmployeeController : Controller
    {
        //
        // GET: /Employee/
        public ActionResult Index()
        {
            EmployeeDBHandle dbhandle = new EmployeeDBHandle();
            ModelState.Clear();
            return View(dbhandle.GetEmployee());
        }
    }
}

Now, you have to add View and for that, you have to press right click on action method of controller "Index()" and select "Add View".

Now, Add name of the view, Select Empty Template and select name of Model and simply click on Add button to add empty view in your application. If you observe then you can see in your project directory your name of the view is added with the file extension ".cshtml".

Now write following code your view.

Index.cshtml

@model Codingvila.Models.EmployeeModel
 
@{
    Layout = null;
}
 
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Employee Details</title>
</head>
<body>
    <div>
        <h1>Employee Detail</h1>
        <br>
       Employee Name :@Html.DropDownListFor(m => m.EmpId, new SelectList(Model.EmployeeInformation, "EmpId", "FirstName"), "")
    </div>
</body>
</html>

Now finally, you need to do some changes in RouteConfig.cs File where you have to set your controller name, default action method, and id as the optional parameter for page routing and configuring your URL pattern.

Finally, your application is ready and while you run your application you can see the output as I shown below.

Note: your URL pattern must be something like ( http://yourdomain.com/ name of controller/name of action method ) i.g http://codingvila.com/articles/Index/, where articles are the name of my controller and Index, is my action method.

Summary

This article gives an explanation about how to create MVC application and how to bind drop-down list in ASP.NET MVC from Database using a stored procedure.