Thursday, May 26, 2011

Cascading DropDownList using ASP.NET MVC JsonResult and jQuery

Model:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;

namespace TestDropdownList.Models
{

    public class Person
    {
        
        public int PersonId { get; set; }
        
        public string PersonName { get; set; }
        
        [   Display(Name="Country"),
            Required
        ]   public string CountryCode { get; set; }
        
        [   Display(Name="City"),
            Required
        ]   public string CityCode { get; set; }

    }

    public class Country
    {
        public string CountryCode { get; set; }
        public string CountryName { get; set; }
    }

    public class City
    {
        public string CountryCode { get; set; }
        public string CityCode { get; set; }
        public string CityName { get; set; }
    }


}

View:
@model TestDropdownList.Models.Person
           

<script src="/Scripts/jquery-1.5.1.min.js" type="text/javascript"></script>
@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>



@using (Html.BeginForm())
{    
    
    if (Model.PersonId != 0)
    {
        <text>Editing: </text> @Model.PersonName
    }
    
    <table>

    <tr>
    <td>@Html.LabelFor(x => x.CountryCode)</td>
    <td style="width: 100px">@Html.DropDownListFor(x => x.CountryCode, new List<SelectListItem>(), new { TheOriginalValue = Model.CountryCode })</td>
    </tr>
    
    
    <tr>
    <td>@Html.LabelFor(x => x.CityCode)</td>
    <td style="width: 100px">@Html.DropDownListFor(x => x.CityCode, new List<SelectListItem>(), new { TheOriginalValue = Model.CityCode })</td>
    </tr>      
    
    </table>
}
    


<script type="text/javascript">
    $(function () {


        var countryCodeInitialValue = $('#CountryCode').attr('TheOriginalValue');
        PopulateCountry(countryCodeInitialValue, function () {
            var cityCodeInitialValue = $('#CityCode').attr('TheOriginalValue');
            $('#CityCode').val(cityCodeInitialValue);            
        });





        $('#CountryCode').change(function () {
            // alert($(this).val() + ": " + $('option:selected', $(this)).text());


            // Why use this?
            // alert($('option:selected', '#CountryCode').val());

            // Or this?
            // alert($('option:selected', $(this)).val());


            // When this will suffice?
            PopulateFromCountry($(this).val());
        });


        /////////////////


        function PopulateCountry(countryCode, doneCallback) {
            $.ajax({ url: '/Home/CountryList/',
                type: 'POST',

                dataType: 'json',
                success: function (data) {

                    var options = $('#CountryCode');
                    $.each(data, function () {
                        options.append($('<option />').val(this.CountryCode).text(this.CountryName));
                    });



                    if (countryCode != "") {
                        $(options).val(countryCode);
                    }
                    else {
                        countryCode = $(options).val();
                    }

                    PopulateFromCountry(countryCode, doneCallback);

                } // ajax callback

            }); // ajax call

        } // PopulateCountry()


        function PopulateFromCountry(countryCode, doneCallback) {

            $.ajax({ url: '/Home/CityList/',
                type: 'POST',
                data: { CountryCode: countryCode }, // parameter on CityList method

                dataType: 'json',
                success: function (data) {

                    var options = $('#CityCode');
                    $('option', options).remove(); // will remove all cities

                    // repopulate all cities
                    $.each(data, function () {
                        options.append($('<option />').val(this.CityCode).text(this.CityName));
                    });


                    if (doneCallback != undefined)
                        doneCallback();


                } // ajax callback

            }); // ajax call

        } // PopulateFromCountry()

    }); //jquery ready

</script>

Controller:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using TestDropdownList.Models;

namespace TestDropdownList.Controllers
{
    public class HomeController : Controller
    {
        public static IList<Person> _persons = new List<Person>()
        {
            new Person{ PersonId = 1, PersonName = "Michael", CityCode = "MNL" },
            new Person{ PersonId = 2, PersonName = "Linus", CityCode = "ALB" },            
            new Person{ PersonId = 3, PersonName = "John", CityCode = "SHA" }
        };

