Reading in list using SqlDataReader C#

Question

I have to fill in some lists in while loop as:

while (_myReader_1.Read())
{
   _Row_Counter++;

   int _authorID = _myReader_1.GetInt32(0);
   Author _author = _eAthors.FirstOrDefault(_a => _a._AuthorID == _authorID);
   if (_author == null)
   {
      _author = new Author
      {
         _AuthorID = _authorID,
         _AuthorName = _myReader_1.GetString(1),
         _Attributes = new List<AuthorAttributes>()
      };  
   }  

   var _attribute = new AuthorAttributes()
   {
      _PaperID = new List<int>(),
      _CoAuthorID = new List<int>(),
      _VenueID = new List<int>()
   };

   _attribute._PaperID.Add(_myReader_1.GetInt32(2));
   _attribute._CoAuthorID.Add(_myReader_1.GetInt32(3));
   _attribute._VenueID.Add(_myReader_1.GetInt32(4));
   _attribute._Year = _myReader_1.GetInt32(5);

   _author._Attributes.Add(_attribute);

   _eAthors.Add(_author);

}
_myReader_1.Close();

The data in SQL table looks like:

Author_ID | Author_Name | Paper_ID | CoAuthor_ID | Venue_ID | Year
------------------------------------------------------------------
677       | Nuno Vas    | 812229   | 901706      | 64309    | 2005  
677       | Nuno Vas    | 812486   | 901706      | 65182    | 2005  
677       | Nuno Vas    | 818273   | 901706      | 185787   | 2005  
677       | Nuno Vas    | 975105   | 901706      | 113930   | 2007  
677       | Nuno Vas    | 975105   | 1695352     | 113930   | 2007  
...       | ...         | ...      | ...         | ...      | ...

The issue is each time loop iterates, new lists _PaperID, _CoAuthorID and _VenueID are created, which is not desired. As we have a check if(author == null), then to create a new author, similarly I want to check if a list for _PaperID exists for an author e.g. for Author_ID = 677, then to Add in same list until Author_ID get changed.

Also until the Author_ID = 677, the list _eAuthors should have Count = 1

I'm attaching some images to refine the problem.

Image 1: Showing eAuthors Count = 3, Attributes Count = 3 for AuthorID = 677, while 3 of iterations passed whereas eAuthors Count should = 1.

Image 2: Showing Individual Attribute lists for each row, as in 3rd iteration the Attribute e.g. CoAuthorID, the Count = 1, whereas it should be = 3 while in 3rd iteration and same for rest of the Attributes

This has serious problems. How would you know which paper CoAuthor_ID 1695352 is associated with. Or when the year moved to 2007. You need a more formal data structure. — paparazzo, Jun 12 '16 at 11:48
I need to track Authors and their attributes i.e. Paper, CoAuthor and Venue in respective Years, not desired here that which paper the CoAuthor_ID 1695352 is associated with — maliks, Jun 12 '16 at 11:55
Well you are not even getting *respective* Years here. Why is new Author not initializing those list? Looks sloppy to me. — paparazzo, Jun 12 '16 at 11:59
If you just wanted to know all the unique papers, coauthors, venues and years an author has published, without regard to how they are related, then years should be in a list too. If they need to be related, then you need a class Paper which has PaperID, a list of coauthors, VenueID where it was delivered and year of publication. You'd then have a list of Paper items. — John D, Jun 12 '16 at 12:26
@JohnD Your point clicked well, but will your answered code remains same if we have a `Paper` as a class ? Still we should have lists of Papers, CoAuthors and Venues for each author...what about this? — maliks, Jun 12 '16 at 19:13
The first version of my answer suggested a list of Papers, and then we changed it to use unrelated lists of attributes. It all depends on what questions you're going to ask. "What papers has author published?" or "Who are his coauthors?" can be answered with unrelated lists. Questions like "Which papers did author publish in 2007?" needs a structure. You can still walk through the structure to answer the first two questions. Don't combine the two approaches though - keeping the same data in detail and in summary form is hard to manage. — John D, Jun 12 '16 at 21:27
Can you please post first version of your answer too so that I can go through both of the approaches one to one. — maliks, Jun 12 '16 at 21:39
Click on the blue link at the bottom of my answer "edited 10 hours ago". You'll then goto a page with the history of the edits made to the answer. Click Revision 2 (the revision numbers appear in large numbers on the left). — John D, Jun 12 '16 at 21:46
Do I place this list of Paper items in class `AuthorAttributes.cs` or class `Author.cs` ? — maliks, Jun 13 '16 at 07:35
@Paparazzi What do you mean by "Guess it take two clicks to click for you" ? — maliks, Jun 13 '16 at 09:10
@Paparazzi I prefer to put `List _Papers` inside the `Author.cs` file as a property — maliks, Jun 13 '16 at 09:17
Prefer to do what ever you want. You don't have a plan and that code is sloppy. — paparazzo, Jun 13 '16 at 11:54
@Paparazzi So please suggest the way which is not to be sloppy, where I'm getting wrong? — maliks, Jun 13 '16 at 12:44
I pointed out the problem in the first first comment and you dismissed it. Don't expect someone you dismiss to help you. — paparazzo, Jun 13 '16 at 12:49
Actually you pointed out directly for CoAuthors where Authors are going to matter for me regarding years though your pointing was meaningful but from the other angle — maliks, Jun 13 '16 at 13:23
@Taufel And you keep on dismissing. Oh yes may I help you now. — paparazzo, Jun 14 '16 at 13:00

