title: How to capture SQL with parameters substituted in? (.NET, SqlCommand)
body: <p>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. </p>
  
  <p>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?</p>
  
  <p>Simple Example: I want to capture the output: </p>
  
  <p><strong><em>SELECT subcatId FROM EnrollmentSubCategory WHERE catid = 1</em></strong></p>
  
  <p>.. from this code:</p>
  
  <pre><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)
  </code></pre>
  
  <p>I had assumed Prepare() would do the substitutions, but apparently not. </p>
  
  <p>Thoughts?  Advice?  Thanks in advance. </p>