> I'm porting a Java application to .NET. And it seems that
> ADO.NET is not as good as JDBC in some ways.
> In Java/JDBC you can have several open ResultSets with the
> same connection. In ADO.NET you have to close an open
> DataReader before you can open the next DataReader with the
> same connection.
>
> What I want to do:
>
> - I want to read objects with an 1:m relation (I call them "1-objects"
> and "m-objects").
> - I want to read the 1-objects one by one and read for every
> 1-objects the associated m-objects.
> - I don't want to read *all* 1-objects first before I
> start to read associated "m-objects".
> - I want to read all objects (1 and m) in the same transaction.
retrieval code in transactions is generally not a good idea
because it hurts performance and can cause deadlocks on some RDBMS's
(sqlserver). They also don't have to be in a transaction, since they're
not manipulating any data that has to roll back.
> I could use a second connection for reading m-objects but
> then I'm not in the same transaction as with the first connection.
I think this is not a big deal, I'd opt for a second connection
for a quick dataretrieval and keep my manipulation code in another
transaction.
Of course if you want to perform 'dirty reads', i.e. reads of
data you just manipulated through code running in the same transaction,
you have to perform the selects in the same transaction. But rule of
thumb is that only for multiple DML actions, executed as batch a
transaction is worth the effort and the performance dip.
> How can I do in ADO.NET what I can do with JDBC? Any solution
> to circumvent this limitation in ADO.NET?
Any datareader will block the connection. So you OR have to read
the '1' objects in blocks in datatables using an adapter and then read
the 'm' objects with datareaders (however you can then also use 1 reader
;)).
But if I were you, I'd seriously think about moving the
retrieval code outside the transaction, because it's not necessary to
run retrieval code inside a transaction.
FB
-----------------------------------------------------------------------
Solutions Design : http://www.sd.nl
My open source .NET Software : http://www.sd.nl/software
My .NET Blog : http://weblogs.asp.net/FBouma
-----------------------------------------------------------------------
