Fill generic list from SqlDataReader

Question

I'm reading some fields with different types from an SqlDataReader based on the column ID. I tried to write a generic function to get the fields in a List.

private static List<T> GetSqlFields<T>(SqlDataReader reader, int columnId)
    {
        var fields = new List<T>();
        while (reader.Read())
        {
            T f = reader.Get... // Problem is here
            fields.Add(f);
        }
        return fields;
    }

Problem is I only see methods GetString, GetInt32, etc.. Is there a way to get the fields based on T? Thank you.

I'm doing this to be able to write:

    int fooColumnId = reader.GetOrdinal("Foo");
    int barColumnId = reader.GetOrdinal("Baar");
    List<string> foos = GetSqlFields<string>(reader, fooColumnId);
    List<int> baars = GetSqlFields<int>(reader, barColumnId);

int fooColumnId = (int)reader["Foo"]; int barColumnId = (int)reader["Bar"]; — CodeConstruct, Jul 13 '17 at 08:01
`.GetValue()`, and then you can pipe that through `Convert` (or hard-cast it to `T` if you expect your users to never make a mistake). Beyond that, if you want to avoid boxing, you'll need a big-ass `switch` on `Type.GetTypeCode(typeof(T))`. Or better yet, don't reinvent the wheel and use Dapper (or another micro-ORM if your choosing), they've already optimized the inner loops for this. — Jeroen Mostert, Jul 13 '17 at 08:02
@JeroenMostert: There is a generic `GetFieldValue` so no need to do a big ass-switch (https://xkcd.com/37/) or use `Convert` or even a third party tool. — Chris, Jul 13 '17 at 08:17
@Chris: oh, that's new. (Well, if you consider .NET 4.5 new.) Under the covers, unsurprisingly, that gets the value as an `object` and then uses the aforementioned hard cast. So the big ass-switch would still be needed for tight loops that don't want boxing. (Probably not an issue for the OP; just saying.) — Jeroen Mostert, Jul 13 '17 at 08:19
It turned out I don't really want this in my case because `*DataReader` classes are forward-only iterators (https://stackoverflow.com/a/16361715/5295425). My intent was to iterate over more than one times on `reader` to get my filtered data but I can't do this. Instead I just stored the result from `reader` in a `DataTable` and filtered on that. — bencemeszaros, Jul 13 '17 at 11:15

score 3 · Accepted Answer · answered Jul 13 '17 at 08:12

You'll want: SqlDataReader.GetFieldValue<T>(int i).

This method (as the linked docs shows) takes an int for the column number and returns an object of type T. Of note is that this doesn't support any T but the list that it supports (on that page) covers the things that you might expect to be covered (ie looks at a glance like all types which are .NET equivalents of SQL data types).

If you didn't have the index of the column you are interested in (it looks like you do in this case but I'm adding for completeness) then you can use GetOrdinal(String) to get the column number from a column name.

score 1 · Answer 2 · answered Jul 13 '17 at 08:05

1

Don't know what you're after, but:

private static List<T> GetSqlFields<T>(SqlDataReader reader, int columnId,
    Func<IDataReader, int, T> accessor)
{
    var fields = new List<T>();
    while (reader.Read())
    {            
        T f = accessor(reader, columnId);
        fields.Add(f);
    }

    return fields;
}

List<int> baars = GetSqlFields<int>(reader, barColumnId, (dr, i) => dr.GetInt32(i));

answered Jul 13 '17 at 08:05

Anton Gogolev

113,561
39
200
288

Thank you, I really like this. :) I marked the other answer as accepted because that's builtin functionality. I learned something new by your answer. – bencemeszaros Jul 13 '17 at 11:00

Fill generic list from SqlDataReader

2 Answers2