Configuring SQL Server session mode in ASP .NET

What is a session?

Session is a way to store information so that user data can be retrieved on multiple pages of the application. Session data is stored on user basis so that the data is available only for the same user. Unlike Cookies session data is stored in the server.

If you are unaware about ASP .NET Session state modes, please consider reading about Session in ASP .NET.

SQLServer mode

The SQL Server session mode stores session data to the database. Like State Server mode, SQL Server mode also stores data separate from the application domain. This makes it possible to maintain session data even after an application or server restart. As data is stored in the database, it is accessible to multiple servers running the same application. This is the most reliable and safe session mode in ASP .NET.

The main drawback of this mode is that it is the slower than other modes. If you are giving importance for reliability, speed is a factor that can be ignored.

With SQL Server, we can share session data between more than one applications.

I think, configuring your application to use SQL server mode is a topic that needs some detailed explanation. So I’ll be writing about it later.

Configuring your application to use SQL server mode

Configuring your application to use SQL Server mode includes two steps.

1) Configuring SQL Server

Step 1: To configure your SQL server, open Command Prompt ( Win + R and type cmd) and Navigate to C:\Windows\Microsoft.NET\Framework.

Step 2: If you open this folder in File Explorer, you will notice some folders with the name of .NET Framework versions installed on your system. Navigate to the folder of the newest .NET framework.

For me, it is:

cd C:\Windows\Microsoft.NET\Framework\v4.0.30319\

Step 3: If you have navigated to the folder as shown above, run the following command to configure SQL server to store the session state data.

aspnet_regsql.exe -S [SERVER_NAME] -E -ssadd -sstype p

aspnet_regsql.exe -S DESKTOP-5VM6SVL -E -ssadd -sstype p

Running this command will produce an output similar to the one shown below.

C:\Windows\Microsoft.NET\Framework\v4.0.30319>aspnet_regsql.exe -S DESKTOP-5VM6SVL -E -ssadd -sstype p
Microsoft (R) ASP.NET SQL Registration Tool version 4.7.3190.0
Administrative utility to install and uninstall ASP.NET features on a SQL server.
Copyright (C) Microsoft Corporation. All rights reserved.
Start adding session state.
..
Finished.
C:\Windows\Microsoft.NET\Framework\v4.0.30319>

Step 4: Open SQL Server Management Studio and check for a new database named ASPState. The database will have two tables named ASPStateTempApplications and ASPStateTempSessions. If there’s nothing missing, you have successfully configured your SQL server to store the session state data.

If you hate running commands, open the framework folder as described above and execute the following files (double-click to open in SSMS) in your SQL Server Management Studio.

• InstallSqlState.sql
• InstallSqlStateTemplate.sql

2) Configuring your ASP .NET application

After configuring SQL Server, you should configure your web application to use SQL Server session mode. By default, an ASP .NET application is set to use InProc Session mode. To change this to SQL Server session mode, open web.config and add the following lines within <system.web> tag.

<sessionState 
     mode="SQLServer" 
     stateConnectionString="Data Source=DESKTOP-5VM6SVL; Integrated Security=true"
     timeout="30">
 </sessionState>

Now you have your application and you can build your application as usual. All session data will be stored in the ASPStateTempSessions table in the ASPState database.

A simple example

Create a new project and paste the following code:

1) WebForm1.aspx:

<form id="form1" runat="server">
     <div>
         <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
         <br />
         <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Set session" />
         <br />
         <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
         <br />
         <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="Get session" />
     </div>
 </form>

2) WebForm1.aspx.cs:

using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Web;
 using System.Web.UI;
 using System.Web.UI.WebControls;
 namespace TestASP
 {
     [Serializable]     public partial class WebForm1 : System.Web.UI.Page
     {
         protected void Page_Load(object sender, EventArgs e)
         {
         }
         protected void Button1_Click(object sender, EventArgs e)
         {
             Session["data"] = TextBox1.Text;
         }
         protected void Button2_Click(object sender, EventArgs e)
         {
             TextBox2.Text = Session["data"].ToString();
         }
     }
 }

Run the project and check ASPStateTempSessions table.