An Extensive Examination of the DataGrid Web Control: Part 10

The Tenth Part in a Multi-Part Series

This article is the tenth piece of a multi-part series on using the DataGrid Web control that will span several months. The ASP.NET DataGrid Web control, which displays database information in an HTML table, is highly versatile. The basics of the DataGrid were discussed in Part 1; information on specifying display properties of the DataGrid was discussed in Part 2. In Part 3 we examined how to associate custom events with the DataGrid. In Part 4 we looked at how to extend Part 3 to provide custom sorting on the results of a DataGrid. In Part 5 we examined how to use templates to further customize the DataGrid's appearance. Part 6 examined how to use the DataGrid's built-in editing capabilities, while Part 7 looked at customizing the editing interface using the EditItemTemplate. In Part 8 we looked at how to add client-side code to a ButtonColumn's client-side onclick event. In Part 9 we examined how to enhance the DataGrid's editing interface by having the editing interface's TextBox receive focus when the page is loaded. In this part we'll look at how to (automatically) add filtering buttons so that one can filter the data in a DataGrid! Read Part 11 Read Part 12 Read Part 13 Read Part 14 Read Part 15 Read Part 16 Read Part 17 Read Part 18

Introduction

Realize that the FAQs are broken down into categories, such as: "Arrays," "Database, Errors," "Getting Start," "Security," etc. In fact, in Part 7 of this series we discussed the ASPFAQs.com database schema, presenting the following tables:

tblFAQ FAQID Primary Key, integer Description varchar(255) - the "question" part of the FAQ FAQCategoryID An integer foreign key to the tblFAQCategory table Remaining rows omitted for brevity...

tblFAQCategory FAQCategoryID Primary Key, integer Name varchar(255) - the "title" of the category (Array, ASP.NET, etc.)

To retrieve only a subset of rows from the tblFAQ table corresponding to a particular FAQCategoryID, we can do something like:

SELECT Columns FROM tblFAQ WHERE FAQCategoryID = SomeFAQCategoryID

In this article we will examine how to allow the user to decide what category of FAQs they wish to have displayed. This article will touch upon techniques discussed in the earlier parts of this article series, as well as information contained in the articles Dynamic Controls in ASP.NET and Working with Dynamically Created Controls. If you have not read the earlier parts to this article series, or have yet to read the other two articles, you are highly encouraged to do so before tackling this article.

Changing the BindData Subroutine to Handle Filtering

1. Retrieves the data to display in the DataGrid from the SQL database that houses the ASPFAQs.com database, placing this data in a SqlDataReader.
2. Specifies that the DataGrid's DataSource property should be assigned the SqlDataReader from step (1).
3. Calls the DataGrid's DataBind() method.

In our previous demos the SQL query performed by the BindData() subroutine was typically a call to the sp_Popularity stored procedure.

In order to have the DataGrid display only those FAQs that belong to a certain FAQ category, we will need to have a parameterized SQL query of the following form:

SELECT Columns FROM tblFAQ WHERE FAQCategoryID = @FAQCategoryIDParameter

where the @FAQCategoryIDParameter is a SqlParameter whose value will be set programmatically.

Since we already have this handy BindData() subroutine, let's concentrate on slightly altering this subroutine to provide the above SQL form (as opposed to writing a new subroutine from scratch). Since we want to only display FAQs from a particular FAQ category, let's alter the BindData() subroutine so that it accepts an input parameter: FilterOnFAQCategoryID, which will be an integer that signifies the FAQ category whose FAQs should be displayed in the DataGrid.

After this small change, all that's left to do is adjust the SQL query from a constant that always issues the sp_Popularity stored procedure to one that uses a parameterized SQL query whose parameter is based on the FilterOnFAQCategoryID input parameter. The code below shows the new BindData(FilterOnFAQCategoryID) subroutine:

Sub BindData(FilterOnFAQCategoryID as Integer) '2. Create the command object, passing in the SQL string Dim strSQL as String strSQL = "SELECT FAQID, F.FAQCategoryID, F.Description, " & _ "FC.Name AS CategoryName " & _ "FROM tblFAQ F " & _ "INNER JOIN tblFAQCategory FC ON " & _ "F.FAQCategoryID = FC.FAQCategoryID " & _ "WHERE F.FAQCategoryID = @FAQCatID " & _ "ORDER BY FAQID" 'Set the datagrid's datasource to the datareader and databind Dim myConnection as New _ SqlConnection(ConfigurationSettings.AppSettings("connectionString")) Dim myCommand as SqlCommand = New SqlCommand(strSQL, myConnection) Dim FAQCatIDParam as New SqlParameter("@FAQCatID", SqlDbType.Int, 4) FAQCatIDParam.Value = FilterOnFAQCategoryID myCommand.Parameters.Add(FAQCatIDParam) myConnection.Open() dgPopularFAQs.DataSource = myCommand.ExecuteReader() dgPopularFAQs.DataBind() myConnection.Close() End Sub

The germane pieces of the code above are bolded. With this change to the BindData(FilterOnFAQCategoryID) subroutine, we can have the DataGrid display a list of FAQs belonging to FAQ category n by calling: BindData(n).

Ok, so we have a nifty BindData(FilterOnFAQCategoryID) subroutine that, when called, will display FAQs of a particular FAQ category in the DataGrid. However, we need some way for the user to choose which FAQ category's FAQs she wishes to view. One user interface that would allow for this would be to list, at the top of the page, each of the FAQ categories as a hyperlink or button. If the user clicks one of these FAQ category hyperlinks or buttons, the ASP.NET page should be posted back and the DataGrid should be redisplayed, but this time with a list of the FAQs from the particular category whose hyperlink or button was clicked by the user.

In Part 2 of this article we'll examine just how to do this!