One of the advantages of storing Log4Net log in a database using the AdoNetAppender, is the possibility to store log events from Stored Procedures in the same log.
The use of Stored procedures comes in many flavors, some use them just as a way of compiling the SQL statements which otherwise would be kept in the application. Other use them as major building blocks, to separate business logic and/or to take advantage of the possible performance gain which is possible using Stored procedures correctly.
The reason for logging in a Stored Procedure is the same as for the rest of the application.
The most simple solution to store log events from a Stored prodedure in Log4Net database table would be to insert log statements like:
Insert Log4Net (Date, Level, Logger, Thread, Message, Exeception)
Values (Getdate(), ‘ERROR’, ‘MySQLLogger’, @@spid, ‘My message’, ‘’)
In places we would need to log events.
A somewhat more modular approach would be to wrap the statement in a Stored Procuedre and execute that procuedure wherever we want to log.
Crate Procedure L4NLog
@Level varchar(10)
,@Thread varchar(255)
,@Logger varchar(255)
,@Message varchar(255)
,@ExceptionText varchar(255)
As
Begin
Insert Log4Net (Date, Level, Logger, Thread, Message, Exeception)
Values (Getdate(), @Level, @Thread, @Logger, @Message, @ExceptionText)
End
This Stored procedure could easily be execute/called from wherever we want to issue log events in the procedure, using the syntax:
Exec L4NLog ‘Error’, ‘My thread’, ‘My logger’ ‘My message text’, ‘My exception text’
The columns Logger and Thread, could be filled with information based on several methods. One is to try to extract whatever information we have available, in a stored procedure available information includes username, current database, name of executing procedure. Below I have modified the procedure to include a standard format for the logger and thread columns:
Create Procedure L4NLog
@ProcedureId int
,@Level varchar(10)
,@Message varchar(255)
,@ExceptionText varchar(255) = null
As
Begin
Declare @Thread varchar(255)
Declare @Logger varchar(255)
Select @Thread = convert(varchar, @@spid)
+ '.'
+ loginame
,@Logger = 'SQL.'
+ @@ServerName
+ '.'
+ db.name
+ '.'
+ Object_Name(@ProcedureId)
From master..sysprocesses sp
,master..sysdatabases db
Where spid=@@spid
And db.dbid = sp.dbid
Insert Log4Net..Log4Net(Date, Thread, Level, Logger, Message, Exception)
values(GetDate()
,@Thread
,@Level
,@Logger
,@Message
,@ExceptionText)
End
go
Using this stored procedure the Logger column will be filled with: “SQL.ServerName.CurrentDatabaseNeme.ProcedureName”
The Thread columen will be filled with:
“Spid.LoginName”, where spid is the Sql Server process Id, and LoginName is the name of the logged in user.
From the master..sysprocesses table we could also choose to use other columns, for example hostprocess, applicationname etc.
This procedure is depending on calling procedure supplying the procedure id, i.e. the id of the current execution procedure. This is easily achieved using the system variable @@ProcId. In this version of the procedure I have also made the @ExceptionText parameter optional. The syntax for calling this procedure would be something like:
Exec L4NLog @@ProcId, ‘ERROR’, ‘My message’ <,’My exception text’>
A further improvement would be to create on procedure for each log level, with the log level as a part of the procedure name (L4NFatal, L4NError, L4NWarn, L4NDebug, L4NInfo) and removing Level input parameter. This could be done either by making five, more or less, equal procedures, or by creating wrapper procedures, below I have written an example of a wrapper procedure for Error level logging.
Create Procedure L4NError
@ProcedureId int
,@Message varchar(255)
,@ExceptionText varchar(255) = null
As
Begin
Exec L4NLog @ProcedureId, ‘ERROR’, @Message, @ExceptionText
End
Using this approach you can easily log events from your stored procedure using statements like:
Exec L4NDebug @@ProcId, ‘Procedure start’
Exec L4NError @@ProcId, ‘Customer not found in Customer tablel’