I have read too many articles showing the connection being opened and closed around a DataAdapter Fill() as in the code below. It does no harm, but it not necessary.
Don't do this...
SqlCommand command = new SqlCommand("usp_MyQuery_Select", this._con);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
try
{
this._con.Open(); //unnecessary
adapter.Fill(ds);
}
finally
{
this._con.Close(); //unnecessary
}
Do this...
SqlCommand command = new SqlCommand("usp_MyQuery_Select", this._con);
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
If you track SqlDataAdapter's Fill() method in Lutz Roeder's .NET Reflector, you will see that it ends up at DbDataAdapter's FillFromCommand() method. FillFromCommand() opens and closes the connection in a try-finally block. Making this unnecessary in your own code.
Disassembled from Reflector
private int FillFromCommand(object data, int startRecord, int maxRecords,
string srcTable, IDbCommand command, CommandBehavior behavior)
{
IDbConnection connection1 = DbDataAdapter.GetConnection(command, "Fill");
ConnectionState state1 = ConnectionState.Open;
if (MissingSchemaAction.AddWithKey == base.MissingSchemaAction)
{
behavior |= CommandBehavior.KeyInfo;
}
int num1 = 0;
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;
}
return num1;
}