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

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

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 4

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 5

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 6

this didnt work for me: ArrayList al = new ArrayList(); using (SqlDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { al.Add(dr.GetString(0)); } } string[] str = new string[al.Count - 1]; al.CopyTo(str);

        return str;

Yours well.

Thnx