Search code examples
postgresqlnpgsql

Is there a way to get the full command text of an NpgsqlCommand?

With code like this:

string strCommand = "SELECT * FROM \"MyDataBase\".\"vwUsers\" "
strCommand += "WHERE name LIKE '%' || :name || '%' ";
strCommand += "ORDER BY name ASC LIMIT :page_limit OFFSET :row_offset";

NpgsqlCommand cCommand = new NpgsqlCommand(strCommand);

cCommand.Parameters.Add(new NpgsqlParameter("name", NpgsqlDbType.Text));
cCommand.Parameters[0].Value = strName;
cCommand.Parameters.Add(new NpgsqlParameter("page_limit", NpgsqlDbType.Integer));
cCommand.Parameters[1].Value = nPageAmount;
cCommand.Parameters.Add(new NpgsqlParameter("row_offset", NpgsqlDbType.Integer));
cCommand.Parameters[2].Value = nRowOffset;

Is there a way to get the full text of the command string with all the parameters plugged into it?

Solution

  • Nope. The reason for this is that since Npgsql 3.0 parameters aren't simply substituted in the string; Npgsql actually sends your SQL with parameter placeholders, and transmits the parameters in a separate message and in binary encoding. In other words, there's no point anywhere at which the SQL query is available as text with the parameters.

    (Note: in Npgsql 2.2 client-side parameter binding was done for non-prepared messages, so this was in theory possible).