Getting actual SQL Query from paramaterized query in C# .NET debugger

Question

I am trying to get the query so I can manipulate it and run it directly in SQL Server. It is a parametrized query like so in my .NET 4+ debugger in VS 2017:

SELECT DISTINCT name, 
CASE WHEN title = @searchTitle OR fullname = @searchFullName THEN 1 
ELSE ...
AS sorting
ORDER BY sorting ASC;

Ignore any syntax issues as the query is a sample and runs fine. I have the parameters get set correctly too, and as a note, there are tons of actual parameters so me copying and pasting is out of the question. The query is executed like so:

                return this.dataProvider.ExecuteMyLikeQueryTerms(
                     dr =>
                     {
                         var dt = new DataTable();
                         dt.Load(dr);

                         return dt;
                     },
                query,
                parameters,
                connection);

How can I get the SQL that is exectured with all the parameters added in so I can see it in the debugger and paste it into SQL Server? I have a very big query and am trying to trace it.

The parameters aren't added in, as in textually replaced - the parametrized query is sent to the SQL Server engine "as is", along with the list of parameters and their values.... otherwise it would be wide open to SQL injection attacks again (which is what you're exactly trying to avoid, by using parameters, in the first place) — marc_s
– marc_s, Commented Jun 24, 2021 at 19:10
As mentioned, the parameter values are bound to the parameters in the compiled tree. But you can see a textual representation by using the SQL Profiler (RPC:Completed event) — Charlieface
– Charlieface, Commented Jun 24, 2021 at 19:55

Marc Gravell · Accepted Answer · 2021-06-24 19:37:38Z

1

You already have the exact query being sent, which includes parameters (not parameter values). The closest you can get here is to fake some local variables:

declare @searchTitle nvarchar(max) = N'whatever';
-- etc

before your query (which is what MiniProfiler does, to make it possible to copy/paste runnable queries)

answered Jun 24, 2021 at 19:37

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting actual SQL Query from paramaterized query in C# .NET debugger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related