How to implement Ajax Cascading DropDownList in Asp.Net C# without any Web Service

← PrevNext →

The CascadingDropDownList extender, which comes with the AjaxControlToolkit library, is designed to work with a web service. However, here in this article I’ll show you how to implement Ajax Cascading DropDownList in Asp.Net C# without creating a web service. The effect is more or less the same.

Ajax Cascading DropDownList without WebService

All you need to do is wrap the DropDownList controls inside an Ajax <UpdatePanel> control. This is one simplest way to cascade multiple DropDownList controls using Ajax. The <UpdatePanel> control is readily available in Visual Studio (under AJAX Extension in your toolbox).

So, let’s get on with our example.

Create a Table in SQL Server

We need a database table from which our dropdownlist controls will get its values. I have already created a table for this example.

The SQL Server table: dbo.Books

The table has a list of books, and each book comes under a specified category. The First dropdownlist gets distinct categories from the table and the second (cascading) dropdownlist gets the bookname(s) based on the selected category.

Now, Let’s create our mark up and add few controls in it.

The Markup

I have two DropDownList controls inside the <UpdatePanel> control. The first dropdownlist has attributes such as AutoPostBack with a value set as True and OnSelectedIndexChanged with a value set a Populate_Books.

<asp:ScriptManager ID="scriptManager" runat="server" EnablePageMethods="true" />
<asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional"> 
    <ContentTemplate>

        <asp:DropDownList ID="ddlCategory"
            Font-Names="Verdana"
            AutoPostBack="True"
            OnSelectedIndexChanged="Populate_Books"
            runat="server"> 
                       
        </asp:DropDownList>

        <%--THE SECOND DROPDOWN LIST. GETS ITS DATA BASED ON THE VALUE SELECTED 
            IN THE FIRST DROPDOWNLIST.--%>
        <asp:DropDownList ID="ddlBooks" 
            Font-Names="Verdana" 
            runat="server">

        </asp:DropDownList>

    </ContentTemplate>
