动态添加一列到 Vb.net 中的现有数据表

Dynamically Adding a Column to existing DataTable in Vb.net

提问by DareDevil

I am Working with Vb.Net Project, through Web Service call method I get the result in DataTable's Object , now I need to add more columns after local calculations, I m doing all in loops and adding one by column's data through loop or modifying it, for a larger DataTable its too time taking. Is there any logic that I add those columns in one go rather traversing each DataRow?

我正在使用 Vb.Net 项目，通过 Web 服务调用方法我在 DataTable 的 Object 中得到结果，现在我需要在本地计算后添加更多列，我正在循环中进行所有操作，并通过循环逐列添加数据或修改它, 对于更大的 DataTable 太耗时了。是否有任何逻辑可以一次性添加这些列而不是遍历每个 DataRow？

Suppose I have a DataTable with 4 columns, (Name = dt) I need to add two more into it.

假设我有一个包含 4 列的 DataTable，（Name = dt）我需要再添加两个。

for 2000 rows I have to go for each row to initialize the value of newly added columns.

对于 2000 行，我必须为每一行初始化新添加列的值。

****Suppose I have calculations of new tentative columns into a Temp table. Is There any way so I can update the values of newly added columns (added into dt)by joins The tables (inside VB.NET code) on the bases of a common column (Primary Key Column)****

****假设我将新的暂定列计算到临时表中。有什么方法可以更新新添加的列的值（添加到 dt 中）通过连接基于公共列（主键列）的表（在 VB.NET 代码中）****

回答by M463

Suppose I have calculations of new tentative columns into a Temp table. Is There any way so I can update the values of newly added columns (added into dt)by joins The tables (inside VB.NET code) on the bases of a common column (Primary Key Column)

假设我将新的暂定列计算到临时表中。有什么方法可以更新新添加的列的值（添加到 dt）通过连接基于公共列（主键列）的表（在 VB.NET 代码中）

If the tempTbl is within the same DataSet as your primary Table (containing the Data), and you've got a 1:1 matching key relationship: yes, you can.

如果 tempTbl 与您的主表（包含数据）在同一个数据集中，并且您有一个 1:1 匹配的键关系：是的，您可以。

Add a DataRelation between your two tables within the DataSet and use it to retrieve a combined DataRow, containing the columns of all related tables.

在 DataSet 中的两个表之间添加 DataRelation，并使用它来检索组合的 DataRow，其中包含所有相关表的列。

    '   the variables
    Dim DSet As DataSet = New DataSet("DSet")
    Dim DTbl1 As DataTable = New DataTable("One")
    Dim DTbl2 As DataTable = New DataTable("Two")
    Dim DRelation As DataRelation

    '   setting up sample tables
    DTbl1.Columns.Add("SaleID", GetType(Integer))
    DTbl1.Columns.Add("ProductName", GetType(String))
    DTbl1.Columns.Add("AmountSold", GetType(Double))
    DTbl1.Columns.Add("ItemPrice", GetType(Double))

    DTbl2.Columns.Add("SaleID", GetType(Integer))
    DTbl2.Columns.Add("Turnover", GetType(Double))

    '   host this DataTables in the DataSet
    DSet.Tables.Add(DTbl1)
    DSet.Tables.Add(DTbl2)

    '   this is the exiting part: adding primary keys...
    '   the DataTable.PrimaryKey-property is an Array of DataRow, so I just initialize a new array containing the one column I would like to set as primary key for this table.
    DTbl1.PrimaryKey = {DTbl1.Columns("SaleID")}
    DTbl2.PrimaryKey = {DTbl2.Columns("SaleID")}

    '   ...and the DataRelation
    DRelation = New DataRelation("SaleIDRelation", DSet.Tables("One").Columns(0), DSet.Tables("Two").Columns(0))
    DSet.Relations.Add(DRelation)

    '   populate Tbl1 with some sample data
    DTbl1.Rows.Add(1, "Eggs", 4, 0.2)
    DTbl1.Rows.Add(2, "Apples", 5, 0.5)
    DTbl1.Rows.Add(3, "Milk", 5, 1)

    '   do the calculation
    For Each DRow As DataRow In DSet.Tables("One").Rows
        '   I personally prefer to keep iteration variables scope inside the loops, so the variable can get catched by the GarbegeCollector as soon as the loop is left
        Dim tPrice As Double = 0

        '   I also prefer not to rely on implicit conversion
        tPrice = Convert.ToDouble(DRow("AmountSold")) * Convert.ToDouble(DRow("ItemPrice"))

        '   for each row processed by the loop, add a row to the second table to store the calculations result
        '   this row should have the same SaleID, so the DataReleation will be able to relate the two rows together later on
        DTbl2.Rows.Add(DRow("SaleID"), tPrice)
    Next

    '   so now you'll be able to get the according DataRow(s) of the second table by retriving the depending ChildRows through the DataRelation
    For Each DRow As DataRow In DSet.Tables("One").Rows
        Console.WriteLine(String.Format("Product {0} in SaleID {1} has made a total turnover of {2}", DRow("ProductName"), DRow("SaleID"), DRow.GetChildRows("SaleIDRelation")(0)("Turnover")))
    Next

Output:

输出：

Product Eggs in SaleID 1 has made a total turnover of 0,8
Product Apples in SaleID 2 has made a total turnover of 2,5
Product Milk in SaleID 3 has made a total turnover of 5

The real magic is happening within the loop for the output. I'm accessing the desired value of the firstchild row, because due to the 1:1 DataRelation, I have made sure that each DataRow in Tbl1 has a pedant in Tbl2 that has the same SaleID.

真正的魔法发生在输出的循环中。我正在访问第一个子行的所需值，因为由于 1:1 DataRelation，我确保 Tbl1 中的每个 DataRow 在 Tbl2 中都有一个具有相同 SaleID 的 pedant。

So what I do is DRow.GetChildRows("SaleIDRelation")(0)("Turnover"):

所以我要做的是DRow.GetChildRows("SaleIDRelation")(0)("Turnover")：

• For DRow(this DataRow)
• Get the related ChildRows, using the DataRelation named "SaleIDRelation"
• Use the firstChildRow that is found, indicated by (0)
• And of that DataRow, I would like to have the value of the column ("Turnover")

• 对于DRow（这个 DataRow）
• 使用名为的 DataRelation 获取相关的 ChildRows "SaleIDRelation"
• 使用找到的第一个ChildRow，用(0)表示
• 对于那个 DataRow，我想要列的值（“营业额”）