Step 1 : Create SQL Server tables and insert data
Create table tblCountries
(
Id int primary key identity,
Name nvarchar(50)
)
Go
Insert into tblCountries values ('USA')
Insert into tblCountries values ('India')
Insert into tblCountries values ('UK')
Go
Create table tblCities
(
Id int primary key identity,
Name nvarchar(50),
CountryId int foreign key references tblCountries(ID)
)
Go
Insert into tblCities values ('New York', 1)
Insert into tblCities values ('Los Angeles', 1)
Insert into tblCities values ('Chicago', 1)
Insert into tblCities values ('Bangalore', 2)
Insert into tblCities values ('Chennai', 2)
Insert into tblCities values ('London', 3)
Insert into tblCities values ('Manchester', 3)
Insert into tblCities values ('Glasgow', 3)
Go
Step 2 : Create a stored procedure to retrieve selectmenu data
Create Proc spGetSelectMenuData
as
Begin
Select Id, Name from tblCountries;
Select Id, Name, CountryId from tblCities;
End
Step 3 : Create new asp.net web application project. Name it Demo.
Step 4 : Include a connection string in the web.config file to your database.
<add name="DBCS"
connectionString="server=.;database=SampleDB;integrated security=SSPI"/>
Step 5 : Add a class file to the project. Name it City.cs. Copy and paste the following code.
namespace Demo
{
public class City
{
public int Id { get; set; }
public string Name { get; set; }
public int CountryId { get; set; }
}
}
Step 6 : Add another class file to the project. Name it Country.cs. Copy and paste the following code.
using System.Collections.Generic;
namespace Demo
{
public class Country
{
public int Id { get; set; }
public string Name { get; set; }
public List<City> Cities { get; set; }
}
}
Step 7 : Add a WebForm to the ASP.NET project. Copy and paste the following HTML and jQuery code
<%@ Page Language="C#" AutoEventWireup="true"
CodeBehind="WebForm1.aspx.cs" Inherits="Demo.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="jquery-1.11.2.js"></script>
<script src="jquery-ui.js"></script>
<link href="jquery-ui.css" rel="stylesheet" />
<script type="text/javascript">
$(document).ready(function () {
$('#selectMenu').selectmenu({
width: 200,
select: function (event, ui) {
alert('Label = ' + ui.item.label + ' '
+ 'Value = ' + ui.item.value);
}
});
});
</script>
</head>
<body style="font-family: Arial">
<form id="form1" runat="server">
<select id="selectMenu">
<asp:Repeater ID="repeaterCountries" runat="server">
<ItemTemplate>
<optgroup label="<%#Eval("Name") %>">
<asp:Repeater ID="repeaterCities" runat="server"
DataSource='<%# Eval("Cities")%>'>
<ItemTemplate>
<option value="<%#Eval("Id") %>">
<%#Eval("Name") %>
</option>
</ItemTemplate>
</asp:Repeater>
</optgroup>
</ItemTemplate>
</asp:Repeater>
</select>
</form>
</body>
</html>
Step 8 : Copy and paste the following code in the code-behind file.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace Demo
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
repeaterCountries.DataSource = GetSelectMenuData();
repeaterCountries.DataBind();
}
public List<Country> GetSelectMenuData()
{
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
SqlConnection con = new SqlConnection(cs);
SqlDataAdapter da = new SqlDataAdapter("spGetSelectMenuData", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
da.Fill(ds);
List<Country> listCountries = new List<Country>();
foreach (DataRow countryRow in ds.Tables[0].Rows)
{
Country country = new Country();
country.Id = Convert.ToInt32(countryRow["Id"]);
country.Name = countryRow["Name"].ToString();
DataRow[] cityRows = ds.Tables[1].Select("CountryId="
+ country.Id.ToString());
List<City> listCities = new List<City>();
foreach (DataRow cityRow in cityRows)
{
City city = new City();
city.Id = Convert.ToInt32(cityRow["Id"]);
city.Name = cityRow["Name"].ToString();
city.CountryId = Convert.ToInt32(cityRow["CountryId"]);
listCities.Add(city);
}
country.Cities = listCities;
listCountries.Add(country);
}
return listCountries;
}
}
}
Comments
Post a Comment