how to join data tables in a dataset RRS feed

  • Question

  • Hi,
    I have a dataset with variable number of tables (table1, table2,......). These tables are created at runtime so i don't have its schema at design time. I can assume that both tables have same number of rows.

    Lets say tabl1 has 3 columns (ID, DataID, table1) and tabl2 also have 3 columns (ID, DataID, table2). User passes an expression which i need to execute on these tables. Lets say user passes (table1 + table2), then in need to join table1 and table2 on ID and DataID column and add table1 column of table1 table and table2 column of table2 table and insert the result into a new table (table3).

    This purpose can be achieved in T-SQL by the following query:

    Select A.ID, A.DataID, A.table1+B.Table2
    FROM  Table1 A INNER JOIN Table2 B
               ON A.ID = B.ID AND A.DataID = B.DataID

    But how to do it in data set and data tables. Also number of tables vary in data set, and user can pass any expression. If user passes (table1 + table2 * table3) then i need to join three tables of data set and perform this operation.

    Can anybody help me how to do this. If you have any query please post. Thanks in advance.

    Wednesday, July 22, 2009 6:08 AM


All replies

  • I have not tested this myself, but this seems to be what you are looking for.

    "HOW TO: Implement a DataSet JOIN helper class in Visual C# .NET"

    This posting is provided "AS IS" with no warranties.
    Wednesday, July 22, 2009 8:09 AM
  • Thanks Michael. But this class uses loops. First loops iterates on each row in parent table and second loop iterates on each row of child table for ever row of parent table. Since there are huge number of records in tables, this would affect the performance. Secondly i can't execute dynamic expression through this class.

    Isn't there the possibility of executing dynamic query on data tables of dataset as we can do in SQL Server as Dynamic SQL?

    Wednesday, July 22, 2009 8:56 AM
  • Hey Sulaman,

    You can not execute a join query against a DataSet or on two datatables. If you want to do this on the client side using DataSet's then the way Michael's suggested is the only way. If you want to do compilated join queries I would make a view on the server and then bring this into a DataSet.

    Chris Robinson
    Program Manager - DataSet

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, July 22, 2009 5:09 PM