Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

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

Other Wiki Sections

Software

PoweredBy

Command Text Generation from a SqlCommand Object

RSS
Modified on Fri, Oct 23, 2009, 12:24 PM by Administrator Categorized as SQL Server, ┬ĚNet Framework
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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.