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

Question

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 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

Should your break be a continue? In other words, if its not equal continue searching? — Nix, Jul 15 '11 at 11:45
@Nix: I am sorry, I forgot to add 'currentJCounter = j;' in the break code block. I would like to break if its not equal as I know that next invoice# items would start from that point onwards. — sveerap, Jul 15 '11 at 11:51
It looks to me like you're trying to do the work that the database should be doing for you. Is there a reason you're implementing this join programatically instead of having it take place in the database? — Rick Liddle, Jul 15 '11 at 11:58
If applicable, I suggest you to put both DataTable in the same DataSet, then apply a DataRelation between both. http://stackoverflow.com/questions/331265/how-to-use-datarelation-to-perform-a-join-on-two-datatables-in-a-dataset — Larry, Jul 15 '11 at 12:06
@Rick : I am doing it programmatically to avoid the number of round trips to the database , otherwise for each invoice I have to call a procedure which returns the line items — sveerap, Jul 16 '11 at 04:25

Jin-Wook Chung · Answer 1 · 2011-07-15T12:01:57.657

0

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.

edited Jul 15 '11 at 12:01

answered Jul 15 '11 at 11:55

Jin-Wook Chung

4,196
1
26
45

Thorsten Dittmar · Answer 2 · 2011-07-15T12:18:59.020

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.

Thanks , I have sorted both the master table and details records table by invoice #. I am not sure but it seems LINQ will have performance problem as we performance 'select' operation for each invoice. I thought about using dictionary but my only concern is as we do 'hashing' internally, that would also consume time, correct me if I am wrong — sveerap, Jul 16 '11 at 04:27

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

2 Answers2