public async Task<SpgSchemeWeeeCsvResult> SpgSchemeWeeeCsvAsync(int complianceYear, Guid? schemeId, string obligationType) { SpgSchemeWeeeCsvResult result = new SpgSchemeWeeeCsvResult(); var command = context.Database.Connection.CreateCommand(); command.CommandType = System.Data.CommandType.StoredProcedure; command.CommandText = "[PCS].[SpgSchemeWeeeCsv]"; DbParameter complianceYearParameter = command.CreateParameter(); complianceYearParameter.DbType = System.Data.DbType.Int32; complianceYearParameter.Value = complianceYear; complianceYearParameter.ParameterName = "@ComplianceYear"; command.Parameters.Add(complianceYearParameter); DbParameter schemeIdParameter = command.CreateParameter(); schemeIdParameter.DbType = System.Data.DbType.Guid; schemeIdParameter.Value = schemeId; schemeIdParameter.ParameterName = "@SchemeId"; command.Parameters.Add(schemeIdParameter); DbParameter obligationTypeParameter = command.CreateParameter(); obligationTypeParameter.DbType = System.Data.DbType.String; obligationTypeParameter.Value = obligationType; obligationTypeParameter.ParameterName = "@ObligationType"; command.Parameters.Add(obligationTypeParameter); await context.Database.Connection.OpenAsync(); DbDataReader dataReader = await command.ExecuteReaderAsync(); while (await dataReader.ReadAsync()) { Guid id = dataReader.GetGuid(dataReader.GetOrdinal("Id")); string schemeName = dataReader.GetString(dataReader.GetOrdinal("SchemeName")); string approvalNumber = dataReader.GetString(dataReader.GetOrdinal("ApprovalNumber")); result.Schemes.Add(new SpgSchemeWeeeCsvResult.SchemeResult() { SchemeId = id, SchemeName = schemeName, ApprovalNumber = approvalNumber }); } await dataReader.NextResultAsync(); while (await dataReader.ReadAsync()) { Guid id = dataReader.GetGuid(dataReader.GetOrdinal("SchemeId")); int quarter = dataReader.GetInt32(dataReader.GetOrdinal("Quarter")); int weeeCategory = dataReader.GetInt32(dataReader.GetOrdinal("WeeeCategory")); int sourceType = dataReader.GetInt32(dataReader.GetOrdinal("SourceType")); decimal tonnage = dataReader.GetDecimal(dataReader.GetOrdinal("Tonnage")); result.CollectedAmounts.Add(new SpgSchemeWeeeCsvResult.CollectedAmountResult() { SchemeId = id, QuarterType = quarter, WeeeCategory = weeeCategory, SourceType = sourceType, Tonnage = tonnage }); } await dataReader.NextResultAsync(); while (await dataReader.ReadAsync()) { Guid id = dataReader.GetGuid(dataReader.GetOrdinal("SchemeId")); int quarter = dataReader.GetInt32(dataReader.GetOrdinal("Quarter")); int weeeCategory = dataReader.GetInt32(dataReader.GetOrdinal("WeeeCategory")); int locationType = dataReader.GetInt32(dataReader.GetOrdinal("LocationType")); string locationApprovalNumber = dataReader.GetString(dataReader.GetOrdinal("LocationApprovalNumber")); decimal tonnage = dataReader.GetDecimal(dataReader.GetOrdinal("Tonnage")); result.DeliveredAmounts.Add(new SpgSchemeWeeeCsvResult.DeliveredAmountResult() { SchemeId = id, QuarterType = quarter, WeeeCategory = weeeCategory, LocationType = locationType, LocationApprovalNumber = locationApprovalNumber, Tonnage = tonnage }); } return result; }
public async Task <SpgSchemeWeeeCsvResult> SpgSchemeWeeeCsvAsync(int complianceYear, Guid?schemeId, string obligationType) { var result = new SpgSchemeWeeeCsvResult(); var command = context.Database.Connection.CreateCommand(); command.CommandType = System.Data.CommandType.StoredProcedure; command.CommandText = "[PCS].[SpgSchemeWeeeCsv]"; var complianceYearParameter = command.CreateParameter(); complianceYearParameter.DbType = System.Data.DbType.Int32; complianceYearParameter.Value = complianceYear; complianceYearParameter.ParameterName = "@ComplianceYear"; command.Parameters.Add(complianceYearParameter); var schemeIdParameter = command.CreateParameter(); schemeIdParameter.DbType = System.Data.DbType.Guid; schemeIdParameter.Value = schemeId; schemeIdParameter.ParameterName = "@SchemeId"; command.Parameters.Add(schemeIdParameter); var obligationTypeParameter = command.CreateParameter(); obligationTypeParameter.DbType = System.Data.DbType.String; obligationTypeParameter.Value = obligationType; obligationTypeParameter.ParameterName = "@ObligationType"; command.Parameters.Add(obligationTypeParameter); await context.Database.Connection.OpenAsync(); var dataReader = await command.ExecuteReaderAsync(); while (await dataReader.ReadAsync()) { var id = dataReader.GetGuid(dataReader.GetOrdinal("Id")); var schemeName = dataReader.GetString(dataReader.GetOrdinal("SchemeName")); var approvalNumber = dataReader.GetString(dataReader.GetOrdinal("ApprovalNumber")); result.Schemes.Add(new SpgSchemeWeeeCsvResult.SchemeResult() { SchemeId = id, SchemeName = schemeName, ApprovalNumber = approvalNumber }); } await dataReader.NextResultAsync(); while (await dataReader.ReadAsync()) { var id = dataReader.GetGuid(dataReader.GetOrdinal("SchemeId")); var quarter = dataReader.GetInt32(dataReader.GetOrdinal("Quarter")); var weeeCategory = dataReader.GetInt32(dataReader.GetOrdinal("WeeeCategory")); var sourceType = dataReader.GetInt32(dataReader.GetOrdinal("SourceType")); var tonnage = dataReader.GetDecimal(dataReader.GetOrdinal("Tonnage")); result.CollectedAmounts.Add(new SpgSchemeWeeeCsvResult.CollectedAmountResult() { SchemeId = id, QuarterType = quarter, WeeeCategory = weeeCategory, SourceType = sourceType, Tonnage = tonnage }); } await dataReader.NextResultAsync(); while (await dataReader.ReadAsync()) { var id = dataReader.GetGuid(dataReader.GetOrdinal("SchemeId")); var quarter = dataReader.GetInt32(dataReader.GetOrdinal("Quarter")); var weeeCategory = dataReader.GetInt32(dataReader.GetOrdinal("WeeeCategory")); var locationType = dataReader.GetInt32(dataReader.GetOrdinal("LocationType")); var locationApprovalNumber = dataReader.GetString(dataReader.GetOrdinal("LocationApprovalNumber")); var tonnage = dataReader.GetDecimal(dataReader.GetOrdinal("Tonnage")); result.DeliveredAmounts.Add(new SpgSchemeWeeeCsvResult.DeliveredAmountResult() { SchemeId = id, QuarterType = quarter, WeeeCategory = weeeCategory, LocationType = locationType, LocationApprovalNumber = locationApprovalNumber, Tonnage = tonnage }); } return(result); }