Multi Set SQL translation

See more:

C#

ASP.NET

SQL

Hey Everyone,

I have a SQL procedure that does a join to return a bunch of objects and some secondary stored procedure executions that will fill up next results of the reference objects in the main class.

Everything is working great, but when I do a join to return the main class and reference class, I am trying to do next results and fill up the object but without luck. I have tried multiple things but nothing seems to work. Any help would be great as I would like to minimize the trips to the DB.

Some of the example code below

//levelstamp - reference class pulled in the main SQL
if (reader.NextResult())
{
   if (returnLevel.levelStamps == null)
      returnLevel.levelStamps = new List<levelstamp>();

   returnLevel.levelStamps = reader.ToLevelStampList();
}

//method
public static List<levelstamp> ToLevelStampList(this IDataReader dataReader)
{
   var returnList = new List<levelstamp>();
   while (dataReader.Read())
   {
      LevelStamp levelStamps = new LevelStamp()
      {
         LevelStampApplyPreferences = Boolean.Parse(dataReader["LevelStampApplyPreferences"].ToString()),
         LevelId = Int32.Parse(dataReader["LevelId"].ToString()),
         LevelStampIsActive = Boolean.Parse(dataReader["LevelStampIsActive"].ToString()),
         StampId = Int32.Parse(dataReader["StampId"].ToString()),
         LevelStampSortOrder = Int32.Parse(dataReader["LevelStampSortOrder"].ToString()),
         stamp = dataReader.ToStampObject() //call to stamp object
      };

      returnList.Add(levelStamps);
   }
   return returnList;
}

The levelstamp class is pulled by the main store procedure while the stamp is called from the LevelStamp stored proc, so in essensce, the main store proc fills up the base class, then its secondary stored proc execution are called to fill up the levelstamp which lives in the base class, and that store proc calls another stored proc to fill up the stamp class

additional information copied from comment below
when this method is called, it is outside of the Index range

public static Stamp ToStampObject(this IDataReader dataReader)
{
   Stamp stamp = null;
   while (dataReader.Read())
   {
      stamp = new Stamp()
      {
         //assign content
      };
   }
   return stamp;
}