In the previous article, we covered how to retrieve data from a database using jQuery Ajax in ASP.NET MVC. In this article, we'll delve into creating an MVC Cascading Dropdown using jQuery AJAX. This involves cascading one dropdown list with another dropdown list in ASP.NET MVC using jQuery AJAX. Specifically, we'll work with three dropdown lists: Organization, Employees, and Employee Address.
We'll demonstrate how to populate the Employees dropdown list based on the selection made in the Organization dropdown list. Additionally, we'll populate the Employee Address dropdown list based on the selected Employee.
Step 1: Create Database Table
Let's create the database tables required for our task of implementing cascading dropdown lists in ASP.NET MVC using jQuery AJAX. We'll create three tables: "Organization" with columns Id and Name, "Employees" with columns Id, Name, Salary, and OrganizationId, and "Employee_address" with columns Id, Address, City, ZipCode, and EmployeeId.
You can find the SQL Script of the table below.
/****** Object: Table [dbo].[Employee_address] Script Date: 2/11/2022 2:51:52 PM ******/
CREATE TABLE [dbo].[Employee_address](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Address] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[ZipCode] [int] NULL,
[EmployeeId] [int] NULL,
CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED
(
[Id] ASC
))
/****** Object: Table [dbo].[Employees] Script Date: 2/11/2022 2:51:52 PM ******/
CREATE TABLE [dbo].[Employees](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Salary] [float] NULL,
[OrganizationId] [int] NULL,
CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED
(
[Id] ASC
) ON [PRIMARY])
/****** Object: Table [dbo].[Organization] Script Date: 2/11/2022 2:51:52 PM ******/
CREATE TABLE [dbo].[Organization](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](50) NULL,
CONSTRAINT [PK_Organization] PRIMARY KEY CLUSTERED
(
[Id] ASC
))
GO
SET IDENTITY_INSERT [dbo].[Employee_address] ON
GO
INSERT [dbo].[Employee_address] ([Id], [Address], [City], [ZipCode], [EmployeeId]) VALUES (1, '3899 Grey Fox Farm Road', 'Houston', 20120, 1)
GO
INSERT [dbo].[Employee_address] ([Id], [Address], [City], [ZipCode], [EmployeeId]) VALUES (2, '4790 Happy Hollow Road', 'Wilmington', 28405, 2)
GO
INSERT [dbo].[Employee_address] ([Id], [Address], [City], [ZipCode], [EmployeeId]) VALUES (3, '742 Arbutus Drive', 'Miami', 33145, 3)
GO
INSERT [dbo].[Employee_address] ([Id], [Address], [City], [ZipCode], [EmployeeId]) VALUES (4, '1852 Willow Greene Drive', 'Montgomery', 5522, 4)
GO
INSERT [dbo].[Employee_address] ([Id], [Address], [City], [ZipCode], [EmployeeId]) VALUES (5, '7742 Arbutus Drive', 'Miami', 33145, 5)
GO
INSERT [dbo].[Employee_address] ([Id], [Address], [City], [ZipCode], [EmployeeId]) VALUES (6, '742 Drive', 'Miami', 33145, 6)
GO
SET IDENTITY_INSERT [dbo].[Employee_address] OFF
GO
SET IDENTITY_INSERT [dbo].[Employees] ON
GO
INSERT [dbo].[Employees] ([Id], [Name], [Salary], [OrganizationId]) VALUES (1, 'Mark', 6000, 1)
GO
INSERT [dbo].[Employees] ([Id], [Name], [Salary], [OrganizationId]) VALUES (2, 'Henry', 3000, 1)
GO
INSERT [dbo].[Employees] ([Id], [Name], [Salary], [OrganizationId]) VALUES (3, 'Thomas', 4000, 2)
GO
INSERT [dbo].[Employees] ([Id], [Name], [Salary], [OrganizationId]) VALUES (4, 'Jack', 6000, 2)
GO
INSERT [dbo].[Employees] ([Id], [Name], [Salary], [OrganizationId]) VALUES (5, 'Tim', 5000, 3)
GO
INSERT [dbo].[Employees] ([Id], [Name], [Salary], [OrganizationId]) VALUES (6, 'Luis', 4000, 3)
GO
INSERT [dbo].[Employees] ([Id], [Name], [Salary], [OrganizationId]) VALUES (7, 'Southy', 6000, 3)
GO
SET IDENTITY_INSERT [dbo].[Employees] OFF
GO
SET IDENTITY_INSERT [dbo].[Organization] ON
GO
INSERT [dbo].[Organization] ([Id], [CompanyName]) VALUES (1, 'Microsoft')
GO
INSERT [dbo].[Organization] ([Id], [CompanyName]) VALUES (2, 'Google')
GO
INSERT [dbo].[Organization] ([Id], [CompanyName]) VALUES (3, 'Apple')
GO
SET IDENTITY_INSERT [dbo].[Organization] OFF
GO
ALTER TABLE [dbo].[Employee_address] WITH CHECK ADD CONSTRAINT [FK_Departments_Employees] FOREIGN KEY([EmployeeId])
REFERENCES [dbo].[Employees] ([Id])
GO
ALTER TABLE [dbo].[Employee_address] CHECK CONSTRAINT [FK_Departments_Employees]
GO
ALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_Organization] FOREIGN KEY([OrganizationId])
REFERENCES [dbo].[Organization] ([Id])
GO
ALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Organization]
GO
Step 2 :Open your VS19 or VS22, create an empty MVC website if don’t know how to create an empty project then please read below basic crud operation article.
Step 3 : we are using Entity Framework for Cascading dropdown
In Model Folder, right-click then select Add New Item -> Data option on the left side menu. select ADO .net entity framework.
and follow the below step.
Step 4: Adding HomeController in our project
Now, let's add a HomeController to our project to display the list. Right-click on the Controller folder in the project and select 'Add' => 'Controller' => 'Empty Controller'.
Next, open HomeController.cs and write the code for our controller, adding the following action methods. Here, I'll add one action method to the controller that returns the Index view.
- HomeController Class:This class inherits from the Controller class, which is a base class for MVC controller classes.It includes an instance of the DemoDatabaseEntities class, which is typically an Entity Framework DbContext class representing the database context.
- Index Action Method: responsible for rendering the Index view.It retrieves a list of organizations from the database and passes it to the view.
- GetEmployeedByOrganizationIdId Action Method: decorated with the [HttpPost] attribute, indicating that it responds to HTTP POST requests.It accepts an OrganizationId parameter sent via POST request.It queries the database to retrieve a list of employees belonging to the specified organization.The retrieved employee data is projected into an anonymous type containing Id and Name fields.The result is serialized into JSON format and returned as a JsonResult.
- GetEmployeeAddressBydId Action Method: Similar to the previous action method, this one also responds to HTTP POST requests.It accepts an EmployeeId parameter sent via POST request.It queries the database to retrieve a list of addresses associated with the specified employee.The retrieved address data is projected into an anonymous type containing Id and Address fields.The result is serialized into JSON format and returned as a JsonResult.
Handle AJAX requests sent from the client-side JavaScript to retrieve specific data from the server based on the provided parameters, such as OrganizationId and EmployeeId. The retrieved data is then serialized into JSON format and sent back to the client for further processing.
Homecontroller.cs
using Cascading.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace Cascading.Controllers
{
public class HomeController : Controller
{
DemoDatabaseEntities context = new DemoDatabaseEntities();
// GET: Home
public ActionResult Index()
{
var Organizations = context.Organizations.ToList();
return View(Organizations);
}
//retrun all employee by OrganizationsId
[HttpPost]
public JsonResult GetEmployeedByOrganizationIdId(int OrganizationId)
{
var Employees = (from r in context.Employees
where r.OrganizationId == OrganizationId
select new
{
r.Id,
r.Name
}).ToList();
return Json(Employees, JsonRequestBehavior.AllowGet);
}
//return all employee address because a employee can have multiple address
[HttpPost]
public JsonResult GetEmployeeAddressBydId(int EmployeeId)
{
var EmployeesAddress = (from r in context.Employee_address
where r.EmployeeId == EmployeeId
select new
{
r.Id,
r.Address
}).ToList();
return Json(EmployeesAddress, JsonRequestBehavior.AllowGet);
}
}
}Step 5: Create copy paste the below code in Index.cshtml view
@model IEnumerable<Cascading.Models.Organization>
@{
ViewBag.Title = "Index";
}
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<h2>Cascading Dropdownlist Example</h2>
<div>
<label>Organizations</label>
<select id="ddlOrganization" onchange="LoadEmployees()">
<option>Select Organization</option>
@if (Model != null && Model.Count() > 0)
{
foreach (var item in Model)
{
<option value="@item.Id">@item.CompanyName</option>
}
}
</select>
<br />
<br />
<label>Employees</label>
<select id="ddlEmployees" onchange="LoadEmployeesAddress()">
</select>
<br />
<br />
<label>Employees Address</label>
<select id="ddlAddress">
</select>
</div>
<script type="text/javascript">
function LoadEmployees()
{
var OrzanId = $("#ddlOrganization").val();
if (OrzanId) {
$.ajax({
type: "POST",
url: "/Home/GetEmployeedByOrganizationIdId",
data: { OrganizationId: OrzanId},
success: function (empdata) {
var ddlEmployees = $("#ddlEmployees");
ddlEmployees.empty()
$("#ddlAddress").empty();
if (empdata) {
ddlEmployees.append($('<option/>', {
value: 0,
text: "Select a employees"
}));
$.each(empdata, function (index, empData) {
ddlEmployees.append($('<option/>', {
value: empData.Id,
text: empData.Name
}));
});
}
}
,
error: function (ex) {
alert(ex);
}
});
}
}
function LoadEmployeesAddress() {
var EmployeesId = $("#ddlEmployees").val();
if (EmployeesId) {
$.ajax({
type: "POST",
url: "/Home/GetEmployeeAddressBydId",
data: { EmployeeId: EmployeesId },
success: function (empaddressdata) {
var ddlAddress = $("#ddlAddress");
ddlAddress.empty()
if (empaddressdata) {
$.each(empaddressdata, function (index, empaddData) {
ddlAddress.append($('<option/>', {
value: empaddData.Id,
text: empaddData.Address
}));
});
}
}
,
error: function (ex) {
alert(ex);
}
});
}
}
</script>
- The Organizations dropdown list is populated with options retrieved from the model passed to the view.
- The onchange event of the Organizations dropdown list triggers the LoadEmployees() JavaScript function.
- The LoadEmployees() function makes an AJAX POST request to the "/Home/GetEmployeedByOrganizationIdId" URL, passing the selected OrganizationId as data.
- Upon successful retrieval of employee data, the Employees dropdown list is populated with the received data.
- Another onchange event handler is defined for the Employees dropdown list, which triggers the LoadEmployeesAddress() function.
- The LoadEmployeesAddress() function makes an AJAX POST request to the "/Home/GetEmployeeAddressBydId" URL, passing the selected EmployeeId as data.
- Upon successful retrieval of employee address data, the Employee Address dropdown list is populated with the received data.
Above code shows you the dynamic population of dropdown lists based on user selections, creating a cascading effect where the selection in one dropdown influences the options available in another dropdown.
Step:6 Build Run Project
