Click here to Skip to main content
15,867,704 members
Articles / Web Development / HTML

Cascading Dropdown List With MVC, LINQ to SQL and AJAX

Rate me:
Please Sign up or sign in to vote.
4.89/5 (46 votes)
13 Nov 2014CPOL5 min read 205.4K   6.5K   48   33
This article explains how to populate a child DropDownList depending on each new value as values are selected in the parent's DropDown List.

Introduction

This article explains how to populate a child dropdown list depending on each new value as values are selected in the parent's DropDown List.

Database Design

I use two tables to populate DropDown Lists. One is a Country table and another is a State table. The Country table is a parent table and the State table is a child table. These tables have primary key and foreign key relationships using the CountryId column.

Relationship between Country and State Table

Figure 1.1 Relationship between Country and State Table 
SQL
CREATE TABLE Country
(
     Id int Primary Key Identity(1,1),
     Name NVarchar(50) Not Null
)
 
CREATE TABLE [State]
(
     Id int Primary Key Identity(1,1),
     CountryId int Not Null,
     Name NVarchar(50) Not Null
)

Create a relationship between Country table and State table using foreign key. It is a one to many relationship where a single country has multiple states.

SQL
ALTER TABLE [State]
ADD CONSTRAINT FK_COUNTRY_STATE FOREIGN KEY(CountryId) REFERENCES
Country(Id) ON DELETE CASCADE

First, populate the parent dropdown list using the Country table then populate the child dropdown list using the State table by the selecting the CountryId from the parent dropdown list.

Create an MVC Application

I will create an MVC application using Visual Studio 2012. So let's see the procedure for creating an MVC application.

  • Step 1

    Go to "File" -> "New" -> "Project...".

  • Step 2

    Choose "ASP.NET MVC 4 Web Application" from the list, then give the application name "CountryStateApplication" and set the path in the location input where you want to create the application.

  • Step 3

    Now choose the Project Template "Empty" and select "Razor" as the view engine from the dropdown list.

Adding a LINQ to SQL Class

Entity classes are created and stored in LINQ to SQL Classes files (.dbml files). The O/R Designer opens when you open a .dbml file. It is a DataContext class that contains methods and properties for connecting to a database and manipulating the data in the database. The DataContext name corresponds to the name that you provided for the .dbml file.

  • Step 1

    Right-click on the Models folder in the Solution Explorer, then go to "Add" and click on "Class.."

  • Step 2

    Choose "LINQ to SQL Classes" from the list and give the name "Address" for the dbml name. After that, click on "Add".

    Create dbml file for Database operation

    Figure 1.2: Create dbml file for Database operation
  • Step 3

    Drag both tables (Country table and State table) from the database in the Server Explorer and drop onto the O/R Designer surface of the "Address.dbml" file.

    Tables in Address.dbml file

    Figure 1.3: Tables in Address.dbml file

Using the Repository Pattern

The Repository Pattern, according to Martin Fowler, is a repository that mediates between the domain and data mapping layers, acting like an in-memory domain object collection. Client objects construct query specifications declaratively and submit them to the repository for satisfaction. Objects can be added to and removed from the repository, since they can form a simple collection of objects, and the mapping code encapsulated by the repository will carry out the appropriate operations behind the scenes. Conceptually, a repository encapsulates the set of objects persisted in a data store and the operations performed over them, providing a more object-oriented view of the persistence layer. The repository also supports the objective of achieving a clean separation and one-way dependency between the domain and data mapping layers. In practice, it is usually a collection of data access services, grouped in a similar way to the domain model classes.

I implement the Repository pattern by defining one repository class for both domain model entities that require specialized data access methods. A repository class contains the specialized data access methods required for its corresponding domain model entity.

Address repository interface and class

Figure 1.4: Address repository interface and class

When you create the repository class, you create an interface that represents all of the methods used by the repository class. Within your controllers, you write your code against the interface instead of the repository. That way, you can implement the repository using various data access technologies in the future. So first of all, you need to create an interface "IAddressRepository" under the Models folder that contains basic access methods for country and states by countryId.

C#
using System.Collections.Generic;
namespace CountryStateApplication.Models
{
     public interface IAddressRepository
    {
         IList<country> GetAllCountries();
         IList<state> GetAllStatesByCountryId(int countryId);
    }
} 

Thereafter, create a repository class "AdderessRepository" that implements the "IAddressRepository" interface under the Models folder.

C#
using System.Collections.Generic;
using System.Linq;

 namespace CountryStateApplication.Models
{
     public class AddressRepository : IAddressRepository
    {
         private AddressDataContext _dataContext;
 
         public AddressRepository()
        {
            _dataContext = new AddressDataContext();
        }
 
