Does SqlDataAdapter.Dispose actually Close an associated SqlConnection?

Question

Does anyone know if the SqlDataAdapter.Dispose method actually closes or disposes any SqlConnections? I loaded up Reflector and I see that SqlDataAdapter inherits from DbDataAdapter. If I disassemble and look at the dispose method in that class, there appears to be no disposal of any SqlConnections. I suppose I could write a test for this, but I figured I would ask to see if anyone had any insight on this.

Answer 1

Nope, it doesn't dispose the connection. I believe it shouldn't. You might want to use it elsewhere.

Answer 2

As far as I know it does not. I use nested Using statements to achieve this, create the connection first, then create the adapter and as the using statements "pop", the adapter is Disposed, then the connection is Disposed, which does trigger a close:

Using conn as New SqlConnection("")
    Using adapter as New SqlDataAdapter() With {.Connection = conn}
        'Do stuff'
    End Using
End Using

The syntax is virtually identical for C#, if that's your language of choice.

Answer 3

The first thing to be aware of is that the DataAdapter does manage and close your connection in some circumstances. For example, if you're using a DataAdapter you're probably operating on DataTables/DataSets using the .Fill() and .Update() functions.

From the .Fill() docs:

The connection object associated with the SELECT statement must be valid, but it does not need to be open. If the connection is closed before Fill is called, it is opened to retrieve data, then closed. If the connection is open before Fill is called, it remains open.

The .Update() docs don't mention anything about the connection at all, so I would expect to need to manage it manually.

Now you asked specifically about the Dispose() method. Like Update, the Dispose() docs don't specifically mention the connection, so I would expect to need to close it manually.

Finally, we can improve on Bob King's code slightly like this:

Using conn as New SqlConnection(""), _
      adapter as New SqlDataAdapter() With {.Connection = conn}
    ''//Do stuff
End Using

Or in C#:

using (SqlConnection conn = new SqlConnection(""))
using (SqlDataAdapter adapter = new SqlDataAdapter() {Connection = conn})
{
    // Do stuff
}

Not 100% I got the initialize syntax for the adapter right, but I typed it directly into the reply window. I'll fix it later if needed.