Populate city dropdown based on state dropdown

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
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

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


  1. Open Visual Studio.
  2. Click on File -> New -> Project.
  3. In this project, I am using C# as a programming language, but you can also choose VISUAL BASIC if you know syntaxes of VB.
  4. Inside Visual C# -> Web -> Project -> Select Asp.Net MVC 3, 4 or 5 -> Select Ok.

  5. Save Images
  6. Now one more dialog box would open -> select 'Internet application' as a project template. -> OK.
  7. Save Images

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


  1. Right click on your project name -> click add -> click New Folder -> rename that new folder to 'Entity'.
  2. Save Images

Step 3 : Create an ADO.NET Entity Data Model


  1. Right click on your project -> Add -> New Item.
  2. State and city dropdown
  3. Search for Ado.Net entity data model inside search box. -> Name it as 'StateCityEntity' -> Click Add
  4. State and city dropdown
  5. Inside entity data model wizard -> Select generate from database -> Click next
  6. State and city dropdown
  7. Now from 'New connection' button -> Select data source -> 'Microsoft SQL server' -> Click continue
  8. State and city dropdown
  9. Now select your database (. stands for localhost) name and select database 'StateCity' -> Click OK
  10. State and city dropdown
  11. Now inside 'Entity Data Model Wizard' name your web.config file as StateCityEntities -> Click next
  12. State and city dropdown
  13. Now select table(tblStudent) -> name model namespaces as StateandCityModel -> Click Finish
  14. State and city dropdown
  15. Now you will see Ado.Net Entity Data Model (StudentDataModel) added to your project's files.


Step 4 : Add class file inside model folder


  1. Right click on model folder and add class file and name that file 'StateCityModel'
  2. Add following two methods inside 'StateCityModel'

  3.  1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    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


  1. Right click on controller folder. Name your controller -> HomeController -> Click on Add button.
  2. Now your controller would look something like this

  3.  1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    
    namespace ImageuploadingDemo.Controllers
    {
        public class HomeController : Controller
        {
            //
            // GET: /Home/
    
            public ActionResult Index()
            {
                return View();
            }
    
        }
    }
    

  4. Now add following two methods inside your Home controller

  5.  1
     2
     3
     4
     5
     6
     7
     8
     9
    10
            public ActionResult index()
            {
                StateCityModel stateCityModel = new StateCityModel();
                return View(stateCityModel);
            }
            public JsonResult Cities(int stateId)
            {
                var cityList = StateCityModel.Citys(stateId);
                return Json(cityList, JsonRequestBehavior.AllowGet);
            }
    

Step 6 : Add view

  1. Now right click on index method -> Add index view
  2. State and city dropdown
  3. Add follwing code in index view.

  4.  1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    @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


Download Source Code

Responses




Popular Posts

Facebook

Founded in 2016, Tech Study passionately delivers stylish and dynamic innovative information to programmer & technology lovers. http://www.techstudy.org/.