Populating ASP.NET DropDownList Control using SQL

Aim

We want to Select the Name of the Country from the Dropdownlist and On Selecting a Particular Country, A list of State is populated in the second Dropdownlist control from SQL Database.

Step 1: Creating the Database Structure.

Create Database DDLDemo
Use DDLDemo
Create Table Country(ID int Identity Primary Key, Name nvarchar(50))
Create Table [State](ID int Identity Primary Key, Name nvarchar(50), CountryID int FOREIGN KEY References Country(ID))
Insert Into Country Values ('India')
Insert Into Country Values ('USA')
Insert Into Country Values ('China')
Insert Into Country Values ('Australia')
Insert Into Country Values ('United Kingdom')
Select * from Country
Select * from State
Insert Into State Values ('Delhi',1)
Insert Into State Values ('UP',1)
Insert Into State Values ('Kanpur',1)
Insert Into State Values ('Lucknow',1)
Insert Into State Values ('New York',2)
Insert Into State Values ('Boston',2)
Insert Into State Values ('Chile',2)
Insert Into State Values ('Japan',3)
Insert Into State Values ('Tokyo',3)
Insert Into State Values ('Shinghai',3)
Insert Into State Values ('Vietnam',3)
Insert Into State Values ('Delhi',4)
Insert Into State Values ('Sydney',4)
Insert Into State Values ('Malbourne',4)
Insert Into State Values ('England',5)
Insert Into State Values ('London',5)
Insert Into State Values ('Singapore',5)
--This is a query using Inner join which shows all the city for their respective countries.
Select c.Name as Country, s.Name as State from Country c INNER JOIN State s ON c.ID = s.CountryID

Step 2: Creating the Web Application:

Add a new Webform to the Web Application.
Type the following code in the HTML File.
1. <!DOCTYPE html>
3. <html xmlns="http://www.w3.org/1999/xhtml">
4. <head runat="server">
5. <title></title>
6. <link href="Content/bootstrap.min.css" rel="stylesheet" />
7. </head>
8. <body>
9. <form id="form1" runat="server">
10. <div>
11. <table class="table text-capitalize text-center text-danger" style="font-size: larger">
12. <thead>
13. <tr>
14. <td>Country</td>
15. <td>State</td>
16. </tr>
17. </thead>
18. <tbody>
19. <tr>
20. <td>
21. <%--Please put Autopostback Property true--%>
22. <asp:DropDownList ID="ddl_Country" runat="server" AutoPostBack="True"
24. OnSelectedIndexChanged="ddl_Country_SelectedIndexChanged"></asp:DropDownList>
25. </td>
26. <td>
27. <asp:DropDownList ID="ddl_State" runat="server"></asp:DropDownList>
28. </td>
29. </tr>
30. </tbody>
31. </table>
32. </div>
33. </form>
34. </body>
35. </html>
Type the following code in the Code behind file by Pressing F7 Key.
1. using System;
2. using System.Data;
3. using System.Data.SqlClient;
4. namespace DDLPopulateDemo
5. {
6. public partial class WebForm1 : System.Web.UI.Page
7. {
8. string CS = "Data Source=(local);Initial Catalog=DDLDemo;Integrated Security=True"; //Creating a String to Connect to Database
9. protected void Page_Load(object sender, EventArgs e)
10. {
11. if (!IsPostBack) //Used to Check that Whether the page loads first time or Second Time
12. {
13. using (SqlConnection con = new SqlConnection(CS)) //Created Connection to Connect to Database
14. {
15. con.Open(); //Opened the Connection
16. SqlCommand cmd = new SqlCommand("Select * from Country", con); //Fetching all the Data using SQL Query through Country
18. Table
19. SqlDataAdapter da = new SqlDataAdapter(cmd); // Initializing a Data Adapter to Store the data
20. DataSet ds = new DataSet(); //Created a Dataset
21. da.Fill(ds); //Filled the Dataset with Values contained in DataAdapter
22. ddl_Country.DataSource = ds; //Setting the Datasource of DDL to Dataset
23. ddl_Country.DataTextField = "Name"; //This is the name of the column of Database through which we want to Show the
25. Values
26. ddl_Country.DataValueField = "ID"; //This is the Primary key on which the Data is fetched
27. ddl_Country.DataBind(); //Binding the Fetched Data to the DDL
28. }
29. }
30. }
31. protected void ddl_Country_SelectedIndexChanged(object sender, EventArgs e) //When the Selected index is changed of the DDL, It
33. postbacks the Data and This event is fired
34. {
35. using (SqlConnection con = new SqlConnection(CS))
36. {
37. con.Open();
38. //Created a custom Query which Selects data from the State Table on the Basis of the Foreign key which was Country ID
39. SqlCommand cmd = new SqlCommand("Select * from State where CountryID = '" + (Convert.ToInt32(ddl_Country.SelectedValue)) +
41. "'", con);
42. //Rest of the Code Remains same as Page_Load method
43. SqlDataAdapter da = new SqlDataAdapter(cmd);
44. DataSet ds = new DataSet();
45. da.Fill(ds);
46. ddl_State.DataSource = ds;
47. ddl_State.DataTextField = "Name";
48. ddl_State.DataValueField = "ID";
49. ddl_State.DataBind();
50. }
51. }
52. }
53. }