DataTable with nested Child Tables??
-
Let's assume I have three tables, where Table1 is the topmost table, Table2 is a child relation to Table1 and Table3 is a child relation to Table2. I'd like to get all rows in Table1 where a column in Table3 is a particular value. This is fairly straight forward with an SQL query, but I can't figure out how to do it with DataTables/DataSets. Any suggestions? The only solution I've been able to come up with involves getting every row in Table1, and then GetChildRows twice to get to Table3, and testing the column of interest in Table3... foreach (DataRow row in Table1.Rows) { if (row.GetChildRows("Table1-Table2")[0].GetChildRows("Table2-Table3")[0]["ColumnOfInterest"] == something) { This is one of the desired rows, do something with it } } Seems very cumbersomb. You'd think there'd be a way to execute a single "query" that would return the desired set of rows...
-
Let's assume I have three tables, where Table1 is the topmost table, Table2 is a child relation to Table1 and Table3 is a child relation to Table2. I'd like to get all rows in Table1 where a column in Table3 is a particular value. This is fairly straight forward with an SQL query, but I can't figure out how to do it with DataTables/DataSets. Any suggestions? The only solution I've been able to come up with involves getting every row in Table1, and then GetChildRows twice to get to Table3, and testing the column of interest in Table3... foreach (DataRow row in Table1.Rows) { if (row.GetChildRows("Table1-Table2")[0].GetChildRows("Table2-Table3")[0]["ColumnOfInterest"] == something) { This is one of the desired rows, do something with it } } Seems very cumbersomb. You'd think there'd be a way to execute a single "query" that would return the desired set of rows...
How about reversing the process, and preselecting just the columns of interest DataRow[] interestingRows = Table3.Select("columnOfInterest = something"); foreach(DataRow r1 in interestingRows) { DataRow r2 = r1.GetParentRow("Tabel2-Table3").GetParentRow("Table1-Table2"); // do something with this if not null } Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed. Carl Sandburg
-
How about reversing the process, and preselecting just the columns of interest DataRow[] interestingRows = Table3.Select("columnOfInterest = something"); foreach(DataRow r1 in interestingRows) { DataRow r2 = r1.GetParentRow("Tabel2-Table3").GetParentRow("Table1-Table2"); // do something with this if not null } Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed. Carl Sandburg
Thanks... That would definitely do the trick, and I'll play around with it, but to my question, is there some way to perform this operation with a single Select "like" call? Maybe there isn't a way to do this, and that's fine, I'll continue working with nested foreach constructs, but I keep thinking the disconnected DataSet should be able to perform operations that are possible with SQL. As I'm sure you're aware, the SQL equilalent of what I'm trying to do with the DataSet is as follows... SELECT * FROM TABLE1,TABLE2,TABLE3 WHERE TABLE1.Table2Link=TABLE2.Table1Link AND TABLE2.Table3Link=TABLE3.Table2Link AND TABLE3.ColumnOfInterest=something