Link for all dot net and sql server video tutorial playlists
Link for slides, code samples and text version of the video
In this video we will discuss, how to build a jQuery selectmenu using data from database.
SQL Script
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
Stored procedure to retrieve selectmenu data
Create Proc spGetSelectMenuData
as
Begin
Select Id, Name from tblCountries;
Select Id, Name, CountryId from tblCities;
End
City.cs
namespace Demo
{
public class City
{
public int Id { get; set; }
public string Name { get; set; }
public int CountryId { get; set; }
}
}
Country.cs
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; }
}
}
jQuery
$(document).ready(function () {
$(‘#selectMenu’).selectmenu({
width: 200,
select: function (event, ui) {
alert(‘Label = ‘ + ui.item.label + ‘ ‘
+ ‘Value = ‘ + ui.item.value);
}
});
});
HTML
<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>
code-behind
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;
}
}
}
