Using stored procedures in log4net configuration

Question

I'm trying to use log4net in my C# console application. I'm not too sure how this is used with stored procedures. With the configuration info below, how exactly will doing something like log.Info("message here") execute the stored procedure with parameters if all I pass is the message string?

I'm using SQL Server Express as database. In AssemblyInfo.cs I've added the following line:

[assembly: log4net.Config.XmlConfigurator(Watch = true)]

From what I've found here on StackOverflow, I have the following in my app.config:

<root>
  <level value="DEBUG" />
  <appender-ref ref="FileAppender" />
  <appender-ref ref="ConsoleAppender" />
  <appender-ref ref="AdoNetAppender" />
</root>
<configSections>
  <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
</configSections>
<appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
    <bufferSize value="100" />
    <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral" />
    <connectionString value="data source=MyPC\\SQLEXPRESSPC;initial catalog=MyDBName;integrated security=false;persist security info=True;User ID=MyDBUser;Password=MyDBPassword" />

    <commandText value="InsertGoogleDriveLog" />
    <commandType value="StoredProcedure" />

    <parameter>
        <parameterName value="@user_name" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%username" />
        </layout>
    </parameter>
    <parameter>
        <parameterName value="@message_text" />
        <dbType value="String" />
        <size value="1024" />
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%message" />
        </layout>
    </parameter>
</appender>

In my logger class I have the line:

log.Info("OK, here we are");

Also, I removed , PublicKeyToken=b77a5c561934e089 from the connectionType string value, as I'm not sure where this is obtained from when I copied it from the question here.

I doubt that this could work if you don't change (or add) one of your root appenders to use the AdoNetAppender — Steve, Mar 21 '17 at 13:40
I don't think you meant to have your username formatted as a `RawTimeStampLayout` — stuartd, Mar 21 '17 at 13:52
@Steve , I made the edit just now to the root and added AdoNetAppender. — Just Rudy, Mar 21 '17 at 13:52
@Steve , log4net can't execute a stored proc. with given parameters? Initially I was planning to simply log with a function that takes in whatever parameters, and executes the stored proc. It was then suggested by someone that I use log4net, but I'm not sure how this is more efficient. — Just Rudy, Mar 21 '17 at 13:58
You can use `%username` as in [this question](http://stackoverflow.com/questions/11541289/how-to-get-current-username-instead-of-apppool-identity-in-a-logfile-with-log4ne), note though that the [documentation on that](https://logging.apache.org/log4net/log4net-1.2.13/release/sdk/log4net.Layout.PatternLayout.html) says _"Generating caller WindowsIdentity information is extremely slow. Its use should be avoided unless execution speed is not an issue."_ — stuartd, Mar 21 '17 at 13:59
@stuartd , @ Steve , thank you both for your help. So in gist, using log4net wouldn't necessarily be flexible enough for me. I'd like to log to multiple columns in a table. My table has multiple columns for which the stored proc would insert into, having the column values passed to it. What is the downside of just having a method call to execute the stored proc? — Just Rudy, Mar 21 '17 at 14:36

score 1 · Accepted Answer · answered Mar 24 '17 at 10:59

it seems you are missing some elements in your configuration (log4net -> should contain the root and appender elements):

<configSections>
  <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler, log4net"/>
</configSections>
<log4net>
 <root>
  <level value="DEBUG" />
  <appender-ref ref="AdoNetAppender" />
 </root>
 <appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
    <bufferSize value="100" />
    <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral" />
    <connectionString value="data source=MyPC\\SQLEXPRESSPC;initial catalog=MyDBName;integrated security=false;persist security info=True;User ID=MyDBUser;Password=MyDBPassword" />

    <commandText value="InsertGoogleDriveLog" />
    <commandType value="StoredProcedure" />

    <parameter>
        <parameterName value="@user_name" />
        <dbType value="String" />
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%username" />
        </layout>
    </parameter>
    <parameter>
        <parameterName value="@message_text" />
        <dbType value="String" />
        <size value="1024" />
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%message" />
        </layout>
    </parameter>
 </appender>
</log4net>

thanks. I'm still unsure how stored proc is executed from `log.Info("some message")`. Is the ParameterName value the name of the parameter defined in the stored proc? And what if I had more values to pass for the stored proc? Thanks again. — Just Rudy, Mar 24 '17 at 13:55

Using stored procedures in log4net configuration

1 Answers1