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 where StateName='AndhraPradesh'))

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

Stored Procedures:

Create procedure [sp_GetStates]
As
Begin
select * from tbl_States
End  

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

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>