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).
Add a comment
|
8 Answers
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.
10 Comments
Tarik
There are masters here. Nice answer.
shawn
how do I display the list values to the console.
Pavel Minaev
You cannot generate two lists in one pass, but you shouldn't be trying to. Instead of two lists of strings, use a single list - either of anonymous class instances (you'll need to use
var to declare the variable in that case, since there's no way to describe the type), or of Tuple instances, or of your own struct/class. Anonymous classes are usually easiest, e.g.: var list = (from IDataRecord r in dataReader select new { FieldName1 = (string)r["FieldName1"], FieldName2 = (string)r["FieldName2"] }).ToList();Pavel Minaev
dataReader.Cast<IDataRecord>().Select(r => (string)r["FieldName"]).ToList()
Kai Hartmann
make sure referencing
using System.Linq;. |
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?
1 Comment
user161433
Do you think that TaArray() method is just more overhead?
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.
1 Comment
Nando
Size your array to have the datas
object[] data = new object[reader.FieldCount];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.
Comments
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>() );
Comments
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;
}
Comments
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.
Comments
var array = reader.GetValue("field_name") as long[];
1 Comment
John Conde
While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply.