Creating Filtering Buttons

When displaying data, it is often the case that there is so much that attempting to display it all on one Web page would make the information difficult to read and comprehend. Imagine for a moment how well you could analyze a company's financial data if you were shown weekly revenue numbers for the past 10 years all on one page! Clearly, to make data consumable, it is vital that it be presented in smaller chunks.

One way to space out data on a Web page is to paginate the data and enable the user to page through the information. Search engines are a classic example of pagination a search on Google for "ASP.NET" yields more than 1.2 million hits, but fortunately I am only shown 10 results at a time. In Chapter 8, "Providing DataGrid Pagination," we'll examine how to provide pagination support for the DataGrid Web control.

Another more user-proactive approach to reducing the sheer amount of data is to provide filter buttons. Filter buttons are simple ASP.NET command button controls (Buttons, LinkButtons, or ImageButtons) that enable the user to filter the results of a data Web control.

In the past couple of chapters, we have been looking at code examples that have involved the titles database table. One of the fields in the titles table is type. Let's look at an example that provides a series of filter buttons to display the various types of books in the titles table.

Before we concern ourselves with creating filter buttons, though, let's create an ASP.NET Web page that uses a DataGrid to display all the rows in the titles table. After this, we'll add our filter buttons and examine the code changes needed to support filtering. Listing 6.1 contains the complete code and HTML markup for an ASP.NET Web page that displays the entire contents of the titles table.

Listing 6.1 The Contents of the titles Table Are Displayed

  1: <%@ import Namespace="System.Data" %>   2: <%@ import Namespace="System.Data.SqlClient" %>   3: <script runat="server" language="VB">   4:   Sub Page_Load(sender as Object, e as EventArgs)   5:    If Not Page.IsPostBack then   6:     BindData()   7:    End If    8:   End Sub   9:  10:  11:   Sub BindData()  12:    '1. Create a connection  13:    Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs"  14:    Dim objConn as New SqlConnection(strConnString)  15:  16:    '2. Create a command object for the query  17:    Const strSQL as String = "SELECT * FROM titles"  18:    Dim objCmd as New SqlCommand(strSQL, objConn)  19:  20:    objConn.Open()  'Open the connection  21:  22:    'Finally, specify the DataSource and call DataBind()  23:    dgTitles.DataSource = objCmd.ExecuteReader(CommandBehavior.CloseConnection)  24:    dgTitles.DataBind()  25:  26:    objConn.Close()  'Close the connection  27:   End Sub  28: </script>  29:  30: <form runat="server">  31:  <asp:DataGrid runat="server"   32:    AutoGenerateColumns="False" CellPadding="5"  33:    Font-Name="Verdana" Font-Size="9pt"  34:    AlternatingItemStyle-BackColor="#dddddd">  35:  36:   <HeaderStyle BackColor="Navy" ForeColor="White" Font-Size="13pt"  37:        Font-Bold="True" HorizontalAlign="Center" />  38:  39:   <Columns>  40:    <asp:BoundColumn DataField="title_id" HeaderText="Title ID"  41:       ItemStyle-HorizontalAlign="Center" />  42:    <asp:BoundColumn DataField="title" HeaderText="Title" />  43:    <asp:BoundColumn DataField="type" HeaderText="Type" />  44:   </Columns>  45:  </asp:DataGrid>  46: </form> 

You should be comfortable with the code in Listing 6.1. It simply calls the BindData() function on the first page load (when Page.IsPostBack is False, lines 5 7) and binds the results of the SQL query SELECT * FROM titles to the DataGrid dgTitles (lines 18 24). The DataGrid control (lines 31 45) displays three of the titles table's fields title_id, title, and type (lines 40 43) and specifies some stylistic and display settings. Note that the DataGrid is surrounded by a server-side form (lines 30 and 46); with the code given in Listing 6.1 this Web form is superfluous, but it will be needed as we build on this code, adding command buttons. Figure 6.1 shows a screenshot of the code in Listing 6.1 when viewed through a browser.

Figure 6.1. A DataGrid displays three fields of the titles table.

By inspecting Figure 6.1 you might be able to see that, for the rows in the titles table, the type column contains one of six values: "business," "mod_cook," "popular_comp," "psychology," "trad_cook," or "UNDECIDED." When providing filtering buttons, one option would be to create seven such buttons one to show all results, and one for each of the type values. That is, we'd have a button labeled Show ALL Books, one labeled Show Books of Type business, another one labeled Show Books of Type mod_cook, and so on, one for each type value listed.

