CODEWITHSUNDEEP
c#sql
David.Chu.ca
David.Chu.ca

Reputation: 38644

How to debug parameterized SQL query

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?

Upvotes: 5

Views: 7944

Answers (4)

CraigP
CraigP

Reputation: 125

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

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);
}

Upvotes: -1

Christian Gollhardt
Christian Gollhardt

Reputation: 17004

SQL Server

For SQL Server you could user the SQL Server Profiler:

SQL Profiler

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

Trace

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.

Upvotes: 3

ɐsɹǝʌ ǝɔıʌ
ɐsɹǝʌ ǝɔıʌ

Reputation: 4512

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

string debugSQL = cmd.CommandText;

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

Upvotes: 7

bortzmeyer
bortzmeyer

Reputation: 35459

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).

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).

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.

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

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

I typically use also:

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

Upvotes: 1

Related Questions