Introduction
![]() |
Bind Dropdownlist in ASP.NET MVC From Database Using Stored Procedure |
Requirement
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
Note: The name of the controller must end with controller i.g EmployeeController, StudentController, ProductController and etc.
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; } } }
Web.Config
<connectionStrings> <add name="Conn" connectionString="Data Source=DESKTOP-P1PHIU6\SQLEXPRESS;Initial Catalog=DB_Codingvila;Integrated Security=True"/> </connectionStrings>
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()); } } }
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>
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.