         public IList<country> GetAllCountries()
        {          
             var query = from countries in _dataContext.Countries
                         select countries;
             var content = query.ToList<country>();
             return content;           
        }
         public IList<state> GetAllStatesByCountryId(int countryId)
        {
             var query = from states in _dataContext.States
                         where states.CountryId == countryId
                         select states;
             var content = query.ToList<state>();
             return content;
        }
    }
} 

Create Model Class

The MVC Model contains all application logic (business logic, validation logic, and data access logic), except pure view and controller logic. Create an "AddressModel" class under the Models folder and create properties for label field and dropdown list values.

C#
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Web.Mvc; 

namespace CountryStateApplication.Models
{
     public class AddressModel
    {
         public AddressModel()
        {
            AvailableCountries = new List<SelectListItem>();
            AvailableStates = new List<SelectListItem>();
        }
        [Display(Name="Country")]
         public int CountryId { get; set; }
         public IList<SelectListItem> AvailableCountries { get; set; }
        [Display(Name = "State")]
         public int StateId { get; set; }
         public IList<SelectListItem> AvailableStates { get; set; }
    }

Create Controller

You need to create a controller to handle request from the browser. In this application, I created the "AddressController" controller under the Controllers folder with two action methods. One action method "Index" is used to display the view with countries data filled in the country dropdown list by browser request and another action method "GetStatesByCountryId" is used to fill in the state dropdown list based on the selected country from the country dropdown list.

C#
using System;
using System.Linq;
using System.Web.Mvc;
using CountryStateApplication.Models;

namespace CountryStateApplication.Controllers
{
    public class AddressController : Controller
    {
         private IAddressRepository _repository;
 
         public AddressController() : this(new AddressRepository())
        {
        }
 
         public AddressController(IAddressRepository repository)
        {
            _repository = repository;
        }
       public ActionResult Index()
       {
             AddressModel model = new AddressModel();
            model.AvailableCountries.Add(new SelectListItem 
            { Text = "-Please select-", Value = "Selects items" });
             var countries = _repository.GetAllCountries();
             foreach (var country in countries)
            {
                model.AvailableCountries.Add(new SelectListItem()
                {
                    Text = country.Name,
                    Value = country.Id.ToString()
                });
            }
             return View(model);
        }
 
        [AcceptVerbs(HttpVerbs.Get)]
        public ActionResult GetStatesByCountryId(string countryId)
        {            
             if (String.IsNullOrEmpty(countryId))
            {
                 throw new ArgumentNullException("countryId");
            }
             int id = 0;
             bool isValid = Int32.TryParse(countryId, out id);          
            var states = _repository.GetAllStatesByCountryId(id);
             var result = (from s in states
                          select new
                         {
                             id = s.Id,
                             name = s.Name
                         }).ToList();          
             return Json(result, JsonRequestBehavior.AllowGet);
        } 
    }
}

Create Route

You need to create a route to call action method of controller by Ajax so add new route "GetStatesByCountryId" in RouteConfig class (RouteConfig.cs file) under App_Start folder.

C#
using System.Web.Mvc;
using System.Web.Routing;

namespace CountryStateApplication
{
    public class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}"); 
            routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "Address", action = "Index", id = UrlParameter.Optional }
            );

            routes.MapRoute("GetStatesByCountryId",
                            "address/getstatesbycountryid/",
                            new { controller = "Address", action = "GetStatesByCountryId" },
                            new[] { "CountryStateApplication.Controllers" });
        }
    }
}

Create View

A view is used to display data in a browser. I created the "Index.cshtml" view to display data under the Address folder of the View folder. You need to add a reference for jQuery so you can use the Ajax method of jQuery in the view.

HTML
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.1/jquery.min.js"></script>

You need to call the change event of the country dropdown list method when a new country item is selected from the dropdown list. When an item is selected from the dropdown, then an Ajax call is made, on success of the call of the action method, the state dropdown list is filled with data.

HTML
@model CountryStateApplication.Models.AddressModel
@{
    ViewBag.Title = "Index";
}

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.1/jquery.min.js"></script>
<script type="text/javascript">
    $(function () {
        $("#CountryId").change(function () {
                 var selectedItem = $(this).val();
                 var ddlStates = $("#StateId");
              var statesProgress = $("#states-loading-progress");
             statesProgress.show();
             $.ajax({
                 cache: false,
                 type: "GET",
                 url: "@(Url.RouteUrl("GetStatesByCountryId"))",
                    data: { "countryId": selectedItem },
                    success: function (data) {                       
                        ddlStates.html('');
                        $.each(data, function (id, option) {
                            ddlStates.append($('<option></option>').val(option.id).html(option.name));
                        });
                        statesProgress.hide();
                    },
                    error: function (xhr, ajaxOptions, thrownError) {
                        alert('Failed to retrieve states.');
                        statesProgress.hide();
                    }
                });
            });
        });
     </script> 

     @Html.LabelFor(model=>model.CountryId)
     @Html.DropDownListFor(model=>model.CountryId, Model.AvailableCountries)

