Displaying Detail Records for User-Selected Master Records :: Saving the User's Search PreferencesBy Scott Mitchell
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
INkeyword. 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
Productstables 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
which stores user account information in a SQL Server database. I've configured the demo website to use 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 |
I've detailed using the |
Given that the master records are stored in the
Categories table and user account information is stored in the
I created a table named
UserCategoryPreferences with columns named
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:
|Composite Primary Key|
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
UserCategoryPreferences.CategoryID, on the other hand, is of
int to mirror the type of
CategoryID in the
In addition to the table's composite primary key, there are also foreign key constraints from
The following ER diagram shows the
aspnet_Users tables and their relationships.
|Alternative Ways of Storing Master Record Preferences|
To allow users to store multiple category preferences you would need to create another table called, say,
Prompting the User for their Category Preferences
UserCategoryPreferencesdatabase 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.aspxin 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
The list of categories is displayed using a CheckBoxList and SqlDataSource control, just like in the master/details report page (
The page also contains two Button Web controls,
the users selected categories to
UserCategoryPreferences and then displays a message informing them that their preferences were saved.
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.
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
- For each checked category, a record is added to the
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 FROM UserCategoryPreferences WHERE UserID = CurrentlyLoggedOnUserID. Next,
the items in the
CategoryList CheckBoxList are enumerated and for each selected checkbox the
AddCategoryPreference method is
AddCategoryPreference method issues the following
INSERT INTO UserCategoryPreferences(UserId,
CategoryID) VALUES(CurrentlyLoggedOnUserID, SeelctedCategoryID).
Pre-Selecting the User's Current Category Preferences
We just saw how the
~/Users/Preferences.aspxsaves the user's selected category preferences to the
UserCategoryPreferencestable, 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 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
CheckBoxList control. This is accomplished using the following algorithm:
- Get the currently logged on user's category preferences by querying the
- For each of the user's preferred categories, find the corresponding checkbox in
CategoryListand check it.
Page.IsPostBackis 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
UserIdvalue 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
CategoryListCheckBoxList 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_Loadevent 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
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.
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.
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.