Configure SQL Server Session State In ASP.NET Core

Introduction

 

Session is a feature in ASP.NET Core that enables us to save/store user data. Session stores the data in a dictionary on the Server and SessionId is used as a key. The SessionId is stored on the client at the cookie. The SessionId cookie is sent with every request. The SessionId cookie is Browser specific  and it cannot be shared among the Browsers. There is no timeout specified for SessionId cookie and they are deleted when the Browser session ends.

Session are of two types: InProc or In-memory and OutProc or Distributed session. If our session is in-memory and our Application is hosted on Web-Farm environment, we need to use sticky sessions to tie each session to a specific server. Whereas OutProc session does not require sticky sessions and they are the most preferred way to use session in our application.

In my previous article, I talked about InProc or In-memory session. In this article, we will learn about OutProc - SQL Server Session state in ASP.NET Core. 

 

Configure SQL Server session state

 

In SQL Server Session state, previous version of ASP.NET requires a number of tables and stored procedures to manage the storage of session in SQL server and this can be configured, using “aspnet_regsql” command or a tool. ASP.NET Core requires only one table. There are multiple ways to generate this storage table. The first is to use "Microsoft.Extensions.Caching.SqlConfig.Tools" tool. Before we run the commands of these tools, we need to install this tool. To install this tool, we need to add this tool in project.json and perform "dotnet restore" command. 

 

Project.json

 "tools": {
      "Microsoft.Extensions.Caching.SqlConfig.Tools": "1.0.0-preview2-final"
}

Once this tool is installed for our project, we can execute "sql-cache" command, which generates the required table and an index. The command is in the format given below. 

dotnet sql-cache create <connection string>  <schema>  <table name>

Here, I am using SQL Express edition and following becomes full command, which generates the table and an index.

dotnet sql-cache create "Data Source=.\SQLExpress;Initial Catalog=Test;Integrated Security=True;" "dbo" "SQLSessions"

 

 

 

 

Alternatively, we can create this table and an index manually. The script is given below to create a table and an index. 

CREATE TABLE [dbo].[SQLSessions](
    [Id] [nvarchar](449) NOT NULL,
    [Value] [varbinary](max) NOT NULL,
    [ExpiresAtTime] [datetimeoffset](7) NOT NULL,
    [SlidingExpirationInSeconds] [bigint] NULL,
    [AbsoluteExpiration] [datetimeoffset](7) NULL,
 CONSTRAINT [pk_Id] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [Index_ExpiresAtTime] ON [dbo].[SQLSessions]
(
    [ExpiresAtTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

SQL session state internally uses memory cache, so as to enable SQL Server session. We need to add "Microsoft.Extensions.Caching.SqlServer" dependency along with "Microsoft.AspNet.Session" into project.json file.

 

project.json

{
  "version": "1.0.0-*",
  "buildOptions": {
    "preserveCompilationContext": true,
    "debugType": "portable",
    "emitEntryPoint": true
  },
  "tools": {
      "Microsoft.Extensions.Caching.SqlConfig.Tools": "1.0.0-preview2-final"
  },
  "dependencies": {},
  "frameworks": {
    "netcoreapp1.0": {
      "dependencies": {
        "Microsoft.NETCore.App": {
          "type": "platform",
          "version": "1.0.1"
        },
    "Microsoft.AspNetCore.Server.Kestrel": "1.0.0",
    "Microsoft.AspNetCore.Mvc": "1.0.0",
    "Microsoft.AspNetCore.Session" : "1.0.0",
    "Microsoft.Extensions.Caching.SqlServer" : "1.0.0",
    "Microsoft.Extensions.Configuration.Json" : "1.0.0"
      },
      "imports": "dnxcore50"
    }
  }
}

We can add a connection string to appSetting.json file or put it directly in ConfigureServices method of startup class. Following is the definition of ConfigureServices and Configure method of startup class. 

 

Startup.cs

using System;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.DependencyInjection;

namespace WebApplication {
    public class Startup{
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDistributedSqlServerCache(options =>
            {
                options.ConnectionString = @"Data Source=.\SQLExpress;Initial Catalog=Test;Integrated Security=True;";
                options.SchemaName = "dbo";
                options.TableName = "SQLSessions";
            });

            services.AddSession(options => {
                options.CookieName = "Test.Session";
                options.IdleTimeout = TimeSpan.FromMinutes(60);
            });


            services.AddMvc();
        }
        public void Configure(IApplicationBuilder app){
            app.UseSession();
            app.UseMvc();
            app.Run(context => {
                return context.Response.WriteAsync("Hello Readers!");
            });
        }
    }
}

Example

In the example given below, I have set my name into session in the first request and retrieved the session value in another request. 

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Caching.Distributed;

public class HomeController : Controller
{
    [Route("home/index")]
    public IActionResult Index()
    {
        HttpContext.Session.SetString("name","Jignesh Trivedi");
        return View();
    }
    [Route("home/GetSessionData")]
    public IActionResult GetSessionData()
    {
        ViewBag.data = HttpContext.Session.GetString("name");
        return View();
    }
}

Output

 

With SQL server state in ASP.NET Core, we can also create and use the session extension methods and we can also store complex objects in to the session.

Summary

 

This article described the steps required to enable SQL Server as a storage mechanism for session state in an ASP.NET Core MVC Application. The usage of SQL server session state is slightly different than the classic ASP.NET Application.