Can ADODB.RecordSet iterating order change during iteration

user_11864737 0
Viewed 43

I have a following legacy code chunk, that I need to rewrite in c#:

Dim dif As Integer
Dim rstTemp As ADODB.Recordset
Set rstTemp = database.OpenRecordset("select * from TABLE order by VALUE desc")
Set dif = <input>
Do Until dif = 0
    rstTemp.Fields("VALUE") = rstTemp.Fields("VALUE") - 1
    rstTemp.Update
    rstTemp.MoveNext
    dif = dif - 1
Loop

There is no check for rstTemp.EOF, so it looks to me, that the update changes the iterating order during iteration and it always jumps to the first one? The dif can be a lot bigger, than the number of records, so it has to loop itself somehow. Would this C# code be equivalent?

int dif = <input>;
while (dif != 0)
{
    var record = _context.TABLE.OrderByDescending(x => x.VALUE).First();
    record.VALUE--;
    _context.SaveChanges();
    dif--;
}
vb.net database c#
1 Answers

That code is very different; in the old (VB6?) version, you query once; in the C# version it might query once (if _context spots some kind of caching optimization), but it might (and probably will) query once per loop iteration - and it will almost always get the same row each time, rather than taking successive records from the query. At a minimum, you could try:

int dif = <input>;
foreach (var record in _context.TABLE.OrderByDescending(x => x.VALUE).Take(dif))
{
    record.VALUE--;
    _context.SaveChanges();
    if (--dif == 0) break;
}

However, because of how batching works, it might make more sense to just do a single SaveChanges() at the end.

As to your actual question, which I think relates to the interaction of the ORDER BY and in-progress updates; that will depend a lot on isolation level and other settings; I can only say "probably not, but ... maybe"! Perhaps the real trick here is to understand what the original code was trying to achieve (rather than how it did it). In particular, this seems like something that could be offloaded entirely server side, without ever involving fetching records over the network - with suitably crafted SQL. This would also give you much better atomicity without lots of round-trip overhead leading to a long-running transaction.

Marc_Gravell_233540
Related