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

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
timeout=”20″ />

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

  • 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

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
  • UninstallPersistSqlState.sql–Contains scripts for uninstalling
    persistent state management
  • UninstallSqlState.sql–Contains scripts for uninstalling state

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
<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” %>
<script language=”vb” runat=”server”>
Private Sub Page_Load(sender As Object, e As
Session(“FirstName”) = “Tony”
Session(“LastName”) = “Patton”
Session(“Site”) = “”
Response.Redirect(“WebForm2.aspx”, true)
End Sub

Here’s the Page_Load event on the second

<%@ Page Language=”vb” %>
<script language=”vb” runat=”server”>
Private ReadOnly newLine As String = “<br>”
Private Sub Page_Load(sender As Object, e As
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!