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> 

 

 

Bind Dependent Dropdown Data to Asp.net Dropdownlist from Database in C# using 3 Tier Architecture

Bind Dependent Dropdown Data to Asp.net Dropdown list from Database in C# using 3 Tier Architecture.

Hi Friends,

Here I will explain how to bind dependent dropdown list or show dependent dropdown data in drop-down list from database in asp.net using C# .net.

Before implement this example first design tables in your database as shown below :-

Add table name Country

Add some entries in country

Add table name State

add entry is state table 

Add table name City

add some entries in City table

ON ASPX PAGE :-


<table>
  <tr>
          <td><label class="Label">
                                    Country
                                </label>
          </td>
          <td><asp:DropDownList ID="ddCountryDrpdwn" CssClass="DropDown" runat="server" AutoPostBack="true"
                                    OnSelectedIndexChanged="ddCountryDrpdwn_SelectedIndexChanged">
                                </asp:DropDownList>
                                <asp:RequiredFieldValidator ID="RFVCountry" runat="server" ControlToValidate="ddCountryDrpdwn"
                                    Display="Dynamic" SetFocusOnError="true" ForeColor="Red" CssClass="failureNotification"
                                    ErrorMessage="Select country." InitialValue="0" ToolTip="Select country." ValidationGroup="sbmitbtn"></asp:RequiredFieldValidator>
          </td>
 </tr>
<tr>
          <td><label class="Label">
                                    State
                                </label>
          </td>
          <td><asp:DropDownList ID="ddStateDrpdwn" CssClass="DropDown" runat="server" AutoPostBack="true"
                                    OnSelectedIndexChanged="ddStateDrpdwn_SelectedIndexChanged">
                                </asp:DropDownList>
                                <asp:RequiredFieldValidator ID="RFVState" runat="server" ControlToValidate="ddStateDrpdwn"
                                    Display="Dynamic" SetFocusOnError="true" ForeColor="Red" CssClass="failureNotification"
                                    ErrorMessage="Select state." InitialValue="0" ToolTip="Select state." ValidationGroup="sbmitbtn"></asp:RequiredFieldValidator>
          </td>
 </tr>
<tr>
          <td><label class="Label">
                                    City
                                </label>
          </td>
          <td><asp:DropDownList ID="ddCityDrpdwn" CssClass="DropDown" runat="server" AutoPostBack="true"
                                    OnSelectedIndexChanged="ddCityDrpdwn_SelectedIndexChanged">
                                </asp:DropDownList>
                                <asp:RequiredFieldValidator ID="RFVCity" runat="server" ControlToValidate="ddCityDrpdwn"
                                    Display="Dynamic" SetFocusOnError="true" ForeColor="Red" CssClass="failureNotification"
                                    ErrorMessage="Select city." InitialValue="0" ToolTip="Select city." ValidationGroup="sbmitbtn"></asp:RequiredFieldValidator>
          </td>
 </tr>


</table>


On CODE BEHIND ASPX.CS PAGE


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using BusinessLayer;
using DataLayer;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Drawing;
using System.Globalization;
using System.Text.RegularExpressions;


 public void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bindcountry();
        }
    }


 //Function to Bind Counrty
    protected void Bindcountry()
    {
        DS = BusinessLayer.Home.getcountry();
        if (DS.Tables[0].Rows.Count > 0)
        {

            ddCountryDrpdwn.DataSource = DS;
            ddCountryDrpdwn.DataTextField = "CountryName";
            ddCountryDrpdwn.DataValueField = "Id";
            ddCountryDrpdwn.DataBind();
            ddCountryDrpdwn.Items.Insert(0, new ListItem("--Select--", "0"));

        }
        else if (DS.Tables[0].Rows.Count == 0)
        {
            ddCountryDrpdwn.DataSource = null;
            ddCountryDrpdwn.DataBind();
        }

    }


 //Function to Bind State
    protected void Bindstate()
    {

        if (ddCountryDrpdwn.SelectedValue != "0")
        {
            int ctr = Convert.ToInt32(ddCountryDrpdwn.SelectedValue);
            DS = BusinessLayer.Home.getstate(ctr);
            if (DS.Tables[0].Rows.Count > 0)
            {
                ddStateDrpdwn.Focus();
                ddStateDrpdwn.DataSource = DS;
                ddStateDrpdwn.DataTextField = "StateName";
                ddStateDrpdwn.DataValueField = "Id";
                ddStateDrpdwn.DataBind();
                ddStateDrpdwn.Items.Insert(0, new ListItem("--Select--", "0"));


            }
            else if (DS.Tables[0].Rows.Count == 0)
            {
                ddStateDrpdwn.DataSource = null;
                ddStateDrpdwn.DataBind();
                ddStateDrpdwn.Focus();
            }
        }
        else
        {
            ddStateDrpdwn.Items.Insert(0, new ListItem("--Select--", "0"));
        }
    }

    //Function to Bind City
    protected void Bindcity()
    {

        if (ddStateDrpdwn.SelectedValue != "0")
        {
            int ctrr = Convert.ToInt32(ddStateDrpdwn.SelectedValue);
            DS = BusinessLayer.Home.getcity(ctrr);
            if (DS.Tables[0].Rows.Count > 0)
            {
                ddCityDrpdwn.Focus();
                ddCityDrpdwn.DataSource = DS;
                ddCityDrpdwn.DataTextField = "CityName";
                ddCityDrpdwn.DataValueField = "Id";
                ddCityDrpdwn.DataBind();
                ddCityDrpdwn.Items.Insert(0, new ListItem("--Select--", "0"));

            }
            else if (DS.Tables[0].Rows.Count == 0)
            {
                ddCityDrpdwn.Items.Clear();
                ddCityDrpdwn.DataSource = null;
                ddCityDrpdwn.DataBind();
                ddCityDrpdwn.Items.Insert(0, new ListItem("--Select--", "0"));
                ddStateDrpdwn.Focus();
            }
        }
        else
        {
            ddCityDrpdwn.Items.Insert(0, new ListItem("--Select--", "0"));
        }
    }

    //On Country selected  index change
    protected void ddCountryDrpdwn_SelectedIndexChanged(object sender, EventArgs e)
    {

        if (ddCountryDrpdwn.SelectedValue == "0")
        {
            ddStateDrpdwn.DataSource = ddCityDrpdwn.DataSource = null;
            ddStateDrpdwn.DataBind();
            ddCityDrpdwn.DataBind();
            ddStateDrpdwn.Items.Clear();
            ddCityDrpdwn.Items.Clear();
            ddStateDrpdwn.Items.Insert(0, new ListItem("--Select--", "0"));
            ddCityDrpdwn.Items.Insert(0, new ListItem("--Select--", "0"));
            ddCountryDrpdwn.Focus();
            txtLandlinecode.Text = "";

        }
        else
        {
            Bindstate();
        }
    }

    //On State selected  index change
    protected void ddStateDrpdwn_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (ddStateDrpdwn.SelectedValue == "0")
        {
            ddCityDrpdwn.DataSource = null;
            ddCityDrpdwn.DataBind();
            ddCityDrpdwn.Items.Clear();
            ddCityDrpdwn.Items.Insert(0, new ListItem("--Select--", "0"));
            ddStateDrpdwn.Focus();
            txtLandlinecode.Text = "";
        }
        else
        {
            Bindcity();
        }
    }


