How to Use CascadingDropDown Control Using WebService Class in AJAX

Database:
Create Database CascadingDropDown

Use CascadingDropDown

Tables:
CREATE TABLE tbl_States([StateId] [int] IDENTITY(1,1) Primary Key,      [StateName] [varchar](100) NULL)
CREATE TABLE [dbo].[tbl_Cities]([CityId] [int] IDENTITY(1,1) NOT NULL,      [CityName] [varchar](100) NULL,      [StateId] [int] references tbl_States(StateId))



Inserting Data into Tables:
insert into tbl_States(StateName) values ('AndhraPradesh')insert into tbl_Cities(CityName,StateId) values ('Hyderabad',(select StateId from tbl_States whereStateName='AndhraPradesh'))

insert into tbl_States(StateName) values ('Maharastra')insert into tbl_Cities(CityName,StateId) values ('Mumbai',(select StateId from tbl_States whereStateName='Maharastra'))

Stored Procedures:
Create procedure [sp_GetStates]As
Begin
select * from tbl_StatesEnd  

Create Procedure [sp_GetCities](@StateId int = null)As
Begin
if @StateId is null begin
select * from tbl_Citiesend
else
begin
select * from tbl_Cities where StateId = @StateIdendEnd

Web.config:
<connectionStrings>
    <add name="constr" connectionString="User Id=sa;Password=123;Database=CascadingDropDown;Data Source=server2;"/>  </connectionStrings>
Classes:

1. Connection Class:
public class Connection

    public static string GetConnectionString()
    {
        return ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    }
}

2. Data Access Layer Class:
public class DAL{
    static SqlConnection con;
    static SqlCommand cmd;
    static DataSet ds;
    static SqlDataAdapter da;

    public static DataSet ExecuteDataSet(string connectionString, CommandType commandType, string commandText,SqlParameter[] parameters)
    {
        try        {
            con = new SqlConnection(connectionString);
            cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = commandText;
            cmd.CommandType = commandType;
            if (parameters == null)
            {
                da = new SqlDataAdapter(cmd);
                ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            else            {
                foreach (SqlParameter p in parameters)
                {
                    if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                    {
                    }
                    cmd.Parameters.Add(p);
                }
                da = new SqlDataAdapter(cmd);
                ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
        }
        catch (SqlException ex)
        {
            throw new ArgumentException(ex.Message);
        }
    }
}

3. Business Object Layer Class:
public class BOL{
    public DataSet GetStates()
    {
        try        {
            SqlParameter[] p = new SqlParameter[0];
            return DAL.ExecuteDataSet(Connection.GetConnectionString(), CommandType.StoredProcedure, "sp_GetStates", p);
        }
        catch (ArgumentException ex)
        {
            throw new ArgumentException(ex.Message);
        }
    }

    public DataSet GetCitiesByStateId(int stateId)
    {
        try        {
            SqlParameter[] p = new SqlParameter[1];
            p[0] = new SqlParameter("@StateId", stateId);
            return DAL.ExecuteDataSet(Connection.GetConnectionString(), CommandType.StoredProcedure, "sp_GetCities", p);
        }
        catch (ArgumentException ex)
        {
            throw new ArgumentException(ex.Message);
        }
    }
}

WebService.asmx
<%@ WebService Language="C#" Class="WebService" %>

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

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

[ScriptService]

public class WebService  : System.Web.Services.WebService {

    [WebMethod]
    public string HelloWorld() {
        return "Hello World";
    }

    [WebMethod]
    public CascadingDropDownNameValue[] GetStates(string knownCategoryValues, string category)
    {
        BOL obj = new BOL();
        DataSet ds = obj.GetStates();
        List<CascadingDropDownNameValue> l = new List<CascadingDropDownNameValue>();
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            l.Add(new CascadingDropDownNameValue(
             dr["StateName"].ToString(),
             dr["StateId"].ToString()));
        }
        return l.ToArray();
    }

    [WebMethod]
    public CascadingDropDownNameValue[] GetCities(string knownCategoryValues, string category)
    {
        int StateId;
        StringDictionary kv = CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);
        if (!kv.ContainsKey("State") || !Int32.TryParse(kv["State"], out StateId))
        {
            throw new ArgumentException("Couldn't find State.");
        };
        BOL obj = new BOL();
        DataSet ds = obj.GetCitiesByStateId(StateId);      
        List<CascadingDropDownNameValue> l = new List<CascadingDropDownNameValue>();
        foreach (DataRow dr in ds.Tables[0].Rows)
        {
            l.Add(new CascadingDropDownNameValue(
             dr["CityName"].ToString(),
             dr["CityId"].ToString()));
        }
        return l.ToArray();
    }
}

Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" EnableEventValidation="false" Inherits="Default" %><%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="ajaxToolkit" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml"><body>  <form id="form1" runat="server">    <asp:ScriptManager ID="asm" runat="server" />    <div style="height: 209px">        <br />                <br />                    <asp:DropDownList ID="StatesList" runat="server" style="width:auto;"
                        ValidationGroup="add" />                    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
                        ControlToValidate="StatesList" Display="Dynamic" ErrorMessage="*"
                        ValidationGroup="add"></asp:RequiredFieldValidator>                    <br />                    <br />                    <asp:DropDownList ID="CitiesList" runat="server" ValidationGroup="add"  />                    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
                        ControlToValidate="CitiesList" Display="Dynamic" ErrorMessage="*"
                        ValidationGroup="add"></asp:RequiredFieldValidator>                    <br />                    <br />                    <asp:LinkButton ID="LinkButton1" runat="server" ValidationGroup="add"
                        PostBackUrl="~/frmNavigate.aspx">Navigate</asp:LinkButton>                    <br />        <br />        <br />        <br />    </div>    <ajaxToolkit:CascadingDropDown ID="ccd1" runat="server"      ServicePath="WebService.asmx" ServiceMethod="GetStates"
      TargetControlID="StatesList" Category="State" EmptyText="No States"      PromptText="Select State" />    <ajaxToolkit:CascadingDropDown ID="ccd2" runat="server"      ServicePath="WebService.asmx" ServiceMethod="GetCities"      TargetControlID="CitiesList" ParentControlID="StatesList" EmptyText="No Cities" Category="City"
      PromptText="Select City" />    <br />  </form></body>
</html>