Cascading Dropdown List With MVC, LINQ to SQL and AJAX

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 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 table and State table:

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.

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 a MVC application using Visual Studio 2012. So let's see the procedure for creating a 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".

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.

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.

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.

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.

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 a "AddressModel" class under the Models folder and create properties for label field and dropdown list values.

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 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.

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.

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.

<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 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.

@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>
<div>
     @Html.LabelFor(model=>model.CountryId)
     @Html.DropDownListFor(model=>model.CountryId, Model.AvailableCountries)
</div>
<br />
<div>
     @Html.LabelFor(model => model.StateId)
     @Html.DropDownListFor(model => model.StateId, Model.AvailableStates)
     <span id="states-loading-progress" style="display: none;">Please wait..</span>
</div>

 

Output screen of application.