ActualCommandText

        ''' <summary>
        ''' Shows the SQL of the CommandText property but replaces all of the parameters with their actual values.
        ''' </summary>
        ''' <param name="cmd"></param>
        ''' <returns></returns>
        ''' <remarks>
        ''' This will allow you to see the SQL with the real values behind the parameters so that you take that text
        ''' and paste it straight into a SQL editor to run without having to swap out parameters for values.
        ''' </remarks>
        <Extension()> _
        Public Function ActualCommandText(ByVal cmd As IDbCommand) As String

            Dim sb As New StringBuilder(cmd.CommandText)

            For Each p As IDataParameter In cmd.Parameters
                Select Case p.DbType                    
                    Case DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.Date, DbType.DateTime, DbType.DateTime2, _
                         DbType.Guid, DbType.String, DbType.StringFixedLength, DbType.Time, DbType.Xml
                        sb = sb.Replace(p.ParameterName, String.Format("'{0}'", p.Value.ToString.Replace("'", "''")))
                    Case Else
                        sb = sb.Replace(p.ParameterName, p.Value)
                End Select
            Next

            Return sb.ToString
        End Function
Example:
' This assumes your cmd is of IDbCommand and is setup already
Dim buf As String = cmd.ActualCommandText()

Description

Shows the SQL of the CommandText property but replaces all of the DB parameters with their actual values.

Details

Double click on the code to select all.

 

;