SqlDataReader - How to convert the current row to a dictionary

Question

Is there an easy way to convert all the columns of the current row of a SqlDataReader to a dictionary?

using (SqlDataReader opReader = command.ExecuteReader())
{
// Convert the current row to a dictionary
}

Thanks

score 77 · Accepted Answer · answered Nov 26 '10 at 14:23

77

You can use LINQ:

return Enumerable.Range(0, reader.FieldCount)
                 .ToDictionary(reader.GetName, reader.GetValue);

answered Nov 26 '10 at 14:23

SLaks

868,454
176
1,908
1,964

1

Your response looks interesting, but I need it in VB.NET, and I don't know how to translate the last part - .ToDictionary(reader.GetName, reader.GetValue). Any sugegstion? Thanks – bzamfir Sep 18 '14 at 21:07
1

@bzamfir: You need `AddressOf`. – SLaks Sep 19 '14 at 01:55
5

I'm wary of jumping on the linq-is-everything bandwagon but this is splendid. – Phil Cooper Mar 31 '15 at 15:44
1

This is the best solution put forward so far – Drew R Jul 01 '15 at 23:18
See also http://stackoverflow.com/questions/11842651/convert-datareader-to-dictionary – gastonmancini Sep 22 '15 at 13:25
2

a m aaaaa zing ly clever. – Ibrahim ben Salah Jul 20 '17 at 21:56
1

I'm 8.5 years post this original post, and I'm here to say THANK YOU! This is beautiful. – Timothy Grant Jul 12 '19 at 00:40

Cade Roux · Answer 2 · 2009-10-15T04:27:50.480

28

Easier than this?:

// Need to read the row in, usually in a while ( opReader.Read ) {} loop...
opReader.Read();

// Convert current row into a dictionary
Dictionary<string, object> dict = new Dictionary<string, object>();
for( int lp = 0 ; lp < opReader.FieldCount ; lp++ ) {
    dict.Add(opReader.GetName(lp), opReader.GetValue(lp));
}

I'm still not sure why you would need this particular transformation from one type of collection to another.

edited Oct 15 '09 at 04:27

answered May 26 '09 at 21:56

Cade Roux

88,164
40
182
265

5

As an answer to your question: if one needs to cache the results, it seems wiser to cache the dictionary copy. Otherwise you end up with a lot of open readers – Toad Mar 02 '10 at 14:18
2

@Toad One can use a DataTable if you want to cache the results. `MyDataTable.Load(dr);` – Cade Roux Feb 16 '12 at 19:32
@CadeRoux a DataTable would read the entire result set instead of a single row no? – Drew R Jul 01 '15 at 23:17
1

@CadeRoux Saving to a Dictionary is like 4-5 times faster than a DataTable. One test: http://lauteikkehn.blogspot.ro/2012/03/datatable-vs-list.html – Valentin Mar 07 '16 at 12:10
3

I don't understand why, instead of simply answering, people inquire "why are you doing this? or why are you asking this?" We do not know what the actual scenario for which somebody asks the question – Simple Fellow Apr 11 '16 at 08:45

score 6 · Answer 3 · edited May 23 '17 at 12:02

6

I came across this question on 3/9/2016 and ended up using the answer provided by SLaks. However, I needed to slightly modify it to:

dataRowDictionary = Enumerable.Range(0, reader.FieldCount).ToDictionary(i => reader.GetName(i), i=> reader.GetValue(i).ToString());

I found guidance from this StackOverflow question: convert dataReader to Dictionary

edited May 23 '17 at 12:02

Community

1
1

answered Mar 09 '16 at 19:26

Mifo

563
6
6

This is great, exactly what I was trying to do. It also converts nulls to empty strings. – Dymas Apr 29 '21 at 03:25

shahkalpesh · Answer 4 · 2009-05-26T22:04:59.673

0

GetValues method accepts & puts in, all the values in a 1D array.
Does that help?

edited May 26 '09 at 22:04

answered May 26 '09 at 21:59

shahkalpesh

33,172
3
63
88

score 0 · Answer 5 · answered May 26 '09 at 22:00

0

It's already an IDataRecord.

That should give you just about the same access (by key) as a dictionary. Since rows don't typically have more than a few handfuls of columns, the performance of the lookups shouldn't be that different. The only important difference is the type of the "payload", and even there your dictionary would have to use object for the value type, so I give the edge to IDataRecord.

answered May 26 '09 at 22:00

Joel Coehoorn

399,467
113
570
794

7

This is useful when you'd like the result of a query to be available after you've freed up the connection. DataReader objects are intrinsically tied to the connection they were created with. – sholsinger Oct 06 '10 at 14:37

SqlDataReader - How to convert the current row to a dictionary

5 Answers5

Linked