<br />

     @Html.LabelFor(model => model.StateId)
     @Html.DropDownListFor(model => model.StateId, Model.AvailableStates)
     <span id="states-loading-progress" style="display: none;">Please wait..</span>

Output screen of application

Figure 1.5: Output screen of application

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
India India
He is awarded for Microsoft TechNet Guru, CodeProject MVP and C# Corner MVP. http://l-knowtech.com/

Comments and Discussions

 
AnswerSoultion for cascading drp down Pin
Member 1407240213-Jan-19 6:47
Member 1407240213-Jan-19 6:47 
AnswerCascading drpdown simple code Pin
Member 1407240213-Jan-19 6:43
Member 1407240213-Jan-19 6:43 
QuestionDefine SQL ? Pin
Member 1305063310-Mar-17 0:11
Member 1305063310-Mar-17 0:11 
GeneralMy vote of 5 Pin
Ryan Flemming23-Feb-17 19:56
Ryan Flemming23-Feb-17 19:56 
Questionmvc4 cascading dropdownlist Pin
Member 1232654514-Feb-16 6:00
Member 1232654514-Feb-16 6:00 
QuestionError : Failed to retrieve states [object object] Pin
shitalumare4-Feb-16 4:59
shitalumare4-Feb-16 4:59 
QuestionQuestion about how to set the default value of the 2nd dropdown list Pin
Member 109669831-Oct-15 10:14
Member 109669831-Oct-15 10:14 
AnswerRe: Question about how to set the default value of the 2nd dropdown list Pin
Member 1232654514-Feb-16 6:02
Member 1232654514-Feb-16 6:02 
QuestionMaking it work DB first workflow Pin
Uwakpeter24-Aug-15 20:44
professionalUwakpeter24-Aug-15 20:44 
QuestionVery Useful Pin
Pascualito26-Jul-15 10:08
professionalPascualito26-Jul-15 10:08 
QuestionNull Exception Occur When PostBack!!!!!!!!!!!!! Pin
BlackViking18-Dec-14 10:37
BlackViking18-Dec-14 10:37 
AnswerRe: Null Exception Occur When PostBack!!!!!!!!!!!!! Pin
Sandeep Singh Shekhawat18-Dec-14 15:08
professionalSandeep Singh Shekhawat18-Dec-14 15:08 
QuestionTrying to take this to another level Pin
TedMM16-Dec-14 12:10
TedMM16-Dec-14 12:10 
AnswerRe: Trying to take this to another level Pin
Sandeep Singh Shekhawat16-Dec-14 15:18
professionalSandeep Singh Shekhawat16-Dec-14 15:18 
GeneralRe: Trying to take this to another level Pin
TedMM17-Dec-14 6:24
TedMM17-Dec-14 6:24 
QuestionGetting this error of 'has no key defined' Pin
Member 1118439419-Nov-14 20:38
Member 1118439419-Nov-14 20:38 
QuestionCan you push it further to make it generic and reusable? Pin
Aurimas14-Nov-14 0:34
Aurimas14-Nov-14 0:34 
QuestionSelect a country in a dropdownList and show a list of states in the same page Pin
Member 1123304613-Nov-14 21:32
Member 1123304613-Nov-14 21:32 
QuestionCan't get this to work in my project Pin
Member 1107002420-Sep-14 6:37
Member 1107002420-Sep-14 6:37 
QuestionSubmit the Selections Pin
Brad718-Aug-14 8:04
Brad718-Aug-14 8:04 
AnswerRe: Submit the Selections Pin
Sandeep Singh Shekhawat30-Aug-14 19:14
professionalSandeep Singh Shekhawat30-Aug-14 19:14 
GeneralMy vote of 5 Pin
Brad714-Aug-14 9:09
Brad714-Aug-14 9:09 
SuggestionAlmost excellent Pin
Claire Streb18-Jun-14 4:26
Claire Streb18-Jun-14 4:26 
QuestionRe: Almost excellent Pin
Claire Streb18-Jun-14 4:49
Claire Streb18-Jun-14 4:49 
AnswerRe: Almost excellent Pin
Sandeep Singh Shekhawat30-Aug-14 19:11
professionalSandeep Singh Shekhawat30-Aug-14 19:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.