Problem

I have a requirement to show a cascading dropdownlist based upon the selection of a different state. I am binding all the states of India with a state dropdownlist and on the change of the listed state, all the cities of that state will change and we can choose the city from the next dropdownlist. I need to do this entire operation using the Web API in the MVC.

Overview

In the ASP.Net MVC 5 project template there are various types of techniques by which we can do the database operations like Create, Read, Update and Delete (CRUD) in the web application. We can use the Entity Framework approaches in the controller using an Entity Data Model and display it from the View. We can create the service for doing data operations and using a service reference we can call the operations using JavaScript like Angular, Knockout or jQuery in the web application.

In the previous article Integrating ASP.Net Web API With MVC we have seen how to integrate the API with the MVC project template based an ASP.Net application and we have also done some operations like fetch the data from the database using the API call and using the MVC controller we call the API and return to the View. In this article we will add some more GET operations and display the cities based upon the states. We will fetch the data from the database using the API call and using the MVC controller we will call the API and return the data to the view.

Note

Please go through here before working with this article. This article is not the second part of the previous article. This is the extension part of the previous one.

Prerequisites

In this article the web applications are created with the new Project Templates like ASP.Net Web API 2 and ASP.Net MVC 5 in the Visual Studio 2013, so there are the following prerequisites before getting started:

Getting Started

Let's begin with the following sections:

Working with Database

In this section we will create the table of state and city and insert the state and city in the both tables. We will also create some Stored Procedures for fetching the data from both tables.

Step 1

Create the State and City tables and insert values into both of the tables.

State table

City Table Fields

Step 2

In the City table create the reference between City and State as shown in the following diagram.

City Table with Reference of State

Step 3

Create the Stored Procedure to select the state with the following code:

  1. Create Proc [dbo].[CT_State_Select]
  2. As
  3. Begin
  4. Set NOCOUNT ON
  5. Select State_ID, State_Name
  6. From State (NOLOCK)
  7. Order By State_Name ASC
  8. End

Step 4

Create the Stored Procedure to select the city with the following code:

  1. CREATE PROCEDURE [dbo].[CT_City_Select] (
  2. @StateId Int)
  3. AS
  4. BEGIN
  5. SET NOCOUNT ON
  6. SELECT
  7. City_ID, State_ID, City_Name
  8. FROM City (NOLOCK)
  9. WHERE State_ID=@StateId
  10. ORDER BY City_Name ASC
  11. END

Step 5

In the following code, we will update the previous Stored Procedure to fetch the College Details data:

  1. ALTER Proc [dbo].[CT_CollegeDetails_Select]
  2. As
  3. Begin
  4. Select
  5. c.CollegeID,
  6. c.[CollegeName],
  7. c.[CollegeAddress],
  8. c.[CollegePhone],
  9. c.[CollegeEmailID],
  10. c.[ContactPerson],
  11. c.[State],
  12. c.[City],
  13. s.State_Name,
  14. ci.City_Name
  15. From CollegeDetails c inner join State s on c.State = s.State_ID inner join City ci on c.City = ci.CIty_ID
  16. End

Creating Model

Since we have created the model in the previous application as said in the previous article, in this section we will add some more classes to add a new model. Start with the following procedure.

Step 1

Right-click on the "CollegeTrackerModels" project and add a new class as the named state with the following code:

  1. namespace CollegeTrackerModels
  2. {
  3. /// <summary>
  4. /// class for state
  5. /// </summary>
  6. public class State
  7. {
  8. #region Properties
  9. /// <summary>
  10. /// get and set the State ID
  11. /// </summary>
  12. public int State_ID { get; set; }
  13. /// <summary>
  14. /// get and set the State Name
  15. /// </summary>
  16. public string State_Name { get; set; }
  17. #endregion
  18. }
  19. }

Step 2

