Lightweight readonly alternative to DataTable for storing data from SqlDataReader?

Question

I am suing SqlDataReader to read data from SQL Server. However sometimes I want to cache my data in memory as read only lightweight connection free object. I couldn't find any options for me in the BCL. Do I need to write my own implementation of such container, or are any available?

What alternatives do I have for storing data from database in lightweight form? For me DataTable is not a candidate at all.

EDIT:

When I know what data I am selecting a can easily use LINQ to transform my reader into List of or something. But instead I would like to be able to cache data which I don't know the structure of.

Answer 1

The best choice would be to have a type that represents your data columns, and then just construct an instance of that class for each row, fill its properties with the values from the reader, and store the instances in a List<YourClass> structure.

Something like:

using(SqlDataReader rdr = sqlCommand.ExecuteReader())
{
    List<YourClass> resultList = new List<YourClass>();

    while(rdr.Read())
    {
         YourClass work = new YourClass();

         // set the properties        
         work.Property1 = rdr["Column1"].Value;
         .....
         work.PropertyN = rdr["ColumnN"].Value;

         resultList.Add(work);
    } 
}

That's basically at its core what any ORM (object-relational mapper) does - read the data from the database using the fastest possible way (DataReader) and construct .NET objects from it.

Marc

Answer 2

Generally you would use a so called Data Transfer Object or a DTO to represent relational data in your application. DTOs are very dumb object that contain no behaviour and only serve as an intermediary between the busoness logic and the database.

So if you have a User table like so:

CREATE TABLE User (
  ID int IDENTITY(1,0),
  FirstName nvarchar(256),
  LastName nvarchar(256)
)

Your DTOs will be something like this (C# 3.5):

public class User
{
  int ID {get; set;}
  public string FirstName {get; set;}
  public string LastName {get; set;}
}

public class Users : List<User>
{
   public Users() : base() {}
}

Answer 3

List<object[]>

seems about as lightweight as you can get.

And if you want more functionality that it offers (such as column names, databinding support, sorting, filtering), then you can derive / encapsulate and add that functionality.

Your requirement's rather vague. What does lightweight mean? What is it about DataTable that is unsuitable? What functionality that's provided by DataTable do you need?

Answer 4

Create your own data structure.

The BCL should not be all things to all people. It consists of building blocks. The BCL contains the building blocks you need to create your own data structure.

If the BCL contained solutions to every problem it would be a bloated mess. Not every piece of functionality should be available "off the shelf", otherwise there would be no value left for you as a programmer to add.

You seem to have fairly clear requirements that are different from "normal" usage patterns. Between DataSets and ORMs, the large majority of projects get along just fine without the functionality your requirements are calling for.

Answer 5

You can use List<Dictionary<String, Object>>, where Dictionary key is column name and value is cell value.

If you have many rows, you can separate column names into List<String> and values List<List<Object>> (or Array).

Answer 6

Use this:

List<string> Variable1 = new List<string>();
//Do something with the generic

string[] MyArray = Variable1.ToArray();

This is the must lightweight and efficient obj to save to memory.