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

Logging is an important aspect of software development, as it provides insights into how an application behaves and helps to identify and resolve issues. ASP.NET Core provides a robust logging framework that can be used to log data to various destinations, including SQL Server. In this article, we will discuss how to log data to SQL Server in ASP.NET Core.

Step 1: Add a Logging Provider

The first step in logging data to SQL Server is to add a logging provider. ASP.NET Core supports various logging providers, including Console, Debug, EventLog, and SQL Server. To add the SQL Server logging provider, we need to add the Microsoft.Extensions.Logging.SqlServer package to our project.

dotnet add package Microsoft.Extensions.Logging.SqlServer

Step 2: Configure the Logging Provider

After adding the SQL Server logging provider, we need to configure it to log data to a SQL Server database. We can do this in the ConfigureServices method of our Startup.cs file.

public void ConfigureServices(IServiceCollection services)
{
    // Add SQL Server logging provider
    services.AddLogging(builder =>
    {
        builder.AddSqlServer(Configuration.GetConnectionString("Logging"));
    });

    // ...
}

In the above code, we add the SQL Server logging provider by calling the AddSqlServer method on the LoggingBuilder. We also pass in the connection string to the logging database, which we can retrieve from the appsettings.json file.

"ConnectionStrings": 
{
    "Logging": "Server=localhost;Database=LoggingDb;Trusted_Connection=True;"
}

Step 3: Create the Logging Table

Before we can log data to a SQL Server database, we need to create a table to store the logs. We can use the following SQL script to create a simple Logs table.

CREATE TABLE [dbo].[Logs](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [LogLevel] [nvarchar](50) NOT NULL,
    [Message] [nvarchar](max) NOT NULL,
    [Exception] [nvarchar](max) NULL,
    [Timestamp] [datetimeoffset](7) NOT NULL,
    CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )
)

Step 4: Configure Logging Levels

We can configure logging levels in the appsettings.json file. By default, ASP.NET Core logs all levels of messages. However, we can configure the logging levels to only log messages that meet a certain severity threshold.

"Logging": 
{
    "LogLevel": 
    {
        "Default": "Information",
        "Microsoft": "Warning",
        "Microsoft.Hosting.Lifetime": "Information"
    }
}

In the above code, we configure the default log level to be Information, which means that messages with severity levels of Information, Warning, Error, and Critical will be logged. We also configure the log level for the Microsoft and Microsoft.Hosting.Lifetime namespaces.

Step 5: Log Data to SQL Server

After configuring the logging provider and creating the logging table, we can now log data to SQL Server. We can do this in our application code by injecting the ILogger<T> interface and calling the appropriate logging method.

public class HomeController : Controller
{
    private readonly ILogger<HomeController> _logger;

    public HomeController(ILogger<HomeController> logger)
    {
        _logger = logger;
    }

    public IActionResult Index()
    {
        _logger.LogInformation("Index page visited");

        return View();
    }
}

In the above code, we inject the 'ILogger<HomeController>' interface into the 'HomeController' constructor and then use it to log an information-level message when the Index action is invoked.

Step 6: View the Logs

Finally, we can view the logs in the SQL Server database by querying the Logs table. We can use any SQL Server client tool, such as SQL Server Management Studio, to connect to the database and run queries.

SELECT * FROM Logs

In the above code, we select all rows from the Logs table to view the logged messages.

Conclusion

In this article, we discussed how to log data to SQL Server in ASP.NET Core. We added the SQL Server logging provider, configured it to log data to a SQL Server database, created a logging table, configured logging levels, logged data to SQL Server, and viewed the logs. By using a logging framework and storing logs in a central database, we can easily monitor and troubleshoot our applications.