Add a new class as the named city with the following code:

  1. namespace CollegeTrackerModels
  2. {
  3. /// <summary>
  4. /// class for City
  5. /// </summary>
  6. public partial class City
  7. {
  8. #region Properties
  9. /// <summary>
  10. /// get and set the city id
  11. /// </summary>
  12. public int City_ID { get; set; }
  13. /// <summary>
  14. /// get and set the state id
  15. /// </summary>
  16. public int State_ID { get; set; }
  17. /// <summary>
  18. /// get and set the city name
  19. /// </summary>
  20. public string City_Name { get; set; }
  21. /// <summary>
  22. /// get and set the value
  23. /// </summary>
  24. public int Value { get; set; }
  25. /// <summary>
  26. /// get and set the text
  27. /// </summary>
  28. public string Text { get; set; }
  29. #endregion
  30. }
  31. }

Step 3

Update the CollegeDetails class with the following code:

  1. namespace CollegeTrackerModels
  2. {
  3. /// <summary>
  4. /// class for College Details
  5. /// </summary>
  6. public class CollegeDetails
  7. {
  8. #region Constructor
  9. public CollegeDetails()
  10. {
  11. this.States = new List<State>();
  12. this.Cities = new List<City>();
  13. }
  14. #endregion
  16. #region Properties
  17. ///<summary>
  18. ///get and set the College ID
  19. ///</summary>
  20. public int CollegeID { get; set; }
  21. ///<summary>
  22. ///get and set the College Name
  23. ///</summary>
  24. [Display(Name = "College Name")]
  25. public string CollegeName { get; set; }
  26. ///<summary>
  27. ///get and set the College Address
  28. ///</summary>
  29. [Display(Name = "College Address")]
  30. public string CollegeAddress { get; set; }
  31. ///<summary>
  32. ///get and set the College Phone
  33. ///</summary>
  34. [Display(Name = "College Phone")]
  35. public string CollegePhone { get; set; }
  36. ///<summary>
  37. ///get and set the College Email ID
  38. ///</summary>
  39. [Display(Name = "College EmailID")]
  40. public string CollegeEmailID { get; set; }
  41. ///<summary>
  42. ///get and set the Contact Person
  43. ///</summary>
  44. [Display(Name = "Contact Person")]
  45. public string ContactPerson { get; set; }
  46. ///<summary>
  47. ///get and set the State Id
  48. ///</summary>
  49. public Int16 StateId { get; set; }
  50. /// <summary>
  51. /// get and set the States
  52. /// </summary>
  53. public IList<State> States { get; set; }
  54. /// <summary>
  55. /// get and set the Cities
  56. /// </summary>
  57. public IList<City> Cities { get; set; }
  58. ///<summary>
  59. ///get and set the City Id
  60. ///</summary>
  61. public Int16 CityId { get; set; }
  62. ///<summary>
  63. ///get and set the status
  64. ///</sumary>
  65. public int Status { get; set; }
  66. /// <summary>
  67. /// get and set the state name
  68. /// </summary>
  69. [Display(Name="State")]
  70. public string State_Name { get; set; }
  71. /// <summary>
  72. /// get and set the city name
  73. /// </summary>
  74. [Display(Name="City")]
  75. public string City_Name { get; set; }
  76. #endregion
  77. }
  78. }

Working with Data Access Layer

In this section we will create some more classes to access the state and city table data.

Step 1

Right-click on the DAL folder and add a new class named "StateDAL" and provide the following code:

  1. using System;
  2. using System.Collections.Generic;
  3. using CollegeTrackerModels;
  4. using System.Configuration;
  5. using System.Data;
  6. using System.Data.SqlClient;
  8. namespace CollegeTrackerCore.DAL
  9. {
  10. public class StateDAL
  11. {
  12. #region Variables
  13. SqlConnection con;
  14. SqlCommand cmd;
  15. SqlDataAdapter adap;
  16. DataTable dt;
  17. DataSet ds;
  18. string connectionstring = ConfigurationManager.ConnectionStrings["CollegeTrackerConnectionString"].ConnectionString;
  19. #endregion
  21. #region Constructor
  22. public StateDAL()
  23. {
  24. con = new SqlConnection(this.connectionstring);
  25. }
  26. #endregion
  28. #region Public Method
  29. /// <summary>
  30. /// This method is used to get all State.
  31. /// </summary>
  32. /// <returns></returns>
  33. public List<State> GetState()
  34. {
  35. List<State> objState = new List<State>();
  36. using (cmd = new SqlCommand("CT_State_Select", con))
  37. {
  38. try
  39. {
  40. cmd.CommandType = CommandType.StoredProcedure;
  41. con.Open();
  42. adap = new SqlDataAdapter();
  43. adap.SelectCommand = cmd;
  44. dt = new DataTable();
  45. adap.Fill(dt);
  47. foreach (DataRow row in dt.Rows)
  48. {
  49. State state = new State();
  50. state.State_ID = Convert.ToInt32(row["State_ID"]);
  51. state.State_Name = row["State_Name"].ToString();
  52. objState.Add(state);
  53. }
  54. }
  55. catch (Exception ex)
  56. {
  57. con.Close();
  58. }
  59. return objState;
  60. }
  61. }
  62. #endregion
  63. }
  64. }

