How to bind country state city in asp.net using sql server

Bind country state city in asp.net:


In this tutorial, I am going to show you "how to bind country state city in asp.net using SQL Server".  In this process, we need three dropdown i.e. country state city. If you select country dropdown then the related state will be bind as same as if we select state drop down then related city will be bind. It means that It is a cascading process of the binding dropdown.



bind country state city in asp.net


Steps to implement a cascading drop-down list for country state city in asp.net: Here is some simple step to implement.

1. Create three tables country state and city.
2. Create three procedure in SQL Server.
3. Add one Asp.net webform.


Back-End 

Tables

CREATE TABLE [Country](
          [cid] [int] IDENTITY(1,1) NOT NULL Primary Key,
          [cname] [varchar](50) NULL,
)

CREATE TABLE [State]
(
          [sid] [int] IDENTITY(1,1) NOT NULL Primary Key,
          [sname] [varchar](50) NULL,
          [cid] [int] NULL,
)

CREATE TABLE [City]
(
          [ctid] [int] IDENTITY(1,1) NOT NULL Primary Key,
          [ctname] [varchar](50) NULL,
          [sid] [int] NULL)



After creating tables do mapping of it.


How to bind country state city in asp.net


SQL Server Procedures

create proc usp_country_get
as
begin
select * from Country
end

===============================

create proc usp_state_get
@cid int
as
begin
select * from State where cid=@cid
end

===============================

create proc usp_city_get
@sid int
as
begin
select * from City where sid=@sid
end




Connection String in webConfig

<?xml version="1.0"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>
    <system.web>

    
     
      <compilation debug="true" targetFramework="4.5" />
      <!--<httpRuntime targetFramework="4.5" />-->
    </system.web>
<connectionStrings>
  <add name="con" connectionString="data source=SANJAY\SQL2012;initial catalog=DotNet;integrated security=true"/>
 
</connectionStrings>
</configuration>


Front-End

Aspx Page

<table>
                 <tr>
                    <td>Name :</td>
                    <td>
                        <asp:TextBox ID="txtname" runat="server"></asp:TextBox></td>
                </tr>
                <tr>
                    <td>Country :</td>
                    <td>
                        <asp:DropDownList ID="ddlcountry" runat="server" AutoPostBack="true" DataValueField="cid" DataTextField="cname" OnSelectedIndexChanged="ddlcountry_SelectedIndexChanged"></asp:DropDownList></td>
                </tr>
                <tr>
                    <td>State :</td>
                    <td>
                        <asp:DropDownList ID="ddlstate" runat="server" AutoPostBack="true" DataValueField="sid" DataTextField="sname" OnSelectedIndexChanged="ddlstate_SelectedIndexChanged"></asp:DropDownList></td>
                </tr>
                <tr>
                    <td>City :</td>
                    <td>
                        <asp:DropDownList ID="ddlcity" runat="server" DataValueField="ctid" DataTextField="ctname"></asp:DropDownList></td>
                </tr>
                <tr>
                    <td></td>
                    <td>
                        <asp:Button ID="btnsave" runat="server" Text="Save" /></td>
                </tr>
            </table>



Code Behind (.Cs page)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;

namespace DDL_DotNet
{
    public partial class country_state_city : System.Web.UI.Page
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString);
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Fill_Country();
                ddlstate.Items.Insert(0, new ListItem("--select state--""0"));
                ddlcity.Items.Insert(0, new ListItem("--select city--""0"));

                ddlstate.Enabled = false;
                ddlcity.Enabled = false;
            }
        }

        public void Fill_Country()
        {
            DataSet ds = new DataSet();
            ds = GetData("usp_country_get",null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                ddlcountry.DataSource = ds;
                ddlcountry.DataBind();
                ddlcountry.Items.Insert(0, new ListItem("--select country--""0"));
            }
        }
      
        private DataSet GetData(string SP_Name, SqlParameter param)
        {
            SqlDataAdapter da = new SqlDataAdapter(SP_Name,con);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            if (param != null)
            {
                da.SelectCommand.Parameters.Add(param);
            }
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;

        }

        protected void ddlcountry_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddlcountry.SelectedValue == "0")
            {
                ddlstate.SelectedValue = "0";
                ddlstate.Enabled = false;
            }
            else
            {
                ddlstate.Enabled = true;
                SqlParameter param = new SqlParameter("@cid",ddlcountry.SelectedValue);
                DataSet ds = new DataSet();
                ds = GetData("usp_state_get",param);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ddlstate.DataSource = ds;
                    ddlstate.DataBind();
                    ddlstate.Items.Insert(0, new ListItem("--select state--""0"));

                    
                }
            }
            ddlcity.SelectedValue = "0";
            ddlcity.Enabled = false;
        }

        protected void ddlstate_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddlstate.SelectedValue == "0")
            {
                ddlcity.SelectedValue = "0";
                ddlcity.Enabled = false;
            }
            else
            {
                ddlcity.Enabled = true;
                SqlParameter param = new SqlParameter("@sid", ddlstate.SelectedValue);
                DataSet ds = new DataSet();
                ds = GetData("usp_city_get", param);
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ddlcity.DataSource = ds;
                    ddlcity.DataBind();
                    ddlcity.Items.Insert(0, new ListItem("--select city--""0"));
                }
            }
        }
    }
}




Thanks for reading the post if you have any doubt please feel free to comment below.
Previous
Next Post »