database - 如何将订单与产品匹配？

Question

我有一组订单，每个订单都有所需的重量，还有一组火鸡，每个都有重量，所有这些都包含在数据库和数据集中，但通过对象访问(这使它变得更复杂，但对象是那里增加了项目的复杂性，而不是因为这是一个好主意)。

如何匹配它们，然后更新数据库和数据集？我正在尝试使用列表和 LINQ 来完成它，但效果不佳。不幸的是，我看不出哪里出错了。编辑:线 For Count = 0 To Max - 1
Orders.UpdateOrder(Count, Count + 1, Orders.OrderDetail(Count, "CustomerID"), TurkeyList(Count)(0), Orders.OrderDetail(Count, "ApproxWeight"))
Next 出现索引越界错误。我知道这意味着什么，但我不明白我为什么会收到它，以及如何解决它。

我已经完成了我的研究 - 事实上，我目前的尝试使用列表(我从未听说过，直到我在 StackOverflow 上看到我可以使用它们)但是，老实说，这不是我一直在做的事情教导，我可以使用一些指导。我最初尝试使用二维数组，但没有用。我认为我当前的实现应该有效——所以我很有可能只是遗漏了一些愚蠢的东西。以下是我的代码的相关部分。

Imports System.Linq
# <summary>
#  SortForm is a public class which handles the events and controls of a single form.
#  </summary>
#  <remarks></remarks>
Public Class SortForm
    Dim Turkeys As New TurkeyDBInteract
    Dim Orders As New OrderDBInteract
    Dim Customers As New CustomerDBInteract
    Public TurkeyList As New List(Of Integer())
    Public OrderList As New List(Of Integer())
    #  <summary>
    #  The FillLists subroutine fills the two lists with data from the dataset.
    #  </summary>
    #  <remarks></remarks>
    Public Sub FillLists()
        For Count = 0 To Orders.OrderNum - 1
            OrderList.Add(New Integer() {Count, Orders.OrderDetail(Count, "ApproxWeight")})
        Next
        For Count = 0 To Turkeys.TurkeyNum - 1
            TurkeyList.Add(New Integer() {Count, Turkeys.TurkeyDetail(Count, "Weight")})
        Next
    End Sub
    #  <summary>
    #  The SortList subroutine takes a List of 1-dimensional integer and sorts it using LINQ to Objects.
    #  </summary>
    #  <param name="List"></param>
    #  <remarks></remarks>
    Public Sub SortList(ByRef List As List(Of Integer()))
        Dim SortedList As New List(Of Integer())
        SortedList = List.OrderBy(Function(Weight) Weight(1)).ToList()
        List = SortedList
    End Sub
    #  <summary>
    #  This is the SortButton click event. This calls the <see cref="FillLists"/> and <see cref="SortList"/> subroutines to process order and turkey data, then matches the lists and uses this to update the dataset and database.
    #  </summary>
    #  <param name="sender"></param>
    #  <param name="e"></param>
    #  <remarks></remarks>
    Private Sub SortButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SortButton.Click
        FillLists()
        SortList(TurkeyList)
        SortList(OrderList)
        Dim Max As Integer
        If TurkeyList.Count > OrderList.Count Then
            Max = OrderList.Count
        Else
            Max = TurkeyList.Count
        End If
        For Count = 0 To Max - 1
            Orders.UpdateOrder(Count, Count + 1, Orders.OrderDetail(Count, "CustomerID"), TurkeyList(Count)(0), Orders.OrderDetail(Count, "ApproxWeight"))
        Next
   End Sub
End Class

这是 OrderDBInteract 类中 OrderUpdate 方法的代码

    # <summary>
    # Subroutine UpdateOrder updates every value of a specific record of the OrderTbl dataset table and then updates the database.
    # </summary>
    # <param name="Row">Row passes the row of the record to be updated.</param>
    # <param name="OrderID">OrderID passes the new primary key value of the record to be updated.</param>
    # <param name="CustomerID">CustomerID passes the new foreign key field value of the record to be updated.</param>
    # <param name="ApproxWeight">ApproxWeight passes a new field value of the record to be updated.</param>
    # <remarks></remarks>
    Public Sub UpdateOrder(ByVal Row, ByVal OrderID, ByVal CustomerID, ByVal TurkeyID, ByVal ApproxWeight)
        Data.Tables("OrderTbl").Rows(Row).Item("OrderID") = OrderID
        Data.Tables("OrderTbl").Rows(Row).Item("CustomerID") = CustomerID
        Data.Tables("OrderTbl").Rows(Row).Item("TurkeyID") = TurkeyID
        Data.Tables("OrderTbl").Rows(Row).Item("ApproxWeight") = ApproxWeight
    End Sub

Answer 1

试试sortedlist根据键对值进行排序。例如:

Dim OrderList As New SortedList
    For Count = 0 To Orders.OrderNum - 1
        OrderList.Add(Orders.OrderDetail(Count, "ApproxWeight"), Count)
    Next

将根据 Orders.OrderDetail 的结果对值 (Count) 进行排序。这样您就不需要调用 SortList 方法。

虽然，这

Data.Tables("OrderTbl").Rows(Row).Item("OrderID") = OrderID

确实更改了数据集表中行的 OrderID 列值，这些更改不会提交到数据库。看看这个msdn walkthrough关于将数据保存到数据库。基本上，您需要一个具有适当 UpdateCommand 设置的 DataAdapter，以使用来自数据集的更改来更新数据库表。如果您在程序中使用类型化数据集，那么将更改提交到数据库将变得更加容易。