Step 2

Right-click on the DAL folder and add a new class named "CityDAL" and provide the following code:

  1. using System;
  2. using System.Collections.Generic;
  3. using CollegeTrackerModels;
  4. using System.Configuration;
  5. using System.Data;
  6. using System.Data.SqlClient;
  8. namespace CollegeTrackerCore.DAL
  9. {
  10. public class CityDAL
  11. {
  12. #region Variables
  13. SqlConnection con;
  14. SqlCommand cmd;
  15. SqlDataAdapter adap;
  16. DataTable dt;
  17. DataSet ds;
  18. string connectionstring = ConfigurationManager.ConnectionStrings["CollegeTrackerConnectionString"].ConnectionString;
  19. #endregion
  21. #region Constructor
  22. public CityDAL()
  23. {
  24. con = new SqlConnection(this.connectionstring);
  25. }
  26. #endregion
  28. #region Public Method
  29. /// <summary>
  30. /// This method is used to get all cities.
  31. /// </summary>
  32. /// <returns></returns>
  33. public List<City> GetCity(int stateId)
  34. {
  35. List<City> objCity = new List<City>();
  36. using (cmd = new SqlCommand("CT_City_Select", con))
  37. {
  38. cmd.CommandType = CommandType.StoredProcedure;
  39. dt = new DataTable();
  40. cmd.Parameters.AddWithValue("@StateId", stateId);
  41. adap = new SqlDataAdapter();
  42. adap.SelectCommand = cmd;
  43. try
  44. {
  45. con.Open();
  46. adap.Fill(dt);
  47. foreach (DataRow row in dt.Rows)
  48. {
  49. City city = new City();
  50. city.City_ID = Convert.ToInt32(row["City_ID"]);
  51. city.State_ID = Convert.ToInt32(row["State_ID"]);
  52. city.City_Name = row["City_Name"].ToString();
  53. objCity.Add(city);
  54. }
  55. }
  56. catch (Exception ex)
  57. {
  58. throw ex;
  59. }
  60. finally
  61. {
  62. con.Close();
  63. }
  64. return objCity;
  65. }
  66. }
  67. #endregion
  68. }
  69. }

Step 3

Update the "CollegeDAL" method named "GetAllCollegeDetails()" with the following code:

  1. public List<CollegeDetails> GetAllCollegeDetails()
  2. {
  3. List<CollegeDetails> objCollegeDetails = new List<CollegeDetails>();
  4. using (cmd = new SqlCommand("CT_CollegeDetails_Select", con))
  5. {
  6. try
  7. {
  8. cmd.CommandType = CommandType.StoredProcedure;
  9. con.Open();
  10. adap = new SqlDataAdapter();
  11. adap.SelectCommand = cmd;
  12. dt = new DataTable();
  13. adap.Fill(dt);
  15. foreach (DataRow row in dt.Rows)
  16. {
  17. CollegeDetails col = new CollegeDetails();
  18. col.CollegeID = Convert.ToInt32(row["CollegeID"]);
  19. col.CollegeName = row["CollegeName"].ToString();
  20. col.CollegeAddress = row["CollegeAddress"].ToString();
  21. col.CollegePhone = row["CollegePhone"].ToString();
  22. col.CollegeEmailID = row["CollegeEmailID"].ToString();
  23. col.ContactPerson = row["ContactPerson"].ToString();
  24. col.State_Name = row["State_Name"].ToString();
  25. col.City_Name = row["City_Name"].ToString();
  26. objCollegeDetails.Add(col);
  27. }
  28. }
  29. catch (Exception ex)
  30. {
  31. con.Close();
  32. }
  33. return objCollegeDetails;
  34. }
  35. }

