VB.NET: determining the datatype of a field in query resultset?

Question

I have numeric data that I am getting from a database. They are all numeric, but they are a mix of int, money and real.

Using VB.NET, how can you programmatically determine the datatype of a field in a resultset?

Answer 1

With access to only the underlying value and not the structure of the database, it's not possible to definitively tell what type the value is. The reason why is that there is an overlap in the domain of Money, Real and Int values. The number 4 for example could be both a Real an Int and likely a Money.

Can you give us some more context on the issue? Are you trying to convert a raw database value into a Int style value in VB.Net? Can you show us some code?

Answer 2

Assuming this table:

CREATE TABLE TestTable
(
Col1 int,
Col2 dec(9,2),
Col3 money
)

With these values:

INSERT INTO TestTable VALUES (1, 2.5, 3.45)

You can use the following code to get the types as .Net types:

    Dim DSN = "SERVER=XYZ;UID=XYZ;PWD=XYZ;DATABASE=XYZ"
    Using Con As New SqlConnection(DSN)
        Con.Open()
        Using Com As New SqlCommand("SELECT * FROM TestTable", Con)
            Com.CommandType = CommandType.Text
            Using RDR = Com.ExecuteReader()
                If RDR.Read Then
                    Trace.WriteLine(RDR.GetProviderSpecificFieldType(0)) 'Returns System.Data.SqlTypes.SqlInt32
                    Trace.WriteLine(RDR.GetProviderSpecificFieldType(1)) 'Returns System.Data.SqlTypes.SqlDecimal
                    Trace.WriteLine(RDR.GetProviderSpecificFieldType(2)) 'Returns System.Data.SqlTypes.SqlMoney
                End If
            End Using
        End Using
        Con.Close()
    End Using

You can also use this to just get the SQL text version of the type:

    Dim DSN = "SERVER=XYZ;UID=XYZ;PWD=XYZ;DATABASE=XYZ"
    Using Con As New SqlConnection(DSN)
        Con.Open()
        Using Com As New SqlCommand("SELECT * FROM TestTable", Con)
            Com.CommandType = CommandType.Text
            Using RDR = Com.ExecuteReader()
                If RDR.Read Then
                    Using SC = RDR.GetSchemaTable()
                        Trace.WriteLine(SC.Rows(0).Item("DataTypeName")) 'Returns int
                        Trace.WriteLine(SC.Rows(1).Item("DataTypeName")) 'Returns decimal
                        Trace.WriteLine(SC.Rows(2).Item("DataTypeName")) 'Returns money
                    End Using
                End If
            End Using
        End Using
        Con.Close()
    End Using

EDIT

Here's how to do type comparison along with a helper function that formats things. Outputs once again assume the above SQL.

    Dim DSN = "SERVER=XYZ;UID=XYZ;PWD=XYZ;DATABASE=XYZ"
    Using Con As New SqlConnection(DSN)
        Con.Open()
        Using Com As New SqlCommand("SELECT * FROM TestTable", Con)
            Com.CommandType = CommandType.Text
            Using RDR = Com.ExecuteReader()
                If RDR.Read Then
                    Trace.WriteLine(FormatNumber(RDR.Item(0), RDR.GetProviderSpecificFieldType(0))) '1
                    Trace.WriteLine(FormatNumber(RDR.Item(1), RDR.GetProviderSpecificFieldType(1))) '2.50
                    Trace.WriteLine(FormatNumber(RDR.Item(2), RDR.GetProviderSpecificFieldType(2))) '$3.45
                End If
            End Using
        End Using
        Con.Close()
    End Using

Private Shared Function FormatNumber(ByVal number As Object, ByVal type As Type) As String
    If number Is Nothing Then Throw New ArgumentNullException("number")
    If type Is Nothing Then Throw New ArgumentNullException("type")
    If type.Equals(GetType(System.Data.SqlTypes.SqlInt32)) Then
        Return Integer.Parse(number)
    ElseIf type.Equals(GetType(System.Data.SqlTypes.SqlDecimal)) Then
        Return Decimal.Parse(number.ToString()).ToString("N")
    ElseIf type.Equals(GetType(System.Data.SqlTypes.SqlMoney)) Then
        Return Decimal.Parse(number.ToString()).ToString("C")
    End If
    Throw New ArgumentOutOfRangeException(String.Format("Unknown type specified : " & type.ToString()))
End Function