Bind Dropdownlist in ASP.NET MVC Using Stored Procedure

Codingvila
5

This article provides an explanation about how to populate a dropdown list from the database using stored procedure in Asp.Net MVC with an 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.

Bind Dropdownlist in ASP.NET MVC  Using Stored Procedure

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 developers/programmers/people 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 are lots 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 purposes, but before that, we will create a database and then create a table and insert some dummy entries and write a stored procedure to get employee data for the 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.

Create ASP.NET MVC 5 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 have shown below.

MVC Web Application

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

MVC Project

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

Structure Of Project

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".

Add Model

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

Employee Model

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

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.

MVC 5 Controller with read/write actions

Now, add the 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.
Name of Controller

Now you have to write the 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 { getset; }
        public int EmpId { getset; }
        public string FirstName { getset; }
        public string Department { getset; }
        public string Designation { getset; }
        public string CompanyName { getset; }
    }
}

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 a database connection so you just follow some steps as I show below.

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

Server Explorer

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

Connect SQL Server Database

Now, you need to add a connection string in the 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 the 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 the action method of the controller "Index()" and select "Add View".

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

Now write the 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.

ASP.NET MVC Page Routing

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.
ASP.NET MVC Bind Dropdownlist

Summary


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

Post a Comment

5 Comments

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

  1. Tried to follow this steps but keep getting Stored Procedure not found error.

    Exception Details: System.Data.SqlClient.SqlException: Could not find stored procedure 'EXEC GetEmployee'.

    I have this in the SQL Stored procedure and able to execute it from SQL Management studio fine.

    ReplyDelete
  2. Will you please check your database connection string.

    ReplyDelete
  3. hello sir,
    i am also getting the same error i.e. stored procedure not found...
    I have checked connection string also...pls help..

    ReplyDelete
  4. please refresh your database or datastore, may changes is not reflected yet....

    ReplyDelete
  5. No need to write "EXEC" in Command only write "Get_Employee" as you have define command type as StoredProcedure.

    ReplyDelete
Join the conversation (5)
To Top