An Extensive Examination of the DataGrid Web Control: Part 10
By Scott Mitchell
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!
ASP.NET Data Web Controls Kick Start is author Scott
Mitchell's most recent book, which thoroughly examines three of the most commonly
used ASP.NET Web controls: the DataGrid, DataList, and Repeater. These three Web controls
can be difficult to master due to their numerous features and capabilities. With this book, you'll
quickly become an expert, learning the gritty details and true capabilities of each.
This 400+ page book explores the topics in this article series in much greater depth, along with
examining various topics and techniques not covered here.
Scott Mitchell is the editor and founder of 4GuysFromRolla.com, author of the An Extensive Examination
of the DataGrid Web Control article series, and author of numerous other ASP and ASP.NET
books.
Introduction
In the previous nine parts of this article series we have looked at a number of live demos that work
with the ASPFAQs.com database. Specifically, we have been calling
a stored procedure, sp_Popularity, that returns the 25 most popular FAQs. In today's article
we will be changing the SQL just slightly, instead using a SQL statement to get all of the FAQs that fit
into a certain category.
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
If you have worked through the live demos throughout this article series you will have noted that
each demo has a BindData() subroutine that is responsible for binding the appropriate data
to the DataGrid. Specifically, this subroutine performs the following tasks:
Retrieves the data to display in the DataGrid from the SQL database that houses the ASPFAQs.com database,
placing this data in a SqlDataReader.
Specifies that the DataGrid's DataSource property should be assigned the
SqlDataReader from step (1).
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!
