Suggestion to add parameters

May 29, 2011 at 2:35 AM

This is a great visualizer! My shop is pretty new to the EF and this will definitely help smooth the implementation. I have something similar that I just use to pipe out to text. The one thing that I suggest adding is pulling in the parameters and their values if possible. Then you've got a completely executable statement. I took a look at your source but I didn't see where something like this would go or if it's even available. Please feel free to take a look at my code and see if you might like to add something similar:

    <Extension()> Public Function ToSQL(ByVal iq As IQueryable) As String
        Contract.Requires(iq IsNot Nothing)
        Dim oq = CType(iq, Objects.ObjectQuery)
        Dim sql = oq.ToTraceString
        Dim sb As New StringBuilder
        For Each p In oq.Parameters
            Dim v As String = p.Value
            Dim fString As String = "Declare @{0} {1} = '{2}'"
            Select Case DotNetToSQLdataType(p.ParameterType)
                Case SqlDbType.SmallInt
                    v = IIf(nf(p.Value), "0", "1")
                Case SqlDbType.VarChar
                    fString = "Declare @{0} {1}(max) = '{2}'"
                Case SqlDbType.Decimal
                    fString = "Declare @{0} {1}(19,2) = '{2}'"
            End Select
            sb.AppendFormattedLine(fString, p.Name, DotNetToSQLdataType(p.ParameterType), v)
        Return sb.ToString
    End Function

    Public Function DotNetToSQLdataType(ByVal DataType As Type) As System.Data.SqlDbType
        Select Case DataType
            Case GetType(String)
                Return SqlDbType.VarChar
            Case GetType(Boolean)
                Return SqlDbType.SmallInt
            Case GetType(DateTime)
                Return SqlDbType.DateTime
            Case GetType(Decimal)
                Return SqlDbType.Decimal
        End Select
    End Function

May 29, 2011 at 5:10 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.