ON BUSSINESSLAYER ADD CODE :-

        public static DataSet getcountry()
        {
            return DataLayer.Home.getcountry();
        }
        public static DataSet getstate(int ctr)
        {
            return DataLayer.Home.getstate(ctr);
        }
        public static DataSet getcity(int ctrr)
        {
            return DataLayer.Home.getcity(ctrr);
        }



ON DATALAYER ADD CODE :-

        //Get state
        public static DataSet getstate(int ctr)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();

            SqlParameter para = new SqlParameter("@CountryName", ctr);
            paralist.Add(para);

            return DataLayer.Data.GetDataSet("usp_selectstate", paralist);
        }

        //Get City
        public static DataSet getcity(int ctrr)
        {
            List<SqlParameter> paralist = new List<SqlParameter>();

            SqlParameter para = new SqlParameter("@StateName", ctrr);
            paralist.Add(para);

            return DataLayer.Data.GetDataSet("usp_selectcity", paralist);
        }

        //for country bind from database
        public static DataSet getcountry()
        {
            return DataLayer.Data.GetDataByQery("select Id,CountryName from      Country");
        } 

ON YOUR DATA CLASS ADD CODE :- 

static string strConn = ConfigurationManager.AppSettings["DBConn"].ToString();
private static SqlConnection DBConn = null;

 public static DataSet GetDataByQery(string Query)
        {
            SqlConnection cn = Data.Connection;
            if (cn.State != ConnectionState.Open)
            {
                cn.Open();
            }
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = Query;
            cmd.CommandType = CommandType.Text;
            cmd.Connection = cn;

            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();

            da.SelectCommand = cmd;
            da.Fill(ds);
            cn.Close();
            return ds;
        } 

public static DataSet GetDataSet(string SPName, List<SqlParameter> Parameters)
        {
            using (SqlConnection con = Connection)
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = SPName;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    cmd.Connection = con;

                    if (Parameters != null)
                    {
                        foreach (SqlParameter parameter in Parameters)
                        {
                            cmd.Parameters.Add(parameter);
                        }
                    }

                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter();

                    da.SelectCommand = cmd;
                    da.Fill(ds);
                    con.Close();
                    return ds;
                }
            }
        } 

 IN YOUR WEBCONFIG ADD CODE :-

<appsetting>
<add key="DBConn" value="Data Source=XXXXXX; Initial Catalog=OskarDB; User ID=user; Password=pass;"/> 
<appsetting> 

STORED PROCEDURE :-

For selecting State :-

ALTER PROCEDURE [dbo].[usp_selectstate]
@CountryName int
As
BEGIN
    Select StateName,Id From State
    WHERE fkCountryId=@CountryName
    ORDER BY [StateName]ASC
END 

For selecting City :-  


ALTER PROCEDURE [dbo].[usp_selectcity]
@StateName int
As
BEGIN
    Select CityName,Id From City
    WHERE fkStateId = @StateName
    ORDER BY [CityName]ASC
END 

By following this way you can bind dependent dropdown list in 3 Tier .