Ajax Cascading Dependent Dropdown list Bind from Database.

Hi Friends,

Here I will explain how to use Cascading dropdownlist with database using asp.net

First of all add three tables like below..

Country

State

City

After this add reference of AjaxControlToolKit.dll and AjaxMin.dll in you solution References like below,remeber both dll file must be added to references.

ON ASPX PAGE :-

Add Code code like below

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<asp:ToolkitScriptManager ID="ToolkitScriptManager1" runat="server">
    </asp:ToolkitScriptManager>
    <div>
        <table>
            <tr>
                <td>
                    Select Country:
                </td>
                <td>
                    <asp:DropDownList ID="ddlcountry" runat="server">
                    </asp:DropDownList>
                    <asp:CascadingDropDown ID="ccdCountry" runat="server" Category="Country" TargetControlID="ddlcountry"
                        PromptText="Select Country" LoadingText="Loading Countries.." ServiceMethod="BindCountryDetails"
                        ServicePath="~/AjaxCascadingDropDown.asmx">
                    </asp:CascadingDropDown>
                </td>
            </tr>
            <tr>
                <td>
                    Select State:
                </td>
                <td>
                    <asp:DropDownList ID="ddlState" runat="server">
                    </asp:DropDownList>
                    <asp:CascadingDropDown ID="ccdState" runat="server" Category="State" ParentControlID="ddlcountry"
                        TargetControlID="ddlState" PromptText="Select State" LoadingText="Loading States.."
                        ServiceMethod="BindStateDetails" ServicePath="~/AjaxCascadingDropDown.asmx">
                    </asp:CascadingDropDown>
                </td>
            </tr>
            <tr>
                <td>
                    Select Region:
                </td>
                <td>
                   <asp:DropDownList ID="ddlCity" runat="server">
                    </asp:DropDownList>
                     <asp:CascadingDropDown ID="ccdCity" runat="server" Category="City" ParentControlID="ddlState"
                        TargetControlID="ddlCity" PromptText="Select City" LoadingText="Loading City.."
                        ServiceMethod="BindCityDetails" ServicePath="~/AjaxCascadingDropDown.asmx">
                    </asp:CascadingDropDown>
                </td>
            </tr>
        </table>
    </div>
    
Now add a webservice in your project and name it  AjaxCascadingDropDown.asmx

Now open web service and paste namespaces like below

using System;
using System.Collections;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections.Specialized;
using AjaxControlToolkit;
using System.Configuration;
using System.Data; 

namespace DailyTask
{
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.Web.Script.Services.ScriptService()]
    public class AjaxCascadingDropDown : System.Web.Services.WebService
    {
        private static string strconnection = ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
       
        SqlConnection concountry = new SqlConnection(strconnection);
        public AjaxCascadingDropDown()
        {

        }
        [WebMethod]
        public CascadingDropDownNameValue[] BindCountryDetails(string knownCategoryValues, string category)
        {
            concountry.Open();
            SqlCommand cmdcountry = new SqlCommand("select * from task6CountryTable", concountry);
            cmdcountry.ExecuteNonQuery();
            SqlDataAdapter dacountry = new SqlDataAdapter(cmdcountry);
            DataSet dscountry = new DataSet();
            dacountry.Fill(dscountry);
            concountry.Close();
            List<CascadingDropDownNameValue> countrydetails = new List<CascadingDropDownNameValue>();
            foreach (DataRow dtrow in dscountry.Tables[0].Rows)
            {
                string CountryID = dtrow["CountryId"].ToString();
                string CountryName = dtrow["CountryName"].ToString();
                countrydetails.Add(new CascadingDropDownNameValue(CountryName, CountryID));
            }

            return countrydetails.ToArray();
        }


        [WebMethod]
        public CascadingDropDownNameValue[] BindStateDetails(string knownCategoryValues, string category)
        {
            int countryID;
            StringDictionary countrydetails = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);
            countryID = Convert.ToInt32(countrydetails["Country"]);
            concountry.Open();
            SqlCommand cmdstate = new SqlCommand("select * from task6StateTable where CountryID=@CountryID", concountry);
            cmdstate.Parameters.AddWithValue("@CountryID", countryID);
            cmdstate.ExecuteNonQuery();
            SqlDataAdapter dastate = new SqlDataAdapter(cmdstate);
            DataSet dsstate = new DataSet();
            dastate.Fill(dsstate);
            concountry.Close();
            List<CascadingDropDownNameValue> statedetails = new List<CascadingDropDownNameValue>();
            foreach (DataRow dtrow in dsstate.Tables[0].Rows)
            {
                string StateID = dtrow["StateID"].ToString();
                string StateName = dtrow["StateName"].ToString();
                statedetails.Add(new CascadingDropDownNameValue(StateName, StateID));
            }
            return statedetails.ToArray();
        }

        [WebMethod]
        public CascadingDropDownNameValue[] BindCityDetails(string knownCategoryValues, string category)
        {
            int stateID;
            StringDictionary statedetails = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);
            stateID = Convert.ToInt32(statedetails["State"]);
            concountry.Open();
            SqlCommand cmdcity = new SqlCommand("select * from task6CityTable where StateID=@StateID", concountry);
            cmdcity.Parameters.AddWithValue("@StateID", stateID);
            cmdcity.ExecuteNonQuery();
            SqlDataAdapter dacity = new SqlDataAdapter(cmdcity);
            DataSet dscity = new DataSet();
            dacity.Fill(dscity);
            concountry.Close();
            List<CascadingDropDownNameValue> citydetails= new List<CascadingDropDownNameValue>();
            foreach (DataRow dtrow in dscity.Tables[0].Rows)
            {
                string CityID = dtrow["CityID"].ToString();
                string CityName = dtrow["CityName"].ToString();
                citydetails.Add(new CascadingDropDownNameValue(CityName, CityID));
            }
            return citydetails.ToArray();
        }
    }
} 

In Your Web.Config paste code like below :-

<connectionStrings>
    <add name="ApplicationServices" connectionString="Data Source=10.1.1.1;Initial Catalog=DailyTaskoskar;Persist Security Info=True;User ID=Username;Password=password" providerName="System.Data.SqlClient"/>
   
  </connectionStrings>