JumpStart # 44 – SQL Server based Session State in ASP.Net Core MVC Application

In this Jumpstart, we are going to see how to use SQL Server as Session store for an ASP.Net Core Application. There are multiple advantages in having a distributed cache storage (in this case it is SQL Server) acting as Session store. Sessions can be shared across multiple applications and also between multiple servers in a cluster. There are also some disadvantages around performance (in-memory is faster than SQL Server based session store) and operational overheads of maintaining additional SQL Server database.

NOTE: Updated this tutorial on 2/26/2017 with MSBuild based Dotnet SDK.

Create an ASP.Net Core Application using VS 2017. I am using following version of SDK which is based on MSBuild/CSProj (remember that previous versions of ASP.Net Core are based on Project.json/XProj.).

image

Now we need to use AddDistributedSqlServerCache. To use SQL Server Cache, we need to add following packages dependencies and tools.

<ItemGroup>
  <PackageReference Include="Microsoft.ApplicationInsights.AspNetCore" Version="2.0.0" />
  <PackageReference Include="Microsoft.AspNetCore" Version="1.0.3" />
  <PackageReference Include="Microsoft.AspNetCore.Mvc" Version="1.0.2" />
  <PackageReference Include="Microsoft.AspNetCore.Session" Version="1.1.0" />
  <PackageReference Include="Microsoft.AspNetCore.StaticFiles" Version="1.0.1" />
  <PackageReference Include="Microsoft.Extensions.Caching.SqlServer" Version="1.1.0" />
  <PackageReference Include="Microsoft.Extensions.Configuration.UserSecrets" Version="1.1.0" />
  <PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="1.0.1" />
  <PackageReference Include="Microsoft.VisualStudio.Web.BrowserLink" Version="1.0.1" />
</ItemGroup>
<ItemGroup>
  <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="1.0.0-msbuild3-final" />
  <DotNetCliToolReference Include="Microsoft.Extensions.SecretManager.Tools" Version="1.0.0-msbuild3-final" />
  <DotNetCliToolReference Include="Microsoft.Extensions.Caching.SqlConfig.Tools" Version="1.0.0-msbuild3-final" />
</ItemGroup>

Open Command Prompt and navigate to the project directory and execute a dotnet restore as shown below (in my case Visual Studio restored package already).

image

Create a new database in SQL Server (I am using SQL Server 2016 Express) with name AspNetSessionDB.

image

Then execute following command to create required AspNetSessions Table in AspNetSessionDB to hold Session values.

dotnet sql-cache create “Data Source=Server Name;Initial Catalog=AspNetSessionDB;Integrated Security=True” “dbo” “AspNetSessions”

image

Following table will be created in SQL Server.

image

Now lets configure ASP.Net Core Application with SQL Server Cache in Startup class under ConfigureServices method.

services.AddDistributedSqlServerCache(options =>
{
    options.ConnectionString = "Server=RAMIDEVVM\\SQLEXPRESS;Database=AspNetSessionDB;Trusted_Connection=True;";
    options.SchemaName = "dbo";
    options.TableName = "AspNetSessions";
});

services.AddSession(options =>
{
    options.CookieHttpOnly = true;
    options.CookieName = ".ASPNetCoreSession";
    options.IdleTimeout = TimeSpan.FromMinutes(10);
    options.CookiePath = "/";
});

As the last step of configuration, lets add Session service to our HTTP Pipeline as shown below in Configure method of Startup class. One important point to be remembered is that UseSession()should be added to pipeline before UseMvc().

app.UseSession();

To demonstrate usage of session, lets create (or update if they already exist) below actions in HomeController.

public IActionResult Index()
{
    HttpContext.Session.SetString("User", "Rami");
    return Json(true);
}

public IActionResult About()
{
    var userContent = HttpContext.Session.GetString("User");
    return Json(userContent);
}

Now using Postman tool, lets make a request to Index action. We should see below response.

image

Lets make a request to About Action. We should see below response with data being loaded from session and sent back in JSON format.

image

If we look at the AspNetSessions table, data will be in below format.

image

That’s it for now. Happy Coding and Stay Tuned!!!

You may also like...