Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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)
)
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;
        }
    }
}

jQuery tutorial for beginners

No comments:

Post a Comment

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.