Dot-Net

根據鍵檢索類值

  • March 14, 2016

您好，我在創建學校項目時遇到了一個問題。

一些描述：

Listy - 它是一個對象，它被一個 sql 查詢附加，後來成為一個列表綁定。客戶 - 客戶：身份證、姓名、姓氏

Listy sql 轉儲範例

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

問題

我的數據網格視圖看起來與 mysql 結果完全相同，我想要得到的是更友好的顯示，所以如果我得到了 byhwo 202（它的客戶 ID），我希望在數據網格視圖中顯示範例名稱範例姓氏。必須以某種方式使用此程式碼來完成。還有一個不錯的功能是能夠以某種方式刪除和更新類客戶。

類核心

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

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

在這種情況下，您的數據庫可能設計或結構不佳，或者 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是List(of Customer). 這在功能上基本上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 查詢以獲取每個參與者的名稱。

查找的變體：

' 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 查詢可以完成的操作。

引用自：https://stackoverflow.com/questions/27237054

相關問答

Dot-Net

如何將多個數據視圖合併為一個？

  • May 3, 2018
檢視問答
Dot-Net

SqlParameter 名稱前是否需要添加@？

  • April 20, 2012
檢視問答
Dot-Net

無法使用 ADO.NET Entity Framework 執行儲存過程

  • March 26, 2012
檢視問答
Dot-Net

如何確定 VB.Net DataRow 中是否存在列

  • October 7, 2008
檢視問答
Dot-Net

有沒有辦法在文本文件中自動縮進 VB.NET 程式碼

  • June 28, 2022
檢視問答
Dot-Net

在 datagridview 中禁用預設的 Enter/Return 鍵行為

  • March 25, 2022
檢視問答