How to use SQLReader to return List

Question

We have a stored procedure that returns a single column of strings. We would like to use SqlHelper.ExecuteReader( ConnectionString,...) to return a list< string > but aren't sure of the syntax.

I should have been more explicit. I didn't want to have to loop through the reader and build the list myself. I was hoping for a more concise, 'one liner' or maybe some kind of casting that I was unaware of.

This SqlHelper? http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/fd9229c0-53d0-4b3b-a7da-5df4047343a3 — Joachim Isaksson, Feb 20 '12 at 19:06
See for example: http://stackoverflow.com/questions/2586674/c-is-this-the-correct-way-to-use-the-sqldatareader-for-daab — Ofer Zelig, Feb 20 '12 at 19:08
Well it's not obsolete in the DNN world. In fact it's used all over the place in DNN versions 4 and 5. Don't be so quick to judge. — Gio, Feb 20 '12 at 19:10
Curious as to the down vote....any helpful hints what not to do next time...? — Gio, Feb 20 '12 at 19:20

score 2 · Accepted Answer · answered Feb 20 '12 at 19:10

2

You can use this extension method:

public static IEnumerable<IDataRecord> AsEnumerable(this IDataReader reader)
{
    while (reader.Read())
    {
        yield return reader;
    }
}

...

using (var reader = SqlHelper.ExecuteReader(connectionString, query))
{
    var list = reader.AsEnumerable().Select(r => r.GetString(0)).ToList();
}

answered Feb 20 '12 at 19:10

Thomas Levesque

286,951
70
623
758

Thanks I was looking for something like this. We have several types of these queries and I was hoping for something more concise than looping through the reader. – Gio Feb 20 '12 at 19:19
@Gio, just a warning: don't try to do something like `reader.AsEnumerable().ToList()`, it won't work... all items in the list will refer to the last row. You need to extract the data while you're enumerating. – Thomas Levesque Feb 20 '12 at 19:24

score 0 · Answer 2 · answered Feb 20 '12 at 19:10

0

Take a look at Retrieving Data Using a C# .NET DataReader. The sample provides the solution for such a problem.

answered Feb 20 '12 at 19:10

Sergey Vyacheslavovich Brunov

17,291
7
48
81

score 0 · Answer 3 · answered Feb 20 '12 at 19:12

Have you tried something like this?

var sl = new List<string>();

// Edit accordingly
string sql = "";

// Edit accordingly
string cs = "Data Source= ;Initial Catalog= ;Integrated Security= ;";

using (var conn = new SqlConnection(cs))
{
    conn.Open();
    using (var cmd = new SqlCommand(sql, conn))
    {
        using (var dr = new command.ExecuteReader())
        {
            var myRow = dr["MyColumn"];
            sl.Add(myRow.ToString());
        }
    }
}

How to use SQLReader to return List

3 Answers3