如何调试参数化SQL查询

I use C# to make connection to a db and then a Ad hoc SQL to get data. This simple SQL query is very convenient to debug since I can log the SQL query string. If I use parametrized SQL query command, is there any way to log sql query string for debug purpose?

我使用c#连接到一个db，然后使用一个特殊的SQL来获取数据。这个简单的SQL查询对于调试非常方便，因为我可以记录SQL查询字符串。如果我使用参数化的SQL查询命令，是否有方法记录SQL查询字符串用于调试?

4 个解决方案

#1

I think this is about it. Place this code where you have configured the query command and you'll have the into debugSQL the SQL statement which will be executed

我认为这是关于它的。将此代码放置在您配置了查询命令的地方，您将获得将要执行的SQL语句into debugSQL

string debugSQL = cmd.CommandText;

foreach (SqlParameter param in cmd.Parameters)
{
    debugSQL = debugSQL.Replace(param.ParameterName, param.Value.ToString());
}

#2

SQL Server

SQL Server

For SQL Server you could user the SQL Server Profiler:

对于SQL Server，您可以使用SQL Server Profiler:

Start a new Trace Session, and you will see everyting which gets executed. You can also filter it.

启动一个新的跟踪会话，您将看到执行的所有内容。你也可以过滤它。

More Information here.

更多的信息在这里。

Everything other

所有其他的

General, you looking for a query profiler. You can search for <RDBMS NAME> query profiler and should find something usefull.

一般来说，您需要查找查询分析器。您可以搜索查询分析器，并应该找到一些有用的东西。

#3

Using the "debug" flag of your library is often the simplest solution. But you're dependent on the library which can lie to you or at least conceal a few things it will do (for instance, psycopg silently changes the default isolation level).

使用库的“调试”标志通常是最简单的解决方案。但是您需要依赖库，库可以对您撒谎，或者至少隐藏它将要做的一些事情(例如，psycopg悄悄地更改默认的隔离级别)。

On the DBMS side, you can always activate logging. The good thing with it is that you will get the exact SQL request, whatever your client library does. In that respect, it is better than the "debug" flag of the library. But, on some DBMS, logging statements can be very slow (this is the case with PostgreSQL).

在DBMS方面，您可以始终激活日志记录。这样做的好处是，无论您的客户端库做什么，您都会得到确切的SQL请求。在这方面，它比库的“调试”标志要好。但是，在某些DBMS中，日志记录可能非常慢(这是PostgreSQL的情况)。

Another solution is to use a sniffer on the network, if you have sufficient privilege. Wireshark can decode the protocols of many DBMS and present the actual SQL request.

另一个解决方案是在网络上使用嗅探器，如果您有足够的特权的话。Wireshark可以解码许多DBMS的协议并给出实际的SQL请求。

On PostgreSQL, activating logging is done in postgresql.conf by:

在PostgreSQL上，激活日志记录是在PostgreSQL中完成的。配置:

log_statement = 'all'                   # none, ddl, mod, all

I typically use also:

我也通常使用:

log_connections = on
log_disconnections = on
log_duration = on
log_hostname = on

#4

The answer by equisde was helpful, but to handle bool and char types, I needed this variation:

equisde的答案是有帮助的，但是要处理bool和char类型，我需要这个变体:

string debugSQL = dbCommand.CommandText;
foreach (SqlParameter param in dbCommand.Parameters)
{
    string val = param.Value.ToString();
    switch (param.DbType)
    {
        case System.Data.DbType.AnsiString:
                val = "'" + val + "'";
            break;
        case System.Data.DbType.Boolean:
            val = (val == "True" ? "1" : "0");
            break;
    }
    debugSQL = debugSQL.Replace("@" + param.ParameterName, val);
}

#1