我可以获取从SqlDataSource执行的查询吗？

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

我的SqlDataSource上的SelectCommand有一个sql查询。它看起来如下:

SELECT * FROM Books WHERE BookID = @BookID

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

TextBox使用Asp:ControlParameter提供@BookID参数。

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

当我单步执行代码时查看SelectCommand时,我看到了:

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

我想要实际看到的是,如果这个人在TextBox中键入3,我想看

SELECT * FROM Books WHERE BookID = 3

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

我无法弄清楚如何访问上面的内容?

6 个解决方案

#1

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

查看实际查询的一种方法是使用SQL事件探查器。

#2

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.

您可以使用相关参数对查询执行“查找/替换”以查看其外观。

#3

(This answer presumes with the SqlClient implementation.)

(这个答案假定与SqlClient实现。)

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.

不,你看不到执行的sql代码。 SqlCommand类调用sp_execute(请参阅SqlCommand.BuildExecute方法以获取确切的实现),它将查询与参数分开。您需要使用Sql Profiler来查看执行的确切查询。

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.

您可以使用提供的DbCommand(来自Selecting事件)来解析CommandText并将参数替换为它们的实际值。这需要一些转义逻辑,它不是Sql Server执行的确切查询。

#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

#5

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).

我猜你将无法看到你想要的select语句,因为参数不会在值为3的语句中被替换,而是像你写入sql server一样发送(带参数)。

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

这实际上很好,因为它会阻止你在文本框中注入一些恶意的sql代码,例如。

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

无论如何,你不能使用这个检索传递给参数的值:

cmd.Parameters(0).Value

where cmd is your SqlCommand?

cmd是你的SqlCommand吗?

#6

This is the C# version of Adam's answer

这是Adam的答案的C#版本

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;
}

Usage:

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

#1