Bind Country, State, and City Dropdownlist in Asp.net MVC using ajax

Here I will explain how to bind Country,State,City Dropdownlist in Asp.net MVC. I will also bind cascading dropdownlist from database in MVC

Introduction:

      Today in this article, I will explain how to bind Country, State and City Dropdownlist in Asp.net MVC.

DropDownlist enables a common scenario in which the contents of one list depends on the selection of another list and does so without having to embed the entire data set in the page or transfer it to the client at all.

In this example we will use Asp.net MVC 4 for Bind Country, State and City Dropdownlist.

Fallow these steps in order to implement “Bind Country, State and City dropdown list in Asp.net MVC using JQuery Ajax”

Step1: Create tables in database.

In this example, I have created fallowing table and stored procedure for Binding Country, State and City dropdownlist.

Create tbl_Country table for store Country name.

 

CREATE TABLE [dbo].[tbl_Country] (

    [Country_id]   INT           IDENTITY (1, 1) NOT NULL,

    [Country_Name] NVARCHAR (50) NULL,

    CONSTRAINT [PK_tbl_Country] PRIMARY KEY CLUSTERED ([Country_id] ASC)

);

 

The design of the table look like this as follows:

 

 

Now create tbl_state table for store state name.

 

 

CREATE TABLE [dbo].[tbl_state] (

    [State_id]   INT            NOT NULL,

    [Country_id] INT            NULL,

    [State]      NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_tbl_state] PRIMARY KEY CLUSTERED ([State_id] ASC)

);

 

The design of the table look like this as follows:

 

 

In last we create tbl_city for store city name

CREATE TABLE [dbo].[tbl_city] (

    [City_id]  INT            NOT NULL,

    [State_id] INT            NULL,

    [City]     NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_tbl_city] PRIMARY KEY CLUSTERED ([City_id] ASC)

);

 

 

The design of the table look like this as follows:

 

 

 

Step2: Create New Project.

 Go to File > New > Project > Web > Asp.net MVC web project > Enter Application Name > Select your project location > click to OK button > It will show new dialog window for select template > here we will select MVC project > then click to ok

 

Step3: Add Connection string in web.config file

 

Here I have add connection string in web.config file under Configuration section as fallows

<connectionStrings>

    <add name="con" connectionString="Data Source=.;Initial Catalog=Country_DB;Integrated Security=True" providerName="System.Data.SqlClient"/>

  </connectionStrings>

 

 

 

Step5: Create Database Access layer.

Go to Solutions Explorer > right click on project solution> Add New Folder > Enter Folder name (Here I rename it as ‘database_access_layer’) > right click on folder > Add new class.

 

Now add the fallowing method into Database access class that access record on the basis of parameters.

 

public class db

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

        // Get All Country

        public DataSet Get_Country()

        {

            SqlCommand com = new SqlCommand("Select * from tbl_Country", con);

            SqlDataAdapter da = new SqlDataAdapter(com);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }

 

        //Get all State

        public DataSet Get_State(string country_id)

        {

                SqlCommand com = new SqlCommand("Select * from tbl_state where Country_id=@catid", con);

                com.Parameters.AddWithValue("@catid", country_id);

                SqlDataAdapter da = new SqlDataAdapter(com);

                DataSet ds = new DataSet();

                da.Fill(ds);

                return ds;

        }

 

        //Get all City

        public DataSet Get_City(string state_id)

        {

            SqlCommand com = new SqlCommand("Select * from tbl_city where State_id=@stateid", con);

            com.Parameters.AddWithValue("@stateid", state_id);

            SqlDataAdapter da = new SqlDataAdapter(com);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }

    }

 

The entire db class file will be fallows

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace Country_State_City.Database_Access_Layer

{

 

    public class db

    {

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

        // Get All Country

        public DataSet Get_Country()

        {

            SqlCommand com = new SqlCommand("Select * from tbl_Country", con);

            SqlDataAdapter da = new SqlDataAdapter(com);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }

 

        //Get all State

        public DataSet Get_State(string country_id)

        {

                SqlCommand com = new SqlCommand("Select * from tbl_state where Country_id=@catid", con);

                com.Parameters.AddWithValue("@catid", country_id);

                SqlDataAdapter da = new SqlDataAdapter(com);

                DataSet ds = new DataSet();

                da.Fill(ds);

                return ds;

        }

 

        //Get all City

        public DataSet Get_City(string state_id)

        {

            SqlCommand com = new SqlCommand("Select * from tbl_city where State_id=@stateid", con);

            com.Parameters.AddWithValue("@stateid", state_id);

            SqlDataAdapter da = new SqlDataAdapter(com);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }

    }

}

 

And also you will see the methods is taking an input parameter country_id and state_id (this is the id of the Country and State Dropdownlist that I created)

Step6: Create a Controller

 Go to Solution Explorer > Right Click on Controllers folder form Solution Explorer > Add > Controller > Enter Controller name > Select Template "empty MVC Controller"> Add.

Here I have created a controller "HomeController.

 

Step7: Add View in HomeController

After adding the controller to the application I am just adding a new action result and naming it Index and also we need to call db class in HomeController.

 

Database_Access_Layer.db dblayer = new Database_Access_Layer.db();

public ActionResult Index()

        {

           

            return View();

        }

 

Step7: Add methods for Bind and populate the Dropdownlist in HomeController

Now we will add fallowing functions in HomeController for populate dropdownlist in Index view

Add below method for populate Country Dropdownlist

 

public void Country_Bind()

        {

            DataSet ds = dblayer.Get_Country();

            List<SelectListItem> coutrylist = new List<SelectListItem>();

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                coutrylist.Add(new SelectListItem { Text = dr["Country_name"].ToString(), Value = dr["Country_id"].ToString() });

            }

            ViewBag.Country = coutrylist;

        }

 

