none
Mapping joined tables from a select to multiple DataTable of a DataSet RRS feed

  • Question

  • Hi,

    Any idea how to do tell the DataAdapter or the DataSet (or ?) to map the values returned form a select statement with a JOIN into specific DataTable.

    For instance:

    Select Table1.Field1, Table1.Field2, Table2.FieldA From Table1 JOIN Table2 ON Table1.KeyField = Table2.FKeyField

     

    Suppose it returns :

    1, 2, "A"

    3, 4, "B"

    5, 6, "C"

     

    Now, I want my DataSet to contain 2 tables:

    "Table1": Field1, Field2

    "Table2": FieldA

     

    And then the values would go in the according tables:

    Table1:

    1, 2

    3, 4

    5, 6

     

    Table2:

    "A"

    "B"

    "C"

     

    I know I could use two separated select statements put that in DataTable in a DataSet and add a DataRelation. But the fact is I don't have control over the resultset creation. Data arrive that way to me...

    Thanks,

    Dave Smile

    Saturday, July 28, 2007 2:07 AM

Answers

  • Then you need to write your own code to split result DataTable into two one. DataAdapter does not do this for you automatically.

    Saturday, July 28, 2007 11:37 AM
    Moderator

All replies

  • It will be one DataTable in this case. You will get one DataTable per SELECT statement. You actually do not need to join your tables, but to execute two SQL statements to load two DataTables. In a case of SQL Server, you could execute them in one shot passing them as one semicolon-delimited string.

     

    Saturday, July 28, 2007 2:14 AM
    Moderator
  • I know I can do that but as I said, I don't have any control on the output of the SQL because I'm not coding the SQL and I don't have access to the database. I receive the resultset as is from a select statement that joins the tables.
    Saturday, July 28, 2007 11:20 AM
  • Then you need to write your own code to split result DataTable into two one. DataAdapter does not do this for you automatically.

    Saturday, July 28, 2007 11:37 AM
    Moderator
  • Thanks. That is the answer I was afraid of...
    Saturday, July 28, 2007 4:38 PM
  • You can try myTable.Select  (SortExpr, Sort Order) method to split datatable. Hope it helps you.

     

    Tuesday, September 25, 2007 7:11 PM