Aug 102005
 

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;
}


 Leave a Reply

(required)

(required)


7 − seven =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>