Retrieving class values based on a key

Question

Hello I came across a problem while creating my school project.

Some description :

Listy - its a object, and its being appended by a sql query and later becomes a listbinding. Customers - Clients : id,name,surname

Listy sql dump example

id+ number                  +letters+forwho+bywho+created            +prority+type
7   900000170300000935295877 0       3      202   2013-11-27 16:37:55 0       1

The problem

My datagrid view looks exactly the same way as a mysql result, what I want to get is a more friendly display so if I got byhwo 202 (its a customers id) I want to be displayed in the datagrid view Example Name Example Surname. It has to be done with this code somehow. Also a nice feature would be to have the ability to delete and update a Class Customers somehow.

Class Core

Class Core
Dim gridDataList As New BindingList(Of Listy)
    Dim cmd As New MySqlCommand
    Dim da As New MySqlDataAdapter
    Dim con As MySqlConnection = jokenconn()
    Public list As New List(Of Customers)
    Public Function jokenconn() As MySqlConnection
        Return New MySqlConnection(.......)
    End Function

    Public Sub init_customers()
        ' Create a list of strings.
        Dim sql As String
        Dim myReader As MySqlDataReader

        con.Open()
        sql = "select * from customers"
        'bind the connection and query
        With cmd
            .Connection = con
            .CommandText = sql
        End With
        myReader = cmd.ExecuteReader()
        While myReader.Read()
            list.Add(New Customers(myReader.GetInt64(0), myReader.GetString(1), myReader.GetString(2)))
        End While
        con.Close()
    End Sub


    Public Function display_single_name()
        Return 0
        'Dim pinfo As propertyinfo = GetType(String).GetProperty("")
        'here i want to return the name and surname of client based on a number/id
    End Function
End Class

Class Customers

Class Customers

    Public Sub New(ByVal id As Integer, ByVal name As String, ByVal surname As String)
        Me.ID = id
        Me.Imie = name
        Me.Nazwisko = surname

    End Sub
#Region "Get/Set"
    Public Property ID() As Integer
        Get
            Return Me._id
        End Get
        Set(ByVal value As Integer)
            Me._id = value
        End Set
    End Property
    Public Property Imie() As String
        Get
            Return Me._imie
        End Get
        Set(ByVal value As String)
            Me._imie = value
        End Set
    End Property
    Public Property Nazwisko() As String
        Get
            Return Me._nazwisko
        End Get
        Set(ByVal value As String)
            Me._nazwisko = value
        End Set
    End Property

#End Region
    Private _id As Integer
    Private _imie As String
    Private _nazwisko As String

End Class

Class Listy

Class Listy

    ' Private _comments As String
    '  Private _firstName As String
    '  Private _secondName As String

    Public Sub New(ByVal id As Integer, ByVal listnumb As String, ByVal list_count As Integer, ByVal by_who As Integer, ByVal for_who As Integer, ByVal created As Date, ByVal prority As Integer, ByVal type As Integer)
        Me.ID = id
        Me.Lista = listnumb
        Me.Listów = list_count
        Me.Wystawione_przez = by_who
        Me.Wystawione_na = for_who
        Me.Priorytet = prority
        Me.Rodzaj_Listy = type
        Me.Utworzono = created

    End Sub
#Region "Get/Set"
    Public Property ID() As Integer
        Get
            Return Me._id
        End Get
        Set(ByVal value As Integer)
            Me._id = value
        End Set
    End Property
    Public Property Lista() As String
        Get
            Return Me._list_Number
        End Get
        Set(ByVal value As String)
            Me._list_Number = value
        End Set
    End Property
    Public Property Listów() As Integer
        Get
            Return Me._Lst_Count
        End Get
        Set(ByVal value As Integer)
            Me._Lst_Count = value
        End Set
    End Property
    Public Property Wystawione_przez() As Integer
        Get
            Return Me._bywho
        End Get
        Set(ByVal value As Integer)
            Me._bywho = value
        End Set
    End Property
    Public Property Wystawione_na() As Integer
        Get
            Return Me._forwho
        End Get
        Set(ByVal value As Integer)
            Me._forwho = value
        End Set
    End Property
    Public Property Priorytet() As Integer
        Get
            Return Me._prority
        End Get
        Set(ByVal value As Integer)
            Me._prority = value
        End Set
    End Property
    Public Property Rodzaj_Listy() As Integer
        Get
            Return Me._type
        End Get
        Set(ByVal value As Integer)
            Me._type = value
        End Set
    End Property
    Public Property Utworzono() As Date
        Get
            Return Me._date
        End Get
        Set(ByVal value As Date)
            Me._date = value
        End Set
    End Property
