Saturday 26 March 2016

Cascading ComboBox in Windows Application using C# .Net

******************** Sql Query*************...................

create database test

use test

--create table county,state,city in database...................

create table CountryDetails(CountryId int primary key,CountryName varchar(50) unique)

create table StateDetails(StateId int primary key,StateName varchar(50)unique,CountryId int foreign key references CountryDetails(CountryId) on delete cascade)

create table CityDetails(CityId int primary key,CityName varchar(50)unique,StateId int foreign key references StateDetails(StateId) on delete cascade)

--insert into tables........................

insert into CountryDetails values(1,'India')
insert into CountryDetails values(2,'England')

insert into StateDetails values(1,'Uttar Pradesh',1)
insert into StateDetails values(2,'Madhya Pradesh',1)

insert into CityDetails values(1,'Varanasi',1)
insert into CityDetails values(2,'Ghaziabad',1)
insert into CityDetails values(3,'Lucknow',1)
insert into CityDetails values(4,'Bhopal',2)
insert into CityDetails values(5,'Reewa',2)

****************** C# .Net      ******************

First we will take Windows Application and add app.config file for SqlConnection String

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="dbcon"
     connectionString="Data Source=DESKTOP-BIISIIA;Initial Catalog=test;Integrated Security=True"
     providerName="System.Data.SqlClient" />
  </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6" />
    </startup>
</configuration>

********************* Code for Form1.cs ***************************

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Configuration;


namespace CascadingComboBox
{

public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

//SqlConnection con = new SqlConnection(@"Data Source=DESKTOP-BIISIIA;Initial Catalog=test;Integrated Security=True");

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbcon"].ConnectionString);

private void Form1_Load(object sender, EventArgs e)
{
CountryBind();
}

// code for Country bind..................

private void CountryBind()
{
SqlCommand cmd = new SqlCommand("select * from CountryDetails", con);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
DataRow dr = dt.NewRow();
dr.ItemArray = new object[] { 0, "Select Country" };
dt.Rows.InsertAt(dr, 0);
cbCountry.DisplayMember = "CountryName";
cbCountry.ValueMember = "CountryId";
cbCountry.DataSource = dt;
}


// code for State  bind..................

private void StateBind(int countryid)
{
SqlCommand cmd = new SqlCommand("select * from StateDetails where CountryId=@CountryId", con);
cmd.Parameters.AddWithValue("@CountryId", countryid);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
DataRow dr = dt.NewRow();
dr.ItemArray = new object[] { 0, "Select State" };
dt.Rows.InsertAt(dr, 0);
cbState.DisplayMember = "StateName";
cbState.ValueMember = "StateId";
cbState.DataSource = dt;
}

// code for City bind..................

private void CityBind(int stateid)
{
SqlCommand cmd = new SqlCommand("select * from CityDetails where StateId=@StateId", con);
cmd.Parameters.AddWithValue("@StateId", stateid);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
DataRow dr = dt.NewRow();
dr.ItemArray = new object[] { 0, "Select City" };
dt.Rows.InsertAt(dr, 0);
cbCity.DisplayMember = "CityName";
cbCity.ValueMember = "CityId";
cbCity.DataSource = dt;

}
private void cbCountry_SelectedIndexChanged(object sender, EventArgs e)
{

// MessageBox.Show(cbCountry.SelectedValue.ToString());
int countryid =Convert.ToInt32(cbCountry.SelectedValue);
StateBind(countryid);

}

private void cbState_SelectedIndexChanged(object sender, EventArgs e)
{
int stateid = Convert.ToInt32(cbState.SelectedValue);
CityBind(stateid);
}
}
}

Result






0 comments:

Post a Comment