To read the article online, visit http://www.4GuysFromRolla.com/articles/071608-1.aspx

Displaying Detail Records for User-Selected Master Records :: Saving the User's Search Preferences

By Scott Mitchell


Introduction


Last week's article, Using a Dynamic IN Clause, showed how to display detail records for a set of user-selected master records. This entailed creating a User Defined Function (UDF) in the database that would translate a comma-delimited string into tabular data that could then be parsed by SQL's IN keyword. Following that, the user interface was created using a CheckBoxList to enumerate the master records and a GridView to display the details records for the checked master records. The demo available at the end of this article includes the exercise created in last week's article, which uses the Northwind database and the Categories and Products tables as the master and details records, respectively.

Most users who view a master/details report are interested in a particular subset of master records. Such users will quickly become frustrated when visiting the master/details report because each time they visit they have to tediously check each master record checkbox of interest before seeing the results. This frustration is heightened when there are many master records that need to be selected. The user experience can be greatly improved by allowing a user to save their default master record choices and then having the master/details report's master records pre-selected accordingly.

This article takes the master/detail report demo created in the Using a Dynamic IN Clause article and extends it so that the report's master records are pre-selected based on the user's preferences. Read on to learn more!

Storing a User's Master Record Preferences


Our goal for this article is to enable users who view the master/details report to save their preferred master record selections so that upon visiting the report their preferred master records are pre-selected, thereby saving them time from having to select them manually. To achieve this, we need to store each user's master record preferences in the database, which entails creating a new database table that serves as a many-to-many join table between users and the master records.

The name and columns of this many-to-many join table depend on how and where user accounts are stored as well as what the master records are for a particular report. The demo examined in last week's article, Using a Dynamic IN Clause, used the Northwind database's Categories table as the master records, but that demo did not include any discussion on user accounts. The good news is that ASP.NET's Membership system and user account-related Web controls make it a cinch to setup and configure a website to support user accounts. The demo application, in fact, includes a log in page, a page to create a new user account, and a page where users can define what categories they want pre-selected in the master/details report. We'll examine these web pages later on in this article.

The ASP.NET Membership system is based on the provider model; this allows for a variety of different implementations to seamlessly be plugged in. One of the Membership providers that ships with ASP.NET is the SqlMembershipProvider, which stores user account information in a SQL Server database. I've configured the demo website to use the SqlMembershipProvider and used the aspnet_regsql.exe tool to create the Membership schema within the Northwind database. Consequently, the Northwind database now includes a number of tables prefixed with aspnet_ that are used by the SqlMembershipProvider. The most important one is aspnet_Users, which contains a row for each user account in the system.

For More Information on Membership and the SqlMembershipProvider
I've detailed using the SqlMembershipProvider in previous articles here on 4Guys and elsewhere. See my Examining ASP.NET 2.0's Membership, Roles, and Profile article series for an in-depth look at using Membership. Part 3 of the series shows how to use the aspnet_regsql.exe tool to add the Membership schema to a SQL Server database. Also check out my Forms Authentication, Membership, Roles, and User Accounts tutorials on www.asp.net, which include step-by-step instructions on configuring your web application to support user accounts.

Given that the master records are stored in the Categories table and user account information is stored in the aspnet_Users table, I created a table named UserCategoryPreferences with columns named UserId and CategoryID to store what categories each user prefers to have pre-selected when viewing the master/details report. The ER diagram for this table follows:

UserCategoryPreferences
ColumnData TypeKey
UserIduniqueidentifierComposite Primary Key
CategoryIDint

Note that the UserCategoryPreferences.UserId column is of type uniqueidentifier. This is because aspnet_Users.UserId column, which uniquely identifies each user in the system, is of type uniqueidentifier. UserCategoryPreferences.CategoryID, on the other hand, is of type int to mirror the type of CategoryID in the Categories table. In addition to the table's composite primary key, there are also foreign key constraints from UserCategoryPreferences.UserId to aspnet_Users.UserId and UserCategoryPreferences.CategoryID to Categories.CategoryID.

The following ER diagram shows the UserCategoryPreferences, Categories, and aspnet_Users tables and their relationships.

The ER diagram.

