How to log data to SQL Server in ASP.NET Core

In this article, we will learn how to use the open-source Serilog library to log structured data from an ASP.NET Core application to a SQL Server.

Create an ASP.NET Core project

To begin, let's create an ASP.NET Core project in Visual Studio. Assuming Visual Studio 2022 is already installed on your system, follow the steps below to set up a new ASP.NET Core project.

1. Launch Visual Studio and click "Create new project."
2. In the "Create new project" window, select "ASP.NET Core Web Application" from the available templates and click Next.
3. Specify the project name and location, then click Create.
4. In the "Create New ASP.NET Core Web Application" window, choose .NET Core as the runtime and ASP.NET Core 3.0 (or later) from the dropdown.
5. Select the "API" template, and make sure "Enable Docker Support" and "Configure for HTTPS" are unchecked. Set Authentication to "No Authentication."
6. Click Create to generate the new ASP.NET Core API project.
7. In the Solution Explorer, navigate to the Controllers folder and select "Add -> Controller" to create a new controller, naming it "DefaultController."

Install the NuGet packages for Serilog

To use Serilog, install the necessary packages from NuGet.

This can be done through the NuGet package manager in Visual Studio 2022 or by running the following commands in the NuGet Package Manager Console:

Install-Package Serilog
Install-Package Serilog.AspNetCore
Install-Package Serilog.Sinks.MSSqlServer
Install-Package Serilog.Settings.Configuration

Initialize Serilog in Program.cs in ASP.NET Core

To integrate Serilog into an ASP.NET Core application, you can use the UseSerilog() extension method to configure Serilog as the logging provider.

public class Program
{
    public static void Main(string[] args)
    {
        IConfigurationRoot configuration = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
            .Build();
        
        Log.Logger = new LoggerConfiguration()
            .ReadFrom.Configuration(configuration)
            .CreateLogger();
        
        BuildWebHost(args).Run();
    }

    public static IWebHost BuildWebHost(string[] args) =>
        WebHost.CreateDefaultBuilder(args)
            .UseStartup<Startup>()
            .UseSerilog()
            .Build();
}

Make sure to include the Serilog namespace in your program, as demonstrated below.

using Serilog;

Configure database connection settings in ASP.NET Core

When creating a new ASP.NET Core project in Visual Studio, the appsettings.json file is automatically generated. To log data to a SQL Server database, open the appsettings.json file from your project and add the following configuration:

{
  "Serilog": {
    "MinimumLevel": "Information",
    "WriteTo": [
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Data Source=.;Initial Catalog=myDb; User Id=sa; Password=123@qaz;",
          "tableName": "Log",
          "autoCreateSqlTable": true
        }
      }
    ]
  }
}

Creating a Database Table for SQL Server Logging

You can create the log table manually using the following script for your SQL Server database.

CREATE TABLE [Log] (
   [Id] int IDENTITY(1,1) NOT NULL,
   [Message] nvarchar(max) NULL,
   [MessageTemplate] nvarchar(max) NULL,
   [Level] nvarchar(max) NULL,
   [TimeStamp] datetimeoffset(7) NOT NULL,
   [Exception] nvarchar(max) NULL,
   [Properties] nvarchar(max) NULL
   CONSTRAINT [PK_Log]
     PRIMARY KEY CLUSTERED ([Id] ASC)
)

When you run the application, a new "Log" table will be created, and ASP.NET Core startup events will be logged in it.

Logging Data in ASP.NET Core Action Methods

You can use dependency injection to inject a logger instance into your controller.

public class DefaultController : Controller
{
   private readonly ILogger<DefaultController> _logger;
   public DefaultController(ILogger<DefaultController> logger)
   {
      _logger = logger;
   }
}

In the following example, Serilog is used in the controller's action methods to log data:

public class DefaultController : Controller
{
    private readonly ILogger<DefaultController> _logger;
    public DefaultController(ILogger<DefaultController> logger)
    {
        _logger = logger;
    }
    
    public IActionResult Index()
    {
        _logger.LogInformation("Hello World");
        return View();
    }
}