</asp:UpdatePanel>
The OnSelectedIndexChanged (after you select a value from the dropdownlist) event will call a code procedure called Populate_Books. If the post back is right, it will fetch a list of BookName from the database table based on the selected category (in the first ddl) and populate the second ddl.
Code behind (C#)
using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data.SqlClient;
using System.Data;

public partial class SiteMaster : System.Web.UI.MasterPage
{

    protected void Page_Load(object sender, System.EventArgs e)
    {
        // ON PAGE LOAD, POPULATE THE FIRST DROP DOWN LIST.
        if (!IsPostBack)
        {
            Bind_DropDownList(ddlCategory, "DISTINCT Category", "Category");
        }
    }

    // POPULATE ddlBooks DROP DOWN LIST BASED ON THE SELECTED CATERORY 
    //(FROM THE FIRST DROP DOWN).

    protected void Populate_Books(object sender, EventArgs args)
    {
        if (!string.IsNullOrEmpty(ddlCategory.SelectedValue))
        {
            Bind_DropDownList(ddlBooks, "BookName", "BookName",
                "Where Category = '" + ddlCategory.SelectedValue + "'");
        }
    }

    // PRIVATE PROCEDURE (A COMMON METHOD) TO BIND THE 
        DROP DOWN LISTS WITH THE SQL SERVER DATABASE TABLE.
    private void Bind_DropDownList(DropDownList ddl, string sSql, string sField, 
        string sWhere = "")
    {
        // PARAMETERS.
        // 1) ddl - THE DROPDOWNLIST CONTROL.
        // 2) sSql - A STRING VALUE THE SQL QUERY OR JUST A FIELD NAME.
        // 3) sField - A STRING CONTAINING THE FIELD NAME TO BIND WITH DROPDOWNLIST.
        // 4) sWhere - AN OPTIONAL STRING VALUE FOR SQL "Where" CLAUSE.

        DataTable dt = new DataTable();

        using (SqlConnection con = new SqlConnection("Data Source=DNA;" +
            "Persist Security Info=False;" + 
            "Initial Catalog=DNAClassified;User Id=sa;Password=;Connect Timeout=30;"))
        {
            string sQuery = "";
            if (string.IsNullOrEmpty(sWhere))
            {
                sQuery = "SELECT " + sSql + " FROM dbo.Books";
            }
            else
            {
                sQuery = "SELECT " + sSql + " FROM dbo.Books " + sWhere;
            }

            using (SqlCommand cmd = new SqlCommand(sQuery))
            {

                SqlDataAdapter sda = new SqlDataAdapter();
                cmd.Connection = con;
                con.Open();
                sda.SelectCommand = cmd;
                sda.Fill(dt);

                ddl.DataSource = dt;

                ddl.DataTextField = sField;
                ddl.DataValueField = sField;

                ddl.DataBind();

                // OPTIONAL.
                ddl.Items.Insert(0, new ListItem("--Select " + sField + "--", "0"));
            }
        }
    }
}
Code behind (Vb)
Option Explicit On
Imports System.Data
Imports System.Data.SqlClient

Partial Class Site
    Inherits System.Web.UI.MasterPage

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        ' ON PAGE LOAD, POPULATE THE FIRST DROP DOWN LIST.
        If Not IsPostBack Then
            Bind_DropDownList(ddlCategory, "DISTINCT Category", "Category")
        End If
    End Sub

    ' POPULATE ddlBooks DROP DOWN LIST BASED ON THE SELECTED CATERORY (FROM THE FIRST DROP DOWN).
    Protected Sub Populate_Books(ByVal sender As Object, ByVal args As EventArgs)
        If ddlCategory.SelectedValue <> "" Then
            Bind_DropDownList(ddlBooks, "BookName", "BookName", 
                "Where Category = '" & ddlCategory.SelectedValue & "'")
        End If
    End Sub

    ' PRIVATE PROCEDURE (A COMMON METHOD) TO BIND THE 
        DROP DOWN LISTS WITH THE SQL SERVER DATABASE TABLE.
    Private Sub Bind_DropDownList(ddl As DropDownList, ByVal sSql As String,
        ByVal sField As String, Optional ByVal sWhere As String = "")

        ' PARAMETERS.
        ' 1) ddl - THE DROPDOWNLIST CONTROL.
        ' 2) sSql - A STRING VALUE THE SQL QUERY OR JUST A FIELD NAME.
        ' 3) sField - A STRING CONTAINING THE FIELD NAME TO BIND WITH DROPDOWNLIST.
        ' 4) sWhere - AN OPTIONAL STRING VALUE FOR SQL "Where" CLAUSE.

        Dim dt As DataTable = New DataTable

        Using con As SqlConnection = New SqlConnection("Data Source=DNA;" & _
            "Persist Security Info=False;" & _
            "Initial Catalog=DNAClassified;User Id=sa;Password=;Connect Timeout=30;")

            Dim sQuery As String = ""
            If Trim(sWhere) = "" Then
                sQuery = "SELECT " & sSql & " FROM dbo.Books"
            Else
                sQuery = "SELECT " & sSql & " FROM dbo.Books " & sWhere
            End If

            Using cmd As SqlCommand = New SqlCommand(sQuery)

                Dim sda As SqlDataAdapter = New SqlDataAdapter
                cmd.Connection = con : con.Open()
                sda.SelectCommand = cmd
                sda.Fill(dt)

                ddl.DataSource = dt

                ddl.DataTextField = sField
                ddl.DataValueField = sField

                ddl.DataBind()

                ' OPTIONAL.
                ddl.Items.Insert(0, New ListItem("--Select " & sField & "--", "0"))
            End Using
        End Using
    End Sub
End Class

The purpose of creating such as example is to keep the cascading method simple. Hope this helps. Thanks for reading.

← PreviousNext →


Like this Article? Subscribe now, and get all the latest articles and tips, right in your inbox.
Delivered by FeedBurner