What's wrong with this code? If anything.

Question

Essentially I want to know if in VB.NET 2005 if using a sqlcommand and then reusing it by using the NEW is wrong. Will it cause a memory leak.

EG:

try

dim mySQL as new sqlcommand(sSQL, cnInput)

// do a sql execute and read the data 

mySQL = new sqlcommand(sSQLdifferent, cnInput)

// do sql execute and read the data
catch ...

finally

if mysql isnot nothing then
   mysql.dispose
   mysql = nothing
end if

EDIT: put try catch in to avoid the comments about not using them

Answer 1

Garbage collection will gather up the first new when it is run.

Only the second one you purposely dispose in the Finally block. The first one will be disposed of the next time the garbage collection is run.

I do not think this is a good idea. If the first command is not closed correctly it is possible you would have an open connection to the database and it will not be disposed.

A better way would be to dispose the first command after you are done using it, and then to reuse it.

Answer 2

No, the garbage collector will find the old version of mySql and deallocate it in due course.

The garbage collector should pick up anything that's been dereferenced as long as it hasn't been moved into the Large Object Heap.

Answer 3

Just to extend what Longhorn213 said, here's the code for it:

Using mysql as SqlCommand = new SqlCommand(sSql, cnInput)
  ' do stuff'
End Using

Using mysql as SqlCommand = new SqlCommand(otherSql, cnInput)
  ' do other stuff'
End Using

(edit) Just as an FYI, using automatically wraps the block of code around a try/finally that calls the Dispose method on the variable it is created with. Thus, it's an easy way to ensure your resource is released. http://msdn.microsoft.com/en-us/library/htd05whh(VS.80).aspx

Answer 4

Whilst garbage collection will clean up after you eventually the dispose pattern is there to help the system release any resources associated with the object sooner, So you should call dispose once you are done with the object before re-assigning to it.

Answer 5

Uh, to all those people saying "it's OK, don't worry about it, the GC will handle it..." the whole point of the Dispose pattern is to handle those resources the GC can't dispose of. So if an object has a Dispose method, you'd better call it when you're done with it!

In summary, Longhorn213 is correct, listen to him.

Answer 6

One thing I never worked out - If I have a class implementing IDisposable, but I never actually dispose it myself, I just leave it hanging around for the GC, will the GC actually call Dispose for me?

Answer 7

Be careful. If you have to do a lot of these in a loop it can be slow. It's much better to just update the .CommandText property of the same command, like this (also, you can clean up the syntax a little):

Using mysql as New SqlCommand(sSql, cnInput)
  ' do stuff'

    mySql.CommandText = otherSql

   'do other stuff'
End Using

Of course, that only works if the first command is no longer active. If you're still in the middle of going through a datareader then you better create a new command.