Storing an SqlDataReader-object in C#/ASP.NET?

Question

I'm currently writing a C#-class in my ASP.NET (3.5) application to handle all database-queries. Some of the methods are there to make a select-query to the database. Inside the method i simply have a SqlDataReader r = command.ExecuteReader(); to fetch the data.

I now want r to be returned by the method, but when i close the database-connection, the datareader closes and the object contains nothing anymore. Thought of putting everything into a string-array, but this is unpractical if i have mixed integer and text-fields in my database. Is there an easy ("premade") way of storing the data in some object to return?

Answer 1

You can use a datatable and SqlDataAdapter:

using(SqlDataAdapter adap = new SqlDataAdapter(cmd))
{
  adap.fill(myDataTable);
}

return myDataTable;

Answer 2

you can just set the commandbehavior to closeconnection. and then when you call r.close the underlying connection will close.

Answer 3

Typically, the results are either stuffed into a collection or a datatable. Datatables are a little easier from the perspective of not having to instantiate objects and storing them.

Answer 4

Have you thought of using Linq or an ORM like subsonic for this