Sql server, .net and c# video tutorial: jQuery selectmenu from database

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

tblCountries
jquery datatables select menu tblCities
jquery selectmenu database

Without jQuery the select element is as shown below
jquery selectmenu in asp.net

With jQuery
jquery selectmenu database asp.net

Step 1 : Create SQL Server tables and insert data

Create table tblCountries

(

Id int primary key identity,

Name nvarchar(50)

)

Insert into tblCountries values ('USA')

Insert into tblCountries values ('India')

Insert into tblCountries values ('UK')

Create table tblCities

(

Id int primary key identity,

Name nvarchar(50),

CountryId int foreign key references tblCountries(ID)

)

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)

Step 2 : Create a stored procedure to retrieve selectmenu data

Create Proc spGetSelectMenuData

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.

<addname="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

{

publicclassCity

{

publicint Id { get; set; }

publicstring Name { get; set; }

publicint 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

{

publicclassCountry

{

publicint Id { get; set; }

publicstring Name { get; set; }

publicList<City> Cities { get; set; }

}

Step 7 : Add a WebForm to the ASP.NET project. Copy and paste the following HTML and jQuery code

<%@PageLanguage="C#"AutoEventWireup="true"

CodeBehind="WebForm1.aspx.cs"Inherits="Demo.WebForm1"%>

<!DOCTYPEhtml>

<htmlxmlns="http://www.w3.org/1999/xhtml">

<headrunat="server">

<scriptsrc="jquery-1.11.2.js"></script>

<scriptsrc="jquery-ui.js"></script>

<linkhref="jquery-ui.css"rel="stylesheet"/>

<scripttype="text/javascript">

$(document).ready(function () {

$('#selectMenu').selectmenu({

width: 200,

select: function (event, ui) {

alert('Label = ' + ui.item.label + ' '

+ 'Value = ' + ui.item.value);

}

});

</script>

</head>

<bodystyle="font-family: Arial">

<formid="form1"runat="server">

<selectid="selectMenu">

<asp:RepeaterID="repeaterCountries"runat="server">

<optgrouplabel="<%#Eval("Name") %>">

<asp:RepeaterID="repeaterCities"runat="server"

DataSource='<%# Eval("Cities")%>'>

<optionvalue="<%#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

{

publicpartialclassWebForm1 : System.Web.UI.Page

{

protectedvoid Page_Load(object sender, EventArgs e)

{

repeaterCountries.DataSource = GetSelectMenuData();

repeaterCountries.DataBind();

}

publicList<Country> GetSelectMenuData()

{

string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

SqlConnection con = newSqlConnection(cs);

SqlDataAdapter da = newSqlDataAdapter("spGetSelectMenuData", con);

da.SelectCommand.CommandType = CommandType.StoredProcedure;

DataSet ds = newDataSet();

da.Fill(ds);

List<Country> listCountries = newList<Country>();

foreach (DataRow countryRow in ds.Tables[0].Rows)

{

Country country = newCountry();

country.Id = Convert.ToInt32(countryRow["Id"]);

country.Name = countryRow["Name"].ToString();

DataRow[] cityRows = ds.Tables[1].Select("CountryId="

+ country.Id.ToString());

List<City> listCities = newList<City>();

foreach (DataRow cityRow in cityRows)

{

City city = newCity();

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;

}

jQuery tutorial for beginners

jQuery selectmenu from database

1 comment: