Post
Ask Question

Populating Cascading Dropdown List From SQL Server Using Angular

Introduction

In my previous article, I explained how you can perform MVC Angular CRUD operation using WEB API 2 with Stored Procedure and in this article, I am going to explain how you can populate Cascading Dropdown List from SQL server database in ASP.Net MVC using Angular.

While working with any web form of master data entry, such as customer registration, product entry, department wise employee registration, and many others; at that time, you may need to implement this kind of functionality within your web forms like to get/save country, state, and city of customer; to get/save the list of employees based on department selection; or to get/save the list of product name based on category and product type etc. So, in this article, I will show you how you can achieve this kind of requirement using AnglerJS in your ASP.NET MVC web application.

Requirement

  1. Create Dropdown Selection for Country, State, and City
  2. Populate the drop-down for Country from SQL Server database at the time of page load.
  3. Populate and select State based on the selected country from the Country drop-down.
  4. Populate and select the City based on the selected state from the State drop-down.

Implementation

First, before we start working on our requirement, we need a database, a few tables, and some dummy records for demonstration. So, here, I will create a database in my SQL Server database engine and also, create 3 different tables within my created database for Country, State, and City and also, insert some dummy records to created tables.

Create Database

  1. CREATE DATABASE db_Dropdown  

 Create Table

