Join two datatables on fields known at runtime [cl

2019-08-08 08:57发布

问题:

In a nutshell: How can I join two datatables on fields that VS only knows at runtime and I know all the time? Is there a solution like

    Dim result = From t1 In dt, t2 In dt2 _  
                     Where t1.Field < DateTime > ("timestamp") = t2.Field < DateTime > ("samplestamp")  
                     Select t1, t2

The names of these tables are only known at runtime since both the tables are created dynamically.

Long version:
In Corner A, I have a Historian server where I get some data. This data represents all samples created in a given time for a given proces(ses)
In Corner B i have an SQL server where i also get some data. This data represents all the batches that have run in the given time.

The project needs this 2 tables to be shown in a chart control.

Now for the fun part. Only at runtime am I able to tell wich columns will be available. I don't know how many columns i will receive because i can't tell if 1 batch ran or 100 did. What i do know is that I can receive all this "data" (which i only see at runtime) can be placed into 2 datatables (vb.net because it's an OLD project)
I "Know" for instance that for datatable 1 (corner A) always exists in a table where the first column is named timestamp. (it's also the only column i know here)
And i also know that the first column in datatable2 (corner B) is named sampletime

So here comes the countdown: How am I able to connect these two datatables on the given names into one flashy datatable which I then use as a datasource for my chart control. Hence the problem is solved.

I have tried to accomplish this and create a single sql table in SQL-server, prolem here is that i work with a server that doesn't like transactions so that didn't work out.

回答1:

Without having read the Longversion and hopefully without having overlooked something:

You could pass these tables to a method that takes two DataTables as parameter.

Then you could use this code to return a reusable IEnumerable(Of Tuple(Of DataRow, DataRow)):

Public Function getSomething(dt1 As DataTable, dt2 As DataTable) As IEnumerable(Of Tuple(Of DataRow, DataRow))
    Return From r1 In dt1
           Join r2 In dt2
            On r1.Field(Of DateTime)("timestamp") Equals r2.Field(Of DateTime)("samplestamp")
           Select Tuple.Create(r1, r2)
End Function

Anonymous types are not meant to be used outside of the method where they are created. Hence i've used this tuple approach. You can evaluate the return value in this way:

Dim rowInfos = getSomething(tbl1, tbl2)
For Each rowInfo In rowInfos
    Dim row1 = rowInfo.Item1
    Dim row2 = rowInfo.Item2
    Dim timestamp = row1.Field(Of DateTime)("timestamp")
    Dim samplestamp = row2.Field(Of DateTime)("samplestamp")
Next