Proper resource deallocation through Dispose()

[Michael Russell]

Hi All,
Trying to get a grip on the proper deallocation of unmanaged resources.
I asked some general questions in another group, here's my ADO
question: If I call Dispose on a DbDataAdapter, for example, am I
correct in understanding that it will dispose of the DbConnection that
it uses? This statement in the .Net 2.0 documentation for
DbDataAdapter.Dispose() seems to imply that it does: "Releases all
resources used by the Component".

It would appear that I'm going to run into problems in this scenario: I
have a single DbConnection as an instance variable in a class. Within
that class, I have a method that creates, and then disposes of, a
DbDataAdapter that uses this connection. The second time I call that
method, it will fail because the disposal of the adapter also disposed
of the connection (as opposed to just closing it).

Or am I totally misinterpreting the documentation?

 

[Michael Russell]

Ok, here's a concrete example of what I was asking about. I have a
class with a DbDataAdapter instance variable, which is used throughout
the class. I have an Update method, and in this method I create a
DbCommandBuilder and assign the dataAdapter to it. When I call
Dispose() on the builder at the end of the method, though, it also
disposes of the dataAdapter.

My question: How do I dispose of the CommandBuilder without also
disposing of the DataAdapter it uses?

Off the top of my head, I can think of two ways of doing this, neither
of which are very palatable:
1) Make the CommandBuilder an instance variable
2) Assign a "dummy" DataAdapter to the builder just before I dispose of it.

 

[Edwin Knoppert]

Combine the two in a custom class having a single (of course) disposed
interface?
I do that for the common database for an application.

 

[Francois Bonin [C# MVP]]

It would appear that I'm going to run into problems in this scenario: I
have a single DbConnection as an instance variable in a class. Within
that class, I have a method that creates, and then disposes of, a
DbDataAdapter that uses this connection. The second time I call that
method, it will fail because the disposal of the adapter also disposed of
the connection (as opposed to just closing it).

Calling SqlConnection.Dispose() does exactly the same thing as calling
SqlConnection.Close().
So calling Dispose() on your DbDataAdapter will not cause the method to fail
the second time.

HTH,
Cois