Configure SQL Server Session State In ASP.NET Core

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

dotnet sql-cache create <connection_string> <schema> <table_name>

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

CREATE TABLE [dbo].[SQLSessions] (  
      NOT NULL,  
    [Value] [varbinary](max) NOT NULL,  
     NOT NULL,  
    [SlidingExpirationInSeconds] [bigint] NULL,  
     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 to enable SQL Server session. We need to add the "Microsoft.Extensions.Caching.SqlServer" dependency along with "Microsoft.AspNet.Session" into the 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 the appSetting.json file or put it directly in the ConfigureServices method of the startup class. Following is the definition of ConfigureServices and the Configure method of the 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 the 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 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.