/// <summary> /// Connects to the DB and loads the results of a single dynamic statement described in <paramref name="args"/>. /// </summary> /// <param name="connString">The connection string of the SQL database from which to load the data.</param> /// <param name="args">All the information needed to connect to the database and execute the statement.</param> /// <param name="cancellation">The cancellation instruction.</param> /// <returns>The requested data packaged in a <see cref="DynamicOutput"/>.</returns> public async Task <DynamicOutput> LoadDynamic(string connString, DynamicLoaderArguments args, CancellationToken cancellation = default) { // Destructure the args var countSql = args.CountSql; var principalStatement = args.PrincipalStatement; var dimAncestorsStatements = args.DimensionAncestorsStatements ?? new List <SqlDimensionAncestorsStatement>(); var ps = args.Parameters; var vars = args.Variables; ////////////// Prepare the complete SQL code // Add any variables in the preparatory SQL string variablesSql = vars.ToSql(); var statements = new List <string>(1 + dimAncestorsStatements.Count) { principalStatement.Sql }; statements.AddRange(dimAncestorsStatements.Select(e => e.Sql)); string sql = PrepareSql( variablesSql: variablesSql, countSql: countSql, statements.ToArray()); // The result DynamicOutput result = null; try { using var trx = TransactionFactory.ReadCommitted(); await ExponentialBackoff(async() => { var rows = new List <DynamicRow>(); var trees = new List <DimensionAncestorsOutput>(); var count = 0; // Connection using var conn = new SqlConnection(connString); // Command Text using var cmd = conn.CreateCommand(); cmd.CommandTimeout = TimeoutInSeconds; cmd.CommandText = sql; // Parameters foreach (var parameter in ps) { cmd.Parameters.Add(parameter); } // Execute try // To capture { await conn.OpenAsync(cancellation); using var reader = await cmd.ExecuteReaderAsync(cancellation); // (1) Load the count if any if (!string.IsNullOrWhiteSpace(countSql)) { if (await reader.ReadAsync(cancellation)) { count = reader.GetInt32(0); } // Go over to the next result set await reader.NextResultAsync(cancellation); } // (2) Load results of the principal query { int columnCount = principalStatement.ColumnCount; while (await reader.ReadAsync(cancellation)) { var row = new DynamicRow(columnCount); for (int index = 0; index < columnCount; index++) { var dbValue = reader.Value(index); row.Add(dbValue); } rows.Add(row); } } // (3) Load the tree dimensions foreach (var treeStatement in dimAncestorsStatements) { int columnCount = treeStatement.TargetIndices.Count(); int index; int minIndex = treeStatement.TargetIndices.Min(); int[] targetIndices = treeStatement.TargetIndices.Select(i => i - minIndex).ToArray(); var treeResult = new DimensionAncestorsOutput ( idIndex: treeStatement.IdIndex, minIndex: minIndex, result: new List <DynamicRow>() ); await reader.NextResultAsync(cancellation); while (await reader.ReadAsync(cancellation)) { var row = new DynamicRow(columnCount); for (index = 0; index < targetIndices.Length; index++) { var dbValue = reader.Value(index); int targetIndex = targetIndices[index]; row.AddAt(dbValue, targetIndex); } treeResult.Result.Add(row); } trees.Add(treeResult); } } catch (SqlException ex) when(ex.Number is 8134) // Divide by zero { throw new QueryException(DivisionByZeroMessage); } finally { // Otherwise it will fail on retry cmd.Parameters.Clear(); } trx.Complete(); result = new DynamicOutput(rows, trees, count); }, cancellation); } catch (Exception ex) when(ex is not OperationCanceledException && ex is not ReportableException) { // Include the SQL and the parameters throw new StatementLoaderException(sql, ps, ex); } return(result); }