Does SqlDataAdapter.Fill() always close the connection?

[M]

Hi,

Does SqlDataAdapter always close the connection (assuming connection was
closed before calling Fill()), even if an exception occurs while calling
Fill()?

Example:
try
{
myDataAdapter.Fill(myDataTable);
}
catch
{
// display some error message or something
}

or should I do this instead

try
{
myDataAdapter.Fill(myDataTable);
}
catch
{
// display some error message or something
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}

Thanks.

 

[BillGatesFan]

Yes You should close it.

 

[Brock Allen]

SqlDataAdapter manages the connection itself, meaning it opens the connection
and it closes the connection. If, OTOH, you tell it the connection and you
open the connection yourself, it detects this and it will not close the connection.
So since you opened it you're responsible for closing it.

 

[TDAVISJR]

The dataAdapter manages the connection so you don't have to close it. The
Adapter does all the work

 

[M]

Hi Brock,

In my example, I did not open the connection. I let SqlDataAdapter open the
connection. What I'm not sure though, is what happens if .Fill() generates
an exception. Will the adapter still close the connection or not? Maybe Fill
has its own try/catch/finally block and therefore will close the connection
in its own finally even if an error occurred; but that is what I'm not sure
about.

Thanks.

 

[M]

Even if Fill causes an exception? Thanks.

The dataAdapter manages the connection so you don't have to close it. The
Adapter does all the work

 

[Brock Allen]

Here's the code in the DataAdapter. QuietOpen and QuietClose open and close
the connection as long as it wasn't already open when you call Fill:

try
{
try
{
DbDataAdapter.QuietOpen(connection1, out state1);
using (IDataReader reader1 = command.ExecuteReader(behavior
| CommandBehavior.SequentialAccess))
{
if (data is DataTable)
{
return this.Fill((DataTable) data, reader1);
}
return this.Fill((DataSet) data, srcTable, reader1,
startRecord, maxRecords);
}
}
finally
{
DbDataAdapter.QuietClose(connection1, state1);
}
}
catch
{
throw;
}

 

[winladen]

I copied this from MSDN. Hope this help

The Fill method retrieves the data from the data source using a SELECT statement. The IDbConnection object associated with the select command must be valid, but it does not need to be open. If the IDbConnection is closed before Fill is called, it is opened to retrieve data and then closed. If the connection is open before Fill is called, it remains open.

from : msdn.microsoft.com/en-us/library/zxkb3c3d.aspx