Introduction

In this article we will learn how to bind data from a table to a DropDownList in MVC using LINQ to SQL and learn how to populate a child dropdown list depending on each new value as values are selected in the parent's DropDownList.

Database structure

Create two tables in the database with the names Country and State.

The following is the create table code for the country table:

  1. CREATE TABLE [dbo].[country]
  2. (
  3. [countryid] [int] NOT NULL,
  4. [countryname] [nvarchar](50) NULL,
  5. )

In the preceding SQL query, countryid is the primary key.

The following is the create table code for the state table:

  1. CREATE TABLE [dbo].[state]
  2. (
  3. [stateid] [int] NOT NULL,
  4. [statename] [nvarchar](50) NULL,
  5. [countryid] [int] NULL,
  7. )

In the preceding SQL query, stateid is the primary key and countryid is the foreign key reference by country table.

Adding a LINQ to SQL Class

Step 1

Right-click on the project and select "Add new item", then select Data from the templates.

Step 2

Choose "LINQ to SQL classes" from the list and provide a name. Now after clicking on Add, you can see the .dbml file in the project.

Step 3

Drag the country and state tables from the database in the Server Explorer.


Create a dft Controller

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.Mvc;
  6. using System.Data.SqlClient;
  8. namespace dropdownlist.Controllers
  9. {
  10. public class dftController : Controller
  11. {
  12. DataClasses1DataContext db = new DataClasses1DataContext();
  13. //
  14. // GET: /dft/
  16. public ActionResult Index()
  17. {
  19. DataClasses1DataContext db = new DataClasses1DataContext();
  20. ViewBag.countryid = new SelectList(db.countries, "countryid", "countryname");
  21. ViewBag.states = new SelectList(new List<state>() ,"stateid","statename");
  22. return View();
  23. }
  25. public IList<state> Getstate(int id)
  26. {
  27. return db.states.Where(m => m.countryid == id).ToList();
  28. }
  30. public JsonResult GetJsonState(int id)
  31. {
  33. var stateListt = this.Getstate(Convert.ToInt32(id));
  34. var statesList = stateListt.Select(m => new SelectListItem()
  35. {
  36. Text = m.statename,
  37. Value = m.countryid.ToString()
  38. });
  40. return Json(statesList, JsonRequestBehavior.AllowGet);
  41. }
  42. }
  43. }

Create a view for showing the values in the DropDownList

Right-click on the Index ActionResult method and select Add View. After selecting Add View, a dialog box will open. The view name is the index by default. Now select your model class and click on the OK button.

  1. @model dropdownlist.country
  3. @{
  4. ViewBag.Title = "Index";
  5. }
  7. <h2>Index</h2>
  9. @using (Html.BeginForm())
  10. {
  11. @Html.DropDownList("country", ViewBag.countryid as SelectList, "Select a Country", htmlAttributes: new { onchange = "Getstate()" })
  13. @Html.DropDownList("State", ViewBag.states as SelectList, "Choose State", new { @class = "inputBox", @id = "DropDownListStates" })
  15. }
  17. <script>
  18. function GetState() {
  20. $.ajax({
  21. url: "@Url.Action("GetJsonState", "dft")",
  22. dataType: "json",
  23. type: "GET",
  24. data:{id:$("#country").val()},
  25. error: function () {
  26. },
  27. beforeSend: function () {
  28. },
  29. success: function (data) {
  30. var items = "";
  31. items = "<option value=''>Choose State</option>";
  32. $.each(data, function (i, item) {
  33. items += "<option value=\"" + item.Value + "\">" + item.Text + "</option>";
  34. });
  35. $("#DropDownListStates").html(items);
  36. }
  37. });
  39. }
  40. </script>
The following is the output of the preceding code.




Summary

In this article we learned how to bind data from a table to a DropDownList in MVC using LINQ to SQL and learn how to populate a child dropdown list depending on each new value as values are selected in the parent's DropDownList.