Introduction
This article gives an explanation about how to populate a cascading dropdown list with angular js and SQL servers using bootstrap 4 in ASP.NET MVC. Here, I'll also explain how to create an entity data model as well as bind the dropdown list using the entity data model in ASP.NET MVC.
In my previous articles, I explained the AngularJS table with bootstrap 4 and how to add and remove row dynamically in angular js table with bootstrap 4 and angularjs pie chart Using highchairs library with example and many other articles on angular that you many like to read, here I'll be going to write another article on angular because of many of beginners requested for posting articles on angular and explain basic information of angular that can help them to build and improve their knowledge in angular and helps to complete their academic projects, so today I am going to explain how to bind or populate a cascading dropdown list from SQL server database with angular js and bootstrap 4.
While you working with any web applications you may have seen such functionality during any data entry form, registration form, product entry form or in any reporting forms.
Let's take one example there is an employee registration form and you have to fill your basic information in that form where few different fields are there in the form that you required to fill such as textbox field for fill your first, last name, email id, mobile number, dropdown fill for choosing your blood group as well as multiline text box for fill your address and then again dropdown field for choosing your country, state, and city.
You may have noticed in few of the forms that dropdown of state and city is in disabled mode and it will enable and disabled based on value selection in previous dropdown list, while you select your country the dropdown list for state is enabled and show the only name of states of selected country in country dropdown, and while you select any state the dropdown list of city will enable and shows the only list of city based on selection of state from the dropdown list of state, while you change country or state it will automatically bind appropriate list of state based on country and appropriate city based on selected state. So, here in this article, we will be doing the same, so you can get more ideas about the process behind it and how to implement such functionality in your ASP.NET MVC application with angular js and bootstrap 4.
Requirement
1) Create tables for the country, state, and city and insert a few dummy entries for demonstration in the SQL server database.
3) Design a web form with 3 different dropdowns for the country, state, and city using bootstrap 4.
4) Bind/populate dropdown of the country while the page load.
5) Bind/populate dropdown of the state based on value selection of country.
6) Bind/populate dropdown of the city based on value selection of state.
Implementation
So, let's start with a simple example as we discussed, so as per the listed requirement above we will create listed tables in the SQL server database.
Create Table Country
CREATE TABLE [dbo].[CountryMaster] ( [ID] INT IDENTITY (1, 1) NOT NULL, [Name] NVARCHAR (50) NULL, [CountryCode] VARCHAR (5) NULL, CONSTRAINT [PK_CountryMaster] PRIMARY KEY CLUSTERED ([ID] ASC) );
Create Table State
CREATE TABLE [dbo].[StateMaster] ( [ID] INT IDENTITY (1, 1) NOT NULL, [Name] NVARCHAR (50) NULL, [CountryID] INT NULL, CONSTRAINT [PK_StateMaster] PRIMARY KEY CLUSTERED ([ID] ASC), CONSTRAINT [FK_StateMaster_CountryMaster] FOREIGN KEY ([CountryID]) REFERENCES [dbo].[CountryMaster] ([ID]) );
Create Table City
CREATE TABLE [dbo].[CityMaster] ( [ID] INT IDENTITY (1, 1) NOT NULL, [Name] NVARCHAR (50) NULL, [StateID] INT NULL, CONSTRAINT [PK_CityMaster] PRIMARY KEY CLUSTERED ([ID] ASC), CONSTRAINT [FK_CityMaster_StateMaster] FOREIGN KEY ([StateID]) REFERENCES [dbo].[StateMaster] ([ID]) );
Before doing entry in the created tables you have to knowledge of created table structure, so I explain to you that we have created 3 different tables as you can see in the script above, here we have created the first table for a country with the name CountryMaster and taken column for the country as ID, Name, and CountryCode where ID is PRIMARY KEY constraint. Then we have created the second table for the state with the name StateMaster where we have taken columns ID, Name and CountryID where ID is a PRIMARY KEY constraint and CountryID is a FOREIGN KEY constraint that REFERENCES by ID column of CountryMaster table. Then we have created the third table for the city with the name CityMaster where we have taken columns ID, Name and StateID where ID is a PRIMARY KEY constraint and StateID is a FOREIGN KEY constraint that REFERENCES by ID column of StateMaster table.
Now, we will do a few dummy entries in the created tables for the country, state, and city as I have shown in the script below.
Insert Entry in Country Table
INSERT [dbo].[CountryMaster] ( [ID],[Name],[CountryCode] ) VALUES ( 1, N'Afghanistan',N'AF' ), ( 2, N'Albania',N'AL' ), ( 3, N'Algeria',N'DZ' ), ( 4, N'American Samoa',N'AS' )
Insert Entry in State Table
INSERT [dbo].[StateMaster] ( [ID], [Name], [CountryID]) VALUES ( 1, N'Badakhshan', 1 ), ( 2, N'Badgis',1 ), ( 3, N'Baglan', 1), ( 4, N'Balkh' 1 )
Insert Entry in City Table
INSERT [dbo].[CityMaster] ( [ID], [Name], [StateID]) VALUES ( 1, N'Eshkashem', 1 ), ( 2, N'Fayzabad',1 ), ( 3, N'Jurm', 1), ( 4, N'Khandud', 1 )
There are so many items in the country, state and city table so, here I can show a list of only a few of them, you just learn how to do entry in the created table.
Now, we will start to populate given dropdowns from SQL server database and our first step is to create an entity data model so, you have to create an entity data model, in your ASP.NET MVC application, If you didn't know about the entity data model then in my next article I'll explain that how to create entity data model, currently, you just follow steps given below.
Create an Entity Data Model
Right-click on your project from solution explorer >> Add >> Add Item >> ADO.NET Entity Data Model and follow further steps and choose tables that you want to include in entity data moded and finish.
Finally your entity data model is looks something like shown below.
Now, You have to write the following code in your home controller, but before that, you have to configure the RouteConfig file as I showed below.
RouteConfig.cs
public static void RegisterRoutes(RouteCollection routes) { routes.IgnoreRoute("{resource}.axd/{*pathInfo}"); routes.MapRoute( name: "Default", url: "{controller}/{action}/{id}", defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional } ); }
HomeController.cs
public class HomeController : Controller { // GET: Home public ActionResult Index() { return View(); } [HttpPost] public JsonResult AjaxDropDownMethod(string type, int value) { List<SelectListItem> items = new List<SelectListItem>(); Codingvila_ExmplesEntities entities = new Codingvila_ExmplesEntities(); switch (type) { default: foreach (var country in entities.CountryMasters.Take(4)) { items.Add(new SelectListItem { Text = country.Name, Value = country.ID.ToString() }); } break; case "CountryId": var states = (from state in entities.StateMasters where state.CountryID == value select state).ToList(); foreach (var state in states) { items.Add(new SelectListItem { Text = state.Name, Value = state.ID.ToString() }); } break; case "StateId": var cities = (from city in entities.CityMasters where city.StateID == value select city).ToList(); foreach (var city in cities) { items.Add(new SelectListItem { Text = city.Name, Value = city.ID.ToString() }); } break; } return Json(items); } }
Explanation
If you analyzed the above code then there are two different action methods are written in the above controller class. The first method for GET operation and the second is for POST, the first method is return view and the second action method AjaxDropDownMethod will be executed when the call is made from AngularJS client in View.
This method AjaxDropDownMethod will be executed in two cases first is when the load event of the page will fire at the same time the dropdown list of the country will populate, and in the second case AjaxDropDownMethod will be executed while selected value change of country and state dropdown list, and populate the dropdown list that will accept type and value as a parameter and fetch records from the database based on parameters and returns to the view in form of JSON string.
Now, lest we start to design view and integrate the application with bootstrap 4, for that we have to write the following code in the Index.cshtml file as shown below.
Before starting the actual design of the web form we have to integrate bootstrap 4 with web application so, first we will link CSS and javascript of bootstrap 4 using bootstrap CDN links, and you have to write following code for the same in-between <head> tag as shown below.
Integrate Bootstrap 4 and link CDN links
<head> <meta name="viewport" content="width=device-width" /> <title>Cascading Dropdown List From SQL Server Using AngularJS and Bootstrap 4</title> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1" /> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css" /> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script> </head>
Index.cshtml
@{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>Cascading Dropdown List From SQL Server Using AngularJS and Bootstrap 4</title> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1" /> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css" /> <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script> <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script> </head> <body class="container"> <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.9/angular.min.js"></script> <script type="text/javascript"> var app = angular.module('MyApp', []) app.controller('CodingvilaController', function ($scope, $http, $window) { $scope.FillDropDown = function (type, value) { switch (type) { default: $scope.CountryId = 0; $scope.CountryLabel = "Loading....."; $scope.Countries = null; break; case "CountryId": $scope.StateId = 0; $scope.StateLabel = "Loading....."; $scope.CityLabel = ""; $scope.States = null; $scope.Cities = null; break; case "StateId": $scope.CityId = 0; $scope.CityLabel = "Loading....."; $scope.Cities = null; break; } $http({ method: "POST", url: "/Home/AjaxDropDownMethod", dataType: 'json', data: '{type: "' + type + '", value: ' + value + '}', headers: { "Content-Type": "application/json" } }).success(function (data, status) { switch (type) { default: $scope.CountryLabel = "Country"; $scope.Countries = data; break; case "CountryId": $scope.StateLabel = ""; if (data.length > 0) { $scope.StateLabel = "State"; $scope.States = data; } break; case "StateId": $scope.CityLabel = ""; if (data.length > 0) { $scope.Cities = data; $scope.CityLabel = "City"; } break; } }).error(function (data, status) { $window.alert(data.Message); }); }; $scope.FillDropDown('', 0); }); </script> <div ng-app="MyApp" ng-controller="CodingvilaController"> <div class="form-row"> <h2 class="form-group text-primary">AngularJS - Cascading Dropdown List</h2> <br /> </div> <div class="form-row"> <div class="form-group col-md-2"> <label for="exampleFormControlSelect1">Country</label> <select class="form-control" data-toggle="dropdown" name="Country" ng-model="CountryId" ng-change="FillDropDown('CountryId', CountryId)"> <option value="0">{{CountryLabel}}</option> <option ng-repeat="item in Countries" value="{{item.Value}}"> {{item.Text}} </option> </select> </div> <div class="form-group col-md-2"> <label for="exampleFormControlSelect1">State</label> <select class="form-control" name="State" ng-model="StateId" ng-change="FillDropDown('StateId', StateId)"> <option value=" 0">{{StateLabel}}</option> <option ng-repeat="item in States" value="{{item.Value}}"> {{item.Text}} </option> </select> </div> <div class="form-group col-md-2"> <label for="exampleFormControlSelect1">City</label> <select class="form-control" name="City"> <option value="0">{{CityLabel}}</option> <option ng-repeat="item in Cities" value="{{item.Value}}"> {{item.Text}} </option> </select> </div> </div> </div> </body> </html>
Explanation
As you can see in the view above, here, we have integrated bootstrap 4 using bootstrap CDN links and the view is designed using simple HTML tags such as division tag, buttons, header tags and few basic CSS classes of bootstrap 4. You can see the division tag <div> contains properties such as ng-app="MyApp" and ng-controller="CodingvilaController" where ng-controller is described as AngularJS Controller and CodingvilaController is the name of AngularJS controller. You can also see in the written script above there is the FillDropDown function that contains $http service that is used for creating an AJAX call to the action method of the controller.
Output
Summary
In this article, we learned how to populate a cascading dropdown list from the SQL Server database with angular js in ASP.NET MVC using bootstrap 4.
