Ajax CascadingDropDown example in asp.net to Fill DropDownList with Countries,states and cities.

Introduction: In previous articles i explained How to Fill Country,State,Cities in the DropDownList and Ajax TabContainer example to create multiple tabs/panels and Ajax Accordion example to create Vertical DropDown menu in asp.net and Create contact us form/page and Ajax HtmlEditorExtender control to format textbox text and send formatted text in email and  Drag & drop to upload multiple files using AjaxFileUpload like Facebook in asp.net and Get age in years,months,days,hours and seconds from DOB in asp.net C#,Vb.Net and Ajax AutoCompleteExtender control example in asp.net C#,VB.Net using web service .
In this article I am going to explain the example of How to use Ajax CascadingDropDown to fill/Bind/Load Countries, states and cities in the DropDownList controls from the Sql Server database by defining web methods in the web service to fetch/get/read Countries, states and cities from database in asp.net using both C# and Vb.net Language.

Click on image to enlarge

  Description: So basically you will learn the following:

• How to use Ajax Cascading Dropdown control to fill country, state and city using web service.
• How to Fill Country, state and city in the DropDownList from the Sql Server Database.
• How to populate one dropdown based on other dropdown. e.g  Populate State dropdownlist based on country dropdownlist selection and similarly populating city dropdownlist based on state dropdownlist selection. 

 Implementation: Let's create the web application to understand. First create a Database in Sql Server and name it "Emp_DB" or whatever you want. Now we need to create tables for County, State and City. Create and Insert some data in all the tables as shown below.

• Create the table and name it “Tbl_Country”

Column Name	Data type
Country_Id_Pk	int	Set is identity=yes
Country_Name	varchar(100)

Tbl_Country table data

Country_Id_Pk	Country_Name
1	India
2	Australia

• Create the table and name it  “Tbl_ State”

Column Name	Data type
State_Id_pk	int	Set is identity=yes
State_Name	varchar(100)
country_Id_Fk	int

Tbl_State table data

State_Id_pk	State_Name	country_Id_Fk
1	Haryana	1
2	Punjab	1
3	Himachal Pradesh	1
4	Queensland	2

• Create the table and name it  “Tbl_City”

Column Name	Data type
City_Id_Pk	int	Set is identity=yes
City_Name	varchar(100)
State_Id_Fk	int

Tbl_City table data

City_Id_Pk	City_Name	State_Id_Fk
1	Panchkula	1
2	Kalka	1
3	Ambala	1
4	Moga	2
5	Bathinda	2
6	Shimla	3
7	kasauli	3
8	Brisbane	4
9	Townsville	4

• In the web.config file create the connection string in <connectionString> element as:

<connectionStrings>

    <add name="conStr" connectionString="Data Source=LocalServer;Initial Catalog=Emp_DB;Integrated Security=True"/>

  </connectionStrings> 

Note: Replace the Data Source and Initial Catalog as per your application.

• In the design page ( .aspx) place a Scriptmanager  control from the AJAX Extension category of the Visual Studio toolbox. And place 3 DropDownList controls and a Label control from the standard category of the Visual Studio toolbox and also place 3 CascadingDropDown controls from the AjaxControlToolkit. If you have not installed the AjaxControlToolkit then read the article How to install AjaxControlToolkit in Visual Studio.

<asp:ScriptManager ID="ScriptManager1" runat="server">

        </asp:ScriptManager>

    <fieldset style="width:340px;">

    <legend>Fill Country,State and City DropDownList in asp.net</legend>

    <table>

    <tr>

        <td width="40%">Select Country:</td>

        <td>

        <asp:DropDownList ID="ddlCountry" runat="server" 

                 Width="187px"></asp:DropDownList>

                </td>

        <asp:CascadingDropDown ID="csdCountry" runat="server"

        Category="Country"

        TargetControlID="ddlCountry"

         PromptText="-- Select Country --"

         LoadingText="[Loading Countries...]"

         ServiceMethod="FetchCountries"

         ServicePath="AjaxCascadingDropDown.asmx">

        </asp:CascadingDropDown>

    </tr>

    <tr>

        <td>Select State:</td>

        <td>

        <asp:DropDownList ID="ddlState" runat="server"

                Width="187px"></asp:DropDownList>

                <asp:CascadingDropDown ID="csdState" runat="server"

        ParentControlID="ddlCountry"

        Category="State"

        TargetControlID="ddlState"

         PromptText="-- Select State --"

         LoadingText="[Loading States...]"

         ServiceMethod="FetchStates"

         ServicePath="AjaxCascadingDropDown.asmx">

        </asp:CascadingDropDown>

                </td>

        </tr>

    <tr>

        <td>Select City:</td>

        <td>

        <asp:DropDownList ID="ddlCity" runat="server" Width="187px" AutoPostBack="True"

                onselectedindexchanged="ddlCity_SelectedIndexChanged"></asp:DropDownList>

        <asp:CascadingDropDown ID="csdCity" runat="server"

        ParentControlID="ddlState"

        Category="City"

        TargetControlID="ddlCity"

         PromptText="-- Select City --"

         LoadingText="[Loading Cities...]"

         ServiceMethod="FetchCities"

         ServicePath="AjaxCascadingDropDown.asmx">

        </asp:CascadingDropDown>

        </td>

    </tr>

    <tr>

        <td colspan="2">

            <asp:Label ID="lblStatus" runat="server" Text="" style="color: #006600"></asp:Label>

        </td>

    </tr>

    </table>

        </fieldset>

