Handling Session Efficiently Using SQLSERVER State Management in .NET

In general, the application you develop is a logical unit where in the data fed in one page is required in the next page for further processing. The data has to be accessible across pages. This is possible through state management.

But how do you accomplish that? Does your web application support this feature by default? To your surprise, the answer is NO. You will be using HTTP Protocol for your web application. This protocol is stateless. It doesn’t relate requests. Each request is distinct. It receives request, processes it. After processing it discards all the data involved in processing.

How to establish state management then? State Management is established using Sessions. Data is different for different Users. Each User’s data across server calls are managed in sessions. To make things much clearer for you, an example in C# is given below:

Storing Data in Session is done as follows:
Session [“UserName”]=”UserABC”;
Session [“Role”] =”Guest”

Now control is redirected to the next page:
Response.Redirect(“NextForm.aspx”,true);

In NextForm.aspx, you can display the session data:
Response.Write(Session[“UserName”].ToString() + “ “);
Response.Write(Session[“Role”].ToString());

The concept is going to be the same in VB.NET. Syntax alone varies slightly. Instead of Session[“UserName”], you will be using Session(“UserName”).

Retaining Session Object is termed as Session State Management. This can be accomplished in three different ways:

• In Process: This is the default state management with high performance. The state information is stored in memory.
• SQL Server: The state information is stored in SQL Server database either temporarily or persistently.
• State Server: State information is stored in IIS.

Hope you have got a basic idea on what state management is. With this, let us get into more details on SQL Server State Management.

In SQL Server state management, data is stored in SQL Server database. In In-Process and State Server, data is not stored persistently. But here Persistent Storage is possible. The following diagram will give you an outline on SQL Server State Management.

As the above diagram depicts, SQL Server State Management can be performed in two ways. In case of persistent data storage, data is stored in a permanent database called ASPState. State tables are created in the ASPState Database when the script InstallPersistSQLState.sql is executed. You can remove State tables from the database by executing UninstallPersistSQLState.sql script.

In non-persistent/temporary data storage, the state tables are created in tempdb which is a temporary database. Tables are created when InstallSQLState.sql script is executed. This Script creates a job called ASPState_Job_DeleteExpired_Sessions which will delete data corresponding to expired sessions. This job will run every minute as long as your SQLServerAgent service is running. Drawback in this non-persistent data storage is that the state tables will be lost if the SQL Server is restarted. Because tempdb database is in temporary storage area which gets cleared every time the server is restarted.

To establish SQL Server State Management, you have to perform the following steps:

Step1: Run either InstallPersistSQLState.sql or InstallSQLState.sql as per your need. The script will be available in <Root Folder>\Microsoft.NET\Framework\<version>. The script can be executed using any SQL Server Utility. You can execute the script in SQL Query Analyzer by doing the following:
• Open SQL Query Analyzer
• Browse through File Menu and Click on Open
• “Open Query File Dialog Box” appears
• Choose the script file from the relevant location and click on Open
• Script file opens in the SQL Query Analyzer
• Browse through Query Menu and Click on Execute
• Script gets executed

Step2: Make the following entry in web.config file of your application:
<configuration>
<system.web>
<sessionState
mode="SQLServer"
stateConnectionString=”tcpip =127.0.0.1:8080”
sqlConnectionString="data source=127.0.0.1; Integrated Security=”SSPI”
cookieless="false"
timeout="30"
/>
</system.web>
</configuration>

Ensure that SQLServerAgent is running. If it is not running then session timeout will not happen and data will still remain in the database.

Though SQL Server State Management is advantageous in terms of providing persistent storage of state, there are few limitations to it as well. They are listed as below:
• Requires SQL Server Installation in the Server
• Application will be slowed when compared to the other two options
• Database Interaction required. This is a Performance Hit.
• In non-persistent storage, the state information is lost when SQL Server goes down.

In spite of all these drawbacks, it is the most reliable and commonly used way of state management.

| Additional Ways of Ensuring Security in .NET | Memory Lifecycle in .NET – An Overview | Few Best Coding Practices for ASP .NET | Handling Session Efficiently Using SQLSERVER State Management in .NET | How to Restrict a Program to Single Instance in .NET? | How to Use Structured Exception Handling in .NET | Understanding Boxing Versus Unboxing in .NET | Understanding Different Levels of Security in .NET | Understanding the Disadvantages of Memory Management in .NET | Using Membership API for Secured Coding in .NET |