View/Debug SQL Stmt Before Execution

  • Thread starter Thread starter Daryll Shatz
  • Start date Start date
D

Daryll Shatz

Is there any way to output the completely populated SQL statement that is
about to be executed in ADO.NET? My SQL statements can contain and error
and I woulds like to debug them. Example:
Dim sqlSelect As String = "INSERT INTO Table(col1, col2) VALUES (@col1Value,
@col2Value)

Dim cmdTable As New SqlCommand(sqlSelect, cs)

....

cmdTerritory.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@col1Value", ...))

cmdTerritory.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@col2Value", ...))

' Debug Breakpoint Here

' IS THERE A WAY VIEW THE COMPLETE SQL STATEMENT TO BE EXECUTED???

cmdTerritory.ExecuteNonQuery()
 
Is there any way to output the completely populated SQL statement that is
about to be executed in ADO.NET? My SQL statements can contain and error
and I woulds like to debug them. Example:
Dim sqlSelect As String = "INSERT INTO Table(col1, col2) VALUES (@col1Value,
@col2Value)

Dim cmdTable As New SqlCommand(sqlSelect, cs)

...

cmdTerritory.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@col1Value", ...))

cmdTerritory.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@col2Value", ...))

' Debug Breakpoint Here

' IS THERE A WAY VIEW THE COMPLETE SQL STATEMENT TO BE EXECUTED???

cmdTerritory.ExecuteNonQuery()
Click to expand...
Depending on the database it might be easier to see the statement
using the database's tools, like profiler for SQL Server.
 
If you are using a CommandBUilder, you can use
myCommandBuilder.SelectCommand().CommandText 'doing the same for other
commands like Update

In your example below, you could use Debug.WritelLine(sqlSelect) for
instance, but that's going to show the @col1Value which I'm guessing you
don't want, you want to see the actual values.

So, you could use String.Format("INSERT INTO Table(col1, col2) VALUES
({0},{1}", cmd.Parameters("@col1Value").Value,
cmd.Parameters("@col2Value").Value) right before execute nonquery and insert
this into a Debug.Writeline or a Messagebox or whatever your choice of
viewing info is.

However, if you are doing individual Inserts, the above will work for you.

Then again, all of this assumes you want to see it client side. Like the
other poster mentioned, you could use Profiler depending on the RDBMS.

HTH,

Bill
 
The problem(s) with the CommandBuilder approach is that the text of the SQL
in the Commands is "pre-stuffed". That is, it contains the raw SQL before
the parameters are added in. It does not reflect what will be sent to the
server--only the Profiler (if available) will show what ADO.NET sends to the
server.

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top