The article makes use of the ubiquitous Northwind database (SQL Server CE 4.0 version) and initially presents the user with a dropdown containing a list of categories drawn from the database. Once the user has selected a category, a second dropdown list is populated with products within that category without the page being posted back to the server. The article will show how to use jQuery to achieve this, using either standard HTML select elements, or the Web Pages HTML helpers for forms.
The first code sample shows a Razor page that includes a standard HTML select element being populated by the categories extracted from the Northwind database:
@{ var db = Database.Open("Northwind"); var categories = db.Query("SELECT CategoryId, CategoryName FROM Categories ORDER BY CategoryName"); } <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8" /> <title>Cascading Dropdown Lists</title> <link href="~/favicon.ico" rel="shortcut icon" type="image/x-icon" /> </head> <body> <div> <select id="categoryId" name="categoryId"> <option value="">-- Select Category --</option> @foreach(var category in categories){ <option value="@category.CategoryId">@category.CategoryName</option> } </select> </div> <div> <select id="productId"> <option value="">-- Select Product --</option> </select> </div> </body> </html>
A second select element is added to the form. This will display products from the chosen category. The next code sample shows the same thing using the Web Pages DropDownList helper instead of HTML elements:
@{ var db = Database.Open("Northwind"); var data = db.Query("SELECT CategoryId, CategoryName FROM Categories ORDER BY CategoryName"); var categories = data.Select(item => new SelectListItem { Value = item.CategoryId.ToString(), Text = item.CategoryName }); } <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8" /> <title>Cascading Dropdown Lists</title> </head> <body> <div> @Html.DropDownList("categoryId", "-- Select Category --", categories) </div> <div> @Html.DropDownList("productId", "-- Select Product --", Enumerable.Empty<SelectListItem>()) </div> </body> </html>
The data is transformed into a collection of SelectListItem objects once it has been obtained from the database. The second (products) dropdown list has an empty collection of SelectListItem objects assigned to it.
When a category has been selected, a filtered list of products needs to be obtained and made available to the second dropdown. Since jQuery is the engine that will be responsible for requesting that data, it makes sense to return it in a format that jQuery is comfortable working with - and that's JSON. Here is the complete GetProducts.cshtml file that is responsible for processing the request and returning the data:
@{ Layout = null; var categoryId = UrlData[0].IsEmpty() ? 1 : UrlData[0].AsInt(); var db = Database.Open("Northwind"); var sql = "SELECT ProductId, ProductName FROM Products WHERE CategoryId = @0 ORDER BY ProductName"; var products = db.Query(sql, categoryId); Json.Write(products, Response.Output); }
Since you are returning JSON, you must not allow other characters to be included in the response. For that reason, all the default markup has been removed from the file. Layout has been set to null at the top of the file. This acts to override any possibilty that a Layout page has been set in a PageStart file, or might be set in one in the future which would result in extra characters interferring with the generated JSON. In this particular example, I am expecting the CategoryID value to be available in UrlData. If there isn't one, I have decided to use 1 as a default value for demo purposes. Once the data has been extracted from the database, The Json helper is used to convert it into JSON and return it to the requester.
Finally, here is the client-side script that manages the process. It is exactly the same regardless whether you are using HTML elements or HTML helpers:
<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.8.1.min.js" type="text/javascript"></script> <script> $(function () { var productsSelect = $('#productId'); productsSelect.attr('disabled', true); $('#categoryId').change(function () { var categoryId = $(this).val(); $.getJSON('/GetProducts/' + categoryId, function (products) { productsSelect.attr('disabled', false); productsSelect.empty(); productsSelect.append( $('<option/>') .attr('value', '') .text('-- Select Product --')); $.each(products, function (index, product) { productsSelect.append( $('<option/>') .attr('value', product.ProductId) .text(product.ProductName) ); }); }); }); }); </script>
You need to include a reference to the jQuery library. I have linked to a CDN-hosted copy of jQuery. The script block initially identifies the dropdown list that the products will appear in, and disables it. Then an event handler is registered that executes when the selected value in the categories dropdown list is changed. The selected value (categoryId) is passed to a getJSON command that requests the GetProducts page. The categoryId value is added to the Url as UrlData. The callback function enables the dropdown list, then clears it of any existing option elements. Then it adds a default option ( -- Select Product -- ) and iterates the products returned in the JSON response and adds those to individual option elements.
A GitHub repo is available which includes a sample site featuring the HTML element approach.
