DataRelation from a single query

Question

Hi,

i have the following piece of VB.NET code:

    Dim conn As New MySql.Data.MySqlClient.MySqlConnection(ConnectionString)
    conn.Open()

    Dim sql = "SELECT * FROM users"
    Dim com = New MySql.Data.MySqlClient.MySqlCommand(sql, conn)

    Dim ds As New DataSet("dsUsers")

    Dim da As New MySql.Data.MySqlClient.MySqlDataAdapter()
    da.SelectCommand = com
    da.TableMappings.Add("Table", "Users")
    da.Fill(ds)

    sql = "SELECT * FROM messages"
    com = New MySql.Data.MySqlClient.MySqlCommand(sql, conn)

    Dim da2 As New MySql.Data.MySqlClient.MySqlDataAdapter()
    da2.SelectCommand = com
    da2.TableMappings.Add("Table", "Messages")
    da2.Fill(ds)

    Dim dr As DataRelation
    Dim col1 As DataColumn
    Dim col2 As DataColumn

    col1 = ds.Tables("Users").Columns("id")
    col2 = ds.Tables("Messages").Columns("users_id")
    dr = New DataRelation("UsersMessages", col1, col2)

    ds.Relations.Add(dr)

Basically, i execute two queries: the first one fetches all the users, the second all the messages. I define two tables within a DataSet and link them through a DataRelation so that the Users table is a parent of Messages table.

This works if I want to select all the rows from the two tables, but what if I have a more complex structure with 4 nested tables and conditional queries?

SELECT t1.*, t2*, t3.*, t4.*
FROM table1 t1, table2 t2, table3 t3, table4 t4
WHERE t1.id = 3
      AND t2.t1_id = t1.id
      AND t3.t2_id = t2.id
      AND t4.t3_id = t3.id

How can I create a DataSet with four tables and the three DataRelations from this single query?

Thanks

Answer 1

The only way to do that would be to write code that does it, i.e. create tables in the data set, set up the relations between the tables and populate the tables with certain fields from the result.

There is no automatic way of doing this, as there is no information in the result about which table each field comes from, or even which tables were queried to get the result.

Answer 2

So, if i populate the 4 tables with the following queries, will ado.net be able to link them (given that i specify the correct DataRelations)?

SELECT * 
FROM table1 WHERE id = 3

SELECT t2.*, t1.id 
FROM table1 t1, table2 t2
WHERE t1.id = 3
     AND t1.id = t2.t1_id

SELECT t3.*, t1.id, t2.t1_id, t2.id
FROM table1 t1, table2 t2, table3 t3
WHERE t1.id = 3
     AND t1.id = t2.t1_id
     AND t2.id = t3.t2_id

SELECT t4.*, t3.id, t3.t2_id, t1.id, t2.t1_id, t2.id
FROM table1 t1, table2 t2, table3 t3, table4 t4
WHERE t1.id = 3
     AND t1.id = t2.t1_id
     AND t2.id = t3.t2_id
     AND t3.id = t4.t3_id