How do I display Country and City information like below?
- India
- Delhi
- Bangalore
- Chennai
- USA
- New York
- Chicago
- UK
- London
For this example, we will use GridView
and ObjectDataSource
.
The CityHelper
class fetches data from database. Fill a collection of countries. And within each country, fill a collection of cities.
// 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; } } }
Display countries in a GridView. Within a template column, define an inner GridView to display cities. The outer GridView is 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. Find the inner GridView. Bind it to the 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(); } }