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] (Step 3: Insert some dummy records in the table.
[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)
);
INSERT INTO Employee_MasterStep 4: Write a Stored Procedure.
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')
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.