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 .