Command Text Generation from a SqlCommand Object

Given a SqlCommand object that has been initialized for execution, the following procedure generates the full command text of the query.

Private Shared Function GetCommandText(ByVal cmd As SqlCommand) As String

    Dim result As String = ""

    Try

        Select Case cmd.CommandType

            Case CommandType.StoredProcedure

                result = "exec "
                result &= cmd.CommandText
                result &= " "

                Dim imax As Integer = cmd.Parameters.Count - 1

                For i As Integer = 0 To imax

                    If i > 0 Then
                        result &= ", "
                    End If

                    Dim p As SqlParameter = cmd.Parameters(i)

                    If p.Value Is DBNull.Value Then

                        result &= "NULL"

                    Else

                        Select Case p.DbType

                            Case DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.Guid, _
                            DbType.String, DbType.StringFixedLength, DbType.Xml

                                result &= "'"
                                result &= p.Value.ToString().Replace("'", "''")
                                result &= "'"

                            Case DbType.Date, DbType.DateTime, DbType.DateTime2, DbType.DateTimeOffset, _
                            DbType.Time

                                result &= "'"
                                result &= Convert.ToDateTime(p.Value).ToString("MM/dd/yyyy hh:mm:ss tt")
                                result &= "'"

                            Case Else

                                result &= p.Value.ToString()

                        End Select

                    End If

                Next

            Case CommandType.Text

                result = cmd.CommandText

        End Select

        Return result

    Catch ex As Exception
        Throw ex
    End Try


End Function