Can I get a query that was executed from SqlDataSource?

Question

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

SELECT * FROM Books WHERE BookID = @BookID 

TextBox passes the @BookID parameter using Asp: ControlParameter.

When I look at SelectCommand while passing the code, I see the following:

 SELECT * FROM Books WHERE BookID = @BookID 

What I really want to see is that if a person types 3 in the text box, 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 to use SQL Profiler.

Answer 2

The request is never executed as

 SELECT * FROM Books WHERE BookID = 3 

In fact, this is a parameterized query with the parameter passed.

You can perform a Find / Replace in the query with the appropriate parameters to see how it will look.

Answer 3

(This answer assumes an implementation of SqlClient.)

No, you do not see sql executable code. The SqlCommand class calls sp_execute (see SqlCommand.BuildExecute Methods for an exact implementation), which separates the request from the parameters. You will need to use Sql Profiler to view the exact query.

You can use the provided DbCommand (from the Selecting event) to parse your CommandText and replace the parameters with their actual values. This will require some logic to escape, and it will not be the exact query that Sql Server performs.

Answer 4

 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 

Answer 5

I think you won’t be able to see the select statement as you want, since the parameter is not replaced by 3 in the statement, but sent just like you wrote it to the sql server (with the parameter).

This is really good, as it will prevent the possibility of entering some malicious sql code into a text field, for example.

In any case, you cannot get the value passed to the parameter using this:

cmd.Parameters(0).Value

where cmd is your SqlCommand?

Answer 6

This is Adam's C # answer version

 public string GenSQLCmd(string InSqlCmd, System.Data.Common.DbParameterCollection p) { foreach (System.Data.Common.DbParameter x in p) { InSqlCmd = InSqlCmd.Replace(x.ParameterName, "'" + x.Value.ToString() + "'"); } return InSqlCmd; } 

Using:

 string DebugQuery = GenSQLCmd(cmd.CommandText, cmd.Parameters); //cmd is a SqlCommand instance