/// <summary>
/// Executes the query for extract data
/// </summary>
/// <param name="sql">Query to execute for extract data</param>
/// <param name="parameters">Parameters to set</param>
/// <param name="countSql">Query to execute for count (null if no count required)</param>
/// <returns>Result of the query</returns>
private async Task <FetchEntitiesResponse> ExecuteQueryAsync(string sql, List <SqlParameter> parameters, string countSql = null)
{
if (string.IsNullOrEmpty(sql))
{
throw new ArgumentNullException(nameof(sql));
}
if (parameters == null)
{
throw new ArgumentNullException(nameof(parameters));
}
var result = new FetchEntitiesResponse();
// Start connection
using (var connection = new SqlConnection(Options.ConnectionString))
{
await connection.OpenAsync();
// Create command for data
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
foreach (var param in parameters)
{
command.Parameters.Add(param);
}
// Query execution
using (var reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
{
if (reader.HasRows)
{
while (await reader.ReadAsync())
{
var obj = new ExpandoObject() as IDictionary <string, object>;
for (int i = 0; i < reader.FieldCount; i++)
{
if (!await reader.IsDBNullAsync(i))
{
obj.Add(reader.GetName(i), reader.GetValue(i));
}
}
result.Entities.Add(obj);
}
}
}
// Count (use the same command so I haven't to set parameters once more)
if (!string.IsNullOrEmpty(countSql))
{
command.CommandText = $"WITH sel as ({countSql}) SELECT COUNT(*) FROM sel";;
// Query execution
result.TotalCount = (int)await command.ExecuteScalarAsync();
}
}
// Close connection
connection.Close();
}
// Result
return(result);
}
/// <summary>
/// Fetch all entities that match the given parameters
/// </summary>
/// <param name="parameters">Parameters to filter data</param>
/// <returns>Entities that match the given parameters</returns>
public Task <FetchEntitiesResponse> FetchEntitiesAsync(RestParameters parameters)
{
// Check for sort
var hasSort = (parameters?.Sort?.Fields?.Any()).GetValueOrDefault(false);
if (hasSort)
{
return(Task.FromResult(FetchEntitiesWithSort(parameters)));
}
// Read for primary key
var result = new FetchEntitiesResponse();
// Filter as function
var filterFunction = GetFunctionFilter(parameters);
// Take/Skip
var take = (parameters?.Take).GetValueOrDefault();
var skip = (parameters?.Skip).GetValueOrDefault();
// Record count
var recordCount = 0;
// Read file
using (var file = FileSystem.GetVisionFile(FileDefinition.FileName))
{
var records = new List <ExpandoObject>();
file.Open(FileOpenMode.Input);
if (file.Start())
{
IVisionRecord record;
while (true)
{
record = file.ReadNext();
if (record == null)
{
break;
}
// Load data
var element = GetExpandoObjectFromRecord(record);
// Apply filter
if (filterFunction == null || filterFunction(element))
{
// another record
recordCount++;
// Skip
if (recordCount <= skip)
{
continue;
}
// Take
if (take == 0 ||
take >= (recordCount - skip))
{
records.Add(element);
}
// Take
if (!parameters.WithCount && take > 0 && take == (recordCount - skip))
{
break;
}
}
}
}
file.Close();
// Entities
result.Entities = new List <object>(records);
// Total record count
if (parameters.WithCount)
{
result.TotalCount = recordCount;
}
}
return(Task.FromResult(result));
}