Note: In the very first line of the design page(.aspx) set the EnableEventvalidation to false. Like this

 <%@ Page Language="C#" EnableEventValidation="false" AutoEventWireup="true" CodeFile="AjaxCascade.aspx.cs" Inherits="AjaxCascade" %>

  

Note: Have you noticed the line <%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %> added automatically next to the very first line in the design page. Actually it registers the Ajax Control on placing CascadingDropDown control on design page.

•  Now create the web service. Go to website menu -> Add New Item..-> select C# or Visual Basic language from the left pane and Select Web Service from the center pane and name it “AjaxCascadingDropDown.asmx”.

C#.Net Code to fill/Bind/Load Countries, states and cities in the DropDownList using Ajax CascadingDropDown control

The complete code in the code behind file "AjaxCascadingDropDown.cs" that is placed inside App_Code folder will be as:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Services;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using AjaxControlToolkit;

using System.Collections.Specialized;

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.

 [System.Web.Script.Services.ScriptService]

public class AjaxCascadingDropDown : System.Web.Services.WebService

{

    SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["conStr"].ToString());

  

    public AjaxCascadingDropDown () {

        //Uncomment the following line if using designed components

        //InitializeComponent();

    }

    [WebMethod]

    public CascadingDropDownNameValue[] FetchCountries(string knownCategoryValues, string category)

    {

        if (con.State == ConnectionState.Closed)

        {

            con.Open();

        }

        SqlCommand cmd = new SqlCommand("select * from Tbl_Country", con);  

        cmd.ExecuteNonQuery();

        SqlDataAdapter adp = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        adp.Fill(ds);

        con.Close();

      

        List<CascadingDropDownNameValue> countries = new List<CascadingDropDownNameValue>();

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

        {

            string CountryID = dtRow["Country_Id_Pk"].ToString();

            string CountryName = dtRow["Country_Name"].ToString();

            countries.Add(new CascadingDropDownNameValue(CountryName, CountryID));

        }

        return countries.ToArray();

    }

    [WebMethod]

    public CascadingDropDownNameValue[] FetchStates(string knownCategoryValues, string category)

    {

        int countryId;      

        StringDictionary strCountries = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);

        countryId = Convert.ToInt32(strCountries["Country"]);

        con.Open();

        SqlCommand cmd = new SqlCommand("select * from Tbl_State where Country_ID_Fk=@CountryID", con);

        cmd.Parameters.AddWithValue("@CountryID", countryId);

        cmd.ExecuteNonQuery();

        SqlDataAdapter dastate = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        dastate.Fill(ds);

        con.Close();      

        List<CascadingDropDownNameValue> states = new List<CascadingDropDownNameValue>();

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

        {

            string StateID = dtRow["State_Id_Pk"].ToString();

            string StateName = dtRow["State_Name"].ToString();

            states.Add(new CascadingDropDownNameValue(StateName, StateID));

        }

        return states.ToArray();

    }

  

    [WebMethod]

    public CascadingDropDownNameValue[] FetchCities(string knownCategoryValues, string category)

    {

        int stateId;     

        StringDictionary strStates = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);

        stateId = Convert.ToInt32(strStates["State"]);

        con.Open();

        SqlCommand cmd = new SqlCommand("select * from Tbl_City where State_ID_Fk=@StateID", con);

        cmd.Parameters.AddWithValue("@StateID", stateId);

        cmd.ExecuteNonQuery();

        SqlDataAdapter daregion = new SqlDataAdapter(cmd);

        DataSet ds = new DataSet();

        daregion.Fill(ds);

        con.Close();     

        List<CascadingDropDownNameValue> cities = new List<CascadingDropDownNameValue>();

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

        {

            string RegionID = dtRow["City_id_pk"].ToString();

            string RegionName = dtRow["City_Name"].ToString();

            cities.Add(new CascadingDropDownNameValue(RegionName, RegionID));

        }

        return cities.ToArray();

    }   

}