Step 4

Add new classes named "StateBLCore" and "CityBLCore" in the BLL folder. Begin with following procedure.

Working with API Project

In this section we will add ome more classes to access the data layer. Start with the following procedure:

Step 1

In the "CollegeTrackerAPI" project go to the Areas folder and right-click on the BLL folder to add two more classes named "CityBL" and "StateBL". Replace the code with the following code:

  1. using CollegeTrackerCore.BLL;
  2. using CollegeTrackerModels;
  3. using System.Collections.Generic;
  5. namespace CollegeTrackerAPI.Areas.BLL
  6. {
  7. internal sealed class CityBL : CityBLCore
  8. {
  9. /// <summary>
  10. /// This method is used to get the CityDetails
  11. /// </summary>
  12. /// <return></return>
  13. internal new List<City> GetCity(int stateId)
  14. {
  15. return base.GetCity(stateId);
  16. }
  17. }
  18. }

  1. using CollegeTrackerCore.BLL;
  2. using CollegeTrackerModels;
  3. using System.Collections.Generic;
  5. namespace CollegeTrackerAPI.Areas.BLL
  6. {
  7. internal sealed class StateBL : StateBLCore
  8. {
  9. /// <summary>
  10. /// This method is used to get the states
  11. /// </summary>
  12. /// <return></return>
  13. internal new List<State> GetState()
  14. {
  15. return base.GetState();
  16. }
  17. }
  18. }

Step 2

Go to the Controllers folder and add a new Web API 2 Controller named "StateDetails". Replace the code with the following code:

  1. using CollegeTrackerAPI.Areas.BLL;
  2. using CollegeTrackerModels;
  3. using System;
  4. using System.Collections.Generic;
  5. using System.Net;
  6. using System.Net.Http;
  7. using System.Web.Http;
  9. namespace CollegeTrackerAPI.Areas.Controller
  10. {
  11. public class StateDetailsController : ApiController
  12. {
  13. #region variable
  14. ///<summary>
  15. ///variable for StateBL
  16. ///</summary>
  17. private StateBL objStateBL;
  18. ///<summary>
  19. /// variable for httpResponseMessage
  20. /// </summary>
  21. HttpResponseMessage response;
  22. # endregion
  24. #region Response Method
  25. ///summary
  26. /// This method is used to fetch StateDetails
  27. /// <summary>
  28. /// <return></return>
  29. [HttpGet, ActionName("GetAllStateDetails")]
  30. public HttpResponseMessage GetAllStatesDetails()
  31. {
  32. objStateBL = new StateBL();
  33. HttpResponseMessage response;
  34. try
  35. {
  36. var detailsResponse = objStateBL.GetState();
  37. if (detailsResponse != null)
  38. response = Request.CreateResponse<List<State>>(HttpStatusCode.OK, detailsResponse);
  39. else
  41. response = new HttpResponseMessage(HttpStatusCode.NotFound);
  42. }
  43. catch (Exception ex)
  44. {
  45. response = Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message);
  47. }
  48. return response;
  49. }
  50. #endregion
  51. }
  52. }

Step 3

