Complete Query from SQLCommand with Parameters

Question

Using SQLCOmmand to insert row in database. SQL Command is using parameters.

To get the query while debugging at Immediate window I use

?_cmd.CommandText  
Result:
    "UPDATE TABLE SET Smth=@Smth, Smth2=@Smth2 "

How to get complete query where I instead of @Smth and @Smth2 will have values read from parameters?

score 1 · Answer 1 · answered Sep 17 '16 at 09:52

If you have access to SSMS, then you could use SQL Server Profiler to trace the actual SQL commands being issued by your VB.NET application when you run it.

Note that running SQL Server Profiler has a performance overhead on the database server, so would strongly advise against running it on a Production server or a server where you would affect others.

Some links on how to get started with SQL Server Profiler...

https://www.mssqltips.com/sqlservertutorial/272/profiler-and-server-side-traces/

http://blog.sqlauthority.com/2009/08/03/sql-server-introduction-sql-server-2008-profiler-complete/

Profiler/SQL Trace is deprecated in favor of Extended Events in SQL Server 2012 onwards. http://stackoverflow.com/questions/16108586/sql-server-profiler-deprecation-replacement. — Dan Guzman, Sep 17 '16 at 12:15

IamK · Answer 2 · 2016-09-17T07:34:52.860

0

You can write a helper method:

public static class Helper
{
    public static string ToString(this DbParameterCollection params, string query)
    {
        return params.Cast<DbParameter>().Aggregate(query, (c, p) => c.Replace(p.ParameterName, p.Value.ToString()));
    }
}

edited Sep 17 '16 at 07:34

answered Sep 17 '16 at 07:24

IamK

2,753
5
30
39

Yet i dont have a complete query. Like that i will be able to see each parameter value one by one. I want complete query to copy and paste in SQL MS – Anel Hodžić Sep 17 '16 at 07:25
Then put together the query in temporary string variable, but this is a workaround. – IamK Sep 17 '16 at 07:28
@AnelHodžić you want to copy & paste it on sql ms in a query? – Ilyes Sep 17 '16 at 07:43
This may be usefull , Trying to get the vb.net by online comparison – Anel Hodžić Sep 17 '16 at 08:14

score -1 · Answer 3 · answered Sep 17 '16 at 07:33

-1

try :

INSERT INTO TABLE SET Smth=@Smth, Smth2=@Smth2 WHERE <somecondition>
Select @Smth,@Smth2

answered Sep 17 '16 at 07:33

Ilyes

14,640
4
29
55

is this what you need ? – Ilyes Sep 17 '16 at 07:33

Complete Query from SQLCommand with Parameters

3 Answers3