Cleaning up after myself

How should I go about releasing any objects I've created? I've got a very complex CLR stored proc that creates a dataset, many sqlcommand objects, an adapter, etc, etc. It's used to evaluate high school transcripts for completion of the core subjects. It's a very complex SP that takes a single SSN as a parameter.

Right now it works as expected when I call it from a query window in SQL Mgmt Studio, I can stack 39 calls in a row like:

exec usp_Evaluate_eTranscript '011...'
exec usp_Evaluate_eTranscript '012...'
...
exec usp_Evaluate_eTranscript '039...'

And if I build a little test harness in VB.Net calling it in a populated dataset For ... Next loop, it works just fine for the 39 valid values I have.

But, when I build a cursor in a query window in MgmtStudio I get some strange behavior, that just doesn't really make sense. I'm getting a foreign key violation and I'm not even trying to insert any rows, just updating existing ones.

So, the only possible cause that I haven't eliminated yet is that some objects are still hanging around when the cursor tries to call the SP again.

It's declared:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub usp_Evaluate_eTranscript(ByVal SSN As String)

And exits with the standard End Sub.

Is there some way I can help the garbage collector along? Kick it into action? Maybe some

Set sqlcommand = Nothing or
sqlcommand.Dispose

dataset.Clear or
dataset.Dispose or somesuch thing?

I'm closing the context connection immediately after each time I use it (I have to so the next object can work).

? You don't have to worry about releasing objects. That's what the garbage collector is for... But it sounds like you're not properly closing your connections. I recommend wrapping all connections in a "Using" block: Using conn As New SqlConnection(connection_string) '... do what you need to do End Using This will automatically call the Dispose() method on the connection when your code is done with it, which will close the connection immediately -- instead of waiting for the GC to come along and finalize the object... And it frees you from having to remember to call Close() all the time. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- ""R3dD0g"@.discussions.microsoft.com" <"=?UTF-8?B?UjNkRDBn?="@.discussions.microsoft.com> wrote in message news:f9ac67cf-3bcc-4519-86c7-1b44c648ec1f@.discussions.microsoft.com... How should I go about releasing any objects I've created? I've got a very complex CLR stored proc that creates a dataset, many sqlcommand objects, an adapter, etc, etc. It's used to evaluate high school transcripts for completion of the core subjects. It's a very complex SP that takes a single SSN as a parameter. Right now it works as expected when I call it from a query window in SQL Mgmt Studio, I can stack 39 calls in a row like: exec usp_Evaluate_eTranscript '011...'exec usp_Evaluate_eTranscript '012...'...exec usp_Evaluate_eTranscript '039...' And if I build a little test harness in VB.Net calling it in a populated dataset For ... Next loop, it works just fine for the 39 valid values I have. But, when I build a cursor in a query window in MgmtStudio I get some strange behavior, that just doesn't really make sense. I'm getting a foreign key violation and I'm not even trying to insert any rows, just updating existing ones. So, the only possible cause that I haven't eliminated yet is that some objects are still hanging around when the cursor tries to call the SP again. It's declared: <Microsoft.SqlServer.Server.SqlProcedure()> _Public Shared Sub usp_Evaluate_eTranscript(ByVal SSN As String) And exits with the standard End Sub. Is there some way I can help the garbage collector along? Kick it into action? Maybe some Set sqlcommand = Nothing orsqlcommand.Dispose dataset.Clear ordataset.Dispose or somesuch thing? I'm closing the context connection immediately after each time I use it (I have to so the next object can work).|||

I am wrapping all the DB calls inside a Using conn as New SQLConnection ... End Using, additionally, I'm specifically opening and closing the connection inside the using. When I forgot to close it there were problems - I'm hitting the database in about 8 different ways and times.

|||

Avoid calling the Dispose() method.

There seems to be a lot of confusion about the SqlConnection.Close() and SqlConnection.Dispose() methods.

For SqlConnection objects, 'closing' a connection simply returns it to the connection pool - the connection is not really closed. This behavior is desirable. Opening and closing connections is a processor intensive task, which is why connection pooling is so important for writing scalable applications. If you call Dispose() on a connection object, it is first returned to the connection pool. This is because the Dispose() method includes a call to the Close() method. Once the connection object is back in the pool, it releases all unmanaged resources (i.e., the database handle). By releasing its database handle, the connection is rendered useless, and is simply kicked out of the connection pool.

Calling Dispose() effectively destroys the purpose of connection pooling, which is to promote connection reuse. It is hard to imagine a valid reason for calling Dispose() at all. If you really don't want to use connection pooling, you can simply disable it in your connection string (easier to turn it back on from here if you change your mind later). If you want to cleanup after you're done using a connection, simply call the Close() method. The connection is returned to the connection pool, and will be available for reuse next time you application calls the Open() method.

In general, if an object provides both Open() and Close() methods, then it should be obvious the designers intended the pair to be used used together. If you 'open' an object, then logically you would want to 'close' it after you're done.

I hope this helps clear some of the confusion surrounding Close() and Dispose(). For more information, please consult the .NET Framework documentation.

|||

CyberGuru wrote:

Calling Dispose() effectively destroys the purpose of connection pooling

What make you think that a disposed connection does not remain available in the connection pool? All the available documentation would suggest that it does (e.g.:http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx), and all tests I've run would seem to indicate the same. Do you have any evidence to the contrary?

CyberGuru wrote:

In general, if an object provides both Open() and Close() methods, then it should be obvious the designers intended the pair to be used used together. If you 'open' an object, then logically you would want to 'close' it after you're done.

In most cases, "Close" methods simply provide a facade for disposition. They're generally meant to make folks who like Open/Close symmetry feel comfortable, not to provide clean-up mechanism that differs from Dispose. Designing a Close method that is meant to be called instead of the Dispose method for a class that implements IDisposable would probably be considered a design error by most folks, particularly given that it interferes with use of the using statement.

sqlsql