Drilldown and display hierarchical data in an asp.net gridview using sqldatasource control - Part 34

Suggested Videos 
Part 31 - Displaying images in asp.net gridview using imagefield
Part 32 - Displaying GridView in a GridView
Part 33 - Merging cells in gridview footer row



In this video, we will discuss about drilling down and displaying hierarchical data in gridview controls. We will be using the following 3 database tables for this demo.
1. tblContinents
2. tblCountries
3. tblCities

1. When the page loads, a gridview should display the list of all continents available in tblContinents table
2. When a "Select" button is clicked against a continent, all the countries belonging to the selected continent should be displayed in another gridview.
3. Along the same lines, when a "Select" button is clicked against a country, all the cities belonging to the selected country should be displayed in another gridview.





Use the following sql script to create and populate the required tables.

Create Table tblContinents
(
 ContinentId int identity primary key,
 ContinentName nvarchar(50)
)

Insert into tblContinents values ('Asia')
Insert into tblContinents values ('Europe')
Insert into tblContinents values ('South America') 

Create Table tblCountries
(
 CountryId int identity primary key,
 CountryName nvarchar(50),
 ContinentId int foreign key references dbo.tblContinents(ContinentId)
)

Insert into tblCountries values ('India', 1)
Insert into tblCountries values ('Japan', 1)
Insert into tblCountries values ('Malaysia', 1)

Insert into tblCountries values ('United Kingdom', 2)
Insert into tblCountries values ('France', 2)
Insert into tblCountries values ('Germany', 2)

Insert into tblCountries values ('Argentina', 3)
Insert into tblCountries values ('Brazil', 3)
Insert into tblCountries values ('Colombia', 3) 

Create Table tblCities
(
 CityId int identity primary key,
 CityName nvarchar(50),
 CountryId int foreign key references dbo.tblCountries(CountryId)
)

Insert into tblCities values ('Bangalore', 1)
Insert into tblCities values ('Chennai', 1)
Insert into tblCities values ('Mumbai', 1)

Insert into tblCities values ('Tokyo', 2)
Insert into tblCities values ('Hiroshima', 2)
Insert into tblCities values ('Saku', 2)

Insert into tblCities values ('Kuala Lumpur', 3)
Insert into tblCities values ('Ipoh', 3)
Insert into tblCities values ('Tawau', 3)

Insert into tblCities values ('London', 4)
Insert into tblCities values ('Manchester', 4)
Insert into tblCities values ('Birmingham', 4)

Insert into tblCities values ('Paris', 5)
Insert into tblCities values ('Cannes', 5)
Insert into tblCities values ('Nice', 5)

Insert into tblCities values ('Frankfurt', 6)
Insert into tblCities values ('Eutin', 6)
Insert into tblCities values ('Alsfeld', 6)

Insert into tblCities values ('Rosario', 7)
Insert into tblCities values ('Salta', 7)
Insert into tblCities values ('Corrientes', 7)

Insert into tblCities values ('Rio de Janeiro', 8)
Insert into tblCities values ('Salvador', 8)
Insert into tblCities values ('Brasília', 8)

Insert into tblCities values ('Cali', 9)
Insert into tblCities values ('Montería', 9)
Insert into tblCities values ('Bello', 9)

Drag and drop 3 gridview controls and 3 Sqldatasource controls on webform1.aspx.

Step 1: Configure SqlDataSource1 control to retrieve data from tblContinents table.

Step 2: Asscociate SqlDataSource1 control with Gridview1 control.

Step 3: From GridView1 - GridView Tasks pane, please make sure "Enable selection" checkbox is checked.

Step 4: Configure SqlDataSource2 control to retrieve data from tblCountries table. Configure the WHERE clause for the select statement, by clicking on "WHERE" button. ContinentId should come fromt the selected row in GridView1 control. 


Step 5: Asscociate SqlDataSource2 control with Gridview2 control.

Step 6: From GridView2 - GridView Tasks pane, please make sure "Enable selection" checkbox is checked.

Step 7: Configure SqlDataSource3 control to retrieve data from tblCities table. Configure the WHERE clause for the select statement, by clicking on "WHERE" button. CountryId should come fromt the selected row in GridView2 control. 

Step 8: Asscociate SqlDataSource3 control with Gridview3 control.

At this stage the HTML of your webform should be as shown below. Please note the following
1. Values for DataKeyNames and DataSourceID properties of all the 3 gridview controls.
2. SelectCommand and SelectParameters of SqlDataSource2 and SqlDataSource3 controls.