title: I am wondering about the state of connection and impact on code performance by 'yield' while iterating over data reader object
body: <p>Here is my sample code that I am using to fetch data from database:
  on DAO layer:</p>
  
  <pre><code>public IEnumerable&lt;IDataRecord&gt; GetDATA(ICommonSearchCriteriaDto commonSearchCriteriaDto)
  {
      using(DbContext)
      {
          DbDataReader reader = DbContext.GetReader("ABC_PACKAGE.GET_DATA", oracleParams.ToArray(), CommandType.StoredProcedure);
          while (reader.Read())
          {
              yield return reader;
          }
      }
  }
  </code></pre>
  
  <p>On BO layer I am calling the above method like:</p>
  
  <pre><code>List&lt;IGridDataDto&gt; GridDataDtos = MapMultiple(_costDriversGraphDao.GetGraphData(commonSearchCriteriaDto)).ToList();
  </code></pre>
  
  <p>on mapper layer MapMultiple method is defined like:</p>
  
  <pre><code>public IGridDataDto MapSingle(IDataRecord dataRecord)
  {
      return new GridDataDto
      {
          Code = Convert.ToString(dataRecord["Code"]),
          Name = Convert.ToString(dataRecord["Name"]),
          Type = Convert.ToString(dataRecord["Type"])     
      };
  }
  public IEnumerable&lt;IGridDataDto&gt; MapMultiple(IEnumerable&lt;IDataRecord&gt; dataRecords)
  {
      return dataRecords.Select(MapSingle);
  }
  </code></pre>
  
  <p>The above code is working well and good but I am wondering about two concerns with the above code. </p>
  
  <ol>
  <li>How long data reader’s connection will be opened?</li>
  <li>When I consider code performance factor only, Is this a good idea to use ‘yield return’ instead of adding record into a list and returning the whole list? </li>
  </ol>