John D · Answer 1 · 2016-06-12T11:41:27.363

1

Assuming your data structure looks like this:

Author
    AuthorAttributes
        Papers (list)
            PaperID
        CoAuthors (list)
            CoAuthorID
        Venues (list)
            VenueID
        Year

You could try this:

while (_myReader_1.Read())
{
    _Row_Counter++;

    int _authorID = _myReader_1.GetInt32(0);
    string _authorName = _myReader_1.GetString(1);
    int _paperID = _myReader_1.GetInt32(2);
    int _coAuthorID = _myReader_1.GetInt32(3);
    int _venueID = _myReader_1.GetInt32(4);
    int _year = _myReader_1.GetInt32(5);

    Author _author = _eAthors.FirstOrDefault(_a => _a._AuthorID == _authorID);
    if (_author == null)
    {
        _author = new Author
        {
             _AuthorID = _authorID,
             _AuthorName = _authorName,
             _AuthorAttributes = new AuthorAttributes
            {
                _Papers = new List<int>(),
                _Venues = new List<int>(),
                _Year = _year,
                _CoAuthors = new List<int>()
            }
        };  
        _eAthors.Add(_author); // only add if author not found
    }  

    if ( !_author._AuthorAttributes._Papers.Contains( _paperID ) )
        _author._AuthorAttributes._Papers.Add( _paperID );
    if ( !_author._AuthorAttributes._CoAuthors.Contains( _coAuthorID ) )
        _author._AuthorAttributes._CoAuthors.Add( _coAuthorID );
    if ( !_author._AuthorAttributes._Venues.Contains( _venueID ) )
        _author._AuthorAttributes._Venues.Add( _venueID );
}
_myReader_1.Close();

edited Jun 12 '16 at 11:41

answered Jun 12 '16 at 10:12

John D

1,627
1
11
10

The `VenueID` is also list and I don't have `Paper` class here instead I have `AuthorAttributes` class – maliks Jun 12 '16 at 10:29
1

One thing more! `PaperID` is also a list as `Author_ID` has many `Paper_ID` – maliks Jun 12 '16 at 10:50
1

