c# datatable - Selecting detail records from datatable for each master record effectively

I have a master datatable with invoice records sorted by invoice# . And I have another datable with details (line item information) for all those invoice records sorted by invoice no & line item no.

How can I effectively create an array of invoice objects from these two tables. For me it looks like creating nested for loops ..The outer for loop traverses the orders table and the inner for loop navigates through the line items table

And I will keep a temp identifier which holds 开发者_JAVA技巧the current invoice# and once it finds a different invoice# in the nested for loop , it will exit the inner loop .

Below is the pseudo code which I am trying to do

long currentInvoiceNumber=-1;  
int CurrentJCounter=0;
for(int i=0;i<MasterTable.Rows.Count;i++)       
{
    currentInvoiceNumber=MasterTable.Rows[i]["invoiceno"];
    Order oOrder = new Order();
   for(int j=CurrentJCounter;j<ItemDetailsTable.Row.Count;j++)
    {
         if(currentInvoiceNumber!=ItemDetailsTable.Rows[j]["invoiceno"])
          {
                 currentJCounter = j;
                break;
          }
          else
          {
            oOrder.AddItem(ItemDetailsTable.Rows[j]);
           }

    } 
    lstOrders.Add(oOrder);
} 

I have to improve the performance of the appln and i think there would be definitely a better approach than this. Please suggest

Thanks, Sveerap

To improve, you should order the second table with interested fields, which is the inner loop target table(ItemDetailsTable). Of course, it causes negative effect, but enables to avoid the unnecessary inner looping on conditions - great than/less than.

I don't know whether, if you use LINQ to Object/DataSet, it will be applied automatically or not.

APPROACH A:
You could use LINQ to determine a list of all rows for an invoice like:

var invoiceRows = (from r in ItemDetailsTable.Rows where r["invoiceno"].Equals(currentInvoiceNumber) select r);

(Please note that I've not tested this right now - you might have to fiddle with the LINQ statement a bit or might have to use typed datasets in the first place.)

Then you could loop over the results:

foreach (var invoiceRow in invoiceRows)
{
    oOrder.AddItem(invoiceRow);
}

However, I have no idea whether this would be a performance increase - it might "look" more elegant when reading the code, however ;-)

As for exiting the inner loop: is it given that the ItemDetailsTable rows are "sorted" by invoice number? If not, you might be missing some rows for invoice A if a row for invoice B is between two rows for invoice A, as in

Row 1 for invoice A
Row 2 for invoice A
Row 1 for invoice B
Row 3 for invoice A
Row 2 for invoice B
...

APPROACH B
Another approach could be the following: Declare a dictionary, with the invoice number as the key, like

Dictionary<string, Order> orderList = new ...;

Then, iterate the lines only:

for (int j = 0; j < ItemDetailsTable.Row.Count; j++)
{
   if (!orderList.ContainsKey(ItemDetailsTable.Row[j]["invoiceno"]))
       orderList[ItemDetailsTable.Row[j]["invoiceno"]] = new Order();

   Order oOrder = orderList[ItemDetailsTable.Row[j]["invoiceno"]];
   oOrder.AddItem(ItemDetailsTable.Rows[j]);
}

In the end you can access all invoices by key (invoice number) or directly using the value collection of the dictionary.

EDIT
As a note to approach b:

In the end the dictionary may not contain all invoice numbers from MasterTable - if there's no line for an invoice number, no entry will be added. You could get around this by adding one entry for each invoice number from MasterTable before iterating the lines.

Also, the dictionary may end up containing invoice numbers that are not in MasterTable. These would not be included in your nested-loop approach. By adding the invoice numbers in a separate loop before iterating the lines and then modifying my code sample, so that no new items are created in the dictionary if the invoice number is not yet in the dictionary, you could also prevent this.