How can I display Country and City information in a nested fashion like:
India
Delhi
Bangalore
Chennai
USA
New York
Chicago
UK
London
This can be done in three steps:
Create a object that will fetch data from database and structure it as collection of countries
and collection of cities for each country
// Class used by ObjectDataSource
// Fetches data from database
// Puts the data as list of countries with list of cities within
public class CityHelper
{
private Dictionary<Country, List<City>> cities = new Dictionary<Country,List<City>>();
public CityHelper()
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Pubs"].ConnectionString);
conn.Open();
string sql = "SELECT co.CountryID, ci.CityID, co.Country, ci.City FROM Country co INNER JOIN City ci ON co.CountryID = ci.CountryID";
SqlDataAdapter adapter = new SqlDataAdapter(sql,conn);
DataTable table = new DataTable();
adapter.Fill(table);
foreach (DataRow row in table.Rows)
{
// Add the country, city information
// to the Dictionary as KeyValuePair
Country country = new Country();
country.CountryName = (string)row["Country"];
country.CountryID = (int)row["CountryID"];
City city = new City();
city.CityName = (string)row["City"];
city.CityID = (int)row["CityID"];
if (cities.ContainsKey(country))
{
List<City> cityList = cities[country];
cityList.Add(city);
cities[country] = cityList;
}
else
{
List<City> cityList = new List<City>();
cityList.Add(city);
cities.Add(country, cityList);
}
}
}
public Dictionary<Country, List<City>> GetCities()
{
return cities;
}
}
// Country class with CountryID, CountryName
// This class implements IEquatable interface as
// this class is used as Dictionary Key
public class Country : IEquatable<Country>
{
private int countryID;
public int CountryID
{
get { return countryID; }
set { countryID = value; }
}
private string countryName;
public string CountryName
{
get { return countryName; }
set { countryName = value; }
}
// The below three methods are required
// if this class is to be used for Dictionary Key
public bool Equals(Country c)
{
if (countryName == c.countryName)
return true;
else
return false;
}
public override bool Equals(object obj)
{
Country c = (Country)obj;
return Equals(c);
}
public override int GetHashCode()
{
return countryName.GetHashCode();
}
}
// City class with CityID, CityName
public class City
{
private int cityID;
public int CityID
{
get { return cityID; }
set { cityID = value; }
}
private string cityName;
public string CityName
{
get { return cityName; }
set { cityName = value; }
}
}
Create a nested Gridview with outer GridView bound to the ObjectDataSource
<asp:GridView ID="gv" runat="server" AutoGenerateColumns="false"
DataSourceID="odsCities" OnRowDataBound="gv_RowDataBound">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="lblCountry" runat="server"
Text='<%# Eval("Key.CountryName") %>'></asp:Label>
<asp:GridView ID="gvCities" runat="server"
AutoGenerateColumns="false">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<%# Eval("CityName") %>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="odsCities" runat="server"
TypeName="ForumSamples6.CityHelper" SelectMethod="GetCities" />
Handle the OnRowDataBound of the Outer GridView. In this, bind the inner GridView to the appropriate cities collection
// Called when each row of GridView is bound to data
protected void gv_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
// Get the data as KeyValuePair of Dictionary
KeyValuePair<Country, List<City>> kvp =
(KeyValuePair<Country, List<City>>)e.Row.DataItem;
// Bind to the list of cities
GridView gvCities = (GridView)e.Row.FindControl("gvCities");
gvCities.DataSource = kvp.Value;
gvCities.DataBind();
}
}
|