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
– CodeConstruct, Commented Jul 13, 2017 at 8: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
– Jeroen Mostert, Commented Jul 13, 2017 at 8:02
@JeroenMostert: There is a generic GetFieldValue so no need to do a big ass-switch (xkcd.com/37) or use Convert or even a third party tool. — Chris
– Chris, Commented Jul 13, 2017 at 8: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
– Jeroen Mostert, Commented Jul 13, 2017 at 8:19
It turned out I don't really want this in my case because *DataReader classes are forward-only iterators (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
– bencemeszaros, Commented Jul 13, 2017 at 11:15

Chris · Accepted Answer · 2017-07-13 08:12:39Z

3

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.

answered Jul 13, 2017 at 8:12

Chris

27.7k7 gold badges77 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Anton Gogolev · Accepted Answer · 2017-07-13 08:05:34Z

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, 2017 at 8:05

Anton Gogolev

116k39 gold badges204 silver badges293 bronze badges

1 Comment

bencemeszaros Over a year ago

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.

Collectives™ on Stack Overflow

Fill generic list from SqlDataReader

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related