Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

How to make a 'query' between multiple tables in a Dataset

harris_charris_c Member Posts: 2
Hi,

Here is a sample scenario:
a. The program has the option to get data from Oracle (table1), Access (table2) or flatfiles (CSV/XML)(table3).
b. The results are stored in a single dataset.
c. Relate the tables, just like querying them from a single database, like so,

SELECT * FROM table1, table2, table3
WHERE table1.col1 = table2.col1
AND table1.col1 = table3.col1
AND table1.col2 = 'Test'

I know this is easy on a single database, but if data can come from multiple sources, what is the best solution? Aside from getting all the data and making a temporary table for each in a single database and running the above sql against it?

I know that datasets does not permit 'select' statements. Will .relationships and .filters somehow do the job(?).



Thanks very much,

hec",)

Comments

  • raymcdraymcd Member Posts: 284
    : Hi,
    :
    : Here is a sample scenario:
    : a. The program has the option to get data from Oracle (table1), Access (table2) or flatfiles (CSV/XML)(table3).
    : b. The results are stored in a single dataset.
    : c. Relate the tables, just like querying them from a single database, like so,
    :
    : SELECT * FROM table1, table2, table3
    : WHERE table1.col1 = table2.col1
    : AND table1.col1 = table3.col1
    : AND table1.col2 = 'Test'
    :
    : I know this is easy on a single database, but if data can come from multiple sources, what is the best solution? Aside from getting all the data and making a temporary table for each in a single database and running the above sql against it?
    :
    : I know that datasets does not permit 'select' statements. Will .relationships and .filters somehow do the job(?).
    :
    :
    :
    : Thanks very much,
    :
    : hec",)
    :


    Add a relation to the dataset for each relationship. Then, if you want to create a grid showing both sides of the relationship, like order.date, order.custID, order.ID, orderdetails.ItemID, orderdetails.quantity you would do this...

    dim oParent as datarow
    dim oChildren() as datarow

    oParent = dataset.tables(OrderTableIndex).rows.find(OrderID)
    oChildren = oParent.GetChildRows(DataRelationHere)

    Then you can do a for next against oChildren doing this...

    string = oParent.items(0).tostring() & ", " & ... & oChildren(LoopCounter).items(0).tostring()

    You would loop against the children array, but the parent is a single static row.

    -ray
  • harris_charris_c Member Posts: 2
    : : Hi,
    : :
    : : Here is a sample scenario:
    : : a. The program has the option to get data from Oracle (table1), Access (table2) or flatfiles (CSV/XML)(table3).
    : : b. The results are stored in a single dataset.
    : : c. Relate the tables, just like querying them from a single database, like so,
    : :
    : : SELECT * FROM table1, table2, table3
    : : WHERE table1.col1 = table2.col1
    : : AND table1.col1 = table3.col1
    : : AND table1.col2 = 'Test'
    : :
    : : I know this is easy on a single database, but if data can come from multiple sources, what is the best solution? Aside from getting all the data and making a temporary table for each in a single database and running the above sql against it?
    : :
    : : I know that datasets does not permit 'select' statements. Will .relationships and .filters somehow do the job(?).
    : :
    : :
    : :
    : : Thanks very much,
    : :
    : : hec",)
    : :
    :
    :
    : Add a relation to the dataset for each relationship. Then, if you want to create a grid showing both sides of the relationship, like order.date, order.custID, order.ID, orderdetails.ItemID, orderdetails.quantity you would do this...
    :
    : dim oParent as datarow
    : dim oChildren() as datarow
    :
    : oParent = dataset.tables(OrderTableIndex).rows.find(OrderID)
    : oChildren = oParent.GetChildRows(DataRelationHere)
    :
    : Then you can do a for next against oChildren doing this...
    :
    : string = oParent.items(0).tostring() & ", " & ... & oChildren(LoopCounter).items(0).tostring()
    :
    : You would loop against the children array, but the parent is a single static row.
    :
    : -ray
    :


    Hi Ray,

    Thanks for the quick reply.

    I forgot to mention that I am developing some sort of a generic data browser for our company, and it must be able to access all databases currently in used. There is no way to determine the keys.

    I tried running your example, and since I do not have a key I've rewritten your code:

    Dim myRel as DataRelation = new DataRelation("TEST", myDS.Tables(0).Columns(0), myDS.Tables(1).Columns(0), false)
    myDS.Relations.Add(myRel)

    oParent = myDS.Tables(0).Rows(iLoop = 0) 'ill try looping through this later
    oChildren = oParent.GetChildRows("TEST")

    Dim i As Integer
    For Each myDR in oChildren
    Console.WriteLine(oParent.Item(0).ToString ... & "," & oChildren.Item(0).ToString)
    Next


    Your code pretty much pointed me to the right direction. Thanks again!

    hec",)

  • raymcdraymcd Member Posts: 284
    : : : Hi,
    : : :
    : : : Here is a sample scenario:
    : : : a. The program has the option to get data from Oracle (table1), Access (table2) or flatfiles (CSV/XML)(table3).
    : : : b. The results are stored in a single dataset.
    : : : c. Relate the tables, just like querying them from a single database, like so,
    : : :
    : : : SELECT * FROM table1, table2, table3
    : : : WHERE table1.col1 = table2.col1
    : : : AND table1.col1 = table3.col1
    : : : AND table1.col2 = 'Test'
    : : :
    : : : I know this is easy on a single database, but if data can come from multiple sources, what is the best solution? Aside from getting all the data and making a temporary table for each in a single database and running the above sql against it?
    : : :
    : : : I know that datasets does not permit 'select' statements. Will .relationships and .filters somehow do the job(?).
    : : :
    : : :
    : : :
    : : : Thanks very much,
    : : :
    : : : hec",)
    : : :
    : :
    : :
    : : Add a relation to the dataset for each relationship. Then, if you want to create a grid showing both sides of the relationship, like order.date, order.custID, order.ID, orderdetails.ItemID, orderdetails.quantity you would do this...
    : :
    : : dim oParent as datarow
    : : dim oChildren() as datarow
    : :
    : : oParent = dataset.tables(OrderTableIndex).rows.find(OrderID)
    : : oChildren = oParent.GetChildRows(DataRelationHere)
    : :
    : : Then you can do a for next against oChildren doing this...
    : :
    : : string = oParent.items(0).tostring() & ", " & ... & oChildren(LoopCounter).items(0).tostring()
    : :
    : : You would loop against the children array, but the parent is a single static row.
    : :
    : : -ray
    : :
    :
    :
    : Hi Ray,
    :
    : Thanks for the quick reply.
    :
    : I forgot to mention that I am developing some sort of a generic data browser for our company, and it must be able to access all databases currently in used. There is no way to determine the keys.
    :
    : I tried running your example, and since I do not have a key I've rewritten your code:
    :
    : Dim myRel as DataRelation = new DataRelation("TEST", myDS.Tables(0).Columns(0), myDS.Tables(1).Columns(0), false)
    : myDS.Relations.Add(myRel)
    :
    : oParent = myDS.Tables(0).Rows(iLoop = 0) 'ill try looping through this later
    : oChildren = oParent.GetChildRows("TEST")
    :
    : Dim i As Integer
    : For Each myDR in oChildren
    : Console.WriteLine(oParent.Item(0).ToString ... & "," & oChildren.Item(0).ToString)
    : Next
    :
    :
    : Your code pretty much pointed me to the right direction. Thanks again!
    :
    : hec",)
    :
    You can specify a key in the dataset just like you can specify a relation. It probably won't help though since if your data doesn't have predefined keys it invariably is violating any "logical" key anyway. That'd just lead to headaches trying to clean the data.

    Glad it helped...

    -Ray
Sign In or Register to comment.