Data Management

Use SQL Server to maintain session state

While maintaining data between server calls can be difficult, you can use SQL Server to handle session state management (user session data). Here's how.

Maintaining data between server calls is a common dilemma in Web development. You may need to maintain information for the application or for particular user sessions. Storing such data is called state management, and ASP.NET provides the means to accomplish the task via various avenues. This includes storing the data in memory, on a state server, or via Microsoft SQL Server. This article focuses on session state management (user session data) using SQL Server.

Why is state management necessary?

Before we dive into SQL Server setup and usage, you may wonder why it's necessary. One of the more distressing aspects of Web development is the fact that HTTP is a stateless protocol. It works in a disconnected fashion with each Web request serviced as it's received. After the request is processed, all of the data utilized is discarded. The server doesn't remember anything between calls. That is, it doesn't remember unless it has explicit instructions to do so.

Session variables

Session variables are utilized with the following format:

C#: Session["variable_name"] = value;

VB.NET: Session("variable_name") = value

Once this value is stored, it's available throughout the user's session. The variable is discarded when the session ends. You may circumvent the discarding of the value by utilizing persistent state management (which is a topic for another day).

ASP.NET state management

ASP.NET allows you to store session data in memory, via a state server, or in SQL Server. The determination of the storage location is the application's Web.config file. The sessionState element within the system.web element is where the state management option is configured. The following example shows SQL Server utilized:

sqlConnectionString="data source=;user id=username;password=password"
timeout="20" />

Remember that the element names and attributes are case-sensitive. The following are possible values for the mode attribute:

  • InProc—store in memory. This is the fastest for performance, but all data is lost when the ASP.NET process recycles.
  • SQLServer—store data on SQL Server. This is the most reliable since it's disconnected from the Web server. This option uses the sqlConnectionString option. The connection string follows the normal syntax for connecting to a SQL Server database.
  • StateServer—store data on a separate Web server (IIS). This option uses the stateConnectionString attribute.

All options use the remaining. The cookieless attributes signal whether cookies are stored in memory (false) or maintained in the QueryString/URL (true). The timeout attribute signals the length of time (without activity) that session variables are stored. Now let's turn our attention to SQL Server setup.

SQL Server setup

SQL Server requires a special database to handle state management. Thankfully, the .NET Framework installation includes the necessary files to get this up and running in no time. The following scripts are installed:

  • InstallPersistSqlState.sql—contains scripts to set up database for persistent state management
  • InstallSqlState.sql—Contains scripts to set up database for state management
  • UninstallPersistSqlState.sql—Contains scripts for uninstalling persistent state management
  • UninstallSqlState.sql—Contains scripts for uninstalling state management

These scripts may be run from Query Analyzer or via the isql.exe command-line utility. To set up state management, we run InstallSqlState.sql. The result of the script is the creation of a database named ASPState. This handles the storage and maintaining of session variables. You can easily test the functionality with a simple example.

The following C# sample includes one Web form that populates session variables and redirects to another Web form that displays the values:

<%@ Page language="c#" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" Content="C#">
<body MS_POSITIONING="GridLayout">
<script language="C#" runat="server">
private void Page_Load(object sender, System.EventArgs e) {
Session["FirstName"] = "Tony";
Session["LastName"] = "Patton";
Session["Site"] = "";
Response.Redirect("WebForm2.aspx", true);

Here's the second Web form:

<%@ Page language="c#" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<script language="C#" runat="server">
private readonly string newLine = "<br>";
private void Page_Load(object sender, System.EventArgs e) {
Response.Write(Session["FirstName"].ToString() + " ");
Response.Write(Session["LastName"].ToString() + newLine);
Response.Write(Session["Site"].ToString() + newLine);

If you're a VB.NET developer, the pages have the following format:

<%@ Page Language="vb" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<script language="vb" runat="server">
Private Sub Page_Load(sender As Object, e As System.EventArgs)
Session("FirstName") = "Tony"
Session("LastName") = "Patton"
Session("Site") = ""
Response.Redirect("WebForm2.aspx", true)
End Sub

Here's the Page_Load event on the second form:

<%@ Page Language="vb" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<script language="vb" runat="server">
Private ReadOnly newLine As String = "<br>"
Private Sub Page_Load(sender As Object, e As System.EventArgs)
Response.Write(Session("FirstName").ToString() + " ")
Response.Write(Session("LastName").ToString() + newLine)
Response.Write(Session("Site").ToString() + newLine)
End Sub

One note on uninstalling the state management feature: Microsoft recommends stopping the World Wide Web Publishing service before executing the uninstall script. You can accomplish this with the net stop w3svc command from a command line. You can restart it with net start w3svc.

You can easily see the session management feature in action by examining the tempdb database on the SQL Server. It will contain two temporary tables used for session management: ASPStateTempApplications and ASPStateTempSessions.

A viable option

SQL Server provides an alternative if you worry about losing session state data due to Web server downtime. There is a performance hit since database interaction is involved, but it's the most reliable method available.

TechRepublic's free .NET newsletter, delivered each Wednesday, contains useful tips and coding examples on topics such as Web services, ASP.NET, ADO.NET, and Visual Studio .NET. Automatically sign up today!

About Tony Patton

Tony Patton has worn many hats over his 15+ years in the IT industry while witnessing many technologies come and go. He currently focuses on .NET and Web Development while trying to grasp the many facets of supporting such technologies in a productio...

Editor's Picks

Free Newsletters, In your Inbox