The problem with this approach is that it will only allow the filtering of the six hard-coded type values. What happens if a week from now, a new batch of books is entered into the database with a type value of Travel? Ideally, the filter buttons would be dynamically generated, based upon the values of the type field in the database.

We'll examine how to dynamically add such command buttons shortly. First, let's examine what the code would look like if we were satisfied with just hard-coding the potential type values.

Before we examine any code, take a moment to think what must happen when one of the filtering buttons is clicked. As we want to filter the results of the DataGrid, when the event handler is clicked, the DataGrid will need to have its DataSource repopulated with just the results matching the specified filter. This DataSource will then need to be rebound to the DataGrid (via the DataGrid's DataBind() method). In examining Listing 6.1, you can see that we already have a function that does a very similar task: the BindData() function (lines 11 27 in Listing 6.1) populates a SqlDataReader with the SELECT * FROM titles SQL query, and then binds it to the DataGrid. As we'll see in Listing 6.2, we can make a few small changes to this function to incorporate the needed filtering SQL queries.

Listing 6.2 Seven Command Buttons Have Been Added to Allow for Filtering

  1: <%@ import Namespace="System.Data" %>   2: <%@ import Namespace="System.Data.SqlClient" %>   3: <script runat="server" language="VB">   4:   Sub Page_Load(sender as Object, e as EventArgs)   5:    If Not Page.IsPostBack then   6:     BindData(String.Empty)   7:    End If   8:   End Sub   9:  10:  11:   Sub BindData(filterValue as String)  12:    DisplayViewingTypeMessage(filterValue)  13:  14:    '1. Create a connection  15:    Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs"  16:    Dim objConn as New SqlConnection(strConnString)  17:  18:  19:    'Create an appropriate SQL command string  20:    Dim strSQL as String  21:    If filterValue = String.Empty then  22:     strSQL = "SELECT * FROM titles"  23:    Else  24:     'SQL needs WHERE clause  25:     strSQL = "SELECT * FROM titles WHERE type = @TYPE"   26:    End If  27:  28:    '2. Create a command object for the query  29:    Dim objCmd as New SqlCommand(strSQL, objConn)  30:  31:    'Add parameter for WHERE clause if needed  32:    If strSQL <> String.Empty then  33:     Dim filterParam as New SqlParameter("@TYPE", SqlDbType.Char, 12)  34:     filterParam.Value = filterValue  35:     objCmd.Parameters.Add(filterParam)  36:    End If  37:  38:    objConn.Open()  'Open the connection  39:  40:    'Finally, specify the DataSource and call DataBind()  41:    dgTitles.DataSource = objCmd.ExecuteReader(CommandBehavior.CloseConnection)  42:    dgTitles.DataBind()  43:  44:    objConn.Close()  'Close the connection  45:   End Sub  46:  47:  48:   Sub FilterData(sender as Object, e as CommandEventArgs)  49:    BindData(e.CommandArgument)  50:   End Sub  51:  52:  53:   Sub DisplayViewingTypeMessage(filterValue as String)  54:    If filterValue = String.Empty then  55:     lblViewingMsg.Text = "You are viewing all books..."  56:    Else  57:     lblViewingMsg.Text = "You are viewing books of type " & filterValue  58:    End If  59:   End Sub  60: </script>  61:  62: <form runat="server">  63:  <asp:Button Text="View ALL Books" runat="server"  64:       CommandArgument="" OnCommand="FilterData" />  65:  <asp:Button Text="View Books of Type business" runat="server"  66:       CommandArgument="business" OnCommand="FilterData" />   67:  <asp:Button Text="View Books of Type mod_cook" runat="server"  68:       CommandArgument="mod_cook" OnCommand="FilterData" />  69:  <asp:Button Text="View Books of Type popular_comp" runat="server"  70:       CommandArgument="popular_comp" OnCommand="FilterData" />  71:  <asp:Button Text="View Books of Type psychology" runat="server"  72:       CommandArgument="psychology" OnCommand="FilterData" />  73:  <asp:Button Text="View Books of Type trad_cook" runat="server"  74:       CommandArgument="trad_cook" OnCommand="FilterData" />  75:  <asp:Button Text="View Books of Type UNDECIDED" runat="server"  76:       CommandArgument="UNDECIDED" OnCommand="FilterData" />  77:  <p>  78:   <asp:label  runat="server" Font-Name="Verdana"  79:      Font-Italic="True" Font-Color="Red" />  80:  </p>  81:  <asp:DataGrid runat="server"   82:    AutoGenerateColumns="False" CellPadding="5"  83:    Font-Name="Verdana" Font-Size="9pt"  84:    AlternatingItemStyle-BackColor="#dddddd">  85:  86:   <HeaderStyle BackColor="Navy" ForeColor="White" Font-Size="13pt"  87:        Font-Bold="True" HorizontalAlign="Center" />  88:  89:   <Columns>  90:    <asp:BoundColumn DataField="title_id" HeaderText="Title ID"  91:       ItemStyle-HorizontalAlign="Center" />  92:    <asp:BoundColumn DataField="title" HeaderText="Title" />  93:    <asp:BoundColumn DataField="type" HeaderText="Type" />  94:   </Columns>  95:  </asp:DataGrid>  96: </form> 

The first thing you should take note of is the altered BindData(filterValue ) function (lines 11 36). The new version accepts a single string input parameter, filterValue, which specifies the value of the type field to filter on. If an empty string is passed in (either "" or String.Empty), the filtering logic is not applied. For example, in the Page_Load event handler, BindData(String.Empty) is called on the page's first load (line 6), which retrieves all the rows from the title table.

In addition to grabbing the specified subset of title table rows, the BindData(filterValue ) function also makes a call to the helper function DisplayViewingTypeMessage(filterValue ) the call to DisplayViewingTypeMessage(filterValue ) is made on line 12, and the helper function itself is contained from lines 53 to 59. DisplayViewingTypeMessage(filterValue ) accepts a single string parameter, the name of the type value being filtered, and simply sets the ASP.NET Label lblViewingMsg's Text property. This serves as a helpful message to users, so that they realize they are viewing a particular subset of the titles table. (The lblViewingMsg Label control is defined on lines 78 and 79.)

To provide filtering functionality, we need command buttons for each of the filtering options. Because there are seven options (no filtering being one, and then a filtering option for each of the six type values), seven Button controls have been added, spanning lines 63 through 76. I decided to use Button controls, but they need only be command buttons you could use LinkButton or ImageButton controls.

Each command button's Command event is assigned to the same event handler, FilterData. To uniquely identify each Button, the CommandArgument property has been set to the value of the type field that is to be filtered on when the command button is clicked. Because our BindData(filterValue ) function is expecting a single string parameter specifying the value of the type field to filter on, our FilterData event handler is quite simple. The FilterData event handler (lines 48 50) has a single line of code: a call to the BindData(filterValue ) function, passing in the value of the clicked command button's CommandArgument property.

Figure 6.2 shows a screenshot of Listing 6.2 when viewed through a browser. Note that when a filter button is clicked, the ASP.NET Web page is posted back and the DataGrid is rebound with the filtered data. Additionally, a helpful message appears informing the user what subset of the titles table she is viewing.

Figure 6.2. Clicking a filter button causes only a subset of the rows of the titles table to be displayed.

NOTE

When reviewing Listing 6.2, did you wonder why the seven command buttons all had their Command event wired up to the same event handler (FilterData)? We could have opted to have each Button trigger a unique event handler, in which the proper call to BindData(filterValue ) was made. This course of action was not chosen for two reasons: First, it would require seven one-line event handlers as opposed to one, thereby bloating the code and making it less readable; second, using just one event handler is needed when dynamically adding the filtering buttons, which we'll be doing in the next section.

Dynamically Creating the Filtering Buttons

Listing 6.2 demonstrates how to provide filtering on the values of a particular database field. However, the values that can be filtered have been hard-coded as a series of Button controls. This approach would be satisfactory for scenarios where the column being filtered on could only have a certain set of values. For example, if you wanted to sort an employee table by gender, there will obviously never be more than two potential values; hence, in this situation, adding two hard-coded command buttons would be understandable.

The type field in the titles table can have more than just the six values currently in the table. That is, new books may be added to the table with type values that can't be determined at this point in time. To provide filtering support for database fields whose sets of potential values are unknown, it is best to dynamically add the filtering buttons based upon the current values of the database field.

In all our examples thus far, we've created our Web controls by explicitly adding the proper control tag in our HTML section. For example, to add an ASP.NET Label control in a particular place in the HTML page, we simply added

 <asp:label runat="server" ... /> 

wherever it was we wanted the Label displayed. In addition to adding Web controls to an ASP.NET page in this way, we can also add them dynamically by programmatically creating an instance of the control's corresponding class, setting its properties, and then adding it to the Controls collection of a PlaceHolder control on the page.

NOTE

The PlaceHolder control is an ASP.NET control used to indicate where dynamically-added controls should appear on the ASP.NET Web page. For more information on the PlaceHolder control, be sure to read the article "Working with Dynamically Created Controls" mentioned in the "On the Web" section at the end of this chapter.

For example, if we want to add a Button control to an ASP.NET Web page in a specific position, we would first create a PlaceHolder to specify exactly where the dynamically added Button should go. Next, we would add code to the Page_Load event handler to add the Button to the PlaceHolder's Controls collection. The following code snippet illustrates these two steps:

 <script language="VB" runat="server">   Sub Page_Load(sender as Object, e as EventArgs)    ' Create a button control programmatically    Dim myButton as Button    ' Add the button to the PlaceHolder    placeButtonHere.Controls.Add(myButton)    ' Now set the button properties    myButton = New Button()    myButton.Text = "Click Me!"   End Sub  </script>  Click the button below!  <asp:PlaceHolder runat="server"  /> 

A thorough discussion of adding controls dynamically is beyond the scope of this book. However, there are two articles on this topic worth reading in the "On the Web" section at the end of this chapter. If you have not had experience with adding controls dynamically, you might want to read these recommended articles before continuing.

Listing 6.3 enhances Listing 6.2 by dynamically adding filter buttons for each distinct value of the type field. Note that the BindData(filterValue ) and DisplayViewingTypeMessage(filterValue ) functions and the FilterData event handler have not changed from Listing 6.2 to Listing 6.3. Furthermore, the output and functionality of Listings 6.2 and 6.3 are identical; therefore, to see a screenshot of Listing 6.3 when viewed through a browser, refer back to Figure 6.2.

Listing 6.3 The Filter Buttons Displayed Are Dynamically Added on Each Page Load

  1: <%@ import Namespace="System.Data" %>   2: <%@ import Namespace="System.Data.SqlClient" %>   3: <script runat="server" language="VB">   4:  ' ... The BindData and DisplayViewingTypeMessage functions have   5:    been omitted for brevity. See Listing 6.2 for their details ...   6:    7:   Sub Page_Load(sender as Object, e as EventArgs)   8:    AddFilterButtons()   9:  10:    If Not Page.IsPostBack then  11:     'Populate the DataGrid  12:     BindData(String.Empty)  13:    End If  14:   End Sub  15:  16:  17:   Sub FilterData(sender as Object, e as CommandEventArgs)  18:    BindData(e.CommandArgument)  19:   End Sub  20:  21:  22:   Sub AddFilterButtons()  23:    'Dynamically add the control buttons  24:    '1. Create a connection  25:    Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs"  26:    Dim objConn as New SqlConnection(strConnString)  27:  28:  29:    'Create an appropriate SQL command string  30:    Const strSQL as String = "SELECT DISTINCT type FROM titles ORDER BY type"  31:  32:    '2. Create a command object for the query  33:    Dim objCmd as New SqlCommand(strSQL, objConn)  34:  35:    objConn.Open()  'Open the connection  36:    Dim dr as SqlDataReader = objCmd.ExecuteReader()  37:  38:    'Add the View ALL button  39:    Dim filterButton as New Button()  40:    filterButtonLocation.Controls.Add(filterButton)  41:    filterButton.Text = "View ALL Books"  42:    filterButton.CommandArgument = ""  43:    AddHandler filterButton.Command, AddressOf Me.FilterData  44:  45:    'Now, loop through the SqlDataReader, adding buttons  46:    While dr.Read()   47:     filterButton = New Button()  48:     filterButtonLocation.Controls.Add(filterButton)  49:     filterButton.Text = "View Books of Type " & dr("type")  50:     filterButton.CommandArgument = dr("type")  51:  52:     AddHandler filterButton.Command, AddressOf Me.FilterData  53:    End While  54:  55:    objConn.Close()  'Close the connection  56:   End Sub  57:  58: </script>  59:  60: <form runat="server">  61:  <asp:PlaceHolder runat="server"  />  62:  <p>  63:   <asp:label  runat="server" Font-Name="Verdana"  64:      Font-Italic="True" Font-Color="Red" />  65:  </p>  66:  <asp:DataGrid runat="server"   67:    AutoGenerateColumns="False" CellPadding="5"  68:    Font-Name="Verdana" Font-Size="9pt"  69:    AlternatingItemStyle-BackColor="#dddddd">  70:  71:   <HeaderStyle BackColor="Navy" ForeColor="White" Font-Size="13pt"  72:        Font-Bold="True" HorizontalAlign="Center" />  73:  74:   <Columns>  75:    <asp:BoundColumn DataField="title_id" HeaderText="Title ID"  76:       ItemStyle-HorizontalAlign="Center" />  77:    <asp:BoundColumn DataField="title" HeaderText="Title" />  78:    <asp:BoundColumn DataField="type" HeaderText="Type" />  79:   </Columns>  80:  </asp:DataGrid>  81: </form> 

When dynamically adding controls to an ASP.NET Web page, it often helps to provide a PlaceHolder control, so that you know precisely where the newly added controls will appear. Such a PlaceHolder control, filterButtonLocation, has been declared on line 61. This control does nothing but serve as the location where our filtering buttons will be dynamically added.

The AddFilterButtons() function performs the work needed to add the filter buttons to the ASP.NET Web page (see lines 22 56). This function is called on each page load, and it is therefore called from the Page_Load event handler (line 8). Because we do not know what values the type field might have, the AddFilterButtons() function must find out what potential values are in the field and create a Button control for each distinct value. In addition to this, the AddFilterButtons() function should create a filter button to display all books.

To retrieve the values of the type field, a SELECT DISTINCT type FROM titles SQL query is used to populate a SqlDataReader (lines 25 through 36). Following that, the filter buttons are added to the filterButtonLocation PlaceHolder, starting with the View ALL Books Button (lines 40 through 43). To add a Button to the page, we create a new instance of the Button class (see lines 39 and 47) and then set its Text and CommandArgument properties accordingly (see lines 41 42 and 49 50). We then add it to the PlaceHolder control's Controls collection (see lines 40 and 48). Finally, we have to wire up the Button's Command event to the FilterData event handler. This is accomplished by using Visual Basic .NET's AddHandler function, as can be seen on lines 43 and 52.

NOTE

To add an event handler to the Button's Command event in C#, you would use the following syntax:

 filterButton.Command += new CommandEventHandler(this.FilterData); 

To learn more about consuming events, be sure to consult the "Consuming Events" article presented in the "On the Web" section at the end of this chapter.

The output for Listing 6.3 is identical to that of Listing 6.2 (assuming that no new values for the type field appear). Hence, you can refer back to Figure 6.2 for a screenshot of Listing 6.3.

NOTE

Our examples in this section have examined adding filtering buttons to a DataGrid control. However, filtering buttons can also be used to work with both the DataList and Repeater controls. The server-side code presented in Listings 6.2 and 6.3 will work with any data Web control.

A-Z Filter Buttons

An example of filter button usage in a production Web site can be seen at Microsoft's ASP.NET Forums Member List page at http://www.asp.net/Forums/User/ ShowAllUsers.aspx. The ASP.NET Forums, which is an online messageboard site for ASP.NET and ASP.NET-related questions, contains (at the time of this writing) more than 80,000 registered users. Obviously, it is implausible to display all 80,000 users on one Web page. One option would be to just display the 80,000 or so members in a DataGrid using pagination, displaying, say, 50 users per page. The downside to this approach is that if a Web visitor wishes to find a particular user, he might have to page through up to 1,600 pages!

The solution employed by the creators of the ASP.NET Forums was to, by default, list all the users ordered by the date which they signed up for a free user account on the ASP.NET Forums. Visitors can page through this data one at a time if they like. Alternatively, at the top of the page are the letters of the alphabet rendered as hyperlinks. By clicking on a particular letter, only those users whose username begins with that letter are displayed.

The ASP.NET Forum source code creates the 26 hard-coded filter buttons in much the same way that the seven filter buttons of Listing 6.2 (lines 63 76) were created, except that the ASP.NET Forums use LinkButton controls for the 26 filter buttons instead of Button controls. Just like with Listing 6.2, the ASP.NET Forums has each of the 26 filter buttons' Command events handled by one event handler, with each filter button's CommandArgument property set to the letter that the usernames are to be filtered on.

NOTE

To learn more about the ASP.NET Forums (or to post your questions on ASP.NET), visit http://www.asp.net/Forums/. You can even download the complete source code for the ASP.NET Forums. Visit http://www.asp.net/Forums/Download/ to download the code and to learn more about the system requirements for running the ASP.NET Forums on your computer.

A More Optimized Approach to Filtering

In Listings 6.2 and 6.3, each time a filter button is clicked, the ASP.NET Web page is posted back, the DataSource is repopulated with the filtered results, and the DataGrid's DataBind() method is recalled. This means that each time a filter button is clicked, a database access occurs.

At first glance, this might seem less than ideal. After all, on the first page visit, we retrieve all the rows from the titles table. After clicking on a filter button, we want to display a subset of that data. It seems wasteful to have to go back to the database and get the data that we already have! (Granted, we don't have exactly the information we want to display, but the information we want to display is contained in the information we have on hand.)

If you've had much experience with the DataSet object, you're likely aware that a DataSet has a property called Tables, which is a collection of DataTable objects. Each of these DataTable objects represents structured data, such as a database table. Each DataTable object has a DefaultView property, which returns a DataView object. A DataView can be thought of as a "window" into a DataTable. It is through this window that we access the items in the DataTable. A DataView, among other things, can be used to view records of a DataTable that match certain criteria. That is, using the DataView's RowFilter property, we can provide a filtered view of a DataTable, meaning that we can provide a filtered view of our DataSet.

Unfortunately, the DataSet does not natively survive postbacks like the Web controls, with their ViewState, can. If the DataSet cannot survive postbacks, that means we will need to repopulate the DataSet on each postback, which is what we're trying to avoid! To get around this, we can programmatically add the DataSet to the ViewState, as we'll see in Listing 6.4.

To provide a more optimized approach to filtering results in a data Web control, we can start by using a DataSet instead of a DataReader. Upon the page's first load, this DataSet is populated with all of the title table's rows. Furthermore, this DataSet will need to be added to the page's ViewState so that it can survive postbacks.

When the user clicks a filter button, the ASP.NET Web page will be posted back and the FilterData event handler will be executed. Here, rather than calling BindData(filterValue ), we'll want to pull the DataSet out from the ViewState, alter the proper DataTable's DefaultView's RowFilter property, and then rebind this DataSet to the DataGrid.

This series of actions will save a database access with each filtering button-click. That is, the only database access will occur on the first visit to the page.

Listing 6.4 illustrates a few of the server-side functions that are needed to perform this optimization. The code listing builds naturally from Listing 6.3, and doesn't add any user-level functionality; hence, you can refer back to Figure 6.2 for a screenshot of Listing 6.4 in action.

Listing 6.4 A DataSet Is Cached in the ViewState to Save Database Accesses

  1: <%@ import Namespace="System.Data" %>   2: <%@ import Namespace="System.Data.SqlClient" %>   3: <script runat="server" language="VB">   4:   Sub Page_Load(sender as Object, e as EventArgs)   5:    AddFilterButtons()   6:   7:    If Not Page.IsPostBack then   8:     'Populate the DataGrid   9:     BindData()   10:    End If  11:   End Sub  12:  13:  14:   Sub BindData()  15:    '1. Create a connection  16:    Const strConnString as String = "server=localhost;uid=sa;pwd=; database=pubs"  17:    Dim objConn as New SqlConnection(strConnString)  18:  19:  20:    'Create an appropriate SQL command string  21:    Const strSQL as String = "SELECT * FROM titles"  22:  23:    '2. Create a command object for the query  24:    Dim objCmd as New SqlCommand(strSQL, objConn)  25:  26:    '3. Create a DataAdapter and Fill the DataSet  27:    Dim objDA as New SqlDataAdapter()  28:    objDA.SelectCommand = objCmd  29:  30:    Dim objDS as New DataSet()  31:    objDA.Fill(objDS, "titles")  32:  33:    'Add the DataSet to the ViewState  34:    ViewState.Add("TitlesDataSet", objDS)  35:  36:    'Finally, specify the DataSource and call DataBind()  37:    dgTitles.DataSource = ViewState("TitlesDataSet")  38:    dgTitles.DataBind()  39:  40:    objConn.Close()  'Close the connection  41:   End Sub  42:  43:  44:   Sub FilterData(sender as Object, e as CommandEventArgs)  45:    'Inform the user what filtering is being performed  46:    DisplayViewingTypeMessage(e.CommandArgument)  47:  48:    'Get the DataSet out of the ViewState  49:    Dim objDS as DataSet = ViewState("TitlesDataSet")  50:   51:    'Apply the filtering  52:    If e.CommandArgument = String.Empty then  53:      objDS.Tables("titles").DefaultView.RowFilter = ""  54:    Else  55:      objDS.Tables("titles").DefaultView.RowFilter = "type = '" & e.CommandArgument & "'"  56:    End If  57:  58:    'Now, bind the DataGrid to the filtered DataSet  59:    dgTitles.DataSource = objDS.Tables("titles").DefaultView  60:    dgTitles.DataBind()  61:   End Sub  62:  63:   ...  64: </script> 

In Listing 6.4, the BindData() function has been altered to no longer accept a filterValue parameter. Rather, BindData(), which is only called on the first load of the page (that is, not on any postbacks), starts by populating a DataSet with the results of a SELECT * FROM titles SQL query (lines 15 31). Specifically, the Fill method of the SqlDataAdapter adds a DataTable to the DataSet with the name "titles" (line 31). After this populated DataTable has been added to the DataSet, the DataSet is added to the ViewState (line 34). Next, the DataGrid's DataSource property is set to this DataSet and the DataBind() method is called.

By storing the DataSet in the ViewState on line 34, we are essentially storing the DataSet in a page-level cache. This cache is local to this particular page visited by this particular user. It consumes no server-resident resources, just some extra HTML in the _VIEWSTATE hidden form field.

CAUTION

For large DataGrids with many columns and rows, the _VIEWSTATE hidden form field can grow to be very large, on the order of tens of kilobytes. A large _VIEWSTATE increases the total size of the page, requiring longer download times, which could negatively affect a dial-up user's experience. In such cases, you can store the DataGrid's data using ASP.NET's data cache. For more information on this technology, be sure to read the "Caching with ASP.NET" article referenced in the "On the Web" section at the end of this chapter.

When a filtering button is clicked, the ASP.NET page is posted back and the FilterData event handler is executed. Here the DataSet is retrieved from the ViewState (line 49) and the DataTable "titles"'s DefaultView's RowFilter property is set so that only rows whose type field has the value specified by the command button's CommandArgument property are shown. The RowFilter property expects input in the form

 FieldName <operator> Value 

FieldName is the name of a field of the DataTable (type, in our example); <operator> is a comparison operator, like =, <, <>, >=, and so on; Value is a value that FieldName is compared against using the <operator>. For fields of a string type (such as Text, varchar, char, and so on), the Value portion must be surrounded by quotes or a single apostrophe. Hence, on line 55 you'll note that I set the RowFilter property to

 "type = '" & e.CommandArgument & "'" 

If e.CommandArgument equals, say, "popular_comp", the RowFilter property will equal "type = 'popular_comp'". In the event that the View All Records button is clicked, the CommandArgument property will have a blank string as its value. In this case, we want to reset the RowFilter property so that all rows are displayed. The RowFilter property can be reset by assigning it an empty string, as shown on line 53.

After this RowFilter property has been set, all that remains is binding the filtered DataView to the DataGrid. This last step is accomplished on lines 59 and 60.

CAUTION

Whenever you use a cache to reduce database accesses, realize that the data you are displaying on the page can become stale. That is, if the user spends 15 minutes on this page, continually filtering data from the cached DataSet, the data she is seeing is data that is 15 minutes old. Additions, updates, and removals of data from the underlying data store that have occurred in this time period will not be reflected in the ASP.NET Web page. If the data you are providing is rarely updated or is not time-sensitive, this will likely not be a concern.

The concepts we've just discussed cover a lot of intermediate to advanced material that is outside the scope of this book. This includes more advanced features of the DataSet and using the ViewState as a page-level cache. Rather than delve into these topics here, I encourage you to read the related resources in the "On the Web" section at the end of this chapter.