Logging into sql database

[sa123]

Hi,

Can anyone explain how to log data to the database?Is it possible to insert custom values to the table in database?

My requirement is to log the error details to the database whenever there is an error.But i dont know how to bind the values to the parameters of the nlog.Please go through the below code.

The tt_errors is the table where i have to log the values which i will be passing from the code at runtime.I need to insert 11 values to the table.

//Below is the app.config

<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <targets>
      <!--To Log into the Database -->
      <target xsi:type="Database" name="DatabaseLog" connectionString="Data Source=DEVSERVER\DEVSQLSERVER;Initial Catalog=TM_Dev;User ID=tmuser;password=2frogs">
        <commandText>
          INSERT INTO tt_Errors
          (Status
          ,DTStamp
          ,ZoneID
          ,GridID
          ,RouteDetailID
          ,SystemInError
          ,Severity
          ,Recoverability
          ,UnitInError
          ,ErrorNumber
          ,ErrorDescription)
          VALUES
          (@status
          ,@dtStamp
          ,@zoneId
          ,@gridId
          ,@routeDetailId
          ,@systemInError
          ,@severity
          ,@recoverability
          ,@unitInError
          ,@errorNumber
          ,@errorDescription);
        </commandText>
        <parameter name="@status" layout="${integer}"/>
        <parameter name="@dtStamp" layout="${date}"/>
        <parameter name="@zoneId" layout="${integer}"/>
        <parameter name="@gridId" layout="${integer}"/>
        <parameter name="@routeDetailId" layout="${integer}"/>
        <parameter name="@systemInError" layout="${integer}"/>
        <parameter name="@severity" layout="${integer}"/>
        <parameter name="@recoverability" layout="${integer}"/>
        <parameter name="@unitInError" layout="${integer}"/>
        <parameter name="@errorNumber" layout="${integer}"/>
        <parameter name="@errorDescription" layout="${message}"/>
      </target>

<rules>
      <logger name="*" level="Warn" writeTo="DatabaseLog"/>
</rules>
  </nlog>

//This is the method to log the values.

Public Sub LogErrorToDatabase(ByVal status As Integer, ByVal dtStamp As Date, ByVal zoneId As Integer, _
                                   ByVal gridId As Integer, ByVal routeDetailID As Integer, _
                                   ByVal systemInError As Integer, ByVal severity As Integer, _
                                   ByVal recoverability As Integer, ByVal unitInError As Integer, _
                                   ByVal errorNumber As Integer, ByVal errorDescription As String)
        logger.Warn(errorDescription)
    End Sub

If i use the following method

 <parameter name="@status" layout="${integer}"/>
        <parameter name="@dtStamp" layout="${date}"/>
        <parameter name="@zoneId" layout="${integer}"/>
        <parameter name="@gridId" layout="${integer}"/>
        <parameter name="@routeDetailId" layout="${integer}"/>
        <parameter name="@systemInError" layout="${integer}"/>
        <parameter name="@severity" layout="${integer}"/>
        <parameter name="@recoverability" layout="${integer}"/>
        <parameter name="@unitInError" layout="${integer}"/>
        <parameter name="@errorNumber" layout="${integer}"/>
        <parameter name="@errorDescription" layout="${message}"/>

 It logs only the message ie the errodescription and the other values are inserted as 0.

Example method calling statement is given below.

LogErrorToDatabase(1, DateTime.Now, 1, 1, 1, 1, 1, 1, 1, 1, "LogErrorToDatabase")

It creates new row in the table but only datetime and "LogErrorToDatabase" are getting added and the other values are 0.

Please help me.

[Jaroslaw Kowalski]

In this case the best thing to do is to pass additional values you want as event context parameters of LogEventInfo.

 

Instead of calling:

 

logger.Warn(message)

 

