CODEWITHSUNDEEP
vb.netjoindatatabledatarowdatacolumn
DareDevil
DareDevil

Reputation: 5349

Dynamically Adding a Column to existing DataTable in Vb.net

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?

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

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

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

Upvotes: 1

Views: 14512

Answers (1)

M463
M463

Reputation: 2183

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)

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.

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.

    '   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 first child 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.

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

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

Upvotes: 2

Related Questions