CODEWITHSUNDEEP
vb.netclassproperties
Yuu
Yuu

Reputation: 59

Identify the properties of an object and put values into it

I want to identify the properties of specific object that it receives when the method is called and put values in it from the db result that I got. I've searched about it but I'm currently stucked in how I should proceed from here. Here is my code..

Public Class DBModel
Public Sub getFromDB(ByRef lists As List(Of Object), ByVal classType As Type, ByVal tblName as String)

   Dim strSql As String = "SELECT * FROM " & tblName
   Dim props = classType.GetProperties()

    Try
        Using cnn As New SqlConnection("Data Source =  .\; Initial Catalog = DBName;" & "Integrated Security = True;")
            Using cmd As New SqlCommand(strSql, cnn)
                cnn.Open()
                Using dr As SqlDataReader = cmd.ExecuteReader()
                    While dr.Read
                        For Each prop In props
                            For i As Integer = 0 To dr.VisibleFieldCount - 1
                                prop = dr.GetValue(i)
                            Next
                        Next
                        lists.Add(props)
                    End While
                End Using
            End Using
    End Using
        Catch e As Exception
            MessageBox.Show(e.ToString())
        End Try
End Sub
End Class

I'm calling here the getFromDB method to populate the list of customers in this class, but I'll also call the getFromDB method from other classes with another different set of properties..

Public Class CustomerCtrler
   private _CustomerList As New List(Of Customer)

   Public Sub New()
    Dim dbModel As New DBModel
    Dim cust As New Customer
    dbModel.getFromDB(_CustomerList, cust.GetType, "CustTbl")
   End sub
End Class

Public Class Customer
    Public Property custID As Integer
    Public Property FirstName As String
    Public Property LastName As String
    Public Property DateRegistered As DateTime
End Class

But I got a InvalidCastException, so I've searched about converting the data types but I got: "Value type of Integer cannot be converted into PropertyInfo" at the 'prop = dr.GetValue(i)' line..

I'm quite new to object oriented programming so I'm sorry if there's a lot of mistakes there but your help will be really appreciated..

Upvotes: 0

Views: 63

Answers (1)

jmcilhinney
jmcilhinney

Reputation: 54487

I would tend to go with something like this:

Public Function GetListFromDatabase(Of T As New)(tableName As String) As List(Of T)
    Dim itemType = GetType(T)
    Dim allProperties = itemType.GetProperties()
    Dim items As New List(Of T)

    Using connection As New SqlConnection("connection string here"),
          command As New SqlCommand($"SELECT * FROM [{tableName}]", connection)
        connection.Open()

        Using reader = command.ExecuteReader()
            Dim columnNames = reader.GetColumnSchema().
                                     Select(Function(column) column.ColumnName).
                                     ToArray()

            'Ignore properties that don't have a corresponding column.
            Dim properties = allProperties.Where(Function(prop) columnNames.Contains(prop.Name)).
                                           ToArray()

            Do While reader.Read()
                'We can do this because we have specified that T must have a
                'parameterless constructor by using "As New" in the method declaration.
                Dim item As New T

                For Each prop In properties
                    prop.SetValue(item, reader(prop.Name))
                Next

                items.Add(item)
            Loop
        End Using
    End Using

    Return items
End Function

You can then do this:

_CustomerList = dbModel.GetListFromDatabase(Of Customer)("CustTbl")

You can obviously create a variation on that if you really want to pass in an existing list but I don't see the point in that unless the list might already contain items.

EDIT: Here is an alternative method to get the data reader column names. I haven't tested it so it may be that "COLUMN_NAME" isn't quite right but it will be something very close to this:

Dim schemaTable = reader.GetSchemaTable()
Dim columnNames = schemaTable.Rows.
                              Cast(Of DataRow).
                              Select(Function(row) CStr(row("COLUMN_NAME"))).
                              ToArray()

Upvotes: 1

Related Questions