Cascading DropDown Tutorial
Cascading Drop Down Tutorial
--------------------------------------------------------------------------------------------------------------------------
Database Script -
/****** Object: Database [Cascading_ddl] Script Date: 11/4/2012 4:04:19 PM ******/
CREATE DATABASE [Cascading_ddl]
GO
USE [Cascading_ddl]
GO
CREATE TABLE [dbo].[Countries](
[CountryId] [int] NOT NULL,
[CountryName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED
(
[CountryId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[States](
[StateId] [int] NOT NULL,
[CountryId] [int] NOT NULL,
[StateName] [varchar](100) NOT NULL,
CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED
(
[StateId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[States] WITH CHECK ADD CONSTRAINT [FK_States_Countries] FOREIGN KEY([CountryId])
REFERENCES [dbo].[Countries] ([CountryId])
GO
ALTER TABLE [dbo].[States] CHECK CONSTRAINT [FK_States_Countries]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cities](
[CityId] [int] NOT NULL,
[StateId] [int] NOT NULL,
[CityName] [varchar](100) NOT NULL,
CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED
(
[CityId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Cities] WITH CHECK ADD CONSTRAINT [FK_Cities_States] FOREIGN KEY([StateId])
REFERENCES [dbo].[States] ([StateId])
GO
ALTER TABLE [dbo].[Cities] CHECK CONSTRAINT [FK_Cities_States]
GO
insert into Countries
select 1,'USA'
union all
select 2,'India'
union all
select 3,'Canada'
go
insert into States
select 1,1,'Alabama'
union all
select 2,1,'Arizona'
union all
select 3,1,'Alaska'
union all
select 4,2,'Maharashtra'
union all
select 5,2,'Gujarat'
union all
select 6,2,'Goa'
union all
select 7,3,'Ontario'
union all
select 8,3,'Quebec'
union all
select 9,3,'Manitoba'
go
insert into Cities
select 1,1,'Abbeville'
union all
select 2,1,'Argo'
union all
select 3,2,'Buckeye'
union all
select 4,2,'Carefree'
union all
select 5,3,'Juneau'
union all
select 6,3,'Sitka'
union all
select 7,4,'Mumbai'
union all
select 8,4,'Pune'
union all
select 9,5,'Ahmedabad'
union all
select 10,5,'Gandhinagar'
union all
select 11,6,'Panjim'
union all
select 12,6,'Vasco'
union all
select 13,7,'Ottawa'
union all
select 14,7,'Port Hope'
union all
select 15,8,'Chandler'
union all
select 16,8,'Princeville'
union all
select 17,9,'Carman'
union all
select 18,9,'Roblin'
---------------------------------------------------------------------------------------------------------
CREATE TABLE [dbo].[Employee](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Age] [int] NULL,
[State] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[City] [varchar](50) NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
-------------------------------------------------------------------------------------------------------------
Stored Procedure -
ALTER Procedure [dbo].[InsertUpdateEmployee]
(
@Id integer,
@Name nvarchar(50),
@Age integer,
@State nvarchar(50),
@Country nvarchar(50),
@City nvarchar(50),
@Action varchar(10)
)
As
Begin
if @Action='Insert'
Begin
Insert into Employee(Name,Age,[State],Country,City) values(@Name,@Age,@State,@Country,@City);
End
if @Action='Update'
Begin
Update Employee set Name=@Name,Age=@Age,[State]=@State,Country=@Country,City=@City where EmployeeID=@Id;
End
End
Create Procedure [dbo].[SelectEmployee]
as
Begin
Select * from Employee;
End
--------------------------------------------------------------------------------------------------------------------------
MOdels -
Employee.cs
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Web.Mvc;
namespace CRUDAjax.Models
{
public class Employee
{
public int EmployeeID { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public string StateName { get; set; }
public string CountryName { get; set; }
public string CityName { get; set; }
public Employee()
{
this.Countries = new List<SelectListItem>();
this.States = new List<SelectListItem>();
this.Cities = new List<SelectListItem>();
}
public List<SelectListItem> Countries { get; set; }
public List<SelectListItem> States { get; set; }
public List<SelectListItem> Cities { get; set; }
public int Id { get; set; }
public string Email { get; set; }
public string Address { get; set; }
public int CountryId { get; set; }
public int StateId { get; set; }
public int CityId { get; set; }
}
}
---------------------------------------------------------------------------------------------------------
Indesx.cshtml
@model CRUDAjax.Models.Employee
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<script src="https://code.jquery.com/jquery-3.6.0.js" integrity="sha256-H+K7U5CnXl1h5ywQfKtSj8PCmoN9aaq30gDh27Xc0jk=" crossorigin="anonymous"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">
<!-- Optional theme -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" integrity="sha384-rHyoN1iRsVXV4nD0JutlnGaslCJuC7uwjduW9SVrLvRYooPp2bWYgmgJQIXwl/Sp" crossorigin="anonymous">
<!-- Latest compiled and minified JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>
<script src="~/Scripts/jquery-1.9.1.js"></script>
<script src="~/Scripts/bootstrap.js"></script>
<link href="~/Content/bootstrap.css" rel="stylesheet" />
<script src="~/Scripts/jquery.unobtrusive-ajax.js"></script>
<script src="~/Scripts/employee.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="~/Scripts/jquery-1.9.1.js"></script>
<script src="~/Scripts/bootstrap.js"></script>
<link href="~/Content/bootstrap.css" rel="stylesheet" />
<script src="~/Scripts/jquery.unobtrusive-ajax.js"></script>
<script src="~/Scripts/employee.js"></script>
<script>
$(function () {
$("select").each(function () {
if ($(this).find("option").length <= 1) {
$(this).attr("disabled", "disabled");
}
});
$("select").change(function () {
var value = 0;
if ($(this).val() != "") {
value = $(this).val();
}
var id = $(this).attr("id");
debugger;
$.ajax(
{
type: "POST",
url: "/Home/AjaxMethod",
data: '{type: "' + id + '", value: ' + value + '}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (response) {
var dropDownId;
var list;
switch (id) {
case "CountryId":
list = response.States;
DisableDropDown("#StateId");
DisableDropDown("#CityId");
PopulateDropDown("#StateId", list);
break;
case "StateId":
dropDownId = "#CityId";
list = response.Cities;
DisableDropDown("#CityId");
PopulateDropDown("#CityId", list);
break;
}
},
failure: function (response) {
alert(response.responseText);
},
error: function (response) {
alert(response.responseText);
}
});
});
});
function DisableDropDown(dropDownId) {
$(dropDownId).attr("disabled", "disabled");
$(dropDownId).empty().append('<option selected="selected" value="0">Please select</option>');
}
function PopulateDropDown(dropDownId, list) {
if (list != null && list.length > 0) {
$(dropDownId).removeAttr("disabled");
$.each(list, function () {
$(dropDownId).append($("<option></option>").val(this['Value']).html(this['Text']));
});
}
}
//$(function () {
// if ($("#CountryId").val() != "" && $("#StateId").val() != "" && $("#CityId").val() != "") {
// var message = "Country: " + $("#CountryId option:selected").text();
// message += "\nState: " + $("#StateId option:selected").text();
// message += "\nCity: " + $("#CityId option:selected").text();
// alert(message);
// }
//});
</script>
</head>
<body>
<div class="container">
<h2>Employees Record</h2>
<button type="button" class="btn btn-primary" data-toggle="modal" data-target="#myModal" onclick="clearTextBox();">Add New Employee</button><br /><br />
<table class="table table-bordered table-hover">
<thead>
<tr>
<th>
ID
</th>
<th>
Name
</th>
<th>
Age
</th>
<th>
Country
</th>
<th>
State
</th>
<th>
City
</th>
<th>
Action
</th>
</tr>
</thead>
<tbody class="tbody">
</tbody>
</table>
</div>
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
@*<button type="button" class="close" data-dissmiss="modal"><span aria-hidden="true">×</span></button>*@
<button type="button" class="close" data-dismiss="modal">×</button>
<h4 class="modal-title" id="myModalLabel">Add Employee</h4>
</div>
<div class="modal-body">
<form>
<div class="form-group">
<label for="EmployeeId">ID</label>
<input type="text" class="form-control" id="EmployeeID" placeholder="Id" disabled="disabled" />
</div>
<div class="form-group">
<label for="Name">Name</label>
<input type="text" class="form-control" id="Name" placeholder="Name" />
</div>
<div class="form-group">
<label for="Age">Age</label>
<input type="text" class="form-control" id="Age" placeholder="Age" />
</div>
<br />
@Html.DropDownListFor(m => m.CountryId, Model.Countries, "Please select", new { @class = "form-control" })
<br />
<br />
@Html.DropDownListFor(m => m.StateId, Model.States, "Please select", new { @class = "form-control" })
<br />
<br />
@Html.DropDownListFor(m => m.CityId, Model.Cities, "Please select", new { @class = "form-control" })
<br />
<br />
</form>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-primary" id="btnAdd" onclick="return Add();">Add</button>
<button type="button" class="btn btn-primary" id="btnUpdate" style="display:none;" onclick="Update();">Update</button>
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
</body>
</html>
-------------------------------------------------------------------------------------------------------------------
EmployeeDb.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace CRUDAjax.Models
{
public class EmployeeDB
{
//declare connection string
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
//Return list of all Employees
public List<Employee> ListAll()
{
List<Employee> lst = new List<Employee>();
using(SqlConnection con=new SqlConnection(cs))
{
con.Open();
SqlCommand com = new SqlCommand("SelectEmployee",con);
com.CommandType = CommandType.StoredProcedure;
SqlDataReader rdr = com.ExecuteReader();
while(rdr.Read())
{
lst.Add(new Employee
{
EmployeeID = Convert.ToInt32(rdr["EmployeeId"]),
Name = rdr["Name"].ToString(),
Age = Convert.ToInt32(rdr["Age"]),
StateName= rdr["State"].ToString(),
CountryName = rdr["Country"].ToString(),
CityName = rdr["City"].ToString()
}); ;
}
return lst;
}
}
//Method for Adding an Employee
public int Add(Employee emp)
{
int i;
using(SqlConnection con=new SqlConnection(cs))
{
con.Open();
SqlCommand com = new SqlCommand("InsertUpdateEmployee", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@Id",emp.EmployeeID);
com.Parameters.AddWithValue("@Name", emp.Name);
com.Parameters.AddWithValue("@Age", emp.Age);
com.Parameters.AddWithValue("@State", emp.StateName);
com.Parameters.AddWithValue("@Country", emp.CountryName);
com.Parameters.AddWithValue("@City", emp.CityName);
com.Parameters.AddWithValue("@Action", "Insert");
i = com.ExecuteNonQuery();
}
return i;
}
//Method for Updating Employee record
//public int Update(Employee emp)
//{
// int i;
// using (SqlConnection con = new SqlConnection(cs))
// {
// con.Open();
// SqlCommand com = new SqlCommand("InsertUpdateEmployee", con);
// com.CommandType = CommandType.StoredProcedure;
// com.Parameters.AddWithValue("@Id", emp.EmployeeID);
// com.Parameters.AddWithValue("@Name", emp.Name);
// com.Parameters.AddWithValue("@Age", emp.Age);
// com.Parameters.AddWithValue("@State", emp.StateName);
// com.Parameters.AddWithValue("@Country", emp.CountryName);
// com.Parameters.AddWithValue("@City", emp.CityName);
// com.Parameters.AddWithValue("@Action", "Update");
// i = com.ExecuteNonQuery();
// }
// return i;
//}
//Method for Deleting an Employee
//public int Delete(int ID)
//{
// int i;
// using (SqlConnection con = new SqlConnection(cs))
// {
// con.Open();
// SqlCommand com = new SqlCommand("DeleteEmployee", con);
// com.CommandType = CommandType.StoredProcedure;
// com.Parameters.AddWithValue("@Id", ID);
// i = com.ExecuteNonQuery();
// }
// return i;
//}
}
}
-----------------------------------------------------------------------------------------------------------------------------
using CRUDAjax.Models;
using Newtonsoft.Json;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Mvc;
using System.Web.Services;
namespace CRUDAjax.Controllers
{
public class HomeController : Controller
{
EmployeeDB empDB = new EmployeeDB();
// GET: Home
public ActionResult Index()
{
Employee model = new Employee();
model.Countries = PopulateDropDown("SELECT CountryId, CountryName FROM Countries", "CountryName", "CountryId");
return View(model);
}
public JsonResult List()
{
return Json(empDB.ListAll(), JsonRequestBehavior.AllowGet);
}
public JsonResult Add(Employee emp)
{
return Json(empDB.Add(emp), JsonRequestBehavior.AllowGet);
}
public JsonResult GetbyID(int ID)
{
var Employee = empDB.ListAll().Find(x => x.EmployeeID.Equals(ID));
return Json(Employee, JsonRequestBehavior.AllowGet);
}
//public JsonResult Update(Employee emp)
//{
// return Json(empDB.Update(emp), JsonRequestBehavior.AllowGet);
//}
//public JsonResult Delete(int ID)
//{
// return Json(empDB.Delete(ID), JsonRequestBehavior.AllowGet);
//}
public JsonResult AjaxMethod(string type, int value)
{
Employee model = new Employee();
switch (type)
{
case "CountryId":
model.States = PopulateDropDown("SELECT StateId, StateName FROM States WHERE CountryId = " + value, "StateName", "StateId");
break;
case "StateId":
model.Cities = PopulateDropDown("SELECT CityId, CityName FROM Cities WHERE StateId = " + value, "CityName", "CityId");
break;
}
return Json(model);
}
[HttpPost]
public ActionResult Index(int countryId, int stateId, int cityId)
{
Employee model = new Employee();
model.Countries = PopulateDropDown("SELECT CountryId, CountryName FROM Countries", "CountryName", "CountryId");
model.States = PopulateDropDown("SELECT StateId, StateName FROM States WHERE CountryId = " + countryId, "StateName", "StateId");
model.Cities = PopulateDropDown("SELECT CityId, CityName FROM Cities WHERE StateId = " + stateId, "CityName", "CityID");
return View(model);
}
private static List<SelectListItem> PopulateDropDown(string query, string textColumn, string valueColumn)
{
System.Collections.Generic.List<SelectListItem> items = new List<SelectListItem>();
string constr = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
items.Add(new SelectListItem
{
Text = sdr[textColumn].ToString(),
Value = sdr[valueColumn].ToString()
});
}
}
con.Close();
}
}
return items;
}
}
--------------------------------------------------------------
Unsder Script Folder -
employee.js
/// <reference path="jquery-1.9.1.intellisense.js" />
//Load Data in Table when documents is ready
$(document).ready(function () {
loadData();
});
//Load Data function
function loadData() {
$.ajax({
url: "/Home/List",
type: "GET",
contentType: "application/json;charset=utf-8",
dataType: "json",
success: function (result) {
var html = '';
$.each(result, function (key, item) {
html += '<tr>';
html += '<td>' + item.EmployeeID + '</td>';
html += '<td>' + item.Name + '</td>';
html += '<td>' + item.Age + '</td>';
html += '<td>' + item.CountryName + '</td>';
html += '<td>' + item.StateName + '</td>';
html += '<td>' + item.CityName + '</td>';
html += '<td><a href="#" onclick="return getbyID(' + item.EmployeeID + ')">View</a>';
html += '</tr>';
});
$('.tbody').html(html);
},
error: function (errormessage) {
alert(errormessage.responseText);
}
});
}
//Add Data Function
function Add() {
debugger;
// var res = validate();
//if (res == false) {
// return false;
//}
var empObj = {
EmployeeID: $('#EmployeeID').val(),
Name: $('#Name').val(),
Age: $('#Age').val(),
CountryName: $('#CountryId option:selected').text(), //Reading text box values
StateName: $('#StateId option:selected').text(),
CityName: $('#CityId option:selected').text(),
//CountryId: $('#CountryId').val(),
//StateId: $('#StateId').val(),
//CityId: $('#CityId').val()
};
$.ajax({
url: "/Home/Add",
data: JSON.stringify(empObj),
type: "POST",
contentType: "application/json;charset=utf-8",
dataType: "json",
success: function (result) {
loadData();
$('#myModal').modal('hide');
},
error: function (errormessage) {
alert(errormessage.responseText);
}
});
}
//Function for getting the Data Based upon Employee ID
function getbyID(EmpID) {
debugger;
$('#Name').css('border-color', 'lightgrey');
$('#Age').css('border-color', 'lightgrey');
$('#State').css('border-color', 'lightgrey');
$('#Country').css('border-color', 'lightgrey');
$.ajax({
url: "/Home/getbyID/" + EmpID,
typr: "GET",
contentType: "application/json;charset=UTF-8",
dataType: "json",
success: function (result) {
$('#EmployeeID').val(result.EmployeeID);
$('#Name').val(result.Name);
$('#Age').val(result.Age);
$('#StateId option:selected').text(result.StateName);
$('#CountryId option:selected').text(result.CountryName);
$('#CityId option:selected').text(result.CityName);
$('#myModal').modal('show');
$('#btnAdd').hide();
},
error: function (errormessage) {
alert(errormessage.responseText);
}
});
return false;
}
-----------------------------------------------------------------------------------------------
Comments
Post a Comment