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"
%>


JoePosted May 5, 2012, 9:06 AM
h
hari kishorePosted Jan 28, 2011, 6:14 AM
thanks for nice example