Reading into a Dictionary from SqlDataReader

Question

I declared a Dictionary in a class as:

public class AuthorAttributes
{
   //public int _Paper_ID { get; set; }
   public Dictionary<Int32, Int32> _Paper = new Dictionary<Int32, Int32>();
   public int _CoAuthor_ID { get; set; }
   public int _Venue_ID { get; set; }
}

whereas Dictionary will store _Paper_ID and _Paper_Category both of type Int32 as _Paper.

While reading data from SQL Server as:

using (SqlDataReader _myReader_1 = _myCommand_1.ExecuteReader())
{
    while (_myReader_1.Read())
    {
       int _authorID = _myReader_1.GetInt32(0);
       Author _author = _eAthors.FirstOrDefault(_a => _a._Author_ID == _authorID);
       if (_author == null)
       {
          _author = new Author { 
                                 _Author_ID = _authorID, 
                                 _Author_Name = _myReader_1.GetString(1), 
                                 _Year = _myReader_1.GetInt32(6), 
                                 _Attributes = new List<AuthorAttributes>() 
                               };

          _eAthors.Add(_author);
       }

       _author._Attributes.Add(new AuthorAttributes {  
                                                      _Paper = // How to read into Dictionary "_Paper"  
                                                      _CoAuthor_ID = _myReader_1.GetInt32(4),  
                                                      _Venue_ID = _myReader_1.GetInt32(5) 
                                                    }
                               );
    }
    _myReader_1.Close();
}

UPDATE
The Sql query used is as:

_myCommand_1.CommandText = @"SELECT AC.Author_ID, A.Author_Name, AC.Paper_ID,  
                                    P.Paper_Category, AC.CoAuthor_ID, AP.Venue_ID, AC.Year
                             FROM   AuthorCoAuthor  AC
                             JOIN   AuthorPaper     AP ON AP.Author_ID  = AC.Author_ID AND 
                                                          AP.Paper_ID   = AC.Paper_ID
                             JOIN   Author          A  ON A.Author_ID   = AC.Author_ID
                             JOIN   Paper           P  ON P.Paper_ID    = AP.Paper_ID   
                             ORDER BY  
                                    AC.Author_ID, AC.Year, AC.Paper_ID,  
                                    AC.CoAuthor_ID, AP.Venue_ID";

How will I read into Dictionary Key and Value using SqLDataReader

regardless of what data is in your sql response, you need to use '_Paper = new Dictionary();' before trying to add anything. — iamruss, Jun 07 '16 at 10:53
A dictionary cannot be used with a property getter/setter. http://stackoverflow.com/a/11583686/4846465 — Ryan Peters, Jun 07 '16 at 11:01
This is a bit difficult to answer without knowing the SQL query or some example data returned by it. — Dirk, Jun 07 '16 at 11:07
_Adding _underscores _to _everything _regardless _of _visibility _is _a _peculiar _style _I've _never _seen _anywhere _else. It certainly doesn't improve readability. Worse than that, though, is the use of magic constants to read columns, rather than using `.GetOrd()` to determine their position. This code is needlessly brittle in the face of a changing result set (and also hard to verify as correct by a maintainer). — Jeroen Mostert, Jun 07 '16 at 11:13
@JeroenMostert I think I wanted to use underscore only with variable names as: `_Paper_ID`, after your pointing, I ought to change my style as: `_PaperID` fine? — maliks, Jun 07 '16 at 11:23
Although style discussions are almost entirely subjective and not within the scope of SO, it's worth noting that [the naming guidelines of the .NET framework itself](https://msdn.microsoft.com/library/ms229045) advocate avoiding underscores altogether, and certainly in front of public members (what you do in private members and variables matters less). — Jeroen Mostert, Jun 07 '16 at 11:26

score 1 · Accepted Answer · edited May 23 '17 at 11:58

1

This can work using Indexers. Taken from: https://stackoverflow.com/a/11583759/4846465

Update the model:

public class AuthorAttributes
{
    private readonly Dictionary<Int32, Int32> _paper = new Dictionary<Int32, Int32>();

    public Int32 this[Int32 key]
    {
        // returns value if exists
        get { return _paper[key]; }

        // updates if exists, adds if doesn't exist
        set { _paper[key] = value; }
    }
    public int _CoAuthor_ID { get; set; }
    public int _Venue_ID { get; set; }
}

Then to implement (assumptions on where _Paper_ID and _Paper_Category come from):

var attrb = new AuthorAttributes
{
    _CoAuthor_ID = _myReader_1.GetInt32(4),
    _Venue_ID = _myReader_1.GetInt32(5)
}
// Assumes _myReader_1.GetInt32(3) is _Paper_ID
// Assumes _myReader_1.GetInt32(2) is _Paper_Category
attrb[_myReader_1.GetInt32(3)] = _myReader_1.GetInt32(2);
_author._Attributes.Add(attrb);

edited May 23 '17 at 11:58

Community

1
1

answered Jun 07 '16 at 11:13

Ryan Peters

180
9

@Ryan--How will I use it inside `using() {while(){ // Here I have to use it} }` ? – maliks Jun 07 '16 at 11:27
Will it be as `var attrb = new List{};` ? – maliks Jun 07 '16 at 11:33
I've added it in place of `_author._Attributes.Add(new AuthorAttributes {...});` – maliks Jun 07 '16 at 11:39
Sorry for the late reply. Yes, you're absolutely correct. Use the code snippet above in place of: `_author._Attributes.Add(new AuthorAttributes {...});`. Fun exercise, I learned something new. – Ryan Peters Jun 07 '16 at 12:24
That's motivating @Ryan :) – maliks Jun 08 '16 at 06:29

Reading into a Dictionary from SqlDataReader

1 Answers1