Use foreign key constraint with a loop ?
-
Hi, I have a DataSet with two tables that have a common field. Table 1 contains order header data and table 2 order row data. Both tables contain data of multiple orders. I want to handle each order separately, and my current method is a loop inside a loop and a "manual" check. For example something like this:
For Each rwH as DataRow in ds.Tables("H").Rows
'Get some header data
'...
For Each rwR as DataRow in ds.Tables("R").Rows
If rwH.Item("KeyField").ToString = rwR.Item("KeyField").ToString Then
'Get the relevant row data
'...
End If
Next
NextThe question is, is there a way to avoid having to loop through the rows, and use this sort of crudish comparison ? I was thinking maybe creating a foreign key constraint. However, although I know how to create the constraint, I do not know how to use it to my advantage with any loop (assuming it is possible at all) ? Or can someone suggest an alternative (better) method ? All input is appreciated. Cheers, Johan
My advice is free, and you may get what you paid for.
-
Hi, I have a DataSet with two tables that have a common field. Table 1 contains order header data and table 2 order row data. Both tables contain data of multiple orders. I want to handle each order separately, and my current method is a loop inside a loop and a "manual" check. For example something like this:
For Each rwH as DataRow in ds.Tables("H").Rows
'Get some header data
'...
For Each rwR as DataRow in ds.Tables("R").Rows
If rwH.Item("KeyField").ToString = rwR.Item("KeyField").ToString Then
'Get the relevant row data
'...
End If
Next
NextThe question is, is there a way to avoid having to loop through the rows, and use this sort of crudish comparison ? I was thinking maybe creating a foreign key constraint. However, although I know how to create the constraint, I do not know how to use it to my advantage with any loop (assuming it is possible at all) ? Or can someone suggest an alternative (better) method ? All input is appreciated. Cheers, Johan
My advice is free, and you may get what you paid for.
Why not have a query that joins the two tables and groups by
KeyFieldor something of that nature?""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
-
Hi, I have a DataSet with two tables that have a common field. Table 1 contains order header data and table 2 order row data. Both tables contain data of multiple orders. I want to handle each order separately, and my current method is a loop inside a loop and a "manual" check. For example something like this:
For Each rwH as DataRow in ds.Tables("H").Rows
'Get some header data
'...
For Each rwR as DataRow in ds.Tables("R").Rows
If rwH.Item("KeyField").ToString = rwR.Item("KeyField").ToString Then
'Get the relevant row data
'...
End If
Next
NextThe question is, is there a way to avoid having to loop through the rows, and use this sort of crudish comparison ? I was thinking maybe creating a foreign key constraint. However, although I know how to create the constraint, I do not know how to use it to my advantage with any loop (assuming it is possible at all) ? Or can someone suggest an alternative (better) method ? All input is appreciated. Cheers, Johan
My advice is free, and you may get what you paid for.
Soooo, you're going to look at the same set of records (inner loop) over and over again, until you match them up with the records in the outer loop?? How many records in the outer loop again?? That seems AWFULLY inefficient. Why not just create a SQL query that matches everything up for you?
A guide to posting questions on CodeProject[^]
Dave Kreskowiak -
Hi, I have a DataSet with two tables that have a common field. Table 1 contains order header data and table 2 order row data. Both tables contain data of multiple orders. I want to handle each order separately, and my current method is a loop inside a loop and a "manual" check. For example something like this:
For Each rwH as DataRow in ds.Tables("H").Rows
'Get some header data
'...
For Each rwR as DataRow in ds.Tables("R").Rows
If rwH.Item("KeyField").ToString = rwR.Item("KeyField").ToString Then
'Get the relevant row data
'...
End If
Next
NextThe question is, is there a way to avoid having to loop through the rows, and use this sort of crudish comparison ? I was thinking maybe creating a foreign key constraint. However, although I know how to create the constraint, I do not know how to use it to my advantage with any loop (assuming it is possible at all) ? Or can someone suggest an alternative (better) method ? All input is appreciated. Cheers, Johan
My advice is free, and you may get what you paid for.
You can at least eliminate the nested loop with a SELECT:
dim drRows() as DataRow = ds.Tables("R").Select("KeyField = '" & rwH.Item("KeyField").ToString & "'")
If drRows.Length > 0 Then
'do your thing
End If -
Soooo, you're going to look at the same set of records (inner loop) over and over again, until you match them up with the records in the outer loop?? How many records in the outer loop again?? That seems AWFULLY inefficient. Why not just create a SQL query that matches everything up for you?
A guide to posting questions on CodeProject[^]
Dave KreskowiakIt is awfully inefficient, hence the question. :) In this scenario I need to create orders into an ERP system via a proprietary driver. That means that I need to declare a new "order" object, and fill in all its properties. Some header data (customer name, and address, and such), and an unknown number of rows (product number, quantity, price, etc.). In addition I need to translate that same information into an email message. So if I combine both header and row data into a single query, I would have to populate the header data of the order object over and over, for each row, or build in some kind of is-it-already-populated method. Somehow that does not seem very efficient (or elegant) either (I may very well be wrong of course). For clarity, the header table will generally only contain between 1 and 10 records, the rows table however can contain anywhere between 1 and 150 records per header record. Eliminating the nested loop is therefore my main concern here.
My advice is free, and you may get what you paid for.
-
You can at least eliminate the nested loop with a SELECT:
dim drRows() as DataRow = ds.Tables("R").Select("KeyField = '" & rwH.Item("KeyField").ToString & "'")
If drRows.Length > 0 Then
'do your thing
End IfThanks, I didn't know about this one. It seems to be exactly what I need.
My advice is free, and you may get what you paid for.
-
Why not have a query that joins the two tables and groups by
KeyFieldor something of that nature?""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
Please see my answer to Dave's post.
My advice is free, and you may get what you paid for.