Create table for Country

  1. CREATE TABLE [dbo].[Countries](  
  2.     [CountryId] [int] NOT NULL,  
  3.     [CountryName] [varchar](100) NOT NULL,  
  4.  CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED   
  5. (  
  6.     [CountryId] ASC  
  7. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  8. ) ON [PRIMARY]  

Create table for State

  1. CREATE TABLE [dbo].[States](  
  2.     [StateId] [int] NOT NULL,  
  3.     [CountryId] [int] NOT NULL,  
  4.     [StateName] [varchar](100) NOT NULL,  
  5.  CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED   
  6. (  
  7.     [StateId] ASC  
  8. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  9. ) ON [PRIMARY]  

Create table for Cities

  1. CREATE TABLE [dbo].[Cities](  
  2.     [CityId] [int] NOT NULL,  
  3.     [StateId] [int] NOT NULL,  
  4.     [CityName] [varchar](100) NOT NULL,  
  5.  CONSTRAINT [PK_Cities] PRIMARY KEY CLUSTERED   
  6. (  
  7.     [CityId] ASC  
  8. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]  
  9. ) ON [PRIMARY]  

Insert Records

Insert records for Country

  1. INSERT  [dbo].[Countries]  
  2.         ( [CountryId], [CountryName] )  
  3. VALUES  ( 1, N'USA' ),  
  4.         ( 2, N'India' ),  
  5.         ( 3, N'Canada' )  

Insert records for State

  1. INSERT  [dbo].[States]  
  2.         ( [StateId], [CountryId], [StateName] )  
  3. VALUES  ( 1, 1, N'Alabama' ),  
  4.         ( 2, 1, N'Arizona' ),  
  5.         ( 3, 1, N'Alaska' ),  
  6.         ( 4, 2, N'Maharashtra' ),  
  7.         ( 5, 2, N'Gujarat' ),  
  8.         ( 6, 2, N'Goa' ),  
  9.         ( 7, 3, N'Ontario' ),  
  10.         ( 8, 3, N'Quebec' ),  
  11.         ( 9, 3, N'Manitoba' )  

Insert records for Cities

  1. INSERT  INTO Cities  
  2.         ( [CityId], [StateId], [CityName] )  
  3. VALUES  ( 1, 1, N'Abbeville' ),  
  4.         ( 2, 1, N'Argo' ),  
  5.         ( 3, 2, N'Buckeye' ),  
  6.         ( 4, 2, N'Carefree' ),  
  7.         ( 5, 3, N'Juneau' ),  
  8.         ( 6, 3, N'Sitka' ),  
  9.         ( 7, 4, N'Mumbai' ),  
  10.         ( 8, 4, N'Pune' ),  
  11.         ( 9, 5, N'Ahmedabad' ),  
  12.         ( 10, 5, N'Gandhinagar' ),  
  13.         ( 11, 6, N'Panjim' ),  
  14.         ( 12, 6, N'Vasco' ),  
  15.         ( 13, 7, N'Ottawa' ),  
  16.         ( 14, 7, N'Port Hope' ),  
  17.         ( 15, 8, N'Chandler' ),  
  18.         ( 16, 8, N'Princeville' ),  
  19.         ( 17, 9, N'Carman' ),  
  20.         ( 18, 9, N'Roblin' ),  
  21.         ( 19, 5, N'Surat' ),  
  22.         ( 20, 5, 'Rajkot' ),  
  23.         ( 21, 5, 'Jetpur' )  

Now, we will start our example to populate the Cascading drop-down list from the SQL Server database. And our first step is to create Entity Data Model.

Entity Data Model

Entity Data Model 

Now, we will write the following code in the controller.

Controller

  1. using System;  
  2. using System.Linq;  
  3. using System.Web;  
  4. using System.Web.Mvc;  
  5. using System.Collections.Generic;  
  6.   
  7. namespace Cascading_DropDownList_AngularJS_MVC.Controllers  
  8. {  
  9.     public class HomeController : Controller  
  10.     {  
  11.         // GET: Home  
  12.         public ActionResult Index()  
  13.         {  
  14.             return View();  
  15.         }  
  16.   
  17.         [HttpPost]  
  18.         public JsonResult AjaxMethod(string type, int value)  
  19.         {  
  20.             List<SelectListItem> items = new List<SelectListItem>();  
  21.             CascadingEntities entities = new CascadingEntities();  
  22.               
  23.             switch (type)  
  24.             {  
  25.                 default:  
  26.                     foreach (var country in entities.Countries)  
  27.                     {  
  28.                         items.Add(new SelectListItem { Text = country.CountryName, Value = country.CountryId.ToString() });  
  29.                     }  
  30.                     break;  
  31.                 case "CountryId":  
  32.                     var states = (from state in entities.States  
  33.                                   where state.CountryId == value  
  34.                                   select state).ToList();  
  35.                     foreach (var state in states)  
  36.                     {  
  37.                         items.Add(new SelectListItem { Text = state.StateName, Value = state.StateId.ToString() });  
  38.                     }  
  39.                     break;  
  40.                 case "StateId":  
  41.                     var cities = (from city in entities.Cities  
  42.                                   where city.StateId == value  
  43.                                   select city).ToList();  
  44.                     foreach (var city in cities)  
  45.                     {  
  46.                         items.Add(new SelectListItem { Text = city.CityName, Value = city.CityId.ToString() });  
  47.                     }  
  48.                     break;  
  49.             }  
  50.             return Json(items);  
  51.         }  
  52.     }  
  53. }  

If you analyze the written code in our Home Controller, then there are two different action methods - first is the action method for GET operation and the second one is for POST operation, where GET Action method will simply return View. And where the POST Action method will be executed when the call is made from AngularJS and there are two different cases there -

1. When the page load event fires, at that time the Country drop-down List will populate
2. Based on the change event of Country and State, the drop-down list will populate and that will accept the type and value as a parameter and will fetch the records from the database and return to the View in the form of JSON String.

View

  1. @{  
  2.     Layout = null;  
  3. }  
  4.   
  5. <!DOCTYPE html>  
  6.   
  7. <html>  
  8. <head>  
  9.     <meta name="viewport" content="width=device-width" />  
  10.     <title>Index</title>  
  11.     <style type="text/css">  
  12.         body {  
  13.             font-family: Arial;  
  14.             font-size: 12pt;  
  15.             color:#223c88;  
  16.         }  
  17.   
  18.         table td:first-child {  
  19.             padding-right: 10px;  
  20.             font-family: Arial;  
  21.             font-size: 12pt;  
  22.         }  
  23.   
  24.         select {  
  25.             width: 150px;  
  26.             height:30px;  
  27.             color:#223c88;  
  28.             background-color : #ededed;  
  29.             font-family: Arial;  
  30.             font-size: 12pt;  
  31.         }  
  32.     </style>  
  33. </head>  
  34. <body>  
  35.     <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.9/angular.min.js"></script>  
  36.     <script type="text/javascript">  
  37.         var app = angular.module('MyApp', [])  
  38.         app.controller('MyController', function ($scope, $http, $window) {  
  39.             $scope.LoadDropDown = function (type, value) {  
  40.                 switch (type) {  
  41.                     default:  
  42.                         $scope.SelectedCountryId = 0;  
  43.                         $scope.CountryDefaultLabel = "Country Loading.....";  
  44.                         $scope.Countries = null;  
  45.                         break;  
  46.                     case "CountryId":  
  47.                         $scope.SelectedStateId = 0;  
  48.                         $scope.StateDefaultLabel = "State Loading.....";  
  49.                         $scope.CityDefaultLabel = "";  
  50.                         $scope.States = null;  
  51.                         $scope.Cities = null;  
  52.                         break;  
  53.                     case "StateId":  
  54.                         $scope.SelectedCityId = 0;  
  55.                         $scope.CityDefaultLabel = "CityLoading.....";  
  56.                         $scope.Cities = null;  
  57.                         break;  
  58.                 }  
  59.                 $http({  
  60.                     method: "POST",  
  61.                     url: "/Home/AjaxMethod",  
  62.                     dataType: 'json',  
  63.                     data: '{type: "' + type + '", value: ' + value + '}',  
  64.                     headers: { "Content-Type": "application/json" }  
  65.                 }).success(function (data, status) {  
  66.                     switch (type) {  
  67.                         default:  
  68.                             $scope.CountryDefaultLabel = "Select Country";  
  69.                             $scope.Countries = data;  
  70.                             break;  
  71.                         case "CountryId":  
  72.                             $scope.StateDefaultLabel = "";  
  73.                             if (data.length > 0) {  
  74.                                 $scope.StateDefaultLabel = "Select State";  
  75.                                 $scope.States = data;  
  76.                             }  
  77.                             break;  
  78.                         case "StateId":  
  79.                             $scope.CityDefaultLabel = "";  
  80.                             if (data.length > 0) {  
  81.                                 $scope.Cities = data;  
  82.                                 $scope.CityDefaultLabel = "Select City";  
  83.                             }  
  84.                             break;  
  85.                     }  
  86.                 }).error(function (data, status) {  
  87.                     $window.alert(data.Message);  
  88.                 });  
  89.             };  
  90.             $scope.LoadDropDown('', 0);  
  91.         });  
  92.     </script>  
  93.     <div ng-app="MyApp" ng-controller="MyController">  
  94.         <table border="0" cellpadding="0" cellspacing="0">  
  95.             <tr>  
  96.                 <td>Country:</td>  
  97.                 <td>  
  98.                     <select name="Country" ng-model="SelectedCountryId" ng-change="LoadDropDown('CountryId', SelectedCountryId)">  
  99.                         <option value="0">{{CountryDefaultLabel}}</option>  
  100.                         <option ng-repeat="item in Countries" value="{{item.Value}}">  
  101.                             {{item.Text}}  
  102.                         </option>  
  103.                     </select>  
  104.                 </td>  
  105.             </tr>  
  106.             <tr>  
  107.                 <td> </td>  
  108.             </tr>  
  109.             <tr>  
  110.                 <td>State:</td>  
  111.                 <td>  
  112.                     <select name="State" ng-model="SelectedStateId" ng-change="LoadDropDown('StateId', SelectedStateId)">  
  113.                         <option value=" 0">{{StateDefaultLabel}}</option>  
  114.                         <option ng-repeat="item in States" value="{{item.Value}}">  
  115.                             {{item.Text}}  
  116.                         </option>  
  117.                     </select>  
  118.                 </td>  
  119.             </tr>  
  120.             <tr>  
  121.                 <td> </td>  
  122.             </tr>  
  123.             <tr>  
  124.                 <td>City:</td>  
  125.                 <td>  
  126.                     <select name="City">  
  127.                         <option value="0">{{CityDefaultLabel}}</option>  
  128.                         <option ng-repeat="item in Cities" value="{{item.Value}}">  
  129.                             {{item.Text}}  
  130.                         </option>  
  131.                     </select>  
  132.                 </td>  
  133.             </tr>  
  134.         </table>  
  135.     </div>  
  136. </body>  
  137. </html>  

The View will contain simple HTML tags in the form of DIV, FORM, BUTTON, SELECT and etc. Here, Div tag contains properties such as ng-app="MyApp" and ng-controller="MyController" where ng-controller is described as AngularJS Controller and MyController is AngularJS controller name. You can see that within the script, the LoadDropdown function contains $http service that is used for making an AJAX call to the Action method of the Controller.

View also contains 3 different <select> tags - Country, State, and City.

Output

Output 

Summary

The above article explained how to bind/populate Cascading drop-down list from SQL Server database in ASP.NET MVC using AngulerJS.


Similar Articles
Codingvila
Codingvila is an educational website, developed to help tech specialists/beginners.