Logging DML operations in ASP NET MVC project

Question

There are several ways to log dml activities like as insert, update, and delete in an asp.net mvc project. I'm not sure if using aspects in code, triggers, or CDC in the database server is more useful, or if there are other, more logical options.

Answer 1

There are several options.

You might register an application interceptor if you're using Entity Framework as your ORM (see IDbCommandInterceptor). For further information, see Logging and intercepting database activities...

Setting the Database is another way that is very useful for logging ALL database operations.

Assign the following property to your DbContext implementation:

public class YourContext : DbContext
{   
    public YourContext(ILogger<YourContext> logger)
    {
        Database.Log = sql => logger.LogDebug(sql);
    }
}

I found this approach to be quite useful for debugging. I would have a file appender/target/sink (depending on what logging framework you're using e.g. NLog/Log4net/Serilog) specifically for my DbContext implementation, so that I could easily see the SQL of the database operations that are being executed without any noise from other application logs.

The other approach that I've used quite a bit is to use SQL Server Profiler, but this assumes a) you're using SQL Server, and b) you're able to connect to the database server.