Cascading DropDownList with Asp .Net Ajax

Populating list to DropDownList Control is one of most common process in web application. Web page reload every time to populate with data.
Images, textboxes and other components will reload and it increases load between client and server.

Asp .Net Ajax makes web page not entirely reload. Populating process will only affect to related DropDownList Controls. Other controls will not reload everytime.

The Example use SQL Server AdventureWorks Database. It will have Department DropDownList and when selection changed second DropDownList will populate related Employee.
Third DropDownList contains Sales Order Number to chosen Employee. Show Sales Data Button will provide brief information based on chosen sales order number.

Here’s the example how to do it:

  1. Create a web project from VS Web Developer 2010
  2. Default.aspx code:
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:ScriptManager ID="ScriptManager1" runat="server">
            </asp:ScriptManager>
    		<table>
            <tr>
            <td><img id="aspajaximg" alt="Asp .Net Ajax" src="images/asp-net-ajax2.png" width="150" height="100" /></td>
            <td><h3>Cascading DropDownList Example</h3></td>
            </tr>
            </table>
            <asp:UpdatePanel ID="UpdatePanel1" runat="server">
            <ContentTemplate>
                <table>
                <tr>
                    <td>
                    <b>Department:</b>
                    </td>
                    <td>
                    <b>Employee:</b>
                    </td>
                    <td>
                    <b>Sales Order #:</b>
                    </td>
                </tr>
                <tr>
                <td>
                <asp:DropDownList ID="department" runat="server" AutoPostBack="true" OnSelectedIndexChanged="department_change">
                    <asp:ListItem Text="" Value="0"></asp:ListItem>
                </asp:DropDownList>
                </td>
                <td>
                <asp:DropDownList ID="employee" runat="server" AutoPostBack="true" OnSelectedIndexChanged="employee_change">
                <asp:ListItem Value="">-Fill in Department-</asp:ListItem>
                </asp:DropDownList>
                </td>
                <td>
                <asp:DropDownList ID="salesorder" runat="server">
                <asp:ListItem Value="">-Fill in Employee-</asp:ListItem>
                </asp:DropDownList>
                </td>
                </tr>
                <tr><td colspan="3"><asp:Button ID="btnshow" runat="server" Text="Show Sales Data" /></td></tr>
                </table>
               <asp:UpdateProgress ID="UpdateProgress1" runat="server" AssociatedUpdatePanelID="UpdatePanel1" >
                <ProgressTemplate>
                    Loading...
                </ProgressTemplate>
                </asp:UpdateProgress>
                <div id="result" runat="server"></div>
            </ContentTemplate>
            </asp:UpdatePanel>
            
        </div>
        </form>
    </body>
    </html>
    

    Developing Asp .Net Ajax requires at least ScriptManager and UpdatePanel additionally UpdateProgress. We have three DropDownList (DDL) Control: department, employee and salesorder.
    They have AutoPostBack attribute set to true. This enable onchange method on html control. OnSelectedIndexChanged is procedure name in code behind.

  3. Source code of Code file:
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.Data.SqlClient;
    
    namespace CascadeDropDown
    {
        public partial class Default : System.Web.UI.Page
        {
            private string connstring;
            protected void Page_Load(object sender, EventArgs e)
            {
                SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
                csb.DataSource = @".\SQLEXPRESS";
                csb.InitialCatalog = "AdventureWorks";
                csb.IntegratedSecurity = true;
                connstring = csb.ConnectionString;
    
                if (!Page.IsPostBack)
                {
                    SqlConnection conn = new SqlConnection(connstring);
                    conn.Open();
                    string query = "select '' as DepartmentID, '' as Name union all select DepartmentID, Name from HumanResources.Department";
                    SqlDataAdapter da = new SqlDataAdapter(query, conn);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
    
                    department.DataTextField = "Name";
                    department.DataValueField = "DepartmentID";
                    department.DataSource = dt;
                    department.DataBind();
    
                    da.Dispose();
                    dt.Dispose();
                    conn.Close();
                }
            }
    
            protected void department_change(object sender, EventArgs e)
            {
                System.Threading.Thread.Sleep(2000);
                SqlConnection conn = new SqlConnection(connstring);
                conn.Open();
                string query = "select '' as EmployeeID, '' as EmpName union all " +
                                "select Emp.EmployeeID, Pers.FirstName + ' ' + Pers.LastName as EmpName from HumanResources.Department as Dept " +
                               "inner join HumanResources.EmployeeDepartmentHistory as EmpDeptHist " +
                                "on Dept.DepartmentID=EmpDeptHist.DepartmentID " +
                                "inner join HumanResources.Employee as Emp " +
                                "on EmpDeptHist.EmployeeID=Emp.EmployeeID " +
                                "inner join Person.Contact as Pers " +
                                "on Pers.ContactID=Emp.ContactID " +
                                "where EndDate is null and Dept.DepartmentID=" + department.SelectedValue;
    
                SqlDataAdapter da = new SqlDataAdapter(query, conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
    
                employee.DataTextField = "EmpName";
                employee.DataValueField = "EmployeeID";
                employee.DataSource = dt;
                employee.DataBind();
    
                da.Dispose();
                dt.Dispose();
                conn.Close();
    
                salesorder.Items.Clear();
                salesorder.Items.Add(new ListItem("-Fill in Employee-"));
    
            }
    
            protected void employee_change(object sender, EventArgs e)
            {
                System.Threading.Thread.Sleep(2000);
                SqlConnection conn = new SqlConnection(connstring);
                conn.Open();
                string query = "select '' as SalesOrderNumber union all " +
                                "select Soh.SalesOrderNumber from Sales.SalesOrderHeader as Soh " +
                                "inner join Sales.SalesPerson as Pers " +
                                "on Soh.SalesPersonID = Pers.SalesPersonID " +
                                "inner join HumanResources.Employee as Emp " +
                                "on Emp.EmployeeID = Pers.SalesPersonID and Emp.EmployeeID=" + employee.SelectedValue;
    
                SqlDataAdapter da = new SqlDataAdapter(query, conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
    
                salesorder.DataTextField = "SalesOrderNumber";
                salesorder.DataValueField = "SalesOrderNumber";
                salesorder.DataSource = dt;
                salesorder.DataBind();
    
                da.Dispose();
                dt.Dispose();
                conn.Close();
            }
    
            protected void btnshow_Click(object sender, EventArgs e)
            {
                SqlConnection conn = new SqlConnection(connstring);
                conn.Open();
    
                SqlCommand comm = new SqlCommand("select PurchaseOrderNumber, OrderDate, TotalDue, DueDate from Sales.SalesOrderHeader where SalesOrderNumber='" + salesorder.SelectedValue + "'", conn);
    
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    string strresulthtml = dr[0].ToString();
                    strresulthtml += "
    " + dr[1].ToString();
                    strresulthtml += "
    " + dr[2].ToString();
                    strresulthtml += "
    " + dr[3].ToString();
    
                    result.InnerHtml = strresulthtml;
                }
                dr.Close();
                comm.Dispose();
                conn.Close();
            }
        }
    }
    

    If Department DDL control selection is changed then department_change procedure will execute to populate employee DDL. It is also same when user choose employee.
    I hold the process for demonstration purposes with Sleep function. Show Sales Data will view PO #, Order Date, Total Due, and Due Date. All of this process are not reloading the entiry web page.

Regards,
Agung Gugiaji

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s