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>