Using Stored Procedure in NLog on a Database targe

问题:

I am having a bit of a problem using a stored procedure instead of a SQL INSERT statement when using NLog in a C# web application. The connection string "Logger" is correctly configured in Web.config and works properly when replacing the commandText with a SQL statement. I would appreciate a hint in the right direction. In this example the stored procedure is under the "Logs" schema and it is called "LogError".

<targets>
  <target xsi:type="Database"
      name="dberrorlog"
      connectionStringName="Logger"
      keepConnection="true"   
      commandText="[Logs].[LogError]" >
      <parameter name="@ProgName" layout="MyAppName"/>
      <parameter name="@CompName" layout="${machinename}"/>
      <parameter name="@LogLevel" layout="${level}"/>
      <parameter name="@UserName" layout="${identity}"/>
      <parameter name="@Error" layout="${exception:format=Message}"/>
      <parameter name="@SourceObj" layout="${exception:format=Method}"/>
      <parameter name="@StackTrace" layout="${exception:format=StackTrace}"/>
 </target>
</targets>
<rules>
  <logger name="*" minlevel="Error" writeTo="dberrorlog" />
</rules>

回答1:

From this NLog forum post, try using the text to execute the stored procedure:

commandtext="exec AddActivityLog
                            @ApplicationName, 
                            @ApplicationTime, 
                            @Severity, 
                            @Logger, 
                            @SaxoID, 
                            @EventID, 
                            @Message, 
                            @URL, 
                            @URLReferrer, 
                            @RemoteAddress, 
                            @Callsite, 
                            @CurrentUICulture, 
                            @ThreadIdentity, 
                            @WindowsIdentity, 
                            @MachineName, 
                            @ProcessID, 
                            @ThreadID, 
                            @ThreadName, 
                            @Stacktrace, 
                            @Exception,
                            @Cookie,
                            @FormVariables,
                            @QueryString,
                            @HTTPUserAgent"

Side note: Claus Rathje's answer wouldn't render in my browser, so I had to look a the page source to see the configuration he posted.

回答2:

Note that while @JeffOgata's solution works, it's probably not the way you want to go about solving the problem.

You can do something like this instead:

commandText="AddActivityLog"
commandType="StoredProcedure"

That way you don't have to worry about properly formatting an EXEC query.