Alternative way of referencing a column from another column in datatable

[chibbie23]

I have a data table, i want to add a new column on that table, the data in that new column will depend in the data from another column. Now i can think of two ways to accomplish this, first is loop through the whole table and do the statements to get my new columns appropriate data. the second approach would be to give the new column an expression, then convert the table to xml (to remove the expression) then convert the xml string back to datatable.

Now, is there any other way around this? what could be the most optimized way in solving this task?

[Rabbit]

Why not calculate the value in real time instead of storing it?

[chibbie23]

Hi rabbit,

I forgot to include, i am adding the computed column to polish my table and make it more readable for users(e.g. when data in existing column = "1" then data on the added column should be "COMPLETE"), because i am exporting that table as an Excel File. Which is also the reason why i need to compute for that added column as a whole and not one at a time. Thanks

[Rabbit]

Again, there's not reason not to calculate it as needed in real time instead of storing it. Or, an alternative approach is to create a "lookup" table and join to that.

[chibbie23]

Hi rabbit,

here's the scenario, the user would search for a category, then the result would be bound into a repeater. Now that the user has the whole resultset in hand, I want to give them the option to export the result to excel without having to deal with reports. Before i convert the datatable to excel, i would like first to polish it, and make every record readable for users, meaning i have to do this all at the same time. That's why i added a new column with expression referencing another column, i convert the datatable to xml to make it flat data, so i can delete the referenced column, to leave only the new column which is readable for the user. i have also tried looping the whole table and do the statements row per row (to avoid expressions), but i think this is more costly. Now joining to a lookup table seems a good idea, i think i want to try it. Thanks

[Rabbit]

No problem. Let us know if you have trouble setting up the table.

[chibbie23]

Hi Rabbit,

i can get a linq with 2 datatables:

Dim newadtbl As New DataTable
 
    If adtbl IsNot Nothing Then
 
        Dim newatblVal =
 
            From new_col In adtbl.AsEnumerable() _
 
            From old_col In adt.AsEnumerable() _
 
                .Where(Function(oc) new_col.Item(4).Equals(oc.Item(4)) _
 
                            AndAlso new_col.Item(0).Equals(oc.Item(0))) _
 
                .DefaultIfEmpty() _
 
            Select TryCast(IIf(old_col Is Nothing, new_col, old_col), DataRow)
 
            newadtbl = newatblVal.CopyToDataTable
 
    Else
 
        newadtbl = adtbl
 
    End If
 
    Return newadtbl
 
 

Now, i wonder how to do this in three datatables, im not sure of the function(oc) part. Your help is greatly appreciated. Thanks