Datatable object within class function + garbage collection?

[LeonR]

Sorry for the vauge title, it's a bit tricky to summarize in a line!

Basically I have written a class which will be responsible for my SQL transactions.
Within the class there is a function which you parse a query string to, the function will connect to the DB, execute the query and return the data as a datatable to the caller.


I have to create a new instance of a datatable everytime the function is called, if not the returned data (as a datatable) seems to point to the same place, so if I was to fill a datagridview it will populate fine, but then if i use the same function to populate another datagridview using a different query, the data will be lost in the first datagridview, but the schema still remains.

So here is a simple version of the code to explain it a little easier.

myClass

Function SQLquery(data)

dim myDT as new datatable

myDT=getSQLdata 

return DT

end function

end Myclass



** form code **

dim myObj as new myclass



sub button1_click

datagridview1.datasource = myobj.SQLquery("select * from table")   


end sub

I just typed the above quickly to help explain my question.


If I was to click button 1, say 10000 times, what happens to all those 'new objects' created in the function? Do they get removed by the garbage collector?


Sorry it's a little vauge , if its confusing I can try and re-explain

Is there a better way to do this? What I have works fine, I just wanted to check that it's 'aceptable' as I haven't used datatables much.

Thanks!

 

[JohnH]

The datatable created in function is the return value of the function, it is assigned as datasource for the grid, which then holds a reference to it. An object can not be collected as long as any part of executing code is referencing it. When a new datasource is assigned the previous datatable is no longer referenced and garbage collection can and will remove it from memory eventually. Datatable.Reset method can be used to clear all rows, columns and relations from it.

Other objects created in the method is local and when their scope ends, which could be at the end of the method, they are no longer referenced and is eligible for garbage collection also. That said, if these objects implement IDisposable you should call Dispose method before 'releasing' them, disposing is a standard client code feature that allow the objects to properly and timely release all their resources. Such objects, for example a SqlConnection instance, can be used and automatically disposed at end of scope with the Using statement.

This is perhaps more detail than you wished for, but I think it needs mentioning for this topic. If a released object has not been disposed it will still be removed from memory, though GC will never dispose it. The IDisposable pattern has a fallback with the Finalize method, this is a destructor method that is used to release unmanaged resources in case the object was not disposed. Common examples could be socket connections to a database server, file handles, or GDI objects. Sounds good, but problem is it could take a long time before GC goes to collect, and if object is not disposed and need to finalize it will go another round of its own, in all taking a while and in the mean time possible expensive resources remains locked.

 

[LeonR]

That is great JohnH, thankyou very much for explaing it clearly

It seems there is always something new to learn in .net, keeps you on your toes


The reason it confused me originally was because I added the returned datatable in to another datatable declared.. so

dim tempDT as new datatable
tempDT=myclassOBJ.myfunction("select * from tablename")
datagridview1.datasource=TempDT


I had this expectation that tempDT would of 'copied' (so to speak) the datatable to it, but I assume its been smart and just pointed the datasource straight to the parent of TempDT?


Thanks,
Leon

 

[JohnH]

dim tempDT as new datatable
tempDT=myclassOBJ.myfunction("select * from tablename")

You have to distinguish between declaring a variable and creating an object. Declaring a variable is done using the Dim statement where you specify a name and type, creating an object is done with the New keyword that invokes the class constructor. The first line I quoted is short for this declaration (green), object creation (blue) and assignment (=):

[COLOR="#008000"]Dim tempDT As DataTable[/COLOR] = [COLOR="#0000FF"]New Datatable[/COLOR]

What happens in line two is that the empty new datatable you created is discarded and tempDT variable is assigned the datatable returned from function. So creating an object in first line is completely pointless, it is not used for anything and you just throw it away. What you should have done is:

dim tempDT as datatable = myclassOBJ.myfunction("select * from tablename")

Type of myfunction is DataTable (you should be explicit about this (Function Something As DataTable). Since you use VB 2010 and preferably have type inference turned on you can skip the type declaration of the variable since the type of the assigned value is given and can be inferred;

dim tempDT = myclassOBJ.myfunction("select * from tablename")

Compiler here detects the type of object assigned, which is DataTable, and the variable will be that type also.

 

[LeonR]

Sorry John, the 'new' should not be there I typed it without double checking, really sorry about that as you spent a bit of time explaining it all.


Would you communicate to the SQL DB like this, tradionally?

 

[JohnH]

Would you communicate to the SQL DB like this, tradionally?

No, I'd use the wizards and designers if possible.