        public ViewResult Index()
        {                        
            return View(new Person());
        }

        public ViewResult Edit(int id)
        {

            Person personToEdit = (from p in _persons where p.PersonId == id select p).Single();
            personToEdit.CountryCode = (from c in Cities where c.CityCode == personToEdit.CityCode select c.CountryCode).Single();
            return View("Index", personToEdit);
        }

        [HttpPost]
        public JsonResult CountryList()
        {            
            // normally, you pass a list obtained from ORM or ADO.NET DataTable or DataReader
            return Json(Countries);
        }

        [HttpPost]
        public JsonResult CityList(string CountryCode)
        {
            // normally, you pass a list obtained from ORM or ADO.NET DataTable or DataReader
            return Json(from c in Cities 
                        where c.CountryCode == CountryCode 
                        select new { c.CityCode, c.CityName });
            
        }

        // MOCK DATA //


        public List<Country> Countries
        {
            get
            {
                return new List<Country>()
                {
                    new Country { CountryCode = "PH", CountryName = "Philippines" },
                    new Country { CountryCode = "CN", CountryName = "China" },
                    new Country { CountryCode = "CA", CountryName = "Canada" },
                    new Country { CountryCode = "JP", CountryName = "Japan" }
                };
            }
        }//Countries



        public List<City> Cities 
        {
            get 
            {
                return new List<City>()
                {
                    new City { CountryCode = "PH", CityCode = "MNL", CityName = "Manila" },
                    new City { CountryCode = "PH", CityCode = "MKT", CityName = "Makati" },
                    new City { CountryCode = "PH", CityCode = "CBU", CityName = "Cebu" },

                    new City { CountryCode = "CN", CityCode = "BEI", CityName = "Beijing" },
                    new City { CountryCode = "CN", CityCode = "SHA", CityName = "Shanghai" },

                    new City { CountryCode = "CA", CityCode = "TOR", CityName = "Toronto" },
                    new City { CountryCode = "CA", CityCode = "MAN", CityName = "Manitoba" },
                    new City { CountryCode = "CA", CityCode = "ALB", CityName = "Alberta" },
                    new City { CountryCode = "CA", CityCode = "VAN", CityName = "Vancouver" },

                    new City { CountryCode = "JP", CityCode = "TOK", CityName = "Tokyo" }
                    
                };
            }
        }//Cities      

    }//HomeController
}


On the controller code, you can see that we retrieve the CountryCode based on Person's CityCode, you need to do this if your database design is heavily normalized, e.g. you don't store the CountryCode on Person table, as you can query it in City table anyhow.

To edit Linus for example, you type this in URL: http://localhost:1232/Home/Edit/2, Alberta will be retrieved for City and Canada for its Country:








Likewise if you type this in URL: http://localhost:1232/Home/Edit/3, the app will retrieve John and obtain Shanghai for City and China for Country






At first, I did this on View:

var countryCodeInitialValue = $('#CountryCode').attr('TheOriginalValue');
PopulateCountry(countryCodeInitialValue);

var cityCodeInitialValue = $('#CityCode').attr('TheOriginalValue');
$('#CityCode').val(cityCodeInitialValue);            


But it may not work, there's no guarantee that the population of City(done asynchronously too) is already done when you call the PopulateCountry. I forgot that the fetching of list are done asynchronously, so in order to wait for the completion of list population, we must provide a callback so we can do the necessary code when the fetching is completed.

This is the corrected code:

var countryCodeInitialValue = $('#CountryCode').attr('TheOriginalValue');
PopulateCountry(countryCodeInitialValue, function () {
    var cityCodeInitialValue = $('#CityCode').attr('TheOriginalValue');
    $('#CityCode').val(cityCodeInitialValue);            
});


You can get working demo from the code's SVN here: http://code.google.com/p/jquery-dynamic-dropdown-list-demo/source/browse/

Download code here: http://code.google.com/p/jquery-dynamic-dropdown-list-demo/downloads/list

