Fill an array (or arraylist) from SqlDataReader

Question

Is there a way to fill an array via a SqlDataReader (or any other C# ADO.NET object) without looping through all the items? I have a query that is returning a single column, and I want to put that into a string array (or ArrayList, or List, etc).

Answer 1

It is possible. In .NET 2.0+, SqlDataReader inherits from DbDataReader, which implements IEnumerable (non-generic one). This means that you can use LINQ:

List<string> list = (from IDataRecord r in dataReader                      select (string)r["FieldName"]                     ).ToList(); 

That said, the loop is still there, it's just hidden in Enumerable.Select, rather than being explicit in your code.

Answer 2

No, since SqlDataReader is a forward-only read-only stream of rows from a SQL Server database, the stream of rows will be looped through whether explicitly in your code or hidden in a framework implementation (such as DataTable's Load method).

It sounds like using a generic list and then returning the list as an array would be a good option. For example,

List<int> list = new List<int>();  using (SqlDataReader reader = cmd.ExecuteReader()) {     while (reader.Read())     {          list.Add(reader.GetInt32(0));     }     } return list.ToArray(); 

In response to your comment, calling ToArray() may be overhead, it depends. Do you need an array of objects to work with or would a generic collection (such as List<T> or ReadOnlyCollection<T>) be more useful?