C# and ASP.NET - Session State Alternative - Shared Hosting

After many long hours of trying to figure out why my session state information was being dropped randomly while using a shared hosting company I finally gave up and wrote my own way to persist session information in a database.

My Problem:
Many shared hosting company recycle their IIS virtual memory at a rate that was unpredictable and therefor lose any In-Process session state information without warning.  Because most shared hosting companies do not allow you to use the ASP.NET State Service you are really only left the the option of the built in ASP.NET SQLServer Session State mode.

My Solution:
Use a database to store user information.  This is not to say you can't use the built in ASP.NET SQLServer session state mode.  However, by using that you may find yourself in need of purchasing a separate database instance on your shared service provider once your site gets large enough because of the overhead it has the possibility of creating.


Here is what you need to do to implement this:
Create a database (use whatever name you like).

Now create two tables:  User and LoginKey

User Table Columns:

UserID_PK int Primary Key/Identity
Email varchar(75)
Password varchar(20)

LoginKey Table Columns:

LoginKeyID_PK int Primary Key/Identity
LoginKeyGUID varchar(39)
UserID_FK int
DateLoggedIn datetime
IPAddress varchar(15)



Now that you have your tables in place you need a few stored procedures to Get/Update/Delete the data in them.

Stored procedure to Create A User:

-- =============================================
-- Author: Matt Cramer, CramerCode.com
-- Create date:        --
-- Description: Create a user account
-- =============================================
CREATEPROCEDURE [dbo].[CreateNewUser]
@Email varchar(75),
@Password varchar(20),
@IPAddress varchar(15)
AS


DECLARE @UserID int
DECLARE @EmailUsed bit
DECLARE @LoginKey uniqueidentifier
SET @LoginKey = NEWID()
SET @EmailUsed = 0


IF EXISTS( SELECT 
UserID_PK
FROM
[User] WITH (NOLOCK)
WHERE
Email = @Email)
BEGIN
SET @EmailUsed = 1


SELECT @EmailUsed AS EmailUsed, '' AS LoginKey
END


IF(@EmailUsed = 0)
BEGIN
INSERT INTO [User]
(
Email,
[Password]
)
VALUES
(
@Email,
@Password
)


SET @UserID = SCOPE_IDENTITY()


INSERT INTO LoginKey
(
LoginKeyGUID,
UserID_FK,
IPAddress
)
VALUES
(
@LoginKey,
@UserID,
@IPAddress
)


SELECT @EmailUsed AS EmailUsed, @LoginKey AS LoginKey
END


The CreateNewUser stored procedure does exactly what it says it does.  It creates a new user.  You would use this on a signup page most likely.  You will also notice that it returns two columns: EmailUsed, and LoginKey.  EmailUsed lets you know if the email address they are using to sign up with has already been taken and thus a user was not created in the database.  The other column, LoginKey, is used to pass between pages and used to retrieve user information as the user clicks around the site.  This column is in the form of a GUID and needs to be stored in either the query string or a hidden field.

Note:  hidden field is preferred due to being able to encrypt it if you need to via SSL.






Stored procedure to Login A User:

-- =============================================
-- Author: Matt Cramer, CramerCode.com
-- Create date:       -----
-- Description: Log in user
-- =============================================
ALTER PROCEDURE [dbo].[LoginUser]
@Email varchar(75),
@Password varchar(20),
@IPAddress varchar(15)
AS
DECLARE @UserID int


IF EXISTS( SELECT
UserID_PK
FROM
[User] WITH (NOLOCK)
WHERE
Email = @Email
AND
[Password] = @Password)
BEGIN


SET @UserID = ( SELECT
UserID_PK
FROM
[User] WITH (NOLOCK)
WHERE
Email = @Email
AND
[Password] = @Password)


DELETE FROM LoginKey
WHERE
UserID_FK = @UserID


INSERT INTO LoginKey
(
UserID_FK,
IPAddress
)
VALUES
(
@UserID,
@IPAddress
)


SELECT 
LoginKeyGUID
FROM
LoginKey WITH (NOLOCK)
WHERE
LoginKeyID_PK = SCOPE_IDENTITY()
END
ELSE
BEGIN
SELECT ''
END


