Web Dev: Storing Session Data in SQL Server

WEB DEV
Storing Session Data
Make Session Data Available through ASP

In "Using Session and Application Variables" (January 1999), I covered session and application variables and unique session IDs. Session variables make the user name available to use in Active Server Pages (ASP) that a current user visits within an application's scope. Application variables are another way to store information in your application, and they're available to all users (not only users within the application's scope). A unique session ID is a string combining the server name, application name, and date and time, which are unique for a particular session. This month, let's examine the database side and how you can store session data with ADO and SQL Server databases to make the data available through ASP.

First, let's talk about situations where using session variables in ASP isn't appropriate. You might not want to use session variables in ASP for storing session data when your application must perform at its best. Also, you might not want to use session variables when your application runs on servers that users access through a scheduler and the scheduler doesn't route users back to the same server each time. Most developers who forgo using session variables do so to gain speed. Therefore, you need to think about what data you want to store in a database of session information and how to store it. One approach is to create a general-purpose session table in the database and pack session data into one or more table fields by using a structured format such as Extensible Markup Language (XML). Then you can select the data from a record for a particular user session and unpack the session data. Another approach is to create specific fields in session or other tables and use those fields to store temporary session data.

Either approach will work, but you need to decide which meets your specific requirements, such as how users query data or whether you need a specific approach to store certain types of data (such as user preferences). However, when absolute speed is your goal, you need to create specific fields in the database that match your needs for temporary storage, and put the session data in those fields. This approach removes the need for string handling or manipulating other than reading data from a database field. This method also keeps the data processing simple; you don't need much code to work with the data.

To demonstrate the method of creating fields for storing session data, I created an ASP application. The first step is to create a database table for the session data. Screen 1 shows the table structure. SessionID, the index for the table, contains the unique SessionID for a record. This table structure supports storing a username, customerid, and generic customer data. You can add product data, shopping-cart items, and other items to the table's structure that you might want your application to support.

Next, you need to create the code to interact with the database and session data. You can find this code in the link to this article at http://www.sqlmag.com. The RunWithRS function performs all database access. You routinely call three session-related functions from Database.asp: SaveSession to create or update session data, GetSession to retrieve a session data record by SessionID, and DeleteSession to delete a session data record from the database for a particular SessionID.

The SaveSession function is the key to using session or user data in the way that this example shows. When the application needs to save session data, it calls SaveSession. If a SessionID isn't available, ASP calls SaveSession without a SessionID and creates a new database record. If the application calls SaveSession and passes it a SessionID, the code in SaveSession updates the session record for that SessionID, thus freeing you from having to create or manipulate a SessionID. The SaveSession function calls CreateSessionID only when it creates a new SessionID. SaveSession returns the SessionID value.

The cmdGo_onclick event procedure (which Listing 1 shows) in TestSession.asp demonstrates a way to use SaveSession. When the user fills in the fields and clicks OK the first time, SaveSession executes without a SessionID:

     SaveSession("", txtUserName.value,
	 txtCustomerID.value,
	 txtCustomerData.value)

After this code executes, it stores the new SessionID in a label design-time control on the page:

     lblSessionID.setCaption(sSessionID)

Now, when the user clicks the OK button again after changing the other fields, SaveSession executes with a SessionID:

     sTemp = SaveSession(sSessionID, txtUserName.value,
	 _ txtCustomerID.value, txtCustomerData.value)

This format causes SaveSession to update the existing record. The onclick event code also saves a URL by using the following code, which has the SessionID attached:

     sURL = "<A HREF=""TestSession2.asp?SessionID=" & _
        sSessionID & """>Test Session Retrieval</A>"
     lblURL1.setCaption(sURL )

This code stores the URL in the sURL variable by using sURL to set the caption of lblURL1. The setCaption method of the label design-time control places a hyperlink on the page with the SessionID appended to it as a QueryString that the target page will later retrieve.

You call the GetSession function when you need to return session data from the database table. In TestSession 2.asp, the following code demonstrates the execution of GetSession:

     dim rs
     dim SessionID
	 
	 SessionID = Request.QueryString("SessionID")
	 
	 set rs = GetSession(SessionID)

GetSession returns the recordset containing the session data. Then the calling application can extract the session values by using standard the ADO methods:

     Response.Write "Customer ID = " & rs("CustomerID") & "<br>"
     Response.Write "User Name = " & rs("UserName") & "<br>"
     Response.Write "Customer Data = " & rs("CustomerData") & "<br>"

You could argue that returning a recordset is unnecessary because you need to return only one value. However, having the recordset with the data in it for manipulation is handy. ADO and SQL Server are so fast that handling the recordset this way might not add significant overhead. The more string handling you do, the greater the chance that your code will add overhead. You can use a fast XML parser to manage the session data, but then the data will be in XML format, which has its own benefits and challenges.

The function in Database.asp is DeleteSession. DeleteSession takes the SessionID as an incoming parameter and removes the session record from the database. Your application can call DeleteSession any time after you finish with the session record. You might also add a routine on your server that removes old entries in the SessionInfo table. For example, you can add a program to run every day and remove records that are more than 24 hours old.

You can do a lot to improve your application's performance. Tuning the code and the server after writing the application can solve many performance issues. From the start, you can build into the application design programming techniques for handling session variables. Designing these features from the start is much easier than trying to go back to an application and rewire it to use the new techniques.

SQL Server Magazine
Bugs, Comments, Suggestions Subscribe