Populate city dropdown based on state dropdown

Introduction

In this article, we will see how to populate city dropdown based on state dropdown using MVC and entity framework. I will use visual studio 2012 framework and SQL server 2008 R2 for this demo.

So, before opening visual studio let’s create StateCity StateCity and execute following script.

State and city table script

USE [StateCity]
GO
/****** Object:  ForeignKey [FK_State_City]    Script Date: 02/28/2017 19:43:49 ******/
ALTER TABLE [dbo].[City] DROP CONSTRAINT [FK_State_City]
GO
/****** Object:  Table [dbo].[City]    Script Date: 02/28/2017 19:43:49 ******/
ALTER TABLE [dbo].[City] DROP CONSTRAINT [FK_State_City]
GO
DROP TABLE [dbo].[City]
GO
/****** Object:  Table [dbo].[States]    Script Date: 02/28/2017 19:43:49 ******/
DROP TABLE [dbo].[States]
GO
/****** Object:  Table [dbo].[States]    Script Date: 02/28/2017 19:43:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[States](
    [StateID] [int] IDENTITY(1,1) NOT NULL,
    [StateDesc] [varchar](50) NOT NULL,
 CONSTRAINT [PK_States] PRIMARY KEY CLUSTERED 
(
    [StateID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[States] ON
INSERT [dbo].[States] ([StateID], [StateDesc]) VALUES (1, N'Maharashtra')
INSERT [dbo].[States] ([StateID], [StateDesc]) VALUES (2, N'Gujarat')
INSERT [dbo].[States] ([StateID], [StateDesc]) VALUES (3, N'Andhra Pradesh')
SET IDENTITY_INSERT [dbo].[States] OFF
/****** Object:  Table [dbo].[City]    Script Date: 02/28/2017 19:43:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[City](
    [CityID] [int] IDENTITY(1,1) NOT NULL,
    [CityDesc] [varchar](50) NOT NULL,
    [StateID] [int] NOT NULL,
 CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED 
(
    [CityID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[City] ON
INSERT [dbo].[City] ([CityID], [CityDesc], [StateID]) VALUES (1, N'Mumbai', 1)
INSERT [dbo].[City] ([CityID], [CityDesc], [StateID]) VALUES (2, N'Nagpur', 1)
INSERT [dbo].[City] ([CityID], [CityDesc], [StateID]) VALUES (4, N'Pune', 1)
INSERT [dbo].[City] ([CityID], [CityDesc], [StateID]) VALUES (5, N'Ahmedabad', 2)
INSERT [dbo].[City] ([CityID], [CityDesc], [StateID]) VALUES (6, N'Surat', 2)
INSERT [dbo].[City] ([CityID], [CityDesc], [StateID]) VALUES (7, N'Vadodara', 2)
INSERT [dbo].[City] ([CityID], [CityDesc], [StateID]) VALUES (8, N'Visakhapatnam', 3)
INSERT [dbo].[City] ([CityID], [CityDesc], [StateID]) VALUES (9, N'Vijayawada', 3)
INSERT [dbo].[City] ([CityID], [CityDesc], [StateID]) VALUES (10, N'Kakinada', 3)
SET IDENTITY_INSERT [dbo].[City] OFF
/****** Object:  ForeignKey [FK_State_City]    Script Date: 02/28/2017 19:43:49 ******/
ALTER TABLE [dbo].[City]  WITH CHECK ADD  CONSTRAINT [FK_State_City] FOREIGN KEY([StateID])
REFERENCES [dbo].[States] ([StateID])
GO
ALTER TABLE [dbo].[City] CHECK CONSTRAINT [FK_State_City]
GO

Let’s start for this demo step by step using MVC.

Step 1: Create MVC project, by using following steps.

Step 2 : After creating MVC project, you will find list of your project files inside ‘solution explorer’ on your right hand side.

• Right click on your project name -> click add -> click New Folder -> rename that new folder to ‘Entity’.

Step 3 : Create an ADO.NET Entity Data Model

• Right click on your project -> Add -> New Item.

• Search for Ado.Net entity data model inside search box. -> Name it as ‘StateCityEntity’ -> Click Add

• Inside entity data model wizard -> Select generate from database -> Click next

• Now from ‘New connection’ button -> Select data source -> ‘Microsoft SQL server’ -> Click continue

• Now select your database (. stands for localhost) name and select database ‘StateCity’ -> Click OK

• Now inside ‘Entity Data Model Wizard’ name your web.config file as StateCityEntities -> Click next

• Now select table(tblStudent) -> name model namespaces as StateandCityModel -> Click Finish

• Now you will see Ado.Net Entity Data Model (StudentDataModel) added to your project’s files.

Step 4 : Add class file inside model folder

• Right click on model folder and add class file and name that file ‘StateCityModel’
• Add following two methods inside ‘StateCityModel

public SelectList States { get; set; }
        public int StateID { get; set; }
        public StateCityModel()
        {
            using (StateCityEntities db = new StateCityEntities())
            {
                var states = db.States.ToList();
                States = new SelectList(states, "StateID", "StateDesc");
            }

        }

        public static SelectList Citys(int stateId)
        {
            using (StateCityEntities db = new StateCityEntities())
            {
                var cityList = db.Cities.Where(x => x.StateID == stateId).ToList();
                var citys = new SelectList(cityList, "CityID", "CityDesc");
                return citys;
            }
        }

Step 5 : Add new controller

• Right click on controller folder. Name your controller -> HomeController -> Click on Add button.
• Now your controller would look something like this
• Now add following two methods inside your Home controller

Step 6 : Add view

• Now right click on index method -> Add index view

• Add follwing code in index view.

@model StateandCityDropdownDemo.Models.StateCityModel

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

    function GetCitysByState(state) {
        var $select = $('#CityID');
        $select.empty();
        $select.append("<option value='0'>Select City</option>");

        if (state.value != "" && state.value != undefined) {
            var stateId = parseInt(state.value);
            $.ajax({
                url: "/Home/Cities",
                data: { stateId: stateId },
                success: function (data) {

                    $.each(data, function (i, city) {
                        $('<option>', {
                            value: city.Value
                        }).append(city.Text).appendTo($select);
                    });
                },
                error: function (mydata) { alert("error"); alert(mydata); },
                type: 'POST'
            });

        }


    }

</script>

<h2>Select city based on state dropdown</h2> <br />


<body>

       <div> Select State :
        @Html.DropDownListFor(model=>model.StateID, Model.States,"Select State", new {@onchange= "GetCitysByState(this)" })
        </div>
    <div style="margin-top: -20px;margin-left: 221px;">
        Select City :
         <select id="CityID">
             <option value="0">Select City</option>
         </select>
   </div>

</body>

Step 6 : Result