The LoginUser stored procedure is simple enough.  It takes Email, Password, and IPAddress (I'll talk about this later) as parameters to verify if a user for that email/password combination exists in the User table.  If the user is successfully logged in a row will be added to the LoginKey table and the GUID (or login key) will be returned to you.  Again this column is in the form of a GUID and needs to be stored in either the query string or a hidden field and passed around the site as the user clicks around.



Stored procedure to Verify a User is logged in:

-- =============================================
-- Author: Matt Cramer, CramerCode.com
-- Create date:       ----
-- Description: Verify login
-- =============================================
ALTER PROCEDURE [dbo].[VerifyLogin]
@Key uniqueidentifier,
@IPAddress varchar(15)
AS


IF EXISTS( SELECT
LoginKeyID_PK
FROM
LoginKey With (NOLOCK)
WHERE
LoginKeyGUID = @Key
AND
IPAddress = @IPAddress)
BEGIN
SELECT 'LoggedIn'
END
ELSE
BEGIN
SELECT 'NotLoggedIn'
END


This should be called at the beginning of any request to verify the user is who they say they are.  This stored procedure takes a Key (the login key GUID), and the IP address of the user that is making the request.  The response from this stored procedure is simply "LoggedIn" or "NotLoggedIn" based on if the user information was found in the LoginKey table.

Okay, so why the IP address parameter?  This is just an extra layer of security to help prevent a session hijack.  It is not really needed if you are using SSL and storing the login key in a hidden field.  However, if you put the login key in the query string it will go across most proxies as clear text even if you are using SSL.



Last stored procedure will Log a User out:
-- =============================================
-- Author: Matt Cramer, CramerCode.com
-- Create date:        ----
-- Description: Log out user
-- =============================================
ALTER PROCEDURE [dbo].[LogoutUser]
@Key uniqueidentifier
AS


DELETE FROM LoginKey
WHERE
LoginKeyGUID = @Key

This stored procedure should be called when a user wants to log out.


Putting it all together the logic looks like this:
user comes to site ->
user creates an account (CreateNewUser ) ->
user gets redirected to an account page and clicks around (VerifyLogin) for each page visited ->
user logs out for the day (LogoutUser) ->
user comes back the next day and logs in (LoginUser)



Final thoughts:
This technique was very popular before people got hooked on using the Session object to clog up the IIS pipeline.  This technique enforces the idea of serializable information being stored in either the IO or in the database in small chunks.

-Matt

ASP.NET MVC - The developer is back in control

ASP.NET MVC excites me more than just about anything Microsoft has released in the past 5 years (well... I do like Silverlight).  As I am new to MVC development, I am very familiar with classic forms of Microsoft web development (classic ASP, and ASP.NET).

A bit of history is in order here
Back in the nineteen hundred and nineties Microsoft released ASP.  At the time it was a very procedural VB scripting language.  You didn't have objects really (COM was available, but not really the same thing) and  debugging was a nightmare.  Anyone other than me remember writing "Response.Write...." 9863454 times trying to figure out where a logic error was?

A little after ASP 3.0 was released Microsoft released ASP.Net 1.0.  All of a sudden all the VB script programmers were in a rough spot.  ASP.Net was a totally new concept in web development.  Not only did you have to learn a new language (VB or C#) but you also lost a lot of control over what was actually rendered to the browser.

So now there is yet another new and very different way of development?
That's right folks!  It's time to role up those sleeves again and dive in.  Only this time Microsoft didn't make any language changes so the learning curve is a bit smaller.  When I first looked at MVC I was a bit skeptical (because who wants to relearn everything they thought they knew about web development....again).  As I was reading about MVC it was like turning over flat rocks in a field hoping there wasn't a snake underneath.  Slowly over time I realized that Microsoft had done something so wonderful that instead of being afraid of what I would find I was destroying all the information I could find on the subject!  I was back in control of exactly what was to be rendered on a page and not only that, I was able to take advantage of object oriented design patterns too. To top it all off MVC allows for better debugging and Unit Testing of my code.

FINALLY!  The developer is back in control!

-M