views:

582

answers:

6

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.

A: 

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

marc_s
When I know exactly what data I am returning its easy. I am looking for a generic container with functionality like DataTable, but not DataTable :)
Ivan Zlatanov
A: 

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() {}
}
Igor Zevaka
I've edited my question. I want to be able to store generic data structure in IDictionary manner - i.e. I don't know what my reader is returning.
Ivan Zlatanov
+1  A: 
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?

Joe
@Joe By lightweight I not only mean memory usage, but speed. Initial testing showed that DataTable is slower even than LINQ Execute<T> method. About the List<Object[]> solution - you are correct, I sure can do it with adding functionality to that. I was just looking to find any existing implementation in the BCL if any.
Ivan Zlatanov
I'm very unclear about what you're comparing when you talk about speed differences between the DataTable class and the Execute<T> method.
Joe
@Joe DataTable.Load method which reads from SqlDbReader vs LINQ Translate<T> method which reads from SqlDbReader and transforms it into typed enumerable. DataTable is slower and bigger.
Ivan Zlatanov
The typed enumerable is enumerated on the fly, so is more comparable with an IDataReader than a DataTable. You can't really compare performance with filling a DataTable unless you store the data somewhere (e.g. the mimimalist List<object[]>).
Joe
A: 

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.

Michael Maddox
A: 

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).

Matej
A: 

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.

Luke101