Create a new database named SampleData.mdf.
This database will contain 2 tables Cars (to store the
cars data) and CarModels (to store the models for each
car in Cars table).
Right click on the App_Data folder
and select Add New Item. In the new item dialog box, select the SQL Database from the template and change the name to SampleData.mdf. Click Add.
Now the database is created and added to your project. You
can view the data connection to the SampleData.mdf in
the Server Explorer (Ctrl + W, L).
In the server explorer, under the SampleData.mdf,
click on Tables and create 2 tables with the following structure.
Figure 3
Make the fields ModelId (CarModels) and CarId (Cars) as
Identity columns for auto-increment number.
Note: The SampleData.mdf used in the
project is available in the Source Code provided with this article.
Creating the datasets for accessing the Cars and
CarModels data
Right click on the project in the solution explorer and
click on Add New Item. In the dialog box select DataSet and name it as dsCars.xsd and click Add Button. Your dataset will be created
under App_Code folder under the root application
folder.
Figure 4
As soon as the dataset was created, the TableAdapter
Configuration Wizard pops up and you should configure the dataset using
the wizard.
Configuring the DataSets
·
From the data connection dropdownlist of the wizard, select the SampleData.mdf file and click Next.
Figure 5
·
You are asked to save the SampleDataConnectionString
in the web.config file. Check the box next to Yes and click Next.
Figure 6
·
In the command type, choose Use SQL Statements
and click Next.
·
In the Enter a SQL Statement, enter the
following query and click Next.
Figure 7
·
In the Choose Methods to Generate,
uncheck the Fill a DataTable
and in the Return a DataTable Method name use GetAllCars.
Figure 8
·
Wizard Results will be displayed with generated SELECT statement,
table mappings and a Get method.
·
Save the DataSet and Close it.
·
Repeat the Creating DataSets again to add
another dataset dsCarModels.xsd which holds data of
CarModels for the selected Car.
Note: When creating the second dataset,
you will not see the SampleData.mdf in the data connection dropdownlist of step
1. Since you have already stored the connection string in the web.config file,
you will see SampleDataConnectionString (web.config) and you should select it.
·
Use the following query in the Enter a SQL Statement of
configuring the dataset.
SELECT ModelId, CarId, ModelName FROM
CarModels WHERE (CarId = @carId)
Figure 9
·
For the Return a DataTable Method name use GetModelsByCarId.
Figure 10
Creating a webservice to get data from database
Now we have the datasets configured, we write a webservice
to get all the car models for a given CarId. This webservice will be used by
our cascadingdropdown. Later in this article we will see how to configure the
CascadingDropDown to use this webservice.
·
Right Click on the project and click Add New. In the Add
New Item dialog box, select WebService from the list and name it as CarsService.asmx.
Figure 11
·
Two files will be created in your application. One file CarsService.asmx is created in the root of your application
and the code-behind file for the webservice is created in the App_Code
folder with name CarsService.cs.
Note: In Listing 11 the check box Place
code in separate file is checked. Check the box for all the items you create
for which it is applicable. This will separate the code file from the page file.
·
Open the CarsService.cs location
in the App_Code. This is the class file (WebService)
where we write the web method to get all the car models for a given CarId.
·
Add the following namespaces in bold to the existing file
(CarsService.cs).
Listing 1
using System;
using System.Web;
using System.Collections;
using System.Web.Services;
using System.Web.Services.Protocols;
<span class=Bold>using System.Collections.Generic;</span>
<span class=Bold>using AjaxControlToolkit;</span>
<span class=Bold>using System.Data;</span>
<span class=Bold>using System.Data.SqlClient;</span>
·
Your webservice should be decorated with the ScriptService
attribute. Add the following line above the class definition.
Listing 2
/// <summary>
/// Summary description for CarsService
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.Web.Script.Services.ScriptService]
public class CarsService : System.Web.Services.WebService {·
We will write a WebMethod GetModelsByCarId
which returns an array of CascadingDropDownNameValue.
The method signature should match exactly with the one given below including
the parameter names.
Listing 3
[WebMethod]
public CascadingDropDownNameValue[] GetModelsByCarId(string knownCategoryValues, string category)
{·
The knowCategoryValues will contain the
values of the Category defined in the CascadingDropDown
control separated by “;” delimiter.
Listing 4
// Split the knownCategoryValues on ":" with ";" delimiter
// For the first dropdownlist, the values will be "undefined: id of the dropdownelement"
// ex: "undefined: 13;"
// The string at index 1 will be the CarId selected in the dropdownlist.
string[] _categoryValues = knownCategoryValues.Split(':', ';');·
By splitting the knowCategoryValues we
will get the Id of the car selected in the dropdownlist at index 1 in the
string[] _categoryValues
Listing 5
// Convert the element at index 1 in the string[] to get the CarId
int _carID = Convert.ToInt32(_categoryValues[1]);
·
Create a generic List<T> of type CascadingDropDownNameValue
to hold the values returned by the CarsModelTableAdapters.
Listing
// Create a List<T> of CascadingDropDownNameValue to hold the CarModels data
List<CascadingDropDownNameValue> _carModels = new List<CascadingDropDownNameValue>();
·
Loop through all the DataRows in the
DataTable returned by the GetModelsByCarId method and
add them to the List<T> of type CascadingDropDownNameValue.
Listing 7
// Create an instance of CarModels TableAdapter
dsCarModelsTableAdapters.CarModelsTableAdapter _carModelAdapter =
new dsCarModelsTableAdapters.CarModelsTableAdapter();
// For each datarow in the DataTable returned by the GetModelsByCarId method, add
// the modelname and modelid to the List<T>
foreach (DataRow _row in _carModelAdapter.GetModelsByCarId(_carID)) {
_carModels.Add(new CascadingDropDownNameValue(_row["ModelName"].ToString(),
_row["ModelId"].ToString())); }Note: The CascadingDropDown needs a
CascadingDropDownNameValue[] to be displayed in the Target DropDownList.
Listing 8
// Web method to get all the car models for a given carId
// In params: knownCategoryValues from the cascading dropdown
// Out params: CascadingDropDownNameValue array
[WebMethod]
public CascadingDropDownNameValue[] GetModelsByCarId(string knownCategoryValues,
string category)
{
// Split the knownCategoryValues on ":" with ";" delimiter
// For the first dropdownlist, the values will be "undefined: id of the
// dropdownelement"
// ex: "undefined: 13;"
// The string at index 1 will be the CarId selected in the dropdownlist.
string[] _categoryValues = knownCategoryValues.Split(':', ';');
// Convert the element at index 1 in the string[] to get the CarId
int _carID = Convert.ToInt32(_categoryValues[1]);
// Create a List<T> of CascadingDropDownNameValue to hold the CarModels
// data
List<CascadingDropDownNameValue> _carModels = new List<CascadingDropDownNameValue>();
// Create an instance of CarModels TableAdapter
dsCarModelsTableAdapters.CarModelsTableAdapter _carModelAdapter =
new dsCarModelsTableAdapters.CarModelsTableAdapter();
// For each datarow in the DataTable returned by the GetModelsByCarId
// method, add the modelname and modelid to the List<T>
foreach (DataRow _row in _carModelAdapter.GetModelsByCarId(_carID))
{
_carModels.Add(new CascadingDropDownNameValue(_row["ModelName"].ToString(),
_row["ModelId"].ToString()));
}
// convert to array and return the vlaues
return _carModels.ToArray();
}Designing your aspx page
Now we use the WebService and the CascadingDropDown
in a page to display the Cars and CarModels.
·
In the Default.aspx page add a
reference to the AjaxControlToolkit.
Listing 9
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="ajaxToolkit" %>
·
On Default.aspx page, drag two DropDownList. Name them as ddlCars and ddlCarModels.
Listing 10
Car:<asp:DropDownList ID="ddlCars" runat="server" DataSourceID="ods_Cars"
DataTextField="CarName"
DataValueField="CarId"></asp:DropDownList>
Model:<asp:DropDownList ID="ddlCarModels" runat="server"></asp:DropDownList>
·
Create an ObjectDataSource to display all
the Cars available in the ddlCars dropdownlist. By
default, all the cars available in Cars table will be shown when the page
loads.
Listing 11
<asp:ObjectDataSource ID="ods_Cars" runat="server"
OldValuesParameterFormatString="original_{0}" SelectMethod="GetAllCars"
TypeName="dsCarsTableAdapters.CarsTableAdapter"></asp:ObjectDataSource>
·
Add a CascadingDropDown control to the
page and set the following properties.
Category = Cars [Category for the knowCategoryValues]
LoadingText = “Please wait..” [Message to be shown
while the dropdownlist is loading]
ParentControlID = ddlCars [DropDownList from which the
CategoryId should be taken (CarId in this example)]
PromptText=”Select a model” [Text to be displayed if a
selection is not made]
TargetControlID = ddlCarModels [DropDownList into which
the CascadingDropDownNameValue should be loaded]
ServicePath = “CarsService.asmx” [Path to the
webservice]
ServiceMethod = “GetModelsByCarId” [WebMethod that should
be called to get the values]
Listing 12
<ajaxToolkit:CascadingDropDown ID="CascadingDropDown1" runat="server"
Category="Cars" LoadingText="Please wait..." ParentControlID="ddlCars" PromptText="Select a model"
TargetControlID="ddlCarModels" ServicePath="CarsService.asmx"
ServiceMethod="GetModelsByCarId" />
Build the website (Ctrl + F6) and Run (F5). If you see the
Enable Debug Dialog box, click Yes. The ASP.NET AJAX CascadingDropDown is in
action getting values from the database.
