How to capture SQL with parameters substituted in? (.NET, SqlCommand)

If there an easy way to get a completed SQL statement back after parameter substitution? I.e., I want to keep a logfile of all the SQL this program runs.

Or if I want to do this, will I just want to get rid of Parameters, and do the whole query the old school way, in one big string?

Simple Example: I want to capture the output:

SELECT subcatId FROM EnrollmentSubCategory WHERE catid = 1

.. from this code:

    Dim subCatSQL As String = "SELECT subcatId FROM EnrollmentSubCategory WHERE catid = @catId"
    Dim connectionString As String = "X"
    Dim conn As New SqlConnection(connectionString)
    If conn.State = ConnectionState.Closed Then
        conn.Open()
    End If
    Dim cmd As New SqlCommand(subCatSQL, conn)
    With cmd
        .Parameters.Add(New SqlParameter("@catId", SqlDbType.Int, 1)) 
    End With

    Console.WriteLine("Before: " + cmd.C开发者_如何转开发ommandText)
    cmd.Prepare()
    Console.WriteLine("After: " + cmd.CommandText)

I had assumed Prepare() would do the substitutions, but apparently not.

Thoughts? Advice? Thanks in advance.

No, .Prepare doesn't do that, and in fact nothing in the Command does. The parameter values are never substituted into the actual command string. It is sent to the DB separately, which is good for several reasons:

• This allows sqlserver (or other dbs) to cache the query plan for your query and reuse it next time. When a different commandtext string is sent to the db each time, the database has to develop a plan each time.
• By sending the parameters to the db compartmentalized, there is a nature defense against sql injection attacks.

Unless you're using a really old database (sql server 7?), .Prepare() is not necessary and does not actually do anything for you. It used to be helpful in that it would compile the query on the server, but that is done for you automatically now. I haven't use .Prepare() in a long time.

Hmmmmm. Looking here it seems .Prepare() does still do something for you: if any values are larger than the parameter defined length, the .Prepare() truncates the value, so that when you execute you don't get the error and the query succeeds. cool.

I usually add a utility function to my projects that, given a DbCommand object (the parent class of OracleCommand, SqlCommand, etc.) will log the query and parameter values. It might look like this:

Public Shared Sub LogQuery(ByRef cmd As DbCommand)
    If cmd.CommandText Is Nothing Or cmd.CommandText.Length = 0 Then
        Return
    End If

    Dim logFile As CLog = New CLog("sql.log")
    Dim msg As New StringBuilder

    msg.AppendLine("*** Query ***")
    msg.AppendLine(cmd.CommandText)
    msg.AppendLine("*** End Query ***")
    msg.AppendLine("*** Parameters ***")

    For Each p As DbParameter In cmd.Parameters
        msg.AppendLine(String.Format("{0}: {1}", p.ParameterName, p.Value.ToString()))
    Next

    msg.AppendLine("*** End Parameters ***")

    logFile.WriteLog(msg.ToString() & System.Environment.NewLine)
End Sub

While I was writing this, it just occured to me that instead of logging the parameter values separately, you could do a little String.Replace()ing to substitute the parameter values into the query and then log it:

For Each p As DbParameter In cmd.Parameters
    msg = msg.Replace(p.ParameterName, p.Value.ToString())
Next

Parameters are not substituted. The sql as is, and the parameters are themselves passed as parameters to sp_executesql system stored proc

What is sent to SQL Server is not variables substituted with values but a prepared statement, this way it can be reused

Open up profiler in SQL Server and you will see what is being run is not SELECT subcatId FROM EnrollmentSubCategory WHERE catid = 1

This was recently asked here. As the other answers point out, the parameters are sent out of band.

The Profiler is probably what you want to use for capture if you want to do it at the server, and you can create a trace and use it as a startup job.

You could build a wrapper around the System.Data.SqlClient Provider (*Ex. the provider registered in the config file as... * providerName="System.Data.SqlClient"). Essentially an intercept proxy you would have access to all the information passing through the Provider and could siphon-off what you need, aggregate and/or enrich it and send it to the logs. This is a bit more advanced but opens the door to capture a whole range of information and could be inserted/replaced/removed as a separate layer of concern.

Try the CommandAsSql NuGet package