Add a new Controller named "CityDetails" and add the following code:

  1. using CollegeTrackerAPI.Areas.BLL;
  2. using CollegeTrackerModels;
  3. using System;
  4. using System.Collections.Generic;
  5. using System.Net;
  6. using System.Net.Http;
  7. using System.Web.Http;
  9. namespace CollegeTrackerAPI.Areas.Controller
  10. {
  11. public class CityDetailsController : ApiController
  12. {
  13. #region variable
  14. ///<summary>
  15. ///variable for CityBL
  16. ///</summary>
  17. private CityBL objCityBL;
  18. ///<summary>
  19. /// variable for httpResponseMessage
  20. /// </summary>
  21. HttpResponseMessage response;
  22. #endregion
  24. #region Response Method
  25. ///summary
  26. /// This method is used to fetch CityDetails
  27. /// <summary>
  28. /// <return></return>
  29. [HttpGet, ActionName("GetAllCityDetails")]
  30. public HttpResponseMessage GetAllCityDetails(int stateId)
  31. {
  32. objCityBL = new CityBL();
  33. HttpResponseMessage response;
  34. try
  35. {
  36. var detailsResponse = objCityBL.GetCity(stateId);
  37. if (detailsResponse != null)
  38. response = Request.CreateResponse<List<City>>(HttpStatusCode.OK, detailsResponse);
  39. else
  41. response = new HttpResponseMessage(HttpStatusCode.NotFound);
  42. }
  43. catch (Exception ex)
  44. {
  45. response = Request.CreateErrorResponse(HttpStatusCode.InternalServerError, ex.Message);
  47. }
  48. return response;
  49. }
  50. #endregion
  51. }
  52. }

Working with User Interface Layer

In this section we will update the controller and add some new views to create the new view and display the dropdownlist in the view. Use the following procedure.

Step 1

Update the "CollegeDetails" controller and add the following code to it:

  1. public ActionResult CreateCollegeDetails()
  2. {
  3. CollegeDetails objcollege = new CollegeDetails();
  4. objcollege.States = GetAllStates();
  5. return View("~/Views/CollegeDetails/CreateCollegeDetails.cshtml", objcollege);
  6. }
  8. public List<State> GetAllStates()
  9. {
  10. var list = new List<CollegeTrackerModels.State>();
  11. var httpClient = new HttpClient();
  12. httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
  13. HttpResponseMessage response;
  14. response = httpClient.GetAsync("http://localhost:59866/api/" + "StateDetails/GetAllStateDetails/").Result;
  15. response.EnsureSuccessStatusCode();
  16. List<State> stateList = response.Content.ReadAsAsync<List<State>>().Result;
  17. if (!object.Equals(stateList, null))
  18. {
  19. var states = stateList.ToList();
  20. return states;
  21. }
  22. else
  23. {
  24. return null;
  25. }
  26. }
  28. [HttpGet]
  29. public JsonResult GetJsonCity(int stateId)
  30. {
  31. var list = new List<CollegeTrackerModels.City>();
  32. try
  33. {
  34. var httpClient = new HttpClient();
  35. httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
  36. HttpResponseMessage response;
  37. response = httpClient.GetAsync("http://localhost:59866/api/" + "cityDetails/GetAllCityDetails?stateId="+stateId+"").Result;
  38. response.EnsureSuccessStatusCode();
  39. List<City> cityList = response.Content.ReadAsAsync<List<City>>().Result;
  40. if (!object.Equals(cityList, null))
  41. {
  42. var cities = cityList.ToList();
  43. foreach (var item in cities)
  44. {
  45. list.Add(new City
  46. {
  47. Value = item.City_ID,
  48. Text = item.City_Name
  49. });
  50. }
  51. }
  52. }
  53. catch (HttpException ex)
  54. {
  55. throw new HttpException(ex.Message);
  56. }
  57. catch (Exception ex)
  58. {
  59. throw new Exception(ex.Message);
  60. }
  61. return Json(new SelectList(list, "Value", "Text"), JsonRequestBehavior.AllowGet);
  62. }

Step 2

Add the following code before the table in the Views/CollegeDetails/CollegeDetails page:

  1. <p>
  2. @Html.ActionLink("Create New", "CreateCollegeDetails", "CollegeDetails")
  3. </p>

Step 3

