ExtensionMethod.NET Home of 818 C#, Visual Basic, F# and Javascript extension methods

ActualCommandText

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

Source

''' <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()

Author: Blake Pell

Submitted on: 8 mrt 2010

Language: VB

Type: System.Data.IDbCommand

Views: 2694