What is the best way to fetch a single record via an OleDbConnection?

Question

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:

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

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.

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.

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?

Thanks

Answer 1

Use OleDbDataReader and consider using CommandBehavior.SingleRow.

using (OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
    if (reader.Read())
    {
        // Bind your object using the reader.
    }
    else
    {
        // No row matched the query
    }
}

SingleRow provides a hint to the underlying OLEDB provider that allows it to optimize how it processes the result.