you should do something like that in LogErrorToDatabase() (sorry about C# syntax, but I'm not very good at VB):

 

var logEventInfo = new LogEventInfo(LogLevel.Warn, "LoggerName", "logMessage");

logEventInfo.Context["severity"] = mySeverity;

logEventInfo.Context["unitInError"] = unitInError;

// and so on for the remaining parameters

 

// then, at the very end you just do:

logger.Log(logEventInfo);

In your configuration file, you need to reference those context properties using ${event-context}

 

        <parameter name="@severity" layout="${event-context:sever}"/> 

        <parameter name="@unitInError" layout="${event-context:unitinError}"/>

 

Let me know if it helps.

 

Jarek

 

On Mon, Nov 2, 2009 at 4:49 AM, sa123 [via NLog Forum] <[hidden email]> wrote:

Hi,

Can anyone explain how to log data to the database?Is it possible to insert custom values to the table in database?

My requirement is to log the error details to the database whenever there is an error.But i dont know how to bind the values to the parameters of the nlog.Please go through the below code.

The tt_errors is the table where i have to log the values which i will be passing from the code at runtime.I need to insert 11 values to the table.

//Below is the app.config

<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <targets>
      <!--To Log into the Database -->
      <target xsi:type="Database" name="DatabaseLog" connectionString="Data Source=DEVSERVER\DEVSQLSERVER;Initial Catalog=TM_Dev;User ID=tmuser;password=2frogs">
        <commandText>
          INSERT INTO tt_Errors
          (Status
          ,DTStamp
          ,ZoneID
          ,GridID
          ,RouteDetailID
          ,SystemInError
          ,Severity
          ,Recoverability
          ,UnitInError
          ,ErrorNumber
          ,ErrorDescription)
          VALUES
          (@status
          ,@dtStamp
          ,@zoneId
          ,@gridId
          ,@routeDetailId
          ,@systemInError
          ,@severity
          ,@recoverability
          ,@unitInError
          ,@errorNumber
          ,@errorDescription);
        </commandText>
        <parameter name="@status" layout="${integer}"/>
        <parameter name="@dtStamp" layout="${date}"/>
        <parameter name="@zoneId" layout="${integer}"/>
        <parameter name="@gridId" layout="${integer}"/>
        <parameter name="@routeDetailId" layout="${integer}"/>
        <parameter name="@systemInError" layout="${integer}"/>
        <parameter name="@severity" layout="${integer}"/>
        <parameter name="@recoverability" layout="${integer}"/>
        <parameter name="@unitInError" layout="${integer}"/>
        <parameter name="@errorNumber" layout="${integer}"/>
        <parameter name="@errorDescription" layout="${message}"/>
      </target>

<rules>
      <logger name="*" level="Warn" writeTo="DatabaseLog"/>
</rules>
  </nlog>

//This is the method to log the values.

Public Sub LogErrorToDatabase(ByVal status As Integer, ByVal dtStamp As Date, ByVal zoneId As Integer, _
                                   ByVal gridId As Integer, ByVal routeDetailID As Integer, _
                                   ByVal systemInError As Integer, ByVal severity As Integer, _
                                   ByVal recoverability As Integer, ByVal unitInError As Integer, _
                                   ByVal errorNumber As Integer, ByVal errorDescription As String)
        logger.Warn(errorDescription)
    End Sub

If i use the following method

 <parameter name="@status" layout="${integer}"/>
        <parameter name="@dtStamp" layout="${date}"/>
        <parameter name="@zoneId" layout="${integer}"/>
        <parameter name="@gridId" layout="${integer}"/>
        <parameter name="@routeDetailId" layout="${integer}"/>
        <parameter name="@systemInError" layout="${integer}"/>
        <parameter name="@severity" layout="${integer}"/>
        <parameter name="@recoverability" layout="${integer}"/>
        <parameter name="@unitInError" layout="${integer}"/>
        <parameter name="@errorNumber" layout="${integer}"/>
        <parameter name="@errorDescription" layout="${message}"/>

 It logs only the message ie the errodescription and the other values are inserted as 0.

Example method calling statement is given below.

LogErrorToDatabase(1, DateTime.Now, 1, 1, 1, 1, 1, 1, 1, 1, "LogErrorToDatabase")

It creates new row in the table but only datetime and "LogErrorToDatabase" are getting added and the other values are 0.

Please help me.

[sa123]

Hi,

Thanks a lot for the help.The code is working fine now.