AJAX Cascading Dropdown Example using SQL Database

The AJAX Cascading Dropdown extender control can also be used with SQL Server Database to populate the target dropdown controls with data retrieved form the database using web service method. Here we will use the same example of Northwind database in which first drop will display the categories retrieved from the categories table of Northwind and on selected index event will call the web service method to populate the other dropdown control with the products that belong to the selected category.

In this sample example for AJAX cascading dropdown control to work with SQL Database we will see how the Category property plays an important role to retrieve the related results from the database tables.

HTML code for Cascading Dropdown Extender Control

<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>

<table border="0" cellpadding="2" cellspacing="0" width="500">
    <tr>
        <td width="100">
            <b>Select Category :</b></td>
        <td>
            <asp:DropDownList ID="drdCategory" runat="server">
            </asp:DropDownList>
            <ajaxToolkit:CascadingDropDown
            ID="CascadingDropDown1"
            runat="server"
            Category="category"
            TargetControlID="drdCategory"
            PromptText="[Select Category]"
            LoadingText="Loading categories..."
            ServicePath="cascadingdataservice.asmx"
            ServiceMethod="GetDropDownCategories">
            </ajaxToolkit:CascadingDropDown>
        </td>
    </tr>
    <tr>
        <td>
            <b>Select Product :</b></td>
        <td>
            <asp:DropDownList ID="drdProduct" runat="server" OnSelectedIndexChanged="drdProduct_SelectedIndexChanged" AutoPostBack="True">
            </asp:DropDownList>
            <ajaxToolkit:CascadingDropDown ID="CascadingDropDown2" runat="server"
            Category="product"
            TargetControlID="drdProduct"
            ParentControlID="drdCategory"
            PromptText="[Select Product]"
            LoadingText="Loading products..."
            ServicePath="cascadingdataservice.asmx"
            ServiceMethod="GetDropDownProducts">
            </ajaxToolkit:CascadingDropDown>
        </td>
    </tr>
    <tr>
        <td colspan="2">
             </td>
    </tr>
    <tr>
        <td colspan="2">
            <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
                <ContentTemplate>
                    <asp:Label ID="Label1" runat="server" Font-Size="16px" ForeColor="Maroon" Style="padding: 5px;"></asp:Label>
                </ContentTemplate>
                <Triggers>
                    <asp:AsyncPostBackTrigger ControlID="drdProduct" EventName="SelectedIndexChanged" />
                </Triggers>
            </asp:UpdatePanel>
        </td>
    </tr>
</table>

When ParentControlID is passed to the Cascading Dropdown extender it sends the value of the selected item of parent dropdown control along with its key name defined in the Category Property of Parent dropdown control. In the sample code you will see that categoryID of the selected item of categories dropdown (Parent Dropdown control) is sent to the web method when any categoryName is selected. It passed the combination of Category Property value and selected item value of Parent Dropdown control as:

category:3
or
category:5

In the above values category is the key name used in the Category property of first dropdown control of the Cascading Dropdown sample. Numeric value is the categoryID of the selected item. knownCategoryValues parameter of the web method provides the value for the selected item of Parent Dropdown control.

To use the above value you can declare a variable of type StringDictionary and parse it using:

StringDictionary categoryValues = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);

StringDictionary class belongs to the Specialized namespace of System.Collections

List<> type Generic collection can be used to accumulate the results retrieved using SqlDataAdapter:

List<AjaxControlToolkit.CascadingDropDownNameValue> cascadingValues = new List<AjaxControlToolkit.CascadingDropDownNameValue>();

In the web method used for web service of cascading dropdown extender control You can use the different names for the web methods but you have to use the same name and datatype of the web method parameters and return type as used in the provided web service sample. E.g.:

public AjaxControlToolkit.CascadingDropDownNameValue[] GetDropDownCategories(string knownCategoryValues, string category)

You have to set the EnableEventValition="false" in the <% @Page %> header section of ASP.Net web page to enable the Asynchronous postback triggers of UpdatePanel.

C# Page Code

protected void drdProduct_SelectedIndexChanged(object sender, EventArgs e)
{
    Label1.BackColor = System.Drawing.Color.FromName("#FFFF80");
    Label1.Text = string.Format("You selected <b>{0}</b> from <b>{1}</b> category.", drdProduct.SelectedItem.Text, drdCategory.SelectedItem.Text);
}

C# WebService Code

string conString = System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ToString();

public CascadingDataService () {

    //Uncomment the following line if using designed components 
    //InitializeComponent(); 
}

[WebMethod]
public AjaxControlToolkit.CascadingDropDownNameValue[] GetDropDownCategories(string knownCategoryValues, string category)
{
    SqlConnection sqlConn = new SqlConnection(conString);
    sqlConn.Open();
    SqlCommand sqlSelect = new SqlCommand("SELECT * FROM Categories", sqlConn);
    sqlSelect.CommandType = System.Data.CommandType.Text;
    SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlSelect);
    DataSet myDataset = new DataSet();
    sqlAdapter.Fill(myDataset);
    sqlConn.Close();

    List<AjaxControlToolkit.CascadingDropDownNameValue> cascadingValues = new List<AjaxControlToolkit.CascadingDropDownNameValue>();

    foreach (DataRow dRow in myDataset.Tables[0].Rows)
    {
        string categoryID = dRow["categoryID"].ToString();
        string categoryName = dRow["categoryName"].ToString();
        cascadingValues.Add(new AjaxControlToolkit.CascadingDropDownNameValue(categoryName,categoryID));
    }

    return cascadingValues.ToArray();
}

[WebMethod]
public AjaxControlToolkit.CascadingDropDownNameValue[] GetDropDownProducts(string knownCategoryValues, string category)
{
    int categoryID;

    StringDictionary categoryValues = AjaxControlToolkit.CascadingDropDown.ParseKnownCategoryValuesString(knownCategoryValues);
    
    categoryID = Convert.ToInt32(categoryValues["category"]);

    SqlConnection sqlConn = new SqlConnection(conString);
    sqlConn.Open();
    SqlCommand sqlSelect = new SqlCommand("SELECT * FROM Products where categoryID = @categoryID", sqlConn);
    sqlSelect.CommandType = System.Data.CommandType.Text;
    sqlSelect.Parameters.Add("@categoryID", SqlDbType.Int).Value = categoryID;
    SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlSelect);
    DataSet myDataset = new DataSet();
    sqlAdapter.Fill(myDataset);
    sqlConn.Close();

    List<AjaxControlToolkit.CascadingDropDownNameValue> cascadingValues = new List<AjaxControlToolkit.CascadingDropDownNameValue>();

    foreach (DataRow dRow in myDataset.Tables[0].Rows)
    {
        string productID = dRow["productID"].ToString();
        string productName = dRow["productName"].ToString();
        cascadingValues.Add(new AjaxControlToolkit.CascadingDropDownNameValue(productName, productID));
    }

    return cascadingValues.ToArray();
}

Continue to next tutorial: AJAX Cascading Dropdown Example using XML Data to learn how to bind the XML data to Cascading Dropdown control of AJAX toolkit.