23 comments:

  1. Hi there just wonder how about if I don't wan use the Mock data since i already setup my own data. thank.

    ReplyDelete
    Replies
    1. Just change the Linq's source to the ORM of your choice. If you are using NHibernate for example, you use this:

      return Json(

      from c in session.Query<City>
      where c.CountryCode == CountryCode
      select new { c.CityCode, c.CityName }

      );

      If you are using Entity Framework, you can use this:


      return Json(

      from c in NorthwindDbContext.Cities
      where c.CountryCode == CountryCode
      select new { c.CityCode, c.CityName }

      );

      Delete
    2. I get it work in my dummies testing but it don't work when it get into my real project.
      [HttpPost]
      public JsonResult CollegeSystemList()
      {
      // normally, you pass a list obtained from ORM or ADO.NET DataTable or DataReader
      return Json(db.CollegeSystems);
      //return Json(CSystems);
      }
      [HttpPost]
      public JsonResult ModuleList(int CollegeSystemID)
      {
      // normally, you pass a list obtained from ORM or ADO.NET DataTable or DataReader
      return Json(from c in db.CollegeSystemModules
      where c.CollegeSystemID == CollegeSystemID
      select new { c.CollegeSystemModuleID, c.ModuleName });
      }

      It don't pick the db.CollegeSystems data at all but i used the same coding it worked in the dummies. Because in my real project the CollegeSystem have relationship to other tables does it mather?

      Delete
    3. "Because in my real project the CollegeSystem have relationship to other tables does it matter?"


      That might be, as Json might not have a capability to materialize the whole object graph (relationships)


      Try to flatten out the result, e.g.:

      db.CollegeSystems.Select(x => new { x.CollegeId, x.CollegeName });


      That is at least akin to doing SELECT x.CollegeId, x.CollegeName FROM tbl rather than SELECT * FROM tbl


      Hmm.. I never tried passing a live object(from ORMs) to Json method. It might be that dummies(in-memory structure, e.g. List<CollegeSystem>) is working, as the elements are materialized to objects already


      Just select which properties you need in your CollegeSystemList, just like what you did in ModuleList

      Delete
    4. yeah it work now thank a lot :) all i need is db.CollegeSystems.Select(x => new { x.CollegeId, x.CollegeName }); to make it select the data i want it to display :)

      Delete
  2. is it possible to make json that bind the data that based on select for edit? because when I click Edit at the moment it will select the first option from the list.

    ReplyDelete
    Replies
    1. Yes, it's possible. But since we are dynamically populating the dropdown list, we cannot indicated in advance which OPTION tag has SELECTED attribute. So we will just assign the value from database to the SELECT tag's attribute, make your own attribute name. For example, let's name it TheOriginalValue. You introduce this mechanism in the htmlAttributes parameter of Html.DropDownListFor

      Example:


      <td style="width: 100px">@Html.DropDownListFor(x => x.CountryCode, new List<SelectListItem>(), new { TheOriginalValue = Model.CountryCode })</td>


      Then on the PopulateCountry, instead of this:


      var firstCountry = $('option:first', options).val();
      PopulateFromCountry(firstCountry);


      We do this instead:


      var theOriginalValue = $(options).attr('TheOriginalValue');
      var startValue = "";
      if (theOriginalValue != "") {
      // alert(theOriginalValue);
      $(options).val(theOriginalValue);
      startValue = theOriginalValue;
      }
      else {
      startValue = $('option:first', options).val();
      }
      PopulateFromCountry(startValue);



      Note the code's use of jQuery's attr method to extract the SELECT tag's attribute's value. If you are curious how the HTML will look like (from View Source) when the model's values are from Edit, it shall look like this:


      <td style="width: 100px"><select TheOriginalValue="CN" data-val="true" data-val-required="The Country field is required." id="CountryCode" name="CountryCode"></select></td>


      Note the attribute TheOriginalValue of the SELECT tag, it's pre-populated, typically comes from Edit

      Delete
    2. This comment has been removed by the author.

      Delete
  3. Hi there I got another problem I managed to get the it work for my country but the based selected of city always the same for example the original country is Philippines and the City is Cebu but when I clicked on edit it will always display as Philippines and Manila. Do I have to do another Populate for it? or ....

    ReplyDelete
    Replies
    1. i do this to selected the city but it still it don't any idea what i did wrong? it work on the country tho.
      under Function
      function PopulateFromCountry(countryCode) {
      ............
      var theOriginalValue = $(options).attr('TheOriginalValue');
      var startValue = "";
      if (theOriginalValue != "") {
      // alert(theOriginalValue);
      $(options).val(theOriginalValue);
      startValue = theOriginalValue;
      }
      else {
      startValue = $('option:first', options).val();
      }
      PopulateFromCountry(startValue);
      }

      Delete
    2. figured it out I forget change the last polulate that why it not picking up.

      Delete
  4. Hi Michael,
    Can tell me if I want to do two dropdown boxes that base on 1 populate how can I do that? Do I do it under same JScript? SO far I added another Functions due to its base on difference ID. But it only picking up the last function.

    Thank for helping.

    From:
    Alex

    ReplyDelete
  5. This is what i got so far but don't seem working :S
    function PopulateCountry() {
    .........................................
    } // PopulateCountry()

    function PopulateFromCountry(countryCode) {
    ..............................................................
    } // PopulateFromCountry()

    function PopulateFromCountry(AreayCode) {
    ....................................................
    } // PopulateFromCountry()

    ReplyDelete
    Replies
    1. Hi Alex, I already corrected the new code, re-read this post, especially the Edit controller action and the jQuery code in the View

      If the Model comes from Edit, the right Country and City will now be displayed accordingly. I uploaded the code to http://code.google.com/p/jquery-dynamic-dropdown-list-demo/source/browse/

      Delete
    2. This is not what I trying To do. what I want is when user select a country, then it will show the city and the area code which belong to this country such as Country: China/ City: Shanghai / AreaCode: 008621.

      Delete
    3. forget to mention that AreaCode is belong to other Database.
      Model:
      public class Country
      {
      public string CountryCode { get; set; }
      public string CountryName { get; set; }
      public string AreaCode { get; set; }
      }

      public class City
      {
      public string CountryCode { get; set; }
      public string CityCode { get; set; }
      public string CityName { get; set; }
      }

      public class Area
      {
      public string CountryCode { get; set; }
      public string AreaCode { get; set; }
      }

      Delete
    4. function PopulateFromCountry(CountryCode, AreaCode) {
      $.ajax({ url: '/Home/CityList/',
      type: 'POST',
      data: { CountryCode: countryCode },
      dataType: 'json',
      success: function (data) {
      -- var option here --
      });
      }
      });
      $.ajax({ url: '/Home/AreaList/',
      type: 'POST',
      data: { AreaCode: AreaCode},
      dataType: 'json',
      success: function (data) {
      -- var option here --
      });
      }
      });
      }
      But it only work the CountryCode is working but AreaCode work after the CountryCode are removed. Any idea? Thank

      Delete
  6. Hi Michael , I tried add an other dropdown that based on same populate as Alex do but but it give me the wrong populate result its use other my AreaID as binding for 2nd one which i get the result from CompanyDB is it because its multiply populate? It have Country, City, Area and Company tables.

    ReplyDelete
    Replies
    1. the issue i got is when i select Country2 it should display Country2City1-10 which working but the company will display as CountryXAreaXCompany which is wrong. Any idea why? im using the same codes expect i changed the ID from CountryID to AreaID which from my CompanyDB. Many thank.

      Delete
    2. um.. i found out the problem its because its using the CountryID as the ID where can i change it?

      Delete
  7. This is very informative article. Thanks for sharing with us. Below links a very helpful to complite my task.

    http://www.mindstick.com/Articles/65decad5-92c7-4bfc-bfb5-22b04bf6a1ab/?Cascading%20Dropdown%20list%20in%20ASP%20Net%20MVC

    http://blogs.msdn.com/b/rickandy/archive/2012/01/09/cascasding-dropdownlist-in-asp-net-mvc.aspx

    ReplyDelete
  8. i have three cascade dropdown boxes how to populate third dropdown based on second dropdown. kindly help me.

    ReplyDelete