What about if we don't declare `List` but declare it as only `AuthorAttributes` ? As all attributes inside `AuthorAttributes` are lists except `Year` – maliks Jun 12 '16 at 10:53
`_author` is first searched for. If found, it has already been created and it will already have `_AuthorAttributes` defined and `_AuthorAttributes` will already have its lists defined. If it is not found, then these members are created on the spot. Either way, they will exist. – John D Jun 12 '16 at 11:20
`if (!_author._AuthorAttributes._Papers.Contains( _paperID )`, *The name `_author` doesn't exist in current context* and *The name `_paperID` doesn't exist in current context* error raised – maliks Jun 12 '16 at 11:21
Hmmm, that's odd, it was declared at the same level ... let me check – John D Jun 12 '16 at 11:27
Sorry, my bad. I left off the closing `)`. That will teach me to run it through the compiler again before posting. Will edit, – John D Jun 12 '16 at 11:28
I can see `_Year = _year,` ie followed by comma – John D Jun 12 '16 at 11:47
It is depicted while debugging that if a CoAuthorID or PaperID or VenueID repeats in the data, it'll populated only once in respective lists. Is it so ? How should be it look like i.e. to appear only once or as many times as in the database ? – maliks Jun 12 '16 at 11:53
The `Contains` check should ensure that for a given author, you won't add a paperID, coauthorID or venueID that is already in the respective list. I.e. it will only add if the list does *not* already contain that value. – John D Jun 12 '16 at 11:59
an issue here with `_CoAuthors` i.e. the list, that I declared a method in `Author.cs` class (not given here) as: `public List GetCoAuthors() { var _coAuthors = new List(); foreach (var _paper in _Papers) { _coAuthors.AddRange(_paper._CoAuthors.Distinct()); } return _coAuthors; } in which I found this method returns also the duplicate coAuthors from the list `_CoAuthors`, what wrong with it? – maliks Jun 16 '16 at 12:31

score 1 · Accepted Answer · answered Jun 12 '16 at 11:03

Following the data structure shown and seeing what depicted in images, it seems that all attributes (Paper, CoAuthor, Venue) are of type lists, so there is no need to declare attributes as List<AuthorAttributes>. Follow this to what you want to achieve:

while (_myReader_1.Read())
{
   _Row_Counter++;

   int _authorID = _myReader_1.GetInt32(0);
   Author _author = _eAthors.FirstOrDefault(_a => _a._AuthorID == _authorID);
   if (_author == null)
   {
      _author = new Author
      {
         _AuthorID = _authorID,
         _AuthorName = _myReader_1.GetString(1),
         _Attributes = new AuthorAttributes()
      };
   }

   // Check if list _PaperID doesn't exist
   if (_author._Attributes._PaperID == null)
   {
      // Create new _PaperID
      _author._Attributes._PaperID = new List<int>();
      // Add Paper_ID to _PaperID
      _author._Attributes._PaperID.Add(_myReader_1.GetInt32(2));
   }
   else // Add Paper_ID to existing _PaperID list
   _author._Attributes._PaperID.Add(_myReader_1.GetInt32(2));

   // Check if list _CoAuthorID doesn't exist
   if (_author._Attributes._CoAuthorID == null)    
   {
      // Create new _CoAuthorID
      _author._Attributes._CoAuthorID = new List<int>();
      // Add CoAuthor_ID to _CoAuthorID
      _author._Attributes._CoAuthorID.Add(_myReader_1.GetInt32(3));
   }
   else // Add CoAuthor_ID to existing _CoAuthorID list
   _author._Attributes._CoAuthorID.Add(_myReader_1.GetInt32(3));

   // Check if list _CoAuthorID doesn't exist
   if (_author._Attributes._VenueID == null)    
   {
      // Create new _VenueID
      _author._Attributes._VenueID = new List<int>();
      // Add Venue_ID to _VenueID
      _author._Attributes._VenueID.Add(_myReader_1.GetInt32(4));
   }
   else // Add Venue_ID to existing _VenueID list
   _author._Attributes._VenueID.Add(_myReader_1.GetInt32(4));

   // Add Year to _Year
   _author._Attributes._Year =_myReader_1.GetInt32(5);

   if (!_eAthors.Contains(_author))
   _eAthors.Add(_author);
}
_myReader_1.Close();

It looks more prettier though each attribute dealt separately! — maliks, Jun 12 '16 at 11:30
Uh 3 lines, if (_author._Attributes._CoAuthorID == null) _author._Attributes._CoAuthorID = new List(); _author._Attributes._CoAuthorID.Add(_myReader_1.GetInt32(3)); — paparazzo, Jun 21 '16 at 21:17

score -1 · Answer 3 · answered Jun 12 '16 at 11:08

Add the newly created author right after initialization in the author == null check. Then check if author.PaperID == null and if it is, add the AuthorAttributes. Like this:

while (_myReader_1.Read())
{
   _Row_Counter++;

   int _authorID = _myReader_1.GetInt32(0);
   Author _author = _eAthors.FirstOrDefault(_a => _a._AuthorID == _authorID);
   if (_author == null)
   {
      _author = new Author
      {
         _AuthorID = _authorID,
         _AuthorName = _myReader_1.GetString(1),
         _Attributes = new List<AuthorAttributes>()
      };  

      _eAthors.Add(_author); // ********** Add the new author
   }  

   // Watch out!!! author.Attributes may be null for existing authors!!!
   if (author.Attributes.PaperID == null || author.PaperID.Count == 0) // Check for PaperID existence
   {
       var _attribute = new AuthorAttributes()
       {
          _PaperID = new List<int>(),
          _CoAuthorID = new List<int>(),
          _VenueID = new List<int>()
       };

       _attribute._PaperID.Add(_myReader_1.GetInt32(2));
       _attribute._CoAuthorID.Add(_myReader_1.GetInt32(3));
       _attribute._VenueID.Add(_myReader_1.GetInt32(4));
       _attribute._Year = _myReader_1.GetInt32(5);

       _author._Attributes.Add(_attribute);
   }
}
_myReader_1.Close();

Of course, if necessary you can process each attribute separately, by adding an if block for each one.

Reading in list using SqlDataReader C#

3 Answers3