Problem with database logging

[Simeon Brett]

Hi, all,

I am trying to add a database target to my nlog congifuration, but it doesn't like my 'commandText' command.

The configuration is

<target name="DatabaseLog" xsi:type="Database" connectionString="XXXX">

     

<commandText>

insert into LogTable(time_stamp,level,logger,message) values(@time_stamp, @level, @logger, @message);

</commandText>

<parameter name="@time_stamp" layout="${date}" />

<parameter name="@level" layout="${level}" />

<parameter name="@logger" layout="${logger}" />

<parameter name="@message" layout="${message}" />

On building the application, it highlights the 'commandText' text and the message is 'The element cannot contain text.  Content model is empty'.

Any help would be appreciated.

Thanks

[Kim Christensen]

Hi Simon,

The command text should not be entered in an element, it is an attribute on the target element.
See https://github.com/NLog/NLog/wiki/Database-target

[Simeon Brett]

Thank you so much, Kim.  That was very helpful.

--
You received this message because you are subscribed to a topic in the Google Groups "NLog-Users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/nlog-users/rlzoOJyQ8Nw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to nlog-users+...@googlegroups.com.
To post to this group, send email to nlog-...@googlegroups.com.
Visit this group at http://groups.google.com/group/nlog-users.
For more options, visit https://groups.google.com/d/optout.

[Simeon Brett]

Just in case anyone else is having trouble, here is my working config:

 

<?xml version="1.0" encoding="utf-8" ?>

<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

 </targets>

  <target

      xsi:type="Database"

      name="DatabaseLog" 

      connectionString="server=xxxxx;database=DatabaseLog;integrated security=sspi"

            commandText="insert into  DatabaseLog.dbo.LogTable(message) values( @message);"

            >

    <parameter name="@message" layout="${message}" />

    <dbProvider>System.Data.SqlClient</dbProvider>

    </target>

  </targets>

  <rules>

    <logger name="*" minlevel="Trace"  writeTo="DatabaseLog" />

  </rules>

</nlog>

[Rich Ward]

Thanks for posting this Simeon.  By the way the fourth line should be, " <targets>", not " </targets>".

I am having a similar problem - but I still can't get NLog to write to the database.  Isn't there a way to point to an existing connection string instead of having it in the NLog.config file?

Any wisdom greatly appreciated.

Many thanks,

- Rich

[Kim Christensen]

Instead of using the connectionstring attribute, you should use the connectionstringname attribute passing in the name of the connection string.

[Glenn Lanier, II]

Rich,

I use the following code to loop through looking for all database targets. If you have a specific named target, you could be a little more efficient in retrieving the target.

NLog.Config.LoggingConfiguration config = LogManager.Configuration;
if (null != config)
{
	System.Diagnostics.Debug.WriteLine("SqlHelper: Initialize Logger - Config found");
	LogManager.ThrowExceptions = false;
 
	// Get connection strings for the current application 
	SqlConnectionStringBuilder csbApplication = new SqlConnectionStringBuilder();

	csbApplication.ConnectionString = myApplication.getConnection().ConnectionString;

	foreach (Target t in config.AllTargets)
	{
		SqlConnectionStringBuilder csb = null;
		System.Diagnostics.Debug.WriteLine("SqlHelper: Initialize Logger - Target found");
		DatabaseTarget dt = t as DatabaseTarget;
		if (null != dt)
		{
			System.Diagnostics.Debug.WriteLine("SqlHelper: Initialize Logger - Database target found");
 
			// Get connection string from dddd
			if (dt.Name.Equals("MySpecialDbLog"))
			{
				csb = csbSpecialApplication;
			}
			else
			{
				csb = csbApplication;
			}
			if (null != csb)
			{
				System.Diagnostics.Debug.WriteLine(string.Format("SqlHelper: Initialize Logger - Connection string set to {0}", csb.ToString()));
			}
 
			System.Diagnostics.Debug.WriteLine("SqlHelper: Initialize Logger - Setting connection string to stored value");
			// Set the connection string.
			dt.ConnectionString = csb.ToString();
		}
 
			System.Diagnostics.Debug.WriteLine("SqlHelper: Initialize Logger - Reset configuration");
 
			// Set new logging configuration
			LogManager.Configuration = config;
		}
	}
}

