Friday, August 03, 2007

Looping through a .NET DataSet

Abstract: How to keep track of where you are in your data
Somebody asked me how to get the current record in a .NET dataset. I didn't know the answer, but it couldn't possibly be that hard to figure out. Or, at least, so I thought.
Any Delphi programmer can whip up this code in a matter of seconds:
  Table1.First;    while not Table1.EOF do begin      ListBox1.Items.Add(Table1['NAME']);      Table1.Next;    end;  
This code is the good old Delphi way of simply sticking all the country names in a listbox.
I started checking out the documentation for the .NET framework. I found a property on the DataGrid class that looks interesting - CurrentRowIndex.
As it turns out, that's great if I have a DataGrid on my form. But, what if I don't? Then I can't use this property. However, it would make sense if DataSet had a similar property.
To make a long search short, it turns out that DataSet doesn't have this property (or anything like it), and neither does DataSet.Tables. There's no concept of a current row on this component.
Very strange. What to do?
I almost gave up. Then somebody said CurrencyManager...
No, it's not for managing all those different bills you collect during all those international trips. It's for managing the current record in a dataset.
Excellent. I set off again.
The result is this code:
  var      cm : CurrencyManager;    begin      cm := BindingContext[DataSet1,'COUNTRY'] as CurrencyManager;      cm.Position := 0;      while True do begin        ListBox1.Items.Add(DataSet1.Tables['COUNTRY'].Rows[cm.Position].Item['NAME'].ToString);        if cm.Position = cm.Count-1 then          Break;        cm.Position :=  cm.Position+1;      end;    end;  
There are no methods on a CurrencyManager to go to the first, next or last record. Neither is there anything like EOF. So we have to do it all ourselves. And therefore the code weighs in a bit heavier than expected.
Then Joanna (thanks!) said that of course I should use an iterator for this example. Ah, yes. I'd completely forgotten about such things, as they are not in VCL. However, they are definitely there in .NET. It's however interesting that the .NET framework help didn't take me in that direction at all.
Of course, the original question was how to get ahold of the current record. As you can see, there are many ways of doing it, and how you do it, probably depends on what your code is doing.
To complete my particular example, we get this:
  with DataSet1.Tables['COUNTRY'].Rows.GetEnumerator do      while MoveNext do         ListBox1.Items.Add(DataRow(Current).Item['NAME']);  
Since we're playing with the Rows property, you can of course do this as well:
  var      i : Integer;    begin      with DataSet1.Tables['COUNTRY'].Rows do        for i:=0 to Count-1 do          ListBox1.Items.Add(Item[i]['NAME']);    end;  
Or, since we have the very cool VCL for .NET, we can simply not do any conversion of code whatsoever, and simply do it this way:
  Table1.First;    while not Table1.EOF do begin      ListBox1.Items.Add(Table1['NAME']);      Table1.Next;    end;  

Yahoo! oneSearch: Finally, mobile search that gives answers, not web links.