#End Region
    Private _id As Integer
    Private _Lst_Count As Integer
    Private _bywho As Integer
    Private _forwho As Integer
    Private _prority As Integer
    Private _type As Integer
    Private _date As Date
    Private _list_Number As String
End Class

Answer 1

a Listy query has for example 50k rows so it can become slow

In that case, your DB may be poorly designed or structured, or the SQL query could be sub optimal. 50k is not a lot of data, and preloading all the data and using 3 classes to hand code the same result is not likely to be a lot faster, but will be more error prone.

That said, your display_single_name() function might be pretty simple. Assuming:

• The mailing list data is loaded somewhere like a List(of MailItem)
• The app has something like the Customer Class and a List(of Customer)
• We know that ByWho and ForWho are FKs, but what they are FKs to is still not clear. I am assuming Customer since there are no other actors mentioned

The existing code has no where to store this info, so a few changes:

Class Customer
    ...
    Public ReadOnly Property FullName As String
        Get
            Return String.Format("{0} {1}", Name, LastName)
            ' or
            'Return String.Format("{0}, {1}", LastName, Name)
        End Get
    End Property
    ...

Public Class MailItem    ' AKA "listy"

    Public Property CustomerID As Integer
    Public Property CustomerName As String

    Public Property ListNumber As String
    Public Property ListCount As Integer

    Public Property ByWhomID As Integer
    Public Property ByWhomName As String

    Public Property ForWhomID As Integer
    Public Property ForWhomName As String

    Public Property Priority As Integer
    ...etc

The following uses myMailItems which is a List(Of MailItem) to store the mail item info including the resolved names fed to it, and CustList is a List(of Customer). This would functionally be basically Core.init_customers() but there is no real need for it to be a special class.

    ' loading the MailItems ("listy") into a 
    ' list(of MainItem) ("gridDataList"??? it is never used in the OP code
    ' rdr is a SQLReader
    Dim mi As New MailItem(Convert.ToInt32(rdr.Item("ID"))
                           ... all the other fields)

    Dim tmpCust As Customer

    ' load the names of the actors from the ID:
    tmpCust = GetCustomerByID(mi.CustomerID)
    If tmpCust IsNot Nothing Then
        mi.CustomerName = tmpCust.FullName
    Else
        mi.CustomerName = "Unknown!"
    End If

    tmpCust = GetCustomerByID(mi.ForWhomID)
    If tmpCust IsNot Nothing Then
        mi.ForWhomName = tmpCust.FullName
    Else
        mi.ForWhomName = "Unknown!"
    End If

    tmpCust = GetCustomerByID(mi.ByWhomID)
    If tmpCust IsNot Nothing Then
        mi.ByWhomName = tmpCust.FullName
    Else
        mi.ByWhomName = "Unknown!"
    End If

    ' the actor names are resolved, add to the list:
    myMailItems.Add(mi)

The customer lookup is resolved via a helper function:

Private Function GetCustomerByID(id As Integer) As Customer
    Dim cust As Customer = CustList.Find(Function(x) x.ID = id)

    Return cust
End Function

In the sample data, you'd call it with 7, 3 and 202 (apparently) to get the related customer name. The new FullName property formatted however you like, returns the name for storing in the list.

The MailItem class could perform the lookup itself in the constructor, provided it had a reference to the Customer list. Of course, it could also do a SQL look up to get the name(s) for each actor as well.

---

Variants for the look up:

' a simple loop:
For Each Cust As Customer In CustList
    If Cust.Id = id Then Return Cust
End If
Return Nothing         

Recraft the function to get the name:

Function CustmerNameFromID(id as Integer) As String
    ' or use the loop variant here
    Dim cust As Customer = CustList.Find(Function(x) x.ID = id)
    ' test for Nothing here rather than in data load proc
    If cust IsNot Nothing Then
        Return cust.FullName
    Else
        Return ""
    End If

End Function

' use:
mi.CustomerName = CustmerNameFromID(mi.ID)

---

Note:

This is by no means a better solution than a SQL JOIN query; but it does seem to be what you are looking for.

In order for the lookup to work, you have to bring all the customers in the DB to the client PC just in case they might be used in the current "listy" dataset. Then, code has to create an object for each and store it. That will take time and memory.

An alternative would be to fire off SQL lookups to get Cust #3 when needed and cache/store it in a List, so if it is seen again you can reuse it and only hit the DB if it is not in the list. But again, you are just doing in code what can be done with a proper SQL JOIN query.