title: What is the best way to fetch a single record via an OleDbConnection?
body: <p>C#, .Net 2.0:  I have a class that wraps a single record from a database accessed via an OleDbConnection object.  It is quite simple, it executes a "SELECT * FROM table WHERE key = {some value};" and then exposes the fields as properties with a few methods to manipulate the data.  When I create a new instance of this object, the code that gets executed looks like:</p>
  
  <pre><code>        DataSet ds = new DataSet();
          ds.Locale = CultureInfo.InvariantCulture;
          OleDbDataAdapter da = new OleDbDataAdapter(cmd);
  
          if (cmd.Connection.State != ConnectionState.Open)
          {
              cmd.Connection.Close();
              cmd.Connection.Open();
          }
  
          da.Fill(ds);
  
          return ds.Tables[0];
  </code></pre>
  
  <p>cmd is an OleDbCommand object passed to the method. When I execute this, around 95% of the time it takes to create the object is in the da.Fill(ds) call, according to the VS 2008 profiler.  </p>
  
  <p>I also have a class that represents a collection of these objects that implements IEnumerable, and when iterating that object using foreach, each single record object is created on the fly and those da.Fill(ds) statements add up quickly.</p>
  
  <p>My question is, is this the best way to fetch a single record?  Alternately, is there a more preferred way to implement the collection object so iterating it does not take so long?</p>
  
  <p>Thanks</p>