Suggested Videos
Part 85 - jquery menu widget
Part 86 - jQuery dynamic menu from database in asp.net
Part 87 - jquery selectmenu widget
In this video we will discuss, how to build a jQuery selectmenu using data from database.
We will be using the following 2 database tables for this demo
Without jQuery the select element is as shown below
With jQuery
Step 1 : Create SQL Server tables and insert data
Step 2 : Create a stored procedure to retrieve selectmenu data
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.
Step 5 : Add a class file to the project. Name it City.cs. Copy and paste the following code.
Step 6 : Add another class file to the project. Name it Country.cs. Copy and paste the following code.
Step 7 : Add a WebForm to the ASP.NET project. Copy and paste the following HTML and jQuery code
Step 8 : Copy and paste the following code in the code-behind file.
Part 85 - jquery menu widget
Part 86 - jQuery dynamic menu from database in asp.net
Part 87 - jquery selectmenu widget
In this video we will discuss, how to build a jQuery selectmenu using data from database.
We will be using the following 2 database tables for this demo
tblCountries
|
tblCities
|
Without jQuery the select element is as shown below
With jQuery
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;
}
}
}
Hi venkat I like the way you teach. I request you to upload nodejs tutorials as it would be helpful for us
ReplyDelete