Session Replacement in ASP

In order to make the login and session check work, you only need one table in the database.
The table below shows its schema. I named the table tbl_users:

We also need two stored procedures. Usp_checkSessionID is used to check the validity of the sessionID. In order to stay close to the session functionality, this procedure checks the last time this stored procedure was called (ie. Page refresh or redirect) and will give back a value of –1 if the idle time is more than the allowed idle time or if the sessionID does not exist in the database. If it all goes well, a value of 0 is given back to the application.

CREATE PROCEDURE usp_CheckSessionID
 @sessionID UNIQUEIDENIFIER
AS
IF EXISTS (
 SELECT 1
 FROM tbl_users
 WHERE sessionID = @sessionID
 AND  DATEDIFF(n, LastUpdate, GETDATE()) <= 20
)
BEGIN
 UPDATE tbl_users SET
LastUpdate = GETDATE()
 WHERE sessionID = @sessionID

 SELECT 0
END
ELSE
 SELECT –1

RETURN
GO

Lets take this step by step.
 
IF EXISTS (
 SELECT 1
 FROM tbl_users
 WHERE sessionID = @sessionID
 AND  DATEDIFF(n, LastUpdate, GETDATE()) <= 20
)

The T-SQL function IF EXISTS is used to check if records exist for the condition specified. Here, we check whether there are records in tbl_users for which the sessionID exists and where the last update happened less than 20 minutes ago.

The DATEDIFF function needs 3 arguments:
 
1. datepart. In this case we use minutes (n)
2. startdate, for which we use the value in the record
3. enddate, for which we use the current date and time (T-SQL function GETDATE())

This test returns true or false. If the test is true then this code is executed:

BEGIN
UPDATE tbl_users SET
LastUpdate = GETDATE()
 WHERE sessionID = @sessionID

 SELECT 0
END

Basically we update the LastUpdate field with the current date and time. We then select the value 0 to give back to the calling application.

If the IF EXISTS returns false, then we define the value –1 to be given back. We conclude with RETURN, which tells the stored produre that we are done.

Moving onto the second stored procedure, usp_CheckLogin, which is called when we submit the login information. We pass the parameters @usID and @password into this stored procedure, and let it execute:

CREATE PROCEDURE usp_CheckLogin
 @usID VARCHAR(20),
 @Password VARCHAR(20)
AS
DECLARE @sessionID UNIQUEIDENTIFIER
IF EXISTS (
 SELECT 1
 FROM tbl_users
 WHERE usID = @usID
 AND password = @password
 )
BEGIN
 SET @sessionID = NEWID()

 UPDATE tbl_users SET
  SessionID = @sessionID,
  LastUpdate = GETDATE()
 WHERE usID = @usID
 AND password = @password

 SELECT @sessionID
END
ELSE
 SELECT –1

RETURN
GO

Let’s take a step-by-step look at this stored procedure:

DECLARE @sessionID UNIQUEIDENTIFIER

We define a variable @sessionID of Type UNIQUEIDENTIFIER. This data type is a special data type which defines a unique value in the database consisting of the following form:
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f. In SQL server, it is considered a string datatype, so whenever we pass it to a stored procedure we need to handle it as if it were a string datatype.

IF EXISTS (
 SELECT 1
 FROM tbl_users
 WHERE usID = @usID
 AND password = @password
 )

Again, we do a test, this time to check whether the passed usID and password exist in table tbl_users. If this test returns true:

BEGIN
 SET @sessionID = NEWID()

 UPDATE tbl_users SET
  SessionID = @sessionID,
  LastUpdate = GETDATE()
 WHERE usID = @usID
 AND password = @password

 SELECT @sessionID
END

Firstly, we let SQL server determine a new database wide unique value, by using the function NEWID(). Then we update tbl_users where we give the usID/password record a new SessionID, and set the LastUpdate field to the current date and time. Finally, we tell the calling application the new sessionID is @sessionID (SELECT @SessionID).

If the IF EXIST test returned false, then we only give the calling application a value of –1. Again, we end this procedure with RETURN. Now let’s move on to the ASP side of the equation.