Examining ASP.NET's Membership, Roles, and Profile - Part 10By Scott Mitchell
The Membership system automatically tracks the last date and time each user's account has been accessed. With the
SqlMembershipProvider, this information is stored in the
aspnet_Usersdatabase table in a
LastActivityDate. This column is automatically updated to the current UTC date and time whenever a user logs into the site, whenever their user account information is updated, and whenever their user account information is retrieved.
In addition to tracking each user's last activity date and time, the Membership system includes a method named
This method returns the number of users whose last activity date and time is within a specified window; by default, this method returns the number of
aspnet_Users.LastActivityDate value falls within the last 15 minutes.
This article examines the
GetNumberOfUsersOnline method and see how to extend the Membership system to include additional user
activity information. Specifically, we will add a new table to the database used by the
SqlMembershipProvider that associates a description
of each user's current action. We will then update our ASP.NET pages to update the records in this table to include a description of the user's
current action. For example, when visiting the home page we may use the description, "Viewing the home page." Finally, we will create a web page that displays
the list of currently logged on users and their last known action. Read on to learn more!
Tracking the Date and Time of a User's Last Activity
Imagine that you work on a website that supports user accounts and that your boss wants to show on each page how many currently logged in users are visiting the site. In attempting to tackle this problem your first approach might be to create a new database table named
UsersOnlinethat contains a single row and column that indicates the number of users currently logged in. For example, when first deployed this table would have one record with a value of 0. Whenever a user logs onto the site through the login page, you would run an
UPDATEquery to increment this lone record's value by 1. Correspondingly, whenever a user clicks the Logoff link, you would run an
UPDATEquery to decrement this record's value by 1. To display the total number of logged in users, then, you would just return and display this table's single numeric value.
The problem with this approach is that users may log off of the site implicitly. That is, rather than logging off by clicking the Logoff button, they may
just close their browser window. Consequently, as more and more users log on, but then don't log off by clicking the Logoff link, the
UsersOnline table will continue to be less and less accurate, reporting hundreds or thousands of logged on users when there may only be a
In short, it's impossible for your code on the web server to know exactly how many people who have signed into the site are still actively viewing it. A compromise that is commonly used (such as with Session variables) is to define a timeout and to assume anyone who has not accessed the site within the timeout period has logged out.
The Membership system automatically tracks users' last activity dates. With the
this information is stored in the
LastActivityDate column. This column's value is updated to the
current UTC date and time value from a variety of actions:
- Retrieving the user's password. This action is performed whenever the
Membership.ValidateUsermethod is called, which is called by the Login Web control.
- Updating the user's information. This action transpires when the
Membership.UpdateUsermethod is called. This method is commonly used in user administration pages, such as the administration pages created by Dan Clem in the Rolling Your Own Website Administration Tool article.
- Retrieving information about the current user. The
Membership.GetUsermethod returns information about a particular user. It also accepts an option Boolean parameter that indicates whether or not to update the selected user's
aspnet_Users.LastActivityDatevalue. (By default, this column is updated.)
GetNumberOfUsersOnlinemethod that returns the number of users whose
aspnet_Users.LastActivityDatevalue falls within a specified time window. This time window value defaults to 15 minutes but can be customized via the
<membership>configuration element in
|What is UTC Time?|
Coordinated Universal Time, or UTC time, the standard international time that all time zones are expressed as offsets of. UTC does not get adjusted for
daylight savings. To compute local time from UTC, simply add the time zone offset and then add an additional hour if daylight savings time is in effect.
UTC time is commonly used to store date/time values in database systems because it is not tied to the database server's time zone.
For more information on the advantages of storing dates and times using UTC, as well as for information on how to work with UTC in SQL Server and .NET applications, refer to Using Coordinated Universal Time (UTC) to Store Date/Time Values.
Returning the Number of Users Currently Online
Let's create a simple example that shows the
GetNumberOfUsersOnlinemethod in action. At the end of this article you can download a complete working demo application that supports user accounts through the
SqlMembershipProvider; the necessary setup and configuration for using the Membership system was discussed in previous installments of this article series. In the demo's
Default.aspxpage you'll find a Label Web control named
Text property is assigned the numeric value returned by the
Keep in mind that this method only returns the number of authenticated users, which are users who have logged into the site. If there are 100
anonymous users visiting your site, and 20 logged in users, the
GetNumberOfUsersOnline method will return 20.
As noted earlier, the
GetNumberOfUsersOnline method determines whether a user is "logged in" or not based on the delta between the
aspnet_Users.LastActivityDate value and the current date and time. By default, a 15 minute window is used, but this value can be customized.
To change this window to 10 minutes, for example, go to your
Web.config file and add a
userIsOnlineTimeWindow attribute to
<membership> configuration element like so:
Tracking Additional Information About a User's Last Activity
The Membership system's built-in date/time tracking allows us to determine how many authenticated users are currently logged on, but it does not provide any further information. With a little bit of work we can extend this functionality to include a short description as to each user's most recent activity. For example, rather than just displaying the number of authenticated users who are logged onto the site, we could display a grid listing each user and what page on the site she is currently viewing.
To accomplish this we need to create our own database table that associated a description for the user's last activity with their user account record in
aspnet_Users table. Because I am only interested in the most recent activity (and am not interested in maintaining a log of each authenticated
user's activity), I created a table named
UsersCurrentActivity that establishes a one-to-one correspondence with the
table. Specifically, I defined the
UsersCurrentActivity table as having two rows:
UserId- a column of type
uniqueidentifier(so as to match the type for the
aspnet_Users); this column serves as the table's primary key.
Action- a column of type
nvarchar(255)that stores a brief description of the user's last action.
UsersCurrentActivity.UserIdcolumn to the
Logging the User's Activity
After creating the
UsersCurrentActivitytable, my next task was to create a stored procedure that, when called, would update a specified user's
aspnet_Users.LastActivityDatecolumn and update the corresponding row in
UsersCurrentActivitywith a specified
Actionvalue. I created a stored procedure named
sproc_UpdateUsersCurrentActivitythat performed these two tasks:
The above stored procedure starts by updating the specified user's
LastActivityDate column value. Next,
a check is made to see if there exists a record for the specified user in the
UsersCurrentActivity table. If there already exists a record,
UPDATE statement is used to update the user's
Action column value; If not, an
INSERT statement adds a new record
to the table.
Because these stored procedure includes multiple data modification statements, it is wise to place the data modification logic within the scope of a transaction to ensure atomicity. The rational behind this approach, as well as the T-SQL syntax for starting, commiting, and rolling back transactions, are a bit beyond the scope of this article. For more information see Maintaining Database Consistency with Transactions and Managing Transactions in SQL Server Stored Procedures.
sproc_UpdateUsersCurrentActivity Stored Procedure from an ASP.NET Page
Each time an authenticated user visits a page in the site, we want to execute the
sproc_UpdateUsersCurrentActivitystored procedure, passing in an appropriate value for the
UsersCurrentActivity.Actioncolumn. Because this likely needs to be called from every page, it makes sense to utilize a base page class. A base page class is a class that derives from the
Pageclass in the
System.Web.UInamespace and adds additional page-level functionality. Once a base page class has been created, we can update our ASP.NET pages' code-behind classes to derive from this custom base page class (rather than from the
Pageclass in the
System.Web.UInamespace) and then they'll all have access to this added functionality. See Using a Custom Base Page Class for Your ASP.NET Pages' Code-Behind Classes for more information on this technique.
Included in the download at the end of this article is a class named
BasePage that defines a method named
LogActivity accepts a String input and, if the visitor is authenticated, the
sproc_UpdateUsersCurrentActivity stored procedure
is called passing in the currently logged on user's
UserId value, the passed-in String parameter, and the current UTC date and time.
Once this class has been created and the ASP.NET pages' code-behind classes derive from it, simply call
LogActivity to record information
about the user's current activity. For example, if you want to have the action "Visiting the site's homepage" recorded whenever a user visits the home
page, have the home page's code-behind class derive from
BasePage and then, in the
Page_Load event handler, call
LogActivity("Visiting the site's homepage.").
The following code is from the code-behind class for
Default.aspx (the home page).
Displaying Logged On Users and Their Current Actions
Now that we are recording each logged on user's last action, we can create a richer interface displaying information on the currently logged on users. I created a stored procedure named
sproc_GetUsersCurrentActivitythat returns the
Action, and the number of minutes that have transpired since the users last activity for those users whose last activity time is within the specified window.
As you can see, this stored procedure accepts three input parameters:
@CurrentTimeUtc. Because a single Membership user store can contain user information for multiple applications, we want to ensure that this
stored procedure only returns information about logged in users for the specified application; this is the purpose of the
input parameter. The
@CurrentTimeUtc parameters indicate the number of minutes a user is considered
active since their last activity time in
aspnet_Users and the current UTC date and time, respectively. These two parameters are used to
@DateActive time, which is the date and time threshold that separates currently logged on users from logged off users.
The results of this stored procedure can be displayed in an ASP.NET web page through a GridView. The
WhoIsOnline.aspx page, which is part
of the demo downloadable from the end of this article, provides an example. As the following screenshot illustrates, there are currently two users logged
onto the site: Scott and Jisun. Scott is viewing the Who Is Online page while Jisun viewed the Users List page two minutes ago.
You are welcome to use this code and ideas in your websites, but be aware that there are a couple of limitations to keep in mind when evaluating using the Membership system's
GetNumberOfUsersOnlinemethod and my enhancements.
The Membership system's
GetNumberOfUsersOnline method and, by extension, my enhanced version only track authenticated users.
If your site has pages that are accessible by anonymous users as well as authenticated users, however, you may want to also track the number of unauthenticated
users (and where on the site they are currently visiting). Another shortcoming is that logging off does not affect the user's
column value. Consequently, if a user logs onto the site then immediately logs off, they'll count as being one of the site's logged on users for the
next 15 minutes (or for however long you've set this time window).
These limitations have to do with the Membership system's behavior. In other words, these limitations were not just now added by our enhancements.
This article examined the Membership system's capabilities for indicating how many authenticated users are currently logged in. Behind the scenes, the Membership system tracks users' last activity date and includes a
GetNumberOfUsersOnlinemethod that returns the number of user accounts whose last activity date is within a certain interval. Unfortunately, the Membership system does not include any methods for returning a list of users that are logged in; nor does it provide any information as to what the currently logged in users are doing. With a little bit of work, we can add additional functionality that tracks the currently logged on user's current activity, such as what page they are visiting in the site.
TRY...CATCHin SQL Server 2005