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