Dapper error: each grid can be repeated only once

Question

My code is below

try { SqlConnection mapperConnection = SqlAccessHelper.SqlHelper.GetOpenConnection(SqlConnectionHelper.SqlConnectionString()); var parameters = new DynamicParameters(); parameters.Add("@P_MarketId", marketId, DbType.Int32); parameters.Add("@P_Output", dbType: DbType.Int32, direction: ParameterDirection.Output); using (var multi = mapperConnection.QueryMultiple("USP_FetchMarketRecords", parameters, (SqlTransaction)null, 1000000, CommandType.StoredProcedure)) { IEnumerable<MarketRecord.FItem> FItem = multi.Read<MarketRecord.FItem>(); IEnumerable<MarketRecord.FSubsystem> FSubsystem = multi.Read<MarketRecord.FSubsystem>(); objCResponseVO.addObject("FItem",FItem); objCResponseVO.addObject("FSubsystem",FSubsystem); } } catch (Exception ex) { throw ex; } 

first reading is good. The second reading of FSubsystem gives me the exception "Each grid can be repeated only once." What is wrong here? When I traced it in the public IEnumerable Read () method while using the second read, this is true. How can I overcome this.?

Answer 1

A fundamental data reader is only a direct access device; By calling Read second time, you move the reader forward to the next grid. You should consume each grid only in the forward direction. For example, you can change the order of the operators:

 using (var multi = mapperConnection.QueryMultiple("USP_FetchMarketRecords", parameters, (SqlTransaction)null, 1000000, CommandType.StoredProcedure)) { IEnumerable<MarketRecord.FItem> FItem = multi.Read<MarketRecord.FItem>(); objCResponseVO.addObject("FItem",FItem); IEnumerable<MarketRecord.FSubsystem> FSubsystem = multi.Read<MarketRecord.FSubsystem>(); objCResponseVO.addObject("FSubsystem",FSubsystem); } 

Or you can buffer:

 using (var multi = mapperConnection.QueryMultiple("USP_FetchMarketRecords", parameters, (SqlTransaction)null, 1000000, CommandType.StoredProcedure)) { IEnumerable<MarketRecord.FItem> FItem = multi.Read<MarketRecord.FItem>().ToList(); IEnumerable<MarketRecord.FSubsystem> FSubsystem = multi.Read<MarketRecord.FSubsystem>().ToList(); objCResponseVO.addObject("FItem",FItem); objCResponseVO.addObject("FSubsystem",FSubsystem); } 

Answer 2

I just went through a similar problem. Mine is Postgresql instead of SQL Server, but I think that doesn't matter in this case.

Adding ToList () at the end of the result got the trick. Hope this works for you too.

  string sql = @" select * from usuario where id=@id ; select idarea from areausuario where idusuario = @id; select * from area; "; var res = this.Connection.QueryMultiple(sql, p); var usuarioData = res.Read<Entities.Usuario>().First(); var areasSelected = res.Read<int>().ToList(); var areas = res.Read<Entities.CatalogoPadreSelection>().ToList();