Alternative Ways of Storing Master Record Preferences
The UserCategoryPreferences table allows each user to store one set of preferred category records. This relationship arises due to the structure of the table. Namely, by having UserId present and part of the primary key we are associating these choices with a particular user. An alternative approach would be to allow users to save a set of category selections under the heading of a particular name. For example, a user might have three different types of reports she usually runs with different category selections: Beverages Report, Canned Goods Report, and Company Overview report, each of which has different categories selected.

To allow users to store multiple category preferences you would need to create another table called, say, ReportPreferences, which would have a record for each different report (Beverages Report, Canned Goods Report, etc.). This table would have, at minimum, a primary key named ReportPreferencesID and a name for the report preferences; if preferences were specific to a user you would include a UserId column here, too. The UserCategoryPreferences table would be replaced with a table named ReportCategoryPreferences that would tie together what categories belonged to what reports.

Prompting the User for their Category Preferences


With the UserCategoryPreferences database table in place, we are ready to create the user interface where a user can choose his category preferences. This page, which you can find at ~/Users/Preferences.aspx in the demo available for download at the end of this article, needs to display a list of the categories as a series of checkboxes and a Button control that, when clicked, saves the checked categories for the currently logged on user to the UserCategoryPreferences table.

The list of categories is displayed using a CheckBoxList and SqlDataSource control, just like in the master/details report page (ProductsByCategory.aspx). The page also contains two Button Web controls, SavePreferences and CancelButton. Clicking SavePreferences saves the users selected categories to UserCategoryPreferences and then displays a message informing them that their preferences were saved. Clicking CancelButton sends the user back to Default.aspx. The following image shows a portion of the ~/Users/Preferences.aspx when visited by a user who has not yet selected any category preferences.

The user is prompted to select their category preferences.

At this point the user can select a handful of categories and click the "Save Preferences" button. The users choices are saved to the database using the following algorithm:

  • All of the user's current category preferences are removed from UserCategoryPreferences.
  • For each checked category, a record is added to the UserCategoryPreferences table.
This approach - deleting all of the user's existing category selections and then adding back in the checked ones - is a bit hamfisted as it may do more work than necessary. For instance, consider what happens if a user has five categories selected already and wants to add a sixth. Doing so deletes five records then adds six new ones, when all that was needed was a single insert. However, this approach is easier and requires less code than checking each option in the CheckBoxList and deleting or inserting a record if necessary.

The following code implements the above algorithm and is executed when the SavePreferences Button is clicked. It starts by getting the currently logged on user's UserId value and then calls the DeleteCategoryPreferences method. This method, defined in the web page's code-behind class as well, issues the DELETE statement: DELETE FROM UserCategoryPreferences WHERE UserID = CurrentlyLoggedOnUserID. Next, the items in the CategoryList CheckBoxList are enumerated and for each selected checkbox the AddCategoryPreference method is called. The AddCategoryPreference method issues the following INSERT statement: INSERT INTO UserCategoryPreferences(UserId, CategoryID) VALUES(CurrentlyLoggedOnUserID, SeelctedCategoryID).

Protected Sub SavePreferences_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SavePreferences.Click
   'Get the UserId of the logged on user
   Dim userId As Guid = CType(Membership.GetUser().ProviderUserKey, Guid)

   'Delete ALL category preferences for the user
   DeleteCategoryPreferences(userId)

   'Add a category preference for each selected category
   For Each cb As ListItem In CategoryList.Items
      If cb.Selected Then
         AddCategoryPreference(userId, Convert.ToInt32(cb.Value))
      End If
   Next

   'Display the PreferencesSavedMsg Label
   PreferencesSavedMsg.Visible = True
End Sub

Pre-Selecting the User's Current Category Preferences


We just saw how the ~/Users/Preferences.aspx saves the user's selected category preferences to the UserCategoryPreferences table, but we've not yet looked at the code that pre-selects a CheckBoxList with the user's saved preferences. This is needed in two places - here, in ~/Users/Preferences.aspx, and in the master/details report, ProductsByCategory.aspx. The implementation in the two places is virtually identical, so let's look at just examine one of these pages, namely ProductsByCategory.aspx.