In above methods I am calling Get_Country methods from db class and storing list in ViewBag for passing list to the view.

 

Now we will be adding a method for the JSON and script for State Dropdownlist

 

And also you will see the method for JSON is taking an input parameter, id (this is the id of the Country Dropdownlist that I created).

public JsonResult State_Bind(string country_id)

        {

            DataSet ds = dblayer.Get_State(country_id);

            List<SelectListItem> statelist = new List<SelectListItem>();

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                statelist.Add(new SelectListItem { Text = dr["State"].ToString(), Value = dr["State_id"].ToString() });

            }

            return Json(statelist, JsonRequestBehavior.AllowGet);

        }

 

 

 

In last we will be adding a method for the JSON and script for City Dropdownlist

public JsonResult City_Bind(string state_id)

        {

            DataSet ds = dblayer.Get_City(state_id);

            List<SelectListItem> citylist = new List<SelectListItem>();

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                citylist.Add(new SelectListItem { Text = dr["City"].ToString(), Value = dr["City_id"].ToString() });

            }

            return Json(citylist, JsonRequestBehavior.AllowGet);

        }

 

And also you will see the method for JSON is taking an input parameter, country_id and state_id (this is the id of the Country  Dropdownlist that I created).

All above methods are call from Index View.

 

Now entire HomeController class file will be as follows:

 

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using System.Data;

namespace Country_State_City.Controllers

{

    public class HomeController : Controller

    {

      

        Database_Access_Layer.db dblayer = new Database_Access_Layer.db();

        public ActionResult Index()

        {

            Country_Bind();

            return View();

        }

 

        public void Country_Bind()

        {

            DataSet ds = dblayer.Get_Country();

            List<SelectListItem> coutrylist = new List<SelectListItem>();

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                coutrylist.Add(new SelectListItem { Text = dr["Country_name"].ToString(), Value = dr["Country_id"].ToString() });

            }

            ViewBag.Country = coutrylist;

        }

 

        public JsonResult State_Bind(string country_id)

        {

            DataSet ds = dblayer.Get_State(country_id);

            List<SelectListItem> statelist = new List<SelectListItem>();

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                statelist.Add(new SelectListItem { Text = dr["State"].ToString(), Value = dr["State_id"].ToString() });

            }

            return Json(statelist, JsonRequestBehavior.AllowGet);

        }

 

        public JsonResult City_Bind(string state_id)

        {

            DataSet ds = dblayer.Get_City(state_id);

            List<SelectListItem> citylist = new List<SelectListItem>();

            foreach (DataRow dr in ds.Tables[0].Rows)

            {

                citylist.Add(new SelectListItem { Text = dr["City"].ToString(), Value = dr["City_id"].ToString() });

            }

            return Json(citylist, JsonRequestBehavior.AllowGet);

        }

 

    }

}

 

Step8: Add View for Index action

Right Click on Action Method (here right click on Index action) > Add View > Enter View Name > Select "Empty" under Template dropdown > Check use a layout page > Add.

 

Further we need to write script for JSON

 

<script src="~/script/jquery-1.7.1.min.js"></script>

    <script>

        $(document).ready(function () {

            $("#Country").change(function () {

                var id = $(this).val();

                $("#state").empty();

                $.get("State_Bind", { country_id: id }, function (data) {

                    var v = "<option>---Select---</option>";

                    $.each(data, function (i, v1) {

                        v += "<option value=" + v1.Value + ">" + v1.Text + "</option>";

                    });

                    $("#state").html(v);

                });

            });

 

            $("#state").change(function () {

                var id = $(this).val();

                $("#city").empty();

                $.get("City_Bind", { state_id: id }, function (data) {

                    var v = "<option>---Select---</option>";

                    $.each(data, function (i, v1) {

                        v += "<option value=" + v1.Value + ">" + v1.Text + "</option>";

                    });

                    $("#city").html(v);

                });

            });

        });

    </script>

 

Also we need to add a JQuery library reference so don’t forgot to add it.

Now we have to added a Dropdownlist for Country in Index view

@Html.DropDownList("Country", null, "---Select Country----")

 

Entire Index view will be as follows:

@{

    Layout = null;

}

 

<!DOCTYPE html>

 

<html>

<head>

    <meta name="viewport" content="width=device-width" />

    <title>Index</title>

    <script src="~/script/jquery-1.7.1.min.js"></script>

    <script>

        $(document).ready(function () {

            $("#Country").change(function () {

                var id = $(this).val();

                $("#state").empty();

                $.get("State_Bind", { country_id: id }, function (data) {

                    var v = "<option>---Select---</option>";

                    $.each(data, function (i, v1) {

                        v += "<option value=" + v1.Value + ">" + v1.Text + "</option>";

                    });

                    $("#state").html(v);

                });

            });

 

            $("#state").change(function () {

                var id = $(this).val();

                $("#city").empty();

                $.get("City_Bind", { state_id: id }, function (data) {

                    var v = "<option>---Select---</option>";

                    $.each(data, function (i, v1) {

                        v += "<option value=" + v1.Value + ">" + v1.Text + "</option>";

                    });

                    $("#city").html(v);

                });

            });

        });

    </script>

</head>

<body>

    <div>

        <div>

            <b>Country: </b>

            @Html.DropDownList("Country", null, "---Select Country----")<br />

        </div>

        <div>

            <b>State: </b>

            <select id="state"></select><br />

        </div>

        <div>

            <b>City: </b>

            <select id="city"></select><br />

        </div>

    </div>

</body>

</html>

 

 

Step 10: Run Application.

We have done all steps, now it’s time to run the application