I hope this helps.
--G 

--
You received this message because you are subscribed to the Google Groups "NLog-Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nlog-users+...@googlegroups.com.

[Rich Ward]

Thank you so much!  Yes, this fixed it.  To assist anyone else who may run into a similar situation I am including my NLog.config below.  "Logger" refers to the connectionString in my Web.config file:

<add name="Logger" connectionString="Data Source=..... 

One additional question:  I had seen a post where the config recorded the user - but I can't get it to work.  I've included the remmed out statement below.

I hope this helps someone.

Best,

- Rich

<?xml version="1.0" encoding="utf-8" ?>

<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <!-- 

  See http://nlog-project.org/wiki/Configuration_file 

  for information on customizing logging rules and outputs.

   -->

  <targets>

    <!-- add your targets here -->

    <target xsi:type="Database" name="Loggerdb"

            connectionStringName="Logger"

            commandText="INSERT INTO AppLogging..LogMessage(DateTime, Message, Level, Component, Machine) VALUES(getutcdate(), @msg, @level, @logger, @machine);">

      

<!--commandText="INSERT INTO [LogMessage](DateTime, Message, Level, Component, User, Machine) VALUES(getutcdate(), @msg, @level, @logger, @user, @machine);">-->      

      <!-- parameters for the command -->

      <parameter name="@msg" layout="${message}" />

      <parameter name="@level" layout="${level}" />

      <parameter name="@logger" layout="${logger}" />

      <parameter name="@machine" layout="${machinename}"/>

      <parameter name="@user" layout="${windows-identity:domain=true}"/>

      

      <dbProvider>System.Data.SqlClient</dbProvider>

    </target>

  </targets>

  <rules>

    <logger name="*" minlevel="Trace" writeTo="Loggerdb" />

  </rules>

</nlog> 

[Rich Ward]

Wow, thanks Glenn.  I didn't realise you could add logic in config files!  I will definitely check this out.

Best,

- Rich

[Glenn Lanier, II]

Rich,

Please note that I'm not adding the logic to the config file; when my application starts up, I'm modifying the configuration loaded automatically from the config file, before my application "really" starts working.

--G

[Rich Ward]

Thanks for the clarification, Glenn.  Actually, that's makes it more powerful.

[Rob Richardson]

Kim,

I wanted to learn how to use the connectionstringname attribute, since you said we should use it.  I went to the Wiki page about database targets (https://github.com/nlog/NLog/wiki/Database-target) and found the following:

"connectionStringName - Name of the connection string (as specified in . "

Could you please finish that sentence for me?

RobR

[Rich Ward]

Good morning, Rob -

Perhaps I can assist.  The name is simply what you identify your connection string as in you Web (or App) config file.  For instance,  if your connection string in your We.config file is:

<add name="Logger" connectionString="Data Source=localhost;Initial Catalog=Logging;Integrated Security=True;Persist Security Info=True;" providerName="System.Data.SqlClient" />

Then, in your NLog.config file you would have:

<?xml version="1.0" encoding="utf-8" ?>

<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"

      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

  <!-- 

  See http://nlog-project.org/wiki/Configuration_file 

  for information on customizing logging rules and outputs.

   -->

  <targets>

    <!-- add your targets here -->

    <target xsi:type="Database" name="Loggerdb"

            connectionStringName="Logger"

            commandText="INSERT INTO Logging..LogMessage(DateTime, Message, Level, Component, Machine) VALUES(getutcdate(), @msg, @level, @logger, @machine);">         

      <!-- parameters for the command -->

      <parameter name="@msg" layout="${message}" />

      <parameter name="@level" layout="${level}" />

      <parameter name="@logger" layout="${logger}" />

      <parameter name="@machine" layout="${machinename}"/>

      

      <dbProvider>System.Data.SqlClient</dbProvider>

    </target>

  </targets>

  <rules>

    <logger name="*" minlevel="Trace" writeTo="Loggerdb" />

  </rules>

</nlog>

I hope this helps.

All the best,

- Rich

--

[Rob Richardson]

Thanks very much.  That makes sense.  

RobR