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?

Nasal

If SqlDataSource is worth anything at all it will be passing your text box value as the @BookId parameter to help avoid SQL injection attacks. As irperez says below, SQL Profiler is the tool for this job. – Cutlerr

Answer 1

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

Rocaille

SQL Profiler will still show the query and parameter alongside each other, you won't see the full verbose query – Bathesda

I beg to differ. I have used Profiler to see to see what queries were getting passed to see what was going on and it did spit out verbose query. – Rocaille

I know with Linq you can see the actual query in the IDE, but not sure with a sqldatasource. There is no code to step into to see it, unless you connect with MS source server. – Rocaille

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

Bathesda

Answer 3

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

Straighten

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

Perl

Answer 5

Yes, you can view that information but you need to do a bit coding for that.

1. Create an extension method called ToSqlStatement

    public static class SqlExtensions
    {
        public static string ToSqlStatement(this IDbCommand cmd)
        {
            var keyValue = new List<string>();
            foreach (SqlParameter param in cmd.Parameters)
            {
                var value = param.Value == null ? "NULL" : "'" + param.Value + "'";
                keyValue.Add($"{param.ParameterName}={value}");
            }
            return $"{(cmd.CommandType == CommandType.StoredProcedure ? "exec " : string.Empty)}{cmd.CommandText} {string.Join(", ", keyValue)}";
        }
    }

2. Add OnSelecting event handler to SqlDataSource control on your page
3. In you code behind

    protected void sqlDataSource_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        MyLogger.WriteLine(e.Command.ToSqlStatement());
    }

Peggypegma

Answer 6

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?

Meteorite

Answer 7

This is the C# version of Adam's answer

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

Puckery