Logging in MS SQL Database using Serilog - ASP.NET Core 3.1 Web API

In this video we learn how to log information, error into MS Sql database with userName using Serilog library.
I will show you how to configure serilog and add custom columns into Log table.

1. I will show you how to log information, error into MS SQL Server database with User Name
2. Configure Serilog in Web API Project
3. Create Log Table in MS SQL Database
4. Add Custom Columns in Log Table

Below is Log Table Script,
CREATE TABLE [dbo].[WebApiLogs](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Message] [nvarchar](max) NULL,
[MessageTemplate] [nvarchar](max) NULL,
[Level] [nvarchar](128) NULL,
[TimeStamp] [datetime] NOT NULL,
[Exception] [nvarchar](max) NULL,
[Properties] [nvarchar](max) NULL,
[UserName] [varchar](100) NULL,
CONSTRAINT [PK_Logs] 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

[parvkaurav]

Cool Man ":) I tried lot of example. this one I able to get entries in the database.

[jigarpatel]

Correction, we should place LogContext.PushProperty middleware after app.UseAuthentication() to get UserName from User.Identity like below
app.UseAuthentication();
app.Use(async (httpContext, next) =>
{
//Get username
var username = ? : "anonymous";
LogContext.PushProperty("UserName", username);
await next.Invoke();
});
app.UseAuthorization();

[zakblacki]

It's not Random rng = null; it throws error replace with var rng = new Random();
Also I dont have the return result displayed in WebApiLog its empty

[ParamjeetSingh-ofbp]

Can we Use ILogger for database except Serilog

[WikkiOnIlluminati]

Hi,

I downloaded your code, changed connection string to my database. I run the code in visual studio but no logs are written in db table.
Created table the same way.

[vaishaliransing]

Hi, I did the same way as you have mentioned, but the username is still going as null

[DotnetistEnterprise]

Nice. Ive been uploading ASP.net videos also