.net - 根据键检索类值

Question

您好，我在创建学校项目时遇到了问题。

一些描述:

Listy - 它是一个对象，它被附加一个 sql 查询，然后成为一个列表绑定(bind)。客户 - 客户: ID、姓名、姓氏

Listy sql 转储示例

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

问题

我的数据 GridView 看起来与mysql结果完全相同，我想要得到的是更友好的显示，所以如果我得到byhwo 202(它的客户id)，我希望在数据 GridView 中显示示例名称示例姓。必须以某种方式使用此代码来完成此操作。另外一个不错的功能是能够以某种方式删除和更新类(class)客户。

类核心

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

    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

    ' 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

Listy 查询例如有 50k 行，因此可能会变慢

在这种情况下，您的数据库可能设计或结构不佳，或者 SQL 查询可能不是最佳的。 50k 并不是很多数据，预加载所有数据并使用 3 个类手动编写相同的结果不太可能快很多，但会更容易出错。

也就是说，您的 display_single_name() 函数可能非常简单。假设:

• 邮件列表数据加载到诸如 List(of MailItem)
之类的位置
• 该应用具有诸如 Customer 类和(客户)列表之类的内容
• 我们知道 ByWho 和 ForWho 是 FK，但它们要的 FK 仍不清楚。我假设客户，因为没有提到其他参与者

现有代码没有位置存储此信息，因此需要进行一些更改:

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

以下内容使用myMailItems(这是一个List(Of MailItem))来存储邮件项目信息，包括提供给它的已解析名称，以及CustList 是一个(客户)列表。从功能上讲，这基本上是 Core.init_customers() 但实际上并不需要它成为一个特殊的类。

    ' 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)

客户查找是通过辅助函数解决的:

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

    Return cust
End Function

在示例数据中，您可以使用 7、3 和 202(显然)来调用它以获取相关的客户名称。新的 FullName 属性按照您喜欢的格式设置，返回用于存储在列表中的名称。

MailItem 类可以在构造函数中自行执行查找，前提是它具有对 Customer 列表的引用。当然，它也可以执行 SQL 查找来获取每个 Actor 的姓名。

<小时/>

查找的变体:

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

重新编写函数以获取名称:

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)

<小时/>

注意:

这绝不是比 SQL JOIN 查询更好的解决方案；但它似乎确实是您正在寻找的。

为了使查找工作正常进行，您必须将数据库中的所有客户带到客户端 PC 以防万一他们可能> 用于当前的“listy”数据集。然后，代码必须为每个对象创建一个对象并存储它。这需要时间和内存。

另一种方法是在需要时触发 SQL 查找以获取 Cust #3 并将其缓存/存储在列表中，因此如果再次看到它，您可以重用它并且仅访问数据库如果它不在列表中。但同样，您只是在代码中执行使用正确的 SQL JOIN 查询可以完成的操作。