StrataFrame Forum

Dynamic conversion based on business object field data type

http://forum.strataframe.net/Topic24959.aspx

By Tim Dol - 10/21/2009

I am trying to write a generic import routine that imports data into a business object and need some help on data conversion issue.



Example:



Say I have a datatable containing the data I want to import. Assume the column names in the import file match the business object's column names.



I have my customer object and want to populate it.



Dim loBO as New CustomerBO



For Each Row As DataRow In ImportFileDataTable.Rows



loBO.NewRow()

loBO.CurrentRow.Item("CompanyName") = Row.Item("CompanyName")



etc...



Next





This works fine but now I want to ensure the data from the import file converts to the appropriate type based on the business object. Example: The BO field may be integer but the data coming from the import datatable may be string... I want to convert this to the proper type.



I tried this...



Dim loType as Type

Dim loBO as New CustomerBO



For Each Row As DataRow In ImportFileDataTable.Rows



loBO.NewRow()



loType = GetType(loBO.CurrentRow.Item("CompanyName").GetType

loBO.CurrentRow.Item("CompanyName") = Ctype(Row.Item("CompanyName"),loType)



etc...



Next





Problem is 'loType' is not a valid variable for this statement... Is there anyway to dynamically set this type according to the data type on the business object without have to add a bunch of conditional statements?



Thanks,

Tim
By Peter Jones - 10/21/2009

Hi Tim,



This is out of my league but couldn't you use:



loBO.CurrentRow.Item("CompanyName") = Ctype(Row.Item("CompanyName"), GetType(loBO.CurrentRow.Item("CompanyName").GetType)



But this code will, of course, fail if the data can't be conversted , e.g. "ABC" into an integer.





Cheers, Peter
By Tim Dol - 10/21/2009

I tried that Peter. Visual Studio displays a syntax error 'Type loBO.CurrentRow.Item is not defined'
By Ivan George Borges - 10/22/2009

Hi Tim!

I will throw some ideas of what I have done here to get "types". Maybe it can help you.

To get a System.Type of a SqlDBType:

    ''' <summary>
    ''' Find out the SqlDbType of a System.Type variable
    ''' </summary>
    ''' <param name="theType"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function GetDBType(ByVal theType As System.Type) As SqlDbType
        Dim p1 As SqlClient.SqlParameter
        Dim tc As System.ComponentModel.TypeConverter
        p1 = New SqlClient.SqlParameter()
        tc = System.ComponentModel.TypeDescriptor.GetConverter(p1.DbType)
        If tc.CanConvertFrom(theType) Then
            p1.DbType = CType(tc.ConvertFrom(theType.Name), DbType)
        Else
            'Try brute force
            Try
                p1.DbType = CType(tc.ConvertFrom(theType.Name), DbType)
            Catch ex As Exception
                'Do Nothing
            End Try
        End If
        Return p1.SqlDbType
    End Function

To find out a Column type, in order to set defaults:

        '-- set default value
        Dim lb As Byte
        lb = CType(0, Byte)
        Select Case columnType.ToString
            Case "System.String"
                col.DefaultValue = ""
            Case "System.Int32"
                col.DefaultValue = CType(0, Integer)
            Case "System.Boolean"
                col.DefaultValue = False
            Case "System.Decimal"
                col.DefaultValue = CType(0, Decimal)
            Case "System.Byte"
                col.DefaultValue = CType(0, Byte)
            Case "System.DateTime"
                col.DefaultValue = "1800-01-01 00:00:00.000"
        End Select

To convert a System.Data.DbType into a System.Type:

    Public Shared Function GetSystemType(ByVal DbType As System.Data.DbType) As System.Type
        '-- establish locals
        Dim stType As System.Type = GetType(System.String)
        '-- test the sent DbType
        Select Case DbType
            Case Data.DbType.String, Data.DbType.AnsiString, Data.DbType.AnsiStringFixedLength, _
             Data.DbType.StringFixedLength
                stType = GetType(System.String)
            Case Data.DbType.Int32
                stType = GetType(System.Int32)
            Case Data.DbType.Boolean
                stType = GetType(System.Boolean)
            Case Data.DbType.Decimal, Data.DbType.Currency
                stType = GetType(System.Decimal)
            Case Data.DbType.Byte, Data.DbType.SByte
                stType = GetType(System.Byte)
            Case Data.DbType.Date, Data.DbType.DateTime, Data.DbType.DateTime2
                stType = GetType(System.DateTime)
        End Select
        '-- return converted System.Type
        Return stType
    End Function