.NET OleDBDataReader storing read queries

Question

I am looking for the best practices in storing OleDBDataReader's reads. Essentially I want it to retain that same dictionary like reader["Column"]. I am writing an API that returns a data structure made up of "rows." I feel like there must be a better solution than creating an ArrayList of dictionaries but I cannot seem to find a "best practice" for this.

The code below is taken from my current project

using (var commandToQueryDB = new OleDbCommand(query))
{
     commandToQueryDB.Connection = Connection;
     Connection.Open();
     var reader = commandToQueryDB.ExecuteReader(); 
     while (reader.Read())
     {
          //Insert reader's read in some sort of data structure
     }
}

I would like to be able to iterate through the queries and then access each query as a dictionary (e.g query["DistrictName"] if I had a table with DistrictName as a column)

Answer 1

A reader is a "pipe", not a "bucket" - the reader API is not suitable for disconnected data. For that, it depends on whether you know the schema of the data.

If you do know the schema at compile-time, then populate a typed class model - just a List<Foo> will do nicely. There are tools that can make this even simpler, handling the member population for you, etc; "dapper" leaps to mind (although I am biased).

If you do not know the schema in advance, then DataTable may be suitable. I don't usually recommend it, but it does the job here. Just:

table.Load(reader);

is enough to populate a DataTable including schema (columns) and values (rows / cells).