Combining multiple datatables on a unique ID

[11Oppidan]

Hi,

Using VB .NET I want to combine app 100 different ADO.NET datatables, each
with app 50,000 records which have been populated from SQL. They share a
common ID, and I would like to group by this ID and sum up the other 4
columns to give me one master table. I understand how to populate the
datatables but don't know what the best/fastest way to query across
different datatables is.

Any advice on querying one datatable with another on a common ID is much
appreciated.

 

[William \(Bill\) Vaughn]

You need to do this on the server, not the client. Is that what you're
trying to do? It does not sound like it.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Kevin Yu [MSFT]]

Hi 11Oppidan,

I'm not quite sure whether you need to caculate the summary of each column
or you need to add a column to contain the sum of 4 columns in every row.

If the first one is true, after combining all the tables, you can use
DataTable.Compute method to get the summary. Here is an example:

myTable.Compute("Sum(Total)", "EmpID = 5");

For more information about Compute method, please check the following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatatableclasscomputetopic.asp

If the latter one is true, we can add a new column in the DataTable schema
and set its expression to "col1+col2+col3+col4". (Each one represents the
name of a column.)

Here is more information about DataColumn.Expression.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatacolumnclassexpressiontopic.asp

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[11Oppidan]

Thanks for your reply.

Would you suggest that I instead create a tempory table on the server and
append all the records from the 100 queries into this table and then return:

Select A.ID, Sum(A.Column1), etc
from #mytable A
Group by A.ID

For future reference, I am still not sure of the best way to query across
..net datatables on a common id...Kevin's example just uses one datatable
with one parameter, if you wanted to do a simple summation of two datatables
on a common id what would be the best way to do this...

I found this link http://support.microsoft.com/kb/325684/EN-US/, is this on
the right track?

Many thanks for you guidance.

 

[Kevin Yu [MSFT]]

Hi 11Oppidan,

As far as I know, there isn't a direct way to do cross table summaries. The
KB article is actually going through all the rows to find distinct items.
So, we can use this idea, to iterate each table to do the summary. This is
the furthest I can think to achieve this at client side. Or you can try
Bill's advice to do it at server side.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[11Oppidan]

Hi Kevin,

How about DataRelation Objects - could I use these?

 

[Kevin Yu [MSFT]]

Hi 11Oppidan,

DataRelation objects create relationship between master and detail tables.
However, based on my understanding of this issue, you have coordinate
tables to do this summary. So I don't think DataRelation is suitable here.
Please feel free to point me out if my understanding is incorrect. Thank
you.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[11Oppidan]

I am not sure what you mean by coordinate tables.

I have 100 datatables in a dataset. Each table has been populated by an
individual query. Each datatable contains an event id, value1, value2,
value3 and value4, with about 10k rows. There are a finite number of event
ids app 100k. Each datatable can contain any combination of these events
ids.

I want to create one master table by appending all these different tables
together and then grouping by the Event ID and summing the values. So I
will have a final table containing the eventid once and the sum of the
values from all the different tables I have combined.

Hope that makes sense! As William said this is perhaps something that
should be done server side. I am just keen to establish how to query across
datatables!

Thanks for your help.

 

[Kevin Yu [MSFT]]

Hi 11Oppidan,

Thanks for your clarify. As far as I know, there isn't a way to query
across DataTables in a DataSet in this case. We have to do this on server
side. However, if the eventid is primary key in every table, I think we can
try to use DataSet.Merge to merge every table to a master table. Please
check the folllowing link for more information on DataSet.Merge.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatasetclassmergetopic.asp

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[11Oppidan]

I don't think that Merge would work either as my Event Ids are not a unique
primary key which differs in each datatable. The id is unique within each
datatable but not between datatable ...

 

[Kevin Yu [MSFT]]

Hi 11Oppidan,

DataSet.Merge will overwrite one of the rows in two tables according to
your preserveChanges argument when it finds two identical PK value in the
two tables. However, according to this scenario, I think it better to
accomplish this at server side. If at client side, we have to write complex
logics to deal with the DataTables. Sorry for the inconvenience.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."