Go to Views/CollegeDetails and add an empty view named "CreateCollegeDetails" and replace the code with the following code:

  1. @model CollegeTrackerModels.CollegeDetails
  2. @{
  3. ViewBag.Title = "Create CollegeDetails";
  4. }
  6. <h2>Create</h2>
  8. <script type="text/javascript">
  9. function getCities() {
  10. var stateID = $('#DropDownListStates').val();
  11. if (stateID > 0)
  12. {
  13. $.ajax({
  14. url: "@Url.Action("GetJsonCity","CollegeDetails")",
  15. data: { stateId: stateID },
  16. dataType: "json",
  17. type: "GET",
  18. success: function (data) {
  19. var items = "";
  20. items = "<option value=''>--Choose City--</option>";
  21. $.each(data, function (i, item) {
  22. items += "<option value=\"" + item.Value + "\">" + item.Text + "</option>";
  23. });
  24. $('#DropDownListCities').html(items);
  25. }
  26. });
  27. }
  28. }
  29. </script>
  31. @using (Ajax.BeginForm("CreateUpdateCollegeDetails","CollegeDetails",null))
  32. {
  33. @Html.AntiForgeryToken()
  35. <div class="form-horizontal">
  36. <hr />
  37. @Html.ValidationSummary(true)
  39. <div class="form-group">
  40. @Html.LabelFor(model => model.CollegeName, new { @class = "control=label col-md-2" })
  41. <div class="col-md-10">
  42. @Html.EditorFor(model => model.CollegeName)
  43. @Html.ValidationMessageFor(model => model.CollegeName)
  44. </div>
  45. </div>
  47. <div class="form-group">
  48. @Html.LabelFor(model => model.CollegeAddress, new { @class = "control=label col-md-2" })
  49. <div class="col-md-10">
  50. @Html.EditorFor(model => model.CollegeAddress)
  51. @Html.ValidationMessageFor(model => model.CollegeAddress)
  52. </div>
  53. </div>
  55. <div class="form-group">
  56. @Html.LabelFor(model => model.CollegePhone, new { @class = "control=label col-md-2" })
  57. <div class="col-md-10">
  58. @Html.TextBoxFor(model => model.CollegePhone)
  59. @Html.ValidationMessageFor(model => model.CollegePhone)
  60. </div>
  61. </div>
  63. <div class="form-group">
  64. @Html.LabelFor(model => model.CollegeEmailID, new { @class = "control=label col-md-2" })
  65. <div class="col-md-10">
  66. @Html.EditorFor(model => model.CollegeEmailID)
  67. @Html.ValidationMessageFor(model => model.CollegeEmailID)
  68. </div>
  69. </div>
  71. <div class="form-group">
  72. @Html.LabelFor(model => model.ContactPerson, new { @class = "control=label col-md-2" })
  73. <div class="col-md-10">
  74. @Html.EditorFor(model => model.ContactPerson)
  75. @Html.ValidationMessageFor(model => model.ContactPerson)
  76. </div>
  77. </div>
  79. <div class="form-group">
  80. @Html.LabelFor(model => model.StateId, new { @class = "control=label col-md-2" })
  81. <div class="col-md-10">
  82. @Html.DropDownListFor(model => model.StateId, new SelectList(Model.States, "State_ID", "State_Name"), "Choose State", new { onchange = "getCities()", @id = "DropDownListStates" })
  83. @Html.ValidationMessageFor(model => model.StateId)
  85. </div>
  86. </div>
  88. <div class="form-group">
  89. @Html.LabelFor(model => model.CityId, new { @class = "control=label col-md-2" })
  90. <div class="col-md-10">
  91. @Html.DropDownListFor(model => model.CityId, new SelectList(Model.Cities, "City_ID", "State_ID", "City_Name"), "Choose City", new { @id = "DropDownListCities" })
  92. @Html.ValidationMessageFor(model => model.CityId)
  93. </div>
  94. </div>
  96. <div class="form-group">
  97. <div class="col-md-offset-2 col-md-10">
  98. <input type="submit" value="Create" class="btn btn-success" />
  99. </div>
  100. </div>
  101. </div>
  102. }
  104. <div>
  105. @Html.ActionLink("Back to List", "GetCollegeList")
  106. </div>
  108. @section Scripts{
  109. @Scripts.Render("~/bundles/jqueryval");
  110. }

Step 4

Now run the project and click on the Create New Action Link as shown below:

Create New Action Link

Step 5

In the next page you can check that the states are listed in the States dropdownlist.

Cascading Dropdown List

Step 6

Choose the state and the city dropdownlist will update.

City Selection in Cascading Dropdownlist

That's it.

Summary

This article described how to show the cascading dropdownlist based upon the selection of a different state. In the next article we will do the Insert, Delete and Update operation using an API controller using MVC 5. Thanks for reading.