public async Task <IActionResult> ExecuteQuery(string id, [FromBody] DynamicListFetchDataModel fetchDataModel)
{
_logger.Info("Execute query in dynamic list id {id} with fetch data {@fetchDataModel}", id, fetchDataModel);
var dynamicList = await _dynamicListRepository.GetOneAsync(id);
if (dynamicList == null)
{
return(NotFound());
}
var result = await _dynamicService.FetchData(dynamicList, fetchDataModel);
_logger.Info("Result of query: {@result}", result);
return(Ok(result));
}
public Task <DynamicListResponseDataModel> Query(DatabaseConnection databaseConnection, DynamicList dynamicList, DynamicListFetchDataModel fetchDataModel)
{
var response = new DynamicListResponseDataModel();
var hasRows = false;
using (var sqlDbConnection = new SqlConnection(databaseConnection.ConnectionString))
{
var combinedQuery =
_builder
.Init(
dynamicList.ListDatasource.DatabaseConnectionOptions.Query,
fetchDataModel.FilledParameterOptions.FilledParameters,
options =>
{
options.ContainsOperatorFormat = " LIKE '%' + {0} + '%'";
options.PaginationFormat = "OFFSET {1} ROWS FETCH NEXT {0} ROWS ONLY";
options.FieldFormat = "[{0}]";
options.DateCompareFormat = "cast({0} as date){1}cast({2} as date)";
})
.AddTextSearch(fetchDataModel.TextSearch, dynamicList.ColumnsList.ColumndDefs.Where(a => a.SearchOptions.AllowTextSearch).Select(b => b.Name))
.AddFilter(fetchDataModel.FilterGroupOptions.FilterGroups)
.AddSort(fetchDataModel.SortOptions.SortableFields)
.AddPagination(fetchDataModel.PaginationOptions.PageNumber, fetchDataModel.PaginationOptions.PageSize)
.Build();
sqlDbConnection.Open();
using (var cmd = new SqlCommand(combinedQuery.CombinedQuery, sqlDbConnection))
{
foreach (var param in combinedQuery.Parameters)
{
if (param.IsReplacedValue)
{
var castObject = _cSharpMapper.GetCSharpObjectByType(param.Value, param.ReplaceValueType);
cmd.Parameters.Add(
new SqlParameter(
param.Name, _sqlServerMapper.GetSqlDbType(param.ReplaceValueType))
{
Value = castObject,
Direction = System.Data.ParameterDirection.Input
});
}
else
{
cmd.Parameters.Add(
new SqlParameter(
param.Name, GetSqlDbType(param, out var castObject))
{
Value = castObject,
Direction = System.Data.ParameterDirection.Input
});
public async Task <DynamicListResponseDataModel> FetchData(DynamicList dynamicList, DynamicListFetchDataModel fetchDataModel)
{
var databaseConnection = await _databaseServiceProvider.GetOneDatabaseConnectionAsync(dynamicList.ListDatasource.DatabaseConnectionOptions.DatabaseConnectionId);
var foundDynamicListQuery = _dynamicListQueryDatabases.First(a => a.ConnectionType == databaseConnection.GetConnectionType());
return(await foundDynamicListQuery.Query(databaseConnection, dynamicList, fetchDataModel));
}
public async Task <DynamicListResponseDataModel> Query(DatabaseConnection databaseConnection, DynamicList dynamicList, DynamicListFetchDataModel fetchDataModel)
{
dynamicList.GenerateFilters();
var dynamicListResponseDataModel = new DynamicListResponseDataModel();
// Because this flow is very complicated. We MUST UPDATE this flow fequently
// 1. Extract collection name for executing
// 2. Prepare execution query with some parts:
// 2.1 Params
// 2.2 Combine with Filters
// 2.3 Sort
// 3. Execute query with pagination
// 4. Response data
// 5. Note: in case entity, we still use the same way with EntityName field
// 1. Extract collection name for executing, either Entity mode or Custom mode, EntityName is the same
var executingQuery = dynamicList.ListDatasource.DatabaseConnectionOptions.Query;
var parsingObject = JObject.Parse(executingQuery);
var executingCollectionName = (parsingObject[Constants.QUERY_KEY].First as JProperty).Name;
// 2. Prepare execution query
// 2.2 Combine with Text Search, Filters and Sort
var mongoCollection = new MongoClient(databaseConnection.ConnectionString).GetDatabase(databaseConnection.DataSource).GetCollection <BsonDocument>(executingCollectionName);
var filterDefinitionOptions = FilterDefinition <BsonDocument> .Empty;
var collectionQuery = parsingObject[Constants.QUERY_KEY][executingCollectionName].ToString(Newtonsoft.Json.Formatting.Indented);
foreach (var filledParam in fetchDataModel.FilledParameterOptions.FilledParameters)
{
collectionQuery = collectionQuery.Replace("{{" + filledParam.Name + "}}", filledParam.Value);
}
collectionQuery = _mongoOptions.CurrentValue.EliminateDoubleQuotes(collectionQuery);
var aggregatePipes = BsonSerializer.Deserialize <BsonDocument[]>(collectionQuery).Select(a => (PipelineStageDefinition <BsonDocument, BsonDocument>)a).ToList();
var aggregateFluent = mongoCollection.Aggregate();
foreach (var pipe in aggregatePipes)
{
aggregateFluent = aggregateFluent.AppendStage(pipe);
}
// Add Text search first if had
if (!string.IsNullOrEmpty(fetchDataModel.TextSearch))
{
aggregateFluent = aggregateFluent.Match(CombineTextSearch(fetchDataModel.TextSearch, dynamicList.FiltersList));
}
// Add Filter Options if had
if (fetchDataModel.FilterGroupOptions != null &&
fetchDataModel.FilterGroupOptions.FilterGroups != null &&
fetchDataModel.FilterGroupOptions.FilterGroups.Count > 0 &&
(fetchDataModel.FilterGroupOptions.FilterGroups[0].FilterOptions.Count > 0))
{
aggregateFluent = aggregateFluent.AppendStage(PipelineStageDefinitionBuilder.Match(BuildFilters(fetchDataModel.FilterGroupOptions.FilterGroups)));
}
// Projection only columns
var projectDoc = new BsonDocument();
foreach (var column in dynamicList.ColumnsList.ColumnDefs)
{
projectDoc.Add(new BsonElement(column.Name, 1));
}
var projection = new BsonDocument
{
{ "$project", projectDoc }
};
aggregateFluent = aggregateFluent.AppendStage((PipelineStageDefinition <BsonDocument, BsonDocument>)projection);
// Add Sort if had
if (fetchDataModel.SortOptions.SortableFields != null && fetchDataModel.SortOptions.SortableFields.Count > 0)
{
var sortField = fetchDataModel.SortOptions.SortableFields[0];
FieldDefinition <BsonDocument, string> field = sortField.FieldName;
var sortDefinition = sortField.SortType == SortType.Asc
? Builders <BsonDocument> .Sort.Ascending(field) :
Builders <BsonDocument> .Sort.Descending(field);
aggregateFluent = aggregateFluent.AppendStage(PipelineStageDefinitionBuilder.Sort(sortDefinition));
}
if (fetchDataModel.PaginationOptions.NeedTotalItems)
{
var aggregateFluentForCountTotal = aggregateFluent.Count();
var totalItems = await aggregateFluentForCountTotal.FirstOrDefaultAsync();
dynamicListResponseDataModel.TotalItems = totalItems != null ? totalItems.Count : 0;
}
if (fetchDataModel.PaginationOptions.NeedTotalItems && dynamicListResponseDataModel.TotalItems > 0)
{
// Add Pagination
aggregateFluent = aggregateFluent
.Skip(fetchDataModel.PaginationOptions.PageNumber * fetchDataModel.PaginationOptions.PageSize)
.Limit(fetchDataModel.PaginationOptions.PageSize);
using (var executingCursor = await aggregateFluent.ToCursorAsync())
{
while (executingCursor.MoveNext())
{
var currentBson = executingCursor.Current;
foreach (var item in currentBson)
{
var addedFields = new List <BsonElement>();
var removedFields = new List <string>();
foreach (var elem in item)
{
if (elem.Value.IsObjectId)
{
addedFields.Add(new BsonElement(elem.Name == "_id" ? "id" : elem.Name, BsonValue.Create(elem.Value.AsObjectId.ToString())));
removedFields.Add(elem.Name);
}
}
foreach (var removedField in removedFields)
{
item.Remove(removedField);
}
foreach (var addedField in addedFields)
{
item.Add(addedField);
}
}
// Important note: this query must have one row result for extracting params and filters
dynamicListResponseDataModel.Data =
currentBson
.Select(a =>
a.ToJson(
new MongoDB.Bson.IO.JsonWriterSettings
{
OutputMode = MongoDB.Bson.IO.JsonOutputMode.Strict
})).Select(b =>
JsonConvert.DeserializeObject <dynamic>(b, new BsonConverter(GetFormatFields(dynamicList.ColumnsList.ColumnDefs)))).ToList();
}
}
}
else if (!fetchDataModel.PaginationOptions.NeedTotalItems)
{
// Add Pagination
aggregateFluent = aggregateFluent
.Skip(fetchDataModel.PaginationOptions.PageNumber * fetchDataModel.PaginationOptions.PageSize)
.Limit(fetchDataModel.PaginationOptions.PageSize);
using (var executingCursor = await aggregateFluent.ToCursorAsync())
{
while (executingCursor.MoveNext())
{
var currentBson = executingCursor.Current;
foreach (var item in currentBson)
{
var addedFields = new List <BsonElement>();
var removedFields = new List <string>();
foreach (var elem in item)
{
if (elem.Value.IsObjectId)
{
addedFields.Add(new BsonElement(elem.Name == "_id" ? "id" : elem.Name, BsonValue.Create(elem.Value.AsObjectId.ToString())));
removedFields.Add(elem.Name);
}
}
foreach (var removedField in removedFields)
{
item.Remove(removedField);
}
foreach (var addedField in addedFields)
{
item.Add(addedField);
}
}
// Important note: this query must have one row result for extracting params and filters
dynamicListResponseDataModel.Data =
currentBson
.Select(a =>
a.ToJson(
new MongoDB.Bson.IO.JsonWriterSettings
{
OutputMode = MongoDB.Bson.IO.JsonOutputMode.Strict
})).Select(b =>
JsonConvert.DeserializeObject <dynamic>(b, new BsonConverter(GetFormatFields(dynamicList.ColumnsList.ColumnDefs)))).ToList();
}
}
}
return(dynamicListResponseDataModel);
}
public async Task <DynamicListResponseDataModel> FetchData(DynamicList dynamicList, DynamicListFetchDataModel fetchDataModel)
{
if (!string.IsNullOrEmpty(fetchDataModel.TextSearch))
{
fetchDataModel.TextSearch = fetchDataModel.TextSearch.Trim();
}
var databaseConnection = await _databaseServiceProvider.GetOneDatabaseConnectionAsync(dynamicList.ListDatasource.DatabaseConnectionOptions.DatabaseConnectionId);
var foundDynamicListQuery = _dynamicListQueryDatabases.First(a => a.ConnectionType == databaseConnection.GetConnectionType());
return(await foundDynamicListQuery.Query(databaseConnection, dynamicList, fetchDataModel));
}
public Task <DynamicListResponseDataModel> Query(DatabaseConnection databaseConnection, DynamicList dynamicList, DynamicListFetchDataModel fetchDataModel)
{
var response = new DynamicListResponseDataModel();
var hasRows = false;
using (var mysqlDbConnection = new MySqlConnection(databaseConnection.ConnectionString))
{
var combinedQuery =
_builder
.Init(
dynamicList.ListDatasource.DatabaseConnectionOptions.Query,
fetchDataModel.FilledParameterOptions.FilledParameters,
options =>
{
options.ContainsOperatorFormat = "LIKE CONCAT('%', {0},'%')";
options.FieldFormat = "`{0}`";
options.DateCompareFormat = "date({0}){1}date({2}) {3}";
})
.AddTextSearch(fetchDataModel.TextSearch, dynamicList.ColumnsList.ColumnDefs.Where(a => a.SearchOptions.AllowTextSearch).Select(b => b.Name))
.AddFilter(fetchDataModel.FilterGroupOptions.FilterGroups)
.AddSort(fetchDataModel.SortOptions.SortableFields)
.AddPagination(fetchDataModel.PaginationOptions.PageNumber, fetchDataModel.PaginationOptions.PageSize)
.Build();
mysqlDbConnection.Open();
using (var cmd = new MySqlCommand(combinedQuery.CombinedQuery, mysqlDbConnection))
{
foreach (var param in combinedQuery.Parameters)
{
if (param.IsReplacedValue)
{
var castObject = _cSharpMapper.GetCSharpObjectByType(param.Value, param.ReplaceValueType);
cmd.Parameters.Add(
new MySqlParameter(
param.Name, _mySqlMapper.GetMySqlDbType(param.ReplaceValueType))
{
Value = castObject,
Direction = System.Data.ParameterDirection.Input
});
}
else
{
cmd.Parameters.Add(
new MySqlParameter(
param.Name, GetMySqlDbType(param, out var castObj))
{
Value = castObj,
Direction = System.Data.ParameterDirection.Input
});
public Task <DynamicListResponseDataModel> Query(DatabaseConnection databaseConnection, DynamicList dynamicList, DynamicListFetchDataModel fetchDataModel)
{
var response = new DynamicListResponseDataModel();
var hasRows = false;
using (var postgreDbConnection = new NpgsqlConnection(databaseConnection.ConnectionString))
{
var combinedQuery =
_builder
.Init(
dynamicList.ListDatasource.DatabaseConnectionOptions.Query,
fetchDataModel.FilledParameterOptions.FilledParameters)
.AddTextSearch(fetchDataModel.TextSearch, dynamicList.ColumnsList.ColumndDefs.Where(a => a.SearchOptions.AllowTextSearch).Select(b => b.Name))
.AddFilter(fetchDataModel.FilterGroupOptions.FilterGroups)
.AddSort(fetchDataModel.SortOptions.SortableFields)
.AddPagination(fetchDataModel.PaginationOptions.PageNumber, fetchDataModel.PaginationOptions.PageSize)
.Build();
postgreDbConnection.Open();
using (var cmd = new NpgsqlCommand(combinedQuery.CombinedQuery, postgreDbConnection))
{
foreach (var param in combinedQuery.Parameters)
{
if (param.IsReplacedValue)
{
var castObject = _cSharpMapper.GetCSharpObjectByType(param.Value, param.ReplaceValueType);
cmd.Parameters.Add(
new NpgsqlParameter(
param.Name, _postgreSqlMapper.GetNpgsqlDbType(param.ReplaceValueType))
{
Value = castObject,
Direction = System.Data.ParameterDirection.Input
});
}
else
{
cmd.Parameters.Add(
new NpgsqlParameter(
param.Name, GetNpgsqlDbType(param, out var castObject))
{
Value = castObject,
Direction = System.Data.ParameterDirection.Input
});
