时间戳

首页 > 解决方案 > 如何从多个数据表中填充集合

vb.net - 如何从多个数据表中填充集合

问题描述

我正在尝试填充一个集合，该集合一次包含来自两个数据集的两个模型。我能够填充一个但不能为第二个做。我的例子将进一步澄清，

 'Employee Class
    Public Class Employee
      Public Property ID As Integer
      Public Property Name as String
      Public Property Details() as DetailsCol
    End Class

Sub New(ord as Datarow)
  Me.ID= ord("ID")
  Me.Name=ord("Name")
End Sub

Public Class DetailsCol
    Inherits List(Of Details)
    Sub New()

    End Sub
End Class

 'Details class
Public Class Details
  Public Property Address As String
  Public Property Salary as Double
End Class

Sub New(ord as Datarow)
  Me.Address= ord("Address")
  Me.Salary=ord("Salary")
End Sub

'Main collection where DB call need to set both the collection
Public Class EmployeeCol
    Inherits List(Of Employee)

    Sub New()

    End Sub

    Sub New(ByVal EMP_ID As Integer)
        Dim ds As DataSet = Helper.GO_GET_DisplayEmployee(EMP_ID)
        If Not IsNothing(ds) AndAlso Not IsNothing(ds.Tables) AndAlso ds.Tables.Count > 0 Then
            'Populating Employee 
            For Each dr As DataRow In ds.Tables(0).Rows
                Dim empObj As Employee = New Employee(dr)
                Me.Add(empObj)
            Next

            'TO DO: Trying to populate Details model but not finding way to do
            For Each dr As DataRow In ds.Tables(1).Rows

            Next
        End If

    End Sub

End Class

编辑**

谢谢，我正在尝试根据建议重新编写。我能够获取所有记录的员工集合，但用户集合为空。

Dim rtnContent As New List(Of Employee)

        Dim ds As DataSet = Helper.GO_GET_DisplayDetails(emp_id)
        If Not IsNothing(ds) AndAlso Not IsNothing(ds.Tables) AndAlso ds.Tables.Count > 0 Then
            ds.Tables(0).TableName = "employee"
            ds.Tables(1).TableName = "users"

            ds.Relations.Add(New DataRelation("griddisplay", ds.Tables("employee").Columns("emp_id"), ds.Tables("users").Columns("emp_id")))

            rtnContent = ds.GetAccessor(Of Employee)("employee")
        End If

标签： vb.netmodel-view-controller

解决方案

DataRelation该代码存在许多问题，但为了解决您的具体问题，您应该首先确保DataSet两者之间有一个DataTables. 然后，您可以立即获取任何父行的所有子行以及任何子行的父行。作为一般示例，您可以创建这样的DataSet：

Dim data As New DataSet
Dim parentTable = data.Tables.Add("Parent")
Dim childTable = data.Tables.Add("Child")

With parentTable.Columns
    parentTable.PrimaryKey = {.Add("ParentId", GetType(Integer))}
    .Add("ParentName", GetType(String))
End With

With childTable.Columns
    childTable.PrimaryKey = {.Add("ChildId", GetType(Integer))}
    .Add("ParentId", GetType(Integer))
    .Add("ChildName", GetType(String))
End With

data.Relations.Add("ParentChild",
                   parentTable.Columns("ParentId"),
                   childTable.Columns("ParentId"))

然后，您可以访问特定于每个父行的子行，如下所示：

For Each parentRow As DataRow In parentTable.Rows
    For Each childRow As DataRow In parentRow.GetChildRows("ParentChild")
        '...
    Next
Next

在您的情况下，这意味着您可以/应该将EmployeeCol类中的构造函数更改为以下内容：

Sub New(employeeId As Integer)
    Dim data = Helper.GO_GET_DisplayEmployee(employeeId)

    If data?.Tables.Count = 2 Then
        For Each employeeRow As DataRow In data.Tables("Employee").Rows
            Dim detailsList As New List(Of Details)

            For Each detailsRow In employeeRow.GetChildRows("EmployeeDetails")
                detailsList.Add(New Details(detailsRow))
            Next

            Me.Add(New Employee(employeeRow) With {.Details = detailsList.ToArray()})
        Next
    End If
End Sub

编辑：

我之前提到的问题之一是模型类型中包含 ADO.NET 代码这一事实。这是一个糟糕的设计，但是，如果你要这样做，你可以像这样更干净地做到这一点：

Imports System.Collections.ObjectModel

Public Class Details

    Public Property Address As String
    Public Property Salary As Decimal

    Public Sub New(row As DataRow)
        Address = row.Field(Of String)(NameOf(Address))
        Salary = row.Field(Of Decimal)(NameOf(Salary))
    End Sub

End Class

Public Class DetailsCollection
    Inherits Collection(Of Details)

    Public Sub New(table As DataTable)
        Me.New(table.Rows.Cast(Of DataRow)())
    End Sub

    Public Sub New(rows As IEnumerable(Of DataRow))
        For Each row In rows
            Items.Add(New Details(row))
        Next
    End Sub

End Class

Public Class Employee

    Public Property Id As Integer
    Public Property Name As String
    Public ReadOnly Property Details As DetailsCollection

    Public Sub New(row As DataRow)
        Id = row.Field(Of Integer)(NameOf(Id))
        Name = row.Field(Of String)(NameOf(Name))
        Details = New DetailsCollection(row.GetChildRows($"{NameOf(Employee)}{NameOf(Details)}"))
    End Sub

End Class

Public Class EmployeeCollection
    Inherits Collection(Of Employee)

    Public Sub New(employeeId As Integer)
        Me.New(Helper.GO_GET_DisplayEmployee(employeeId).Tables(NameOf(Employee)))
    End Sub

    Public Sub New(table As DataTable)
        Me.New(table.Rows.Cast(Of DataRow)())
    End Sub

    Public Sub New(rows As IEnumerable(Of DataRow))
        For Each row In rows
            Items.Add(New Employee(row))
        Next
    End Sub

End Class

那里的代码结构有很多改进，但是如果从头开始，我仍然会以不同的方式编写代码。DataSet需要注意的一件事是or没有空值检查DataTables。如果是我，我会确保获取数据的方法总是返回 aDataSet并且它总是包含两个DataTables。如果其中没有数据，那就这样吧，你最终会得到一个空的EmployeeCollection.

推荐阅读