/// <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);
}
