Topics
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.
- Open Visual Studio.
- Click on File -> New -> Project.
- In this project, I am using C# as a programming language, but you can also choose VISUAL BASIC if you know syntaxes of VB.
- Inside Visual C# -> Web -> Project -> Select Asp.Net MVC 3, 4 or 5 -> Select Ok.
- Now one more dialog box would open -> select ‘Internet application’ as a project template. -> OK.
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