CODEWITHSUNDEEP
c#sql-serverdictionarysqldatareader
John
John

Reputation: 343

C# Populate dictionary directly from SqlDataReader

In a program that I've been working on, there are three steps to get the data into a Dictionary that's been created:

  1. execute the SQL command
  2. pull those results into a DataTable, then
  3. pull the DataTable into the Dictionary

Code:

var myDr = myLookup.ExecuteReader();
dt.Load(myDr);
customerLookup = dt.AsEnumerable()
    .ToDictionary(key => key.Field<string>("code"),
    value => value.Field<string>("customerText"));

My question is, is it possible to “cut out the middleman,” so to speak, and pull the data from the SqlDataReater directly into the Dictionaries? Or is it necessary to pull it into a DataTable first? If what I'm looking to do is possible, can someone please post code for me to try?

Thanks very much!

Upvotes: 3

Views: 6234

Answers (3)

Shai Cohen
Shai Cohen

Reputation: 6249

Not only can you, but you definitely should. The code, as you show it, shows a complete lack of knowledge of how .NET works.

Some of the code I am suggesting may be considered "overkill" for the question at hand, but it does demonstrate some best practices.

Dictionary<string, string> customerLookup = new Dictionary<string, string>();
using (var reader = myLookup.ExecuteReader())
{
    int ordinalCode = reader.GetOrdinal("code");
    int ordinalCustomerText = reader.GetOrdinal("customerText");
    while (reader.Read())
    {
        //this code assumes the values returned by the reader cannot be null
        customerLookup.Add(reader.GetString(ordinalCode), reader.GetString(ordinalCustomerText))
    }
}

Upvotes: 2

itsme86
itsme86

Reputation: 19496

You can just loop through the rows returned by the reader:

var customerLookup = new Dictionary<string, string>();
using (var reader = myLookup.ExecuteReader())
{
    while (reader.Read())
    {
        customerLookup[(string)reader["code"]] = (string)reader["customerText"];
    }
}

You should be aware that if there are any duplicate codes, subsequent code values will overwrite previous ones in the dictionary. You can use customerLookup.Add() instead if you'd rather an exception be thrown in such a case.

Upvotes: 9

Paviel Kraskoŭski
Paviel Kraskoŭski

Reputation: 1419

Yes, it's possible. You should use SqlDataReader.Read method.

Upvotes: 1

Related Questions