Note: In the code behind file(.aspx.cs) write the following code on selectedindexchanged event of the City dropdownlist to print the selected Country,State and City as:

protected void ddlCity_SelectedIndexChanged(object sender, EventArgs e)

    {

        lblStatus.Text = "You have chosen Country: " + ddlCountry.Text + ", State: " + ddlState.Text + ", City:  " + ddlCity.Text;

    }

VB.Net Code to fill/Bind/Load Countries, states and cities in the DropDownList using Ajax CascadingDropDown control

The complete code in the code behind file "AjaxCascadingDropDown.vb" that is placed inside App_Code folder will be as:

Imports System.Web

Imports System.Web.Services

Imports System.Web.Services.Protocols

Imports System.Configuration

Imports System.Data

Imports System.Data.SqlClient

Imports AjaxControlToolkit

Imports System.Collections.Specialized

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.

<System.Web.Script.Services.ScriptService()> _

<WebService(Namespace:="http://tempuri.org/")> _

<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _

Public Class AjaxCascadingDropDown

    Inherits System.Web.Services.WebService

    Dim con As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("conStr").ToString())

    <WebMethod()> _

    Public Function HelloWorld() As String

        Return "Hello World"

    End Function

    <WebMethod()> _

    Public Function FetchCountries(knownCategoryValues As String, category As String) As CascadingDropDownNameValue()

        If con.State = ConnectionState.Closed Then

            con.Open()

        End If

        Dim cmd As New SqlCommand("select * from Tbl_Country", con)

        cmd.ExecuteNonQuery()

        Dim adp As New SqlDataAdapter(cmd)

        Dim ds As New DataSet()

        adp.Fill(ds)

        con.Close()

        Dim countries As New List(Of CascadingDropDownNameValue)()

        For Each dtRow As DataRow In ds.Tables(0).Rows

            Dim CountryID As String = dtRow("Country_Id_Pk").ToString()

            Dim CountryName As String = dtRow("Country_Name").ToString()

            countries.Add(New CascadingDropDownNameValue(CountryName, CountryID))

        Next

        Return countries.ToArray()

    End Function

    <WebMethod()> _

    Public Function FetchStates(knownCategoryValues As String, category As String) As CascadingDropDownNameValue()

        Dim countryId As Integer

        Dim strCountries As StringDictionary = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

        countryId = Convert.ToInt32(strCountries("Country"))

        con.Open()

        Dim cmd As New SqlCommand("select * from Tbl_State where Country_ID_Fk=@CountryID", con)

        cmd.Parameters.AddWithValue("@CountryID", countryId)

        cmd.ExecuteNonQuery()

        Dim dastate As New SqlDataAdapter(cmd)

        Dim ds As New DataSet()

        dastate.Fill(ds)

        con.Close()

        Dim states As New List(Of CascadingDropDownNameValue)()

        For Each dtRow As DataRow In ds.Tables(0).Rows

            Dim StateID As String = dtRow("State_Id_Pk").ToString()

            Dim StateName As String = dtRow("State_Name").ToString()

            states.Add(New CascadingDropDownNameValue(StateName, StateID))

        Next

        Return states.ToArray()

    End Function

    <WebMethod()> _

    Public Function FetchCities(knownCategoryValues As String, category As String) As CascadingDropDownNameValue()

        Dim stateId As Integer

        Dim strStates As StringDictionary = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues)

        stateId = Convert.ToInt32(strStates("State"))

        con.Open()

        Dim cmd As New SqlCommand("select * from Tbl_City where State_ID_Fk=@StateID", con)

        cmd.Parameters.AddWithValue("@StateID", stateId)

        cmd.ExecuteNonQuery()

        Dim daregion As New SqlDataAdapter(cmd)

        Dim ds As New DataSet()

        daregion.Fill(ds)

        con.Close()

        Dim cities As New List(Of CascadingDropDownNameValue)()

        For Each dtRow As DataRow In ds.Tables(0).Rows

            Dim RegionID As String = dtRow("City_id_pk").ToString()

            Dim RegionName As String = dtRow("City_Name").ToString()

            cities.Add(New CascadingDropDownNameValue(RegionName, RegionID))

        Next

        Return cities.ToArray()

    End Function

End Class

Note: In the code behind file(.aspx.cs) write the following code on selectedindexchanged event of the City dropdownlist to print the selected Country,State and City as:

Protected Sub ddlCity_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlCity.SelectedIndexChanged

        lblStatus.Text = "You have chosen Country: " & ddlCountry.Text & ", State: " & ddlState.Text & ", City:  " & ddlCity.Text

    End Sub

Now over to you:

"If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates."