0

Having SQL with parameter (passing through SelectCommand.Parameters.AddWithValue), can I get easily final SQL to run it immediately? I mean, not to have unresolved parameters in the command, but rather fully resolved SQL. I can do that manually by Replace function, but I have to deal with types, etc. So just wondering if there is a way to grab the SQL statement processed by SQL engine directly (something what I can see in SQL Profiler). E.g. to have

SELECT Name FROM TABLE WHERE Id = 15

instead of

SELECT Name FROM TABLE WHERE Id = @Id

I need to log whole SQL that is runnable copy&paste&run. I can log command and parameters, but in this approach I have to manually construct the SQL statement.

DataTable dt = new DataTable();
query = "SELECT Name FROM TABLE WHERE Id = @Id";

using (SqlDataAdapter da = new SqlDataAdapter(query, String.Format(@"Data Source={0};Initial Catalog={1};Integrated Security=SSPI", relServer, relDatabase)))
{
    if (QueryParams != null && QueryParams.Count > 0)
    {
        foreach (KeyValuePair<string, object> entry in QueryParams)
        {
            da.SelectCommand.Parameters.AddWithValue(entry.Key, entry.Value ?? DBNull.Value);
        }
    }

    da.Fill(dt);
}
Charlieface
  • 52,284
  • 6
  • 19
  • 43
Pavel Sedina
  • 1
  • 1
  • You should use parameters. Problem is the query is a string. If ID is a integer you will get an error because a string will not equal an integer. If you do not use a parameter than you are allowing the driver to guess the variable type and the driver can guess wrong. For example if the ID in the database is a string and the driver see 123 the driver may convert the 123 to an integer and then the code will not work. – jdweng Jul 15 '22 at 09:56
  • 2
    That *is* the final SQL query. Parameterized queries aren't some kind of text replacement. The parameters are sent to the server outside the query itself as part of the RPC call. The database compiles the parameterized query and creates an execution plan that gets executed over and over with different parameter values. If you want to capture the queries received by SQL Server use SQL Server's XEvent Profiler. You can also configure ADO.NET [Event Tracing](https://docs.microsoft.com/en-us/sql/connect/ado-net/enable-eventsource-tracing?view=sql-server-ver16) to capture detailed client events – Panagiotis Kanavos Jul 15 '22 at 10:14
  • 1
    I'll say it louder for the people at the back. Try not to use `AddWithValue`. Just use `Add()` – JamesS Jul 15 '22 at 10:18
  • #Panagiotis cool! thanks. It makes sense. I thought that SQL client (C#) was creating final (resolved/translated) SQL command. If this is job of SQL server, then I cannot find it, of course :-) – Pavel Sedina Jul 15 '22 at 10:19
  • 1
    BTW this is definitely not a common way of using ADO.NET. `AddWithValues` is a bad practice when calling SQL Server because it forces the driver to guess the value types and sizes. Should a `decimal` be mapped to a numeric(38,17)` or a `numeric(4,2)`? Is that `DateTime` a `date`, `datetime` or `datetime2` ? – Panagiotis Kanavos Jul 15 '22 at 10:20
  • @PavelSedina `then I cannot find it, of course :-)` I said **the exact opposite**. Not only can you find it, you already have it. It's the string stored in `query`. I also explained how you can capture the calls both in SQL Server and ADO.NET, the library you're using. I suspect you're trying to work based on assumptions and snippets copied from various articles and blog posts. Don't do that. You'll end up into a lot of trouble (the question's code is a problem already). You need to understand how a library works to use it. – Panagiotis Kanavos Jul 15 '22 at 10:21
  • @PanagiotisKanavos I meant, that I couldn't find "translated" SQL command: SELECT Name FROM TABLE WHERE Id = 15 as this is processed just on server, not on the client side. Am I right in this? – Pavel Sedina Jul 15 '22 at 10:26
  • Wrong. There's no translated query, even on the server. Even the execution plan doesn't contain the parameter value. Again, I already explained how to capture queries both on the client and server. – Panagiotis Kanavos Jul 15 '22 at 10:26
  • @PanagiotisKanavos Ok, I got the point, thanks a lot! Really appreciate your help. – Pavel Sedina Jul 15 '22 at 10:30
  • *Why* do you want to capture the query in the first place? If it's for logging/debugging purposes, that's already available through ADO.NET tracing. There are several duplicate questions already. Tools like Miniprofiler use that to display the queries executed by web pages for debugging purposes – Panagiotis Kanavos Jul 15 '22 at 10:30
  • You can use XEvents or the old SQL Profiler to capture the RPC call to `sp_executesql`, which is what actually passes the SQL command and parameters. It will show up as an `exec sp_executesql` command (even though that isn't quite how it works under the hood) – Charlieface Jul 15 '22 at 10:43
  • Does this answer your question? [Capture passed parameter values in SQL Server extended sessions?](https://stackoverflow.com/questions/23658000/capture-passed-parameter-values-in-sql-server-extended-sessions) Also https://stackoverflow.com/questions/1952830/how-do-i-get-parameter-values-for-sql-server-query-in-sql-server-profiler – Charlieface Jul 15 '22 at 10:45
  • Will check, thank for hints what to look for. – Pavel Sedina Jul 15 '22 at 10:48

0 Answers0