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

Question

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.CommandText)
    cmd.Prepare()
    Console.WriteLine("After: " + cmd.CommandText)

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

Thoughts? Advice? Thanks in advance.

Answer 1

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.

Answer 2

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