CODEWITHSUNDEEP
c#asp.netsql
Gio
Gio

Reputation: 4229

How to use SQLReader to return List<string>

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.

Upvotes: 0

Views: 1305

Answers (3)

LiquidPony
LiquidPony

Reputation: 2208

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());
        }
    }
}

Upvotes: 0

Thomas Levesque
Thomas Levesque

Reputation: 292765

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();
}

Upvotes: 2

Sergey Vyacheslavovich Brunov
Sergey Vyacheslavovich Brunov

Reputation: 18136

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

Upvotes: 0

Related Questions