Update LastActivityDate in aspnet_Users with Cookie Authentication

In case you are using the ASP.NET Membership Provider you probably noticed that LastActivityDate doesn’t update correctly whenever someone checked Remember me on his Login. I got a site running with a Cookie timeout of 10080 seconds, a pretty long time. I need to have an accurate LastActivityDate because I want to display whenever a user has actually been active.

So what now? I want to stick to my cookie timeout value and need to update the LastActivityDate manually. Here’s my approach (I love stored procedures).

First create a stored Procedure:

   31 set ANSI_NULLS ON

   32 set QUOTED_IDENTIFIER OFF

   33 GO

   34 – =============================================

   35 – Author:        Andreas Kraus (http:/www.andreas-kraus.net/blog)

   36 – Create date: 2007-02-10

   37 – Description:    Updates LastActivityDate

   38 – =============================================

   39 ALTER PROCEDURE [dbo].[aspnet_Membership_UpdateLastActivityDate]   

   40     @UserName            nvarchar(256),

   41     @LastActivityDate    datetime

   42 

   43 AS

   44 BEGIN

   45     IF (@UserName IS NULL)

   46     RETURN(1)

   47 

   48     IF (@LastActivityDate IS NULL)

   49     RETURN(1)

   50 

   51     UPDATE dbo.aspnet_Users WITH (ROWLOCK)

   52     SET

   53         LastActivityDate = @LastActivityDate

   54     WHERE

   55        @UserName = UserName

   56 END

Then use this code in your global.asax by hitting the Application_AuthenticateRequest Event:

   31     void Application_AuthenticateRequest(object sender, EventArgs e)

   32     {

   33         if (User.Identity.IsAuthenticated)

   34         {

   35             System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);

   36             System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

   37             cmd.Connection = cn;

   38             cmd.CommandText = “aspnet_Membership_UpdateLastActivityDate”;

   39             cmd.Parameters.AddWithValue(“@UserName “, User.Identity.Name);

   40             cmd.Parameters.AddWithValue(“@LastActivityDate”, DateTime.Now);

   41             cmd.CommandType = System.Data.CommandType.StoredProcedure;

   42             cn.Open();

   43             cmd.ExecuteNonQuery();

   44             cn.Close();

   45         }

   46     }

That’s it, now you have a most accurate LastActivityDate value to work with.

3 comments ↓

#1 Holger from Germany on 03.01.07 at 4:19 pm

thanks for publishing…

ever thought about overloading the membership-class vs. putting this updateprocess into the global.asax?

#2 Ron Dionne on 04.03.08 at 10:00 pm

Good idea. But you really should give your application a name so I changed the SQL so you can pass that in too:

CREATE PROCEDURE aspnet_Membership_UpdateLastActivityDate
@UserName nvarchar(256),
@LastActivityDate datetime,
@ApplicationName nvarchar(256)

AS

BEGIN
IF (@UserName IS NULL)
RETURN(1)

IF (@LastActivityDate IS NULL)
RETURN(1)

UPDATE aspnet_Users WITH (ROWLOCK)
SET LastActivityDate = @LastActivityDate
FROM aspnet_Users u
INNER JOIN aspnet_Applications a
ON u.ApplicationId = a.ApplicationId
WHERE u.UserName = @UserName
AND a.ApplicationName = @ApplicationName
END

#3 Martin H. Normark on 09.04.12 at 1:31 pm

Stored procedure is unnecessary.

You can just use the Membership API like this:

MembershipUser user = Membership.GetUser();
user.LastActivityDate = DateTime.UtcNow;

Membership.UpdateUser(user);

Leave a Comment