When the ProductsByCategory.aspx is first visited we need to determine if the person visiting the page is authenticated (that is, if they have logged on to the site). If so, we need to grab their category preferences and check the appropriate checkboxes in the CategoryList CheckBoxList control. This is accomplished using the following algorithm:

  • Get the currently logged on user's category preferences by querying the UserCategoryPreferences table.
  • For each of the user's preferred categories, find the corresponding checkbox in CategoryList and check it.
The following code implements this algorithm. Note that it starts by making sure that Page.IsPostBack is False. This is only the case when the page is first visited (and not on subsequent postbacks). Next, it determines if the person visiting the page is authenticated or not. If she is, it gets her UserId value and proceeds with the steps of the algorithm details above. Note the line of code that reads CategoryList.DataBind(). This line of code explicitly populates the CategoryList CheckBoxList control, which is bound to a SqlDataSource control in the declarative markup. This line of code is important because we need to ensure that the CheckBoxList has been populated before we attempt to select its checkboxes based on the currently logged in user's preferences. Typically, data Web controls that use declarative data binding are populated after the Page_Load event handler; consequently, we need to explicitly call the CheckBoxList control's DataBind() method so that it is populated before applying the user's preferences.

Next, the currently logged on user's category preferences are queried and then enumerated. For each record returned by the query, the CategoryList's Items collection is searched for a matching checkbox. If found, the checkbox's Selected property is set to True. The net effect is that each category in the user's preferences is automatically checked when the page is first visited.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
   'On the first page visit populate the user's default Category selections
   If Not Page.IsPostBack Then
      'Make sure the user is authenticated...
      If Request.IsAuthenticated Then
         'Get the UserID
         Dim userId As Guid = CType(Membership.GetUser().ProviderUserKey, Guid)

         'Bind the data to CategoryList
         CategoryList.DataBind()

         'Get this user's Category choices
         Dim myReader As SqlDataReader = Nothing
         Try
            myReader = SqlHelper.ExecuteReader(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString, _
                                                      Data.CommandType.Text, "SELECT CategoryID FROM UserCategoryPreferences WHERE UserId = @UserId", _
                                                      New SqlParameter("@UserId", userId))

            While myReader.Read
               'Determine the categoryID value returned from the query for this row
               Dim categoryID As String = myReader("CategoryID").ToString()

               'Find the matching CheckBox in the CheckBoxList
               Dim cb As ListItem = CategoryList.Items.FindByValue(categoryID)
               If cb IsNot Nothing Then
                  cb.Selected = True
               End If
            End While
         Finally
            If myReader IsNot Nothing Then
               myReader.Close()
            End If
         End Try

      End If
   End If
End Sub

The following screen shot shows ProductsByCategory.aspx when visited by user Scott. Scott has three category preferences selected: Meat/Poultry, Confections, and Produce. Consequently, when visiting ProductsByCategory.aspx these three category choices are automatically selected and the corresponding products displayed in the grid below. The user is, at this point, free to customize his report to include other categories or to remove one or more of his pre-selected categories.

The ProductsByCategory.aspx search page, when visited by user Scott.

Conclusion


This two-part article series showed how to build a master/details report where the user could select an arbitrary number of master records. The first installment, Using a Dynamic IN Clause, looked at how to use a User Defined Function (UDF) in SQL Server to retrieve all detail records that belong to a comma-delimited set of master records, as well as how to create a user interface to prompt the user for an arbitrary number of master records.

In this second and final installment we added the ability for users to save their category preferences. This enhancement is useful in scenarios where users commonly query on the same set of master records, as it saves them from having to tediously re-select what master records they are interested in each time they visit the master/details report page.

Happy Programming!

  • By Scott Mitchell


    Further Reading


  • Displaying Detail Records for User-Selected Master Records :: Using a Dynamic IN Clause
  • Tutorials on Creating Master/Detail Reports
  • Examining ASP.NET 2.0's Membership, Roles, and Profile
  • Forms Authentication, Membership, Roles, and User Accounts Tutorials
  • Attachments


  • Download the code used in this article

  • Article Information
    Article Title: ASP.NET.Displaying Detail Records for User-Selected Master Records :: Saving the User's Search Preferences
    Article Author: Scott Mitchell
    Published Date: July 16, 2008
    Article URL: http://www.4GuysFromRolla.com/articles/071608-1.aspx


    Copyright 2014 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers