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

问题描述

难以置信，但我似乎无法找到一个直接的答案:当语句生成异常时，我如何获取 SQL 语句包括参数值当它产生异常时.我知道如何为每个生成的 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 日志 所有 查询，打印漂亮，参数值原位
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 级别将一行记录到记录器 error-buffer，该记录器将其传递给 ErrorBufferingAppender，后者 - 因为它处于 ERROR 级别 - 推送它，连同最后一个查询，到 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.

我实现了一个 RollingFileAppender 的子类，称为 PrettySqlRollingFileAppender(如果有人感兴趣，我很乐意提供)，它从查询的末尾获取参数并替换它们在查询本身内部，使其更具可读性.

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屋！