How to capture SQL with parameters substituted in? (.NET, SqlCommand)
- by Bryan
Hello,
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.
