如何在异常中获取带有参数值的SQL [英] How to get SQL with parameter values on an exception

问题描述

难以置信，但是我似乎找不到一个直接的答案:当该语句生成异常并且仅当它产生异常时.我知道如何记录生成的每个SQL的语句和参数，但这太多了.但是，当存在System.Data.SqlClient.SqlException时，它仅提供SQL，而不提供参数值.在可以访问该数据以便可以对其进行记录的时候如何捕获它?

Hard to believe, but I can't seem to find a straight answer for this: How can I get the SQL statement including the parameter values when the statement generates an exception and only when it generates an exception. I know how to log the statement+parameters for every SQL generated, but that's way too much. When there's a System.Data.SqlClient.SqlException, though, it only provides the SQL, not the parameter values. How can I catch that at a point where I have access to the that data so that I can log it?

推荐答案

基于对各种问题(不仅仅是我的问题)的大量回答，我已经拼凑出一些可以解决问题的方法.我认为这对其他人也可能有用，所以我在这里包括了很多:

Based on a number of responses to various questions (not just mine), I've cobbled something together that does the trick. I think it could be useful to others as well, so I'm including a good deal of it here:

基本思想是

1. 具有NH log 所有查询，打印精美，参数值就地
2. 将所有注销都扔出去，除了在异常发生之前的注销.

1. Have NH log all queries, pretty-printed and with the parameter values in situ
2. Throw all those logs out except the one just prior to the exception.

我使用Log4Net，设置如下:

I use Log4Net, and the setup is like this:

<?xml version="1.0"?>

<log4net>
  <appender name="RockAndRoll" type="Util.PrettySqlRollingFileAppender, Util">
    <file type="log4net.Util.PatternString" >
      <conversionPattern value="%env{Temp}\\%property{LogDir}\\MyApp.log" />
    </file>
    <DatePattern value="MM-dd-yyyy" />
    <appendToFile value="true" />
    <immediateFlush value="true" />
    <rollingStyle value="Composite" />
    <maxSizeRollBackups value="10" />
    <maximumFileSize value="100MB" />
    <staticLogFileName value="true" />
    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern value="%date %-5level %logger - %message%newline" />
    </layout>
  </appender>

  <appender name="ErrorBufferingAppender" type="log4net.Appender.BufferingForwardingAppender">
    <bufferSize value="2" />
    <lossy value="true" />
    <evaluator type="log4net.Core.LevelEvaluator">
      <threshold value="ERROR" />
    </evaluator>
    <appender-ref ref="RockAndRoll" />
    <Fix value="0" />
  </appender>

  <logger name="NHibernate.SQL">
    <additivity>false</additivity>
    <appender-ref ref="ErrorBufferingAppender" />
    <level value="debug" />
  </logger>

  <logger name="error-buffer">
    <additivity>false</additivity>
    <appender-ref ref="ErrorBufferingAppender" />
    <level value="debug" />
  </logger>

  <root>
    <level value="info" />
    <appender-ref ref="RockAndRoll" />
  </root>

</log4net>

NHibernate.SQL记录器将所有查询记录到ErrorBufferingAppender，该查询不断将其抛出，仅将最后一个查询保存在其缓冲区中.当我捕获到异常时，我将一条错误级别的行记录到记录器error-buffer中，该行将其传递到ErrorBufferingAppender，由于它处于错误级别，因此将其与最后一个查询一起推送到RockAndRoll，我的RollingFileAppender.

The NHibernate.SQL logger logs all queries to the ErrorBufferingAppender, which keeps throwing them out and saves only the last one in its buffer. When I catch an exception I log one line at ERROR level to logger error-buffer, which passes it to ErrorBufferingAppender which -- because it's at ERROR level -- pushes it, along with the last query, out to RockAndRoll, my RollingFileAppender.

我实现了名为PrettySqlRollingFileAppender的RollingFileAppender子类(如果有人感兴趣，我很乐意提供)，该子类从查询末尾获取参数并将其替换为查询本身，从而使其更具可读性.

I implemented a subclass of RollingFileAppender called PrettySqlRollingFileAppender (which I'm happy to provide if anyone's interested) that takes the parameters from the end of the query and substitutes them inside the query itself, making it much more readable.

这篇关于如何在异常中获取带有参数值的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！