Session Madness !!!

[Goofy]

I have been trying to implement a 'Whos On' table in my sql backend so that
I can see at a glance who has active sessions ( Potentially ) on my asp.net
application.

I am using windows integrated security. On Session Start, I create an entry
for the user in my Whos on and log the session start for that users in the
events database table. So I see their entry come into whos on and see the
event "Session started for user XXX".

So far so good. . . . .

If the user closes the browser, then comes back, they get a new session, so
they cannot add a new entry for themselves and a violation of primary key
occurs. Allthough they dont see this and continue normally.

Sometime after this the session for the first one ends, this then deletes
the entry for this user, and when the last session expires, there is nothing
to delete.

Does anyone have an idea how to resolve this ???

 

[Goofy]

Yes, I thought about that but the problem is the session is different; I
guess the thing to do is to delete any entry for the user where the session
does not equal the new session.

Its a bit messy as I would like to force the old session to expire, but im
not sure if I can do that .

 

[Goofy]

Sorry, you know what I misread you, yes update with the new session would be
the right way to do it and also, when the session ends one could delete in
the same way making sure that the session and user matched.

Thanks for your input, sometimes its just helpful to talk a problem through

 

[sloan]

I'd say youre primary key is

SessionID and UserID

So you can either add entries for this primary key. And thus a user with 2
different session id's won't crack the database constaint.



Or you can use the "if exists" logic, and then add or update as necessary.


Here's the code for a unique constraint on 2 columns.

CREATE TABLE dbo.MyTable (

entryid int IDENTITY (1,1) PRIMARY KEY NONCLUSTERED, --self explanatory

SessionID varchar(40) ,

UserID int ,



CONSTRAINT CONSTR_MYTABLE_KEY1 UNIQUE NONCLUSTERED ( SessionID, UserID )

)

GO

 

[Robbe Morris [C# MVP]]

You could try an approach like this:

http://www.eggheadcafe.com/articles/20030416.asp

--
Robbe Morris - 2004-2006 Microsoft MVP C#
I've mapped the database to .NET class properties and methods to
implement an multi-layered object oriented environment for your
data access layer. Thus, you should rarely ever have to type the words
SqlCommand, SqlDataAdapter, or SqlConnection again.
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp

 

[Goofy]

Thanks for that it made an interesting read.

You could try an approach like this:

http://www.eggheadcafe.com/articles/20030416.asp

--
Robbe Morris - 2004-2006 Microsoft MVP C#
I've mapped the database to .NET class properties and methods to
implement an multi-layered object oriented environment for your
data access layer. Thus, you should rarely ever have to type the words
SqlCommand, SqlDataAdapter, or SqlConnection again.
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp