CODEWITHSUNDEEP
.netsql-server
Abe Miessler
Abe Miessler

Reputation: 85036

Possible to see the query that gets executed on my database when using Parameterized Queries?

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?

Upvotes: 1

Views: 50

Answers (2)

Cortright
Cortright

Reputation: 1174

I don't believe you can access that sort of thing within Visual Studio since the translation would be happening internally inside ADO.NET libraries, but the answer marc_s posted above is correct. You could verify this using SQL Server Management Studio by looking at the incoming transactions (if you had it available).

Upvotes: 0

marc_s
marc_s

Reputation: 754230

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

Upvotes: 4

Related Questions