Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

Command Text Generation from a SqlCommand Object

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