CODEWITHSUNDEEP
c#.netnlog
user481779
user481779

Reputation: 1081

Writing binary data to NLog database target

I am attempting to write compressed data to a log table via the NLog database target. The logger fails with the message:

"Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query."

The database column is defined as varbinary(max) and the data that is added to the LogEventInfo properties is a byte array. I don't see an NLog layout renderer that I could tell NLog I am sending it binary data. Any ideas?

Thanks in advance.

*** REQUESTED INFORMATION ****

Here is my NLog target

  <target type="Database" name="atom_db" connectionstring="Server=MYSERVER;Database=MYDB;Trusted_Connection=True;">
    <dbprovider>mssql</dbprovider>
    <commandText>insert into TAtomLog ([requestdt], [sessionid], [clientip], [clientuser], [application], [method], [data]) values (@RequestDt, @SessionId, @ClientIP, @ClientUser, @SessionType, @Method, @Data);</commandText>
    <parameter name="@RequestDt" layout="${event-context:item=RequestDt}"/>
    <parameter name="@SessionId" layout="${event-context:item=${guid:SessionId}"/>
    <parameter name="@ClientIP" layout="${event-context:item=ClientIP"/>
    <parameter name="@ClientUser" layout="${event-context:item=ClientUser"/>
    <parameter name="@SessionType" layout="${event-context:item=SessionType"/>
    <parameter name="@Method" layout="${event-context:item=Method"/>
    <parameter name="@Data" layout="${event-context:item=Data"/>
  </target>

Here is my C# code that creates the LogEventInfo and calls the Logger. The call to StringCompressor.CompressString returns "byte []"

    var ev = new LogEventInfo(LogLevel.Info, AtomLogger.LoggerName, string.Empty);
    ev.Properties.Add("RequestDt", DateTime.Now);
    ev.Properties.Add("ClientIP", clientip);
    ev.Properties.Add("ClientUser", clientuser);
    ev.Properties.Add("SessionId", sessionid);
    ev.Properties.Add("SessionType", sessiontype);
    ev.Properties.Add("Method", new StackFrame(1).GetMethod().Name);
    ev.Properties.Add("Data", StringCompressor.CompressString(data));
    AtomLogger.GetDBLoggerInstance().Log(ev);

Upvotes: 1

Views: 1577

Answers (1)

Роман Пикуленко
Роман Пикуленко

Reputation: 36

You can pass your binary data as hexadecimal string and use convert function to cast the string to binary:


    var buffer = new byte[]; // your binary buffer
    var stringToPassInDb = BitConverter.ToString(buffer).Replace("-", string.Empty);
    var logInfo = new LogEventInfo(LogLevel.Info, null, null);
    logInfo.Properties["Data"] = stringToPassInDb;
    _logger.Log(logInfo);

and sql command in Nlog.config shall be simular:

  <commandText>
    insert into dbo.Log
    (
    log_date, log_level, log_message,
    log_machine_name, log_user_name,
    entity_id, command, data
    )
    values
    (
    @time_stamp, @level, @message,
    @machinename, @user_name,
    @entity_id, @command, convert(varbinary(max), @data, 2)
    );
  </commandText>
  <parameter name="@time_stamp" layout="${date:universalTime=true:format=yyyyMMdd HH\:mm\:ss"/>
  <parameter name="@level" layout="${level}"/>
  <parameter name="@message" layout="${message}"/>
  <parameter name="@machinename" layout="${machinename}"/>
  <parameter name="@user_name" layout="${windows-identity:domain=true}"/>
  <parameter name="@entity_id" layout="${event-properties:item=EntityId}" />
  <parameter name="@command" layout="${event-properties:item=Command}" />
  <parameter name="@data" layout="${event-properties:item=Data}" />

Upvotes: 2

Related Questions