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?

Answer 3

Apparently, ever since .NET 1.1 SqlDataReader had the following method:

int size;
object[] data = new object[]{};
size = reader.GetValues(data);

This populates data with the values of the current reader row, assigning into size the number of objects that were put into the array.

Answer 4

Since any IDataReader implementation (SqlDataReader included) will be a forward-only reader by definition, no there is no way to do this without looping. Even if there were a framework library method to do this it would have to loop through the reader, just like you would.

Answer 5

The orignial OP asked for Array, ArrayList or List. You can return Array as well. Just call the .ToArray() method and assign it to a previously declared array. Arrays are very fast when it comes to enumerating each element. Much faster than a List if the list has more than 1000 elements. You can return to Array, List, or Dictionary.

ids_array = (from IDataRecord r in idReader 
select (string)r["ID"]).ToArray<string>();  

Additionally, if you are using a lookup of keys for example, you might consider creating a HashSet object with has excellent lookup performance if you are simply checking one list against another to determine if an elements key exists in the HashSet object. example:

 HashSet<string> hs = new HashSet<string>( 
(from IDataRecord r in idReader select (string)r["ID"]).AsEnumerable<string>() );

Answer 6

You have to loop, but there are projects that can make it simpler. Also, try not to use ArrayList, use List instead.

You can checkout FluentAdo for one: http://fluentado.codeplex.com

    public IList<UserAccount> List()
    {
        var list = new FluentCommand<UserAccount>("SELECT ID, UserName, Password FROM UserAccount")
            .SetMap(reader => new UserAccount
            {
                ID = reader.GetInt("ID"),
                Password = reader.GetString("Password"),
                UserName = reader.GetString("UserName"),
            })
            .AsList();

        return list;
    }

Answer 7

If you read your SqlDataAdapter into a DataTable:

DataTable dt as DataTable; 
dt.fill(data);

Then you can use some of the toys in System.Data.DataSetExtensions as referenced in Joel Muller's answer to this question.

In uses a bit of Linq, so you will net .Net 3.5 or higher.

Answer 8

var array = reader.GetValue("field_name") as long[];