Can I get the query that was executed from the SqlDataSource?

Question

I have a sql query for my SelectCommand on my SqlDataSource. It looks like the following:

SELECT * FROM Books WHERE BookID = @BookID

A TextBox feeds the @BookID parameter using an Asp:ControlParameter.

When I view the SelectCommand when stepping through the code, I see this:

SELECT * FROM Books WHERE BookID = @BookID

What I want to actually see is that if the person types in 3 in the TextBox, I want to see

SELECT * FROM Books WHERE BookID = 3

I can't figure out how to access the above though?

Answer 1

One way to view the actual query is by using SQL Profiler.

Answer 2

I guess you won't be able to see the select statement like you wish, since the parameter is not replaced in the statement with the value 3, but sent just like you wrote it to sql server (with the parameter).

That's actually good since it will prevent one to inject some malicious sql code in your textbox, for example.

Anyway, can't you retrieve the value passed to the parameter using this:

cmd.Parameters(0).Value

where cmd is your SqlCommand?

Answer 3

The query is never executed as

SELECT * FROM Books WHERE BookID = 3

It's actually the parameterised query with the parameter passed.

You can do a "Find/Replace" on the query with the related parameters to see what it would look like.

Answer 4

(This answer presumes with the SqlClient implementation.)

No, you cannot see the executed sql code. The SqlCommand class calls sp_execute (see both SqlCommand.BuildExecute methods for the exact implementation) which separates the query from the parameters. You'll need to use Sql Profiler to see the exact query executed.

You could use the provided DbCommand (from the Selecting event) to parse your CommandText and replace the parameters with their actual values. This would need some logic for escaping, and it will not be the exact query that Sql Server executes.

Answer 5

Public Function GenSQLCmd(ByVal InSqlCmd As String, ByVal p As Data.Common.DbParameterCollection) As String
    For Each x As Data.Common.DbParameter In p
        InSqlCmd = Replace(InSqlCmd, x.ParameterName, x.Value.ToString)
    Next
    Return InSqlCmd
End Function