Distributed Session in AspNet Core using Sql Server

In a distributed environment using the SQL Server cache implementation we can store session values in SQL server.

For more info about distributed caching in asp.net core: distributed caching in asp.net core

Create a SESSION table

note: you need to have .net sdk 2.1 installed.

you can run the following command using command prompt to create table and index for your new session table (this will create a TestSession table under Test DB)

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

I’m going to create a new login/user (security -> new login). I will need this user in order to read and write in the new Session Table.

here I’m giving to the new user the permission to select/update/write/delete on the TestSession table.

Create a MVC web app

Create a new project (ASP.NET core Web Application) selecting NET.Core 2.1 as target Framework and ASP.NET MVC as project template.

Using Nuget install the following packages: Microsoft.Extensions.Caching.SqlServer, Microsoft.Extensions.Caching.SqlConfig.Tools,Microsoft.AspNetCore.Session

In the appsettings.json add the connection string:

	"ConnectionStrings": {
		"SqlSession": "Server=localhost\\SQLEXPRESS;Database=Test;Integrated Security=False;User Id=sessionUser;"
	}

better not to add the Password, otherwise it will end up in your version control repository.

You can use secret manager to store the password:

in the secret.json insert the password

{
	"DbPasswords": {
		"DistributedSessionPassword": "your pwd bla bla.."
	}
}

In the startup.cs, in the ConfigureServices method add the Distributed Sql Server Cache:

var sqlSessionConnString = new SqlConnectionStringBuilder(Configuration.GetConnectionString("SqlSession"));

sqlSessionConnString.Password = Configuration["DbPasswords:DistributedSessionPassword"];
           
services.AddDistributedSqlServerCache(options =>
            {
                options.ConnectionString = sqlSessionConnString.ConnectionString; 
                options.SchemaName = "dbo";
                options.TableName = "TestSession";
            }); 
			
services.AddSession();
			

In the Configure method insert app.UseSession();

app.UseSession();

to test it in any action of a controller save and retrieve a session value

public IActionResult Index()
{
    HttpContext.Session.SetString("TestMessage", "Hello Session!");
    var message = HttpContext.Session.GetString("TestMessage");

    return View();
}

you should be able to see now a new entry in the TestSession table

To store and retrieve complex objects you need to serialize and deserialize, you can use JsonConvert class for example.

HttpContext.Session.SetString("YourKey", JsonConvert.SerializeObject(value));