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

You could use a data reader:

using (var connection = new OleDbConnection(connectionString))
{
    connection.Open();

    var command = new OleDbCommand(queryString, connection);
    var reader = command.ExecuteReader();

    var person = new Person();
    if (reader.Read())
    {
        person.Name = reader["Name"].ToString();
        person.Age = Convert.ToInt32(reader["Age"]);
    }

    return person;
}

Answer 2

If you are expecting just one result, you could use ExecuteScalar

It returns the first column of the first row.

Answer 3

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.