public override async Task <long> CreateAsync(Document document, CancellationToken cancellationToken) { // TODO: cache var schema = (await FieldStore.FindAllAsync(cancellationToken)).ToDictionary(field => field.Id); var dynFields = document.Fields.Where(prop => schema[prop.Key].Type.GetSqlDbType() != null).Select(prop => $"{prop.Key}").ToList(); var dynColNames = string.Join("", dynFields.Select(dynField => $",[{dynField}]")); var dynParamNames = string.Join("", dynFields.Select(dynField => $",@{dynField}")); var parameters = new List <SqlParameter> { Connection.CreateCommandParameter("@CreatedDate", SqlDbType.DateTimeOffset, DateTime.UtcNow), Connection.CreateCommandParameter("@ModifiedDate", SqlDbType.DateTimeOffset, DateTime.UtcNow), Connection.CreateCommandParameter("@ParentId", SqlDbType.NVarChar, document.ParentId ?? string.Empty), }; parameters.AddRange(DocumentHelpers.CreateCommandParameters(document, schema)); var cmdText = $@"INSERT INTO [Document] ( [CreatedDate], [ModifiedDate], [ParentId] {dynColNames} ) VALUES ( @CreatedDate, @ModifiedDate, @ParentId {dynParamNames} ); SELECT SCOPE_IDENTITY();"; var result = await Connection !.ExecuteScalarAsync(Connection.CreateCommand(cmdText, parameters), cancellationToken); var id = decimal.ToInt64((decimal)result); return(id); }
// TODO: add parameter to define view (e.g.: Id, OwnerId, DynProp1, DynProp4) public override async Task <IList <Document> > FindAllAsync(DocumentQueryOptions options, CancellationToken cancellationToken) { // TODO: cache var schema = (await FieldStore.FindAllAsync(cancellationToken)).Where(field => !options.FieldNames.Any() || options.FieldNames.Contains(field.Id)).ToDictionary(field => field.Id); var dynColNames = string.Join("", schema.Keys.Select(dynField => $",[{dynField}]")); var dynParamNames = string.Join("", schema.Keys.Select(dynField => $",@{dynField}")); var whereClauses = new List <string>(); var cmdParameters = new List <SqlParameter>(); if (options.Ids.Any()) { var idsString = string.Join(',', options.Ids); whereClauses.Add(" Id IN (SELECT convert(int, value) FROM string_split(@ids, ',')) "); cmdParameters.Add(Connection.CreateCommandParameter("@ids", SqlDbType.NVarChar, idsString)); } if (options.ParentIds.Any()) { var parentIdsString = string.Join(',', options.ParentIds); whereClauses.Add(" ParentId IN (SELECT value FROM string_split(@parentIds, ',')) "); cmdParameters.Add(Connection.CreateCommandParameter("@parentIds", SqlDbType.NVarChar, parentIdsString)); } var orderClause = $" ORDER BY {(string.IsNullOrEmpty(options.OrderBy) ? "Id" : options.OrderBy)} {(options.OrderAsc ? string.Empty : "DESC")}"; var skipClause = options.Offset > 0 || options.Limit > 0 ? $" OFFSET {options.Offset} ROWS " : string.Empty; var takeClause = options.Limit > 0 ? $" FETCH NEXT {options.Limit} ROWS ONLY " : string.Empty; var cmdText = $@"SELECT [Id], [CreatedDate], [ModifiedDate], [ParentId] {dynColNames} FROM [Document] {(whereClauses.Any() ? $" WHERE {string.Join(" AND ", whereClauses)}" : string.Empty)} {orderClause} {skipClause} {takeClause}"; var documents = await Connection !.ExecuteQueryAsync(Connection.CreateCommand(cmdText, cmdParameters), (sqlReader) => DocumentHelpers.ReadDocuments(sqlReader, schema.Values), cancellationToken); return(documents); }
public override async Task <IDictionary <OptionalValue <object?>, int> > FieldUsageStatsAsync(Field field, CancellationToken cancellationToken) { var cmdText = $@"SELECT DISTINCT [{field.Id}], COUNT(1) FROM [Document] GROUP BY [{field.Id}]"; var fieldUsageStats = await Connection !.ExecuteQueryAsync(Connection.CreateCommand(cmdText), (sqlReader) => DocumentHelpers.ReadFieldUsageStats(sqlReader, field), cancellationToken); return(fieldUsageStats); }
public override async Task <Dictionary <string, int> > CountsByParentsAsync(CancellationToken cancellationToken) { var cmdText = $@"SELECT ParentId, COUNT(1) FROM Document GROUP BY ParentId"; var countByFolders = await Connection !.ExecuteQueryAsync(Connection.CreateCommand(cmdText), (sqlReader) => DocumentHelpers.ReadCountsByParents(sqlReader), cancellationToken); return(countByFolders); }