使用参数化查询时，可以看到在我的数据库上执行的查询？(Possible to see the query that gets executed on my database when using Pa

使用参数化查询时，可以看到在我的数据库上执行的查询？(Possible to see the query that gets executed on my database when using Parameterized Queries?)

源于这个问题： SQLParameter如何阻止SQL注入？

有什么方法可以看到什么查询传递到我的数据库并在执行参数化查询时执行？

我没有可用的SQL事件探查器，所以这不是一个选项。 我希望有可能从视觉工作室那里做到这一点。 可能？

Stemmed from this question: How does SQLParameter prevent SQL Injection?

Is there any way that I can see what query is getting passed to my database and executed when I execute a parametrized query?

I don't have SQL Profiler available to me so that is not an option. I was hoping there might be a way to do it from visual studio. Possible?

最满意答案

嗯，这真的没有神奇或没有黑色艺术 - 在ADO.NET中这样的查询：

string sqlStmt = "SELECT * FROM dbo.Customers WHERE country = @country"; using(SqlConnection _conn = new SqlConnection("server=.;database=Northwind;integrated security=SSPI;")) using(SqlCommand _cmd = new SqlCommand(sqlStmt, _conn)) { _cmd.Parameters.Add("@country", SqlDbType.VarChar, 100).Value = "Switzerland"; DataTable results = new DataTable(); using(SqlDataAdapter dap = new SqlDataAdapter(_cmd)) { dap.Fill(results); } }

将在SQL Server上翻译成这个：

exec sp_executesql N'SELECT * FROM dbo.Customers WHERE country = @country',N'@country varchar(100)',@country='Switzerland'

基本上，ADO.NET / SQL Server不像许多人所认为的那样替换SQL语句字符串中的参数 - 它实际上作为参数化查询传递给SQL Server，以及参数列表及其值。

这个SQL语句来自SQL Profiler - 我不知道你怎么看到那个查询......

你为什么不能使用SQL Profiler？ 我的意思是 - 在SQL Server的每个副本中，对于那些使用免费SQL Server Express版本的人来说，甚至还有一个免费的SQL Express Profiler .....

Well, it's really no magic or no black art - a query like this in ADO.NET:

string sqlStmt = "SELECT * FROM dbo.Customers WHERE country = @country"; using(SqlConnection _conn = new SqlConnection("server=.;database=Northwind;integrated security=SSPI;")) using(SqlCommand _cmd = new SqlCommand(sqlStmt, _conn)) { _cmd.Parameters.Add("@country", SqlDbType.VarChar, 100).Value = "Switzerland"; DataTable results = new DataTable(); using(SqlDataAdapter dap = new SqlDataAdapter(_cmd)) { dap.Fill(results); } }

will be translated into this on SQL Server:

exec sp_executesql N'SELECT * FROM dbo.Customers WHERE country = @country',N'@country varchar(100)',@country='Switzerland'

Basically, ADO.NET / SQL Server do not replace the parameters in the SQL statement string like many folks believe - it is actually passed to SQL Server as a parametrized query, along with a list of parameters and their values.

This SQL statement was taken from SQL Profiler - I don't know how else you could see that query...

Why can't you use SQL Profiler?? I mean - it's in every copy of SQL Server, there's even a free SQL Express Profiler for those using the free SQL Server Express editions.....