We help IT Professionals succeed at work.

ADO.NET and garbage collecting

DonBartholomew
on
I have a  VB.NET prog that checks a number of tables for records belonging to a specific person.
It's an Oracle db.
Obviously, I use a lot of recordsets in the process.
I free them using the ADO 2.7 dispose method.

At a certain point, I get an error:

ORA-01000: maximum open cursors exceeded
and
ORA-00604: error occurred at recursive SQL level 1

Sure I can increase the maximum number of open cursors for the connection, but that's not the point.
It seems that the dispose method does not free the recordsets. The ADO com objects still live in the eyes of the database. Before the prog was converted to VB.NET, I freed them the regular VB6 way, just set them to nothing.

SO MY QUESTION IS:
What does this dispose method do exactly if it doesn't force the object to be garbage collected ?
I don't want to use the System.GC.Collect function every time.
In a .NET article I encountered the function
System.Runtime.InteropServices.Marshal.ReleaseComObject()
Should I use this one ? In that case, do I still have to use the dispose method or set-to-nothing ?

Help would be appreciated.
Thx, Bart.

Note: I use a lot of commands too. These are freed the old way: set them to nothing, the Garbage Collector can do the rest. Normally this shouldn't affect the situation
described, right ?
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
GOLD EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
i guess the main "problem" is the interval at which the gc works...
In vb, there was no GC, so all the ressources where definitively freed when leaving the application, in Vb.net, there is a garbage collector (which is only good for bad developers IMHO)...

CHeers

Author

Commented:
But I don't want to wait till the GC decides it's a good time for cleanup. I want to enforce a garbage collecting of the ADO objects only. They're no longer reachable by the application, but the database doesn't consider them dead yet.

There MUST be a more elegant way to to create and destroy as many recordsets as you like on a single connection, without having to do a System.GC.Collect after every 10 items. Otherwise, ADO simply wouldn't be 'workable' anymore.

Anyone ?
Commented:
>>I want to enforce a garbage collecting of the ADO objects only

Not possible...you can only force collection of a particular generation.


>>What does this dispose method do exactly if it doesn't force the object to be garbage collected ?

It performs the "internal cleanup" of the object, and suppresses the finalize method of the object from being called.


After calling dispose, also try and explicitly set the item to Nothing:

rs.Dispose()
rs = Nothing


There may be some property to tell the GC how often to collect automatically for you (instead of it waiting until its out of stack space).  In addition, notify MS...there is a reason this thing is still only in the BETA phase and they highly encourage feedback on the product.
BRONZE EXPERT
Top Expert 2012

Commented:
You can force garbage collection with the GC.Collect method.

angelIII

>> In vb, there was no GC, so all the ressources where definitively freed when leaving the application,
in Vb.net, there is a garbage collector (which is only good for bad developers IMHO)... <<

I beg to differ.  VB and Basic before that, unlike C, have always had garbage collection.  There is no equivalent of malloc() in VB.  If there was no garbage collection you would very soon run out of memory!

In VB.NET garbarge collection is implemented differently to do away with the current reference counting scheme as used in VB6 (and also used in COM).  This caused problems when there were circular references: a child object has a reference to a parent object, and the parent object has a reference to the child object.

Anthony


BRONZE EXPERT
Author of the Year 2009

Commented:
Hi DonBartholomew,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Accept vblogic's comment(s) as an answer.

DonBartholomew, if you think your question was not answered at all or if you need help, just post a new comment here; Community Support will help you.  DO NOT accept this comment as an answer.

EXPERTS: If you disagree with that recommendation, please post an explanatory comment.
==========
DanRollins -- EE database cleanup volunteer
Per recommendation, force-accepted.

Netminder
CS Moderator