/// <summary> /// Get the formdata based on the input parameters /// </summary> /// <param name="reporteeId">the id of the reportee</param> /// <param name="reporteeElementId">the id of the reporteeelement</param> /// <param name="formId">the id of the form</param> /// <returns>the form data for the given parameters</returns> public async Task <FormData> GetFormDataFromCollectionAsync(string reporteeId, string reporteeElementId, string formId) { try { string sqlQuery = $"SELECT * FROM FORMDATA WHERE FORMDATA.reporteeElementId = '{reporteeElementId}' and FORMDATA.formId = '{formId}'"; IDocumentQuery <dynamic> query = _client.CreateDocumentQuery(_collectionUri, sqlQuery, new FeedOptions { PartitionKey = new PartitionKey(reporteeId) }).AsDocumentQuery(); FormData formData = null; while (query.HasMoreResults) { FeedResponse <FormData> res = await query.ExecuteNextAsync <FormData>(); if (res.Count != 0) { formData = res.First(); break; } } return(formData); } catch (DocumentClientException e) { if (e.StatusCode == System.Net.HttpStatusCode.NotFound) { return(null); } else { throw; } } }
/// <summary> /// Check if a point or polygon is valid using built-in functions. An important thing to note is that since Cosmos DB's query is designed to handle heterogeneous types, /// bad input parameters will evaluate to "undefined" and get skipped over instead of returning an error. For debugging and fixing malformed geospatial objects, please /// use the built-in functions shown below. /// </summary> private static async Task CheckIfPointOrPolygonIsValid() { Console.WriteLine("Checking if a point is valid ..."); QueryDefinition parameterizedSQLQuery = new QueryDefinition("SELECT ST_ISVALID(@point), ST_ISVALIDDETAILED(@point)") .WithParameter("@point", new Point(31.9, -132.8)); FeedResponse <dynamic> results = await container.GetItemQueryIterator <dynamic>(parameterizedSQLQuery).ReadNextAsync(); Console.WriteLine($"Point Valid: {results.First()}"); Console.WriteLine("Checking if a polygon is valid ..."); QueryDefinition parameterizedSQLQuery2 = new QueryDefinition("SELECT ST_ISVALID(@polygon), ST_ISVALIDDETAILED(@polygon)") .WithParameter("@polygon", new Polygon(new[] { new LinearRing(new[] { new Position(31.8, -5), new Position(32, -5), new Position(32, -4.7), new Position(31.8, -4.7) }) })); FeedResponse <dynamic> results2 = await container.GetItemQueryIterator <dynamic>(parameterizedSQLQuery2).ReadNextAsync(); Console.WriteLine($"Polygon Valid: {results2.First()}"); }
public async Task <Game> GetGameInfrequentAsync(String gameId) { //TIP: use cross-partition query for relatively infrequent queries. They're served from the index too Uri collectionUri = UriFactory.CreateDocumentCollectionUri(DatabaseName, PlayerLookupCollectionName); FeedOptions feedOptions = new FeedOptions { EnableCrossPartitionQuery = true, MaxDegreeOfParallelism = -1 }; IDocumentQuery <Game> query = client.CreateDocumentQuery <Game>(collectionUri, feedOptions) .Where(g => g.GameId == gameId).AsDocumentQuery(); while (query.HasMoreResults) { FeedResponse <Game> response = await query.ExecuteNextAsync <Game>(); if (response.Count > 0) { return(response.First()); } } return(null); }
public async Task <IActionResult> Run( [HttpTrigger(AuthorizationLevel.Anonymous, "delete", Route = "Contact/{id}")] HttpRequest req, string id) { IActionResult result = null; try { QueryDefinition queryDefinition = new QueryDefinition( $"SELECT * FROM {_contactContainer.Id} c WHERE c.id = @id") .WithParameter("@id", id); FeedIterator <Contact> iterator = _contactContainer.GetItemQueryIterator <Contact> ( queryDefinition, requestOptions: new QueryRequestOptions() { MaxItemCount = 1 } ); while (iterator.HasMoreResults) { FeedResponse <Contact> response = await iterator.ReadNextAsync(); Contact contact = response.First(); if (contact == null) { _logger.LogWarning($"Contact with id: {id} doesn't exist"); result = new StatusCodeResult(StatusCodes.Status404NotFound); } ItemResponse <Contact> itemResponse = await _contactContainer.DeleteItemAsync <Contact> ( id, new PartitionKey(contact.ContactType) ); result = new StatusCodeResult(StatusCodes.Status204NoContent); } } catch (CosmosException cex) { var statusCode = cex.StatusCode; _logger.LogError($"Cosmos DB Exception. Status code {statusCode}. Error thrown: {cex.Message}"); result = CosmosExceptionHandler.HandleCosmosException(statusCode); } catch (Exception ex) { _logger.LogError($"Internal Server Error. Exception thrown: {ex.Message}"); result = new StatusCodeResult(StatusCodes.Status500InternalServerError); } return(result); }
public bool HasEdge(Attributes.Edge edge, GraphObject from, GraphObject to) { bool hasEdge; string findEdgeQuery = $"g.E().hasLabel('{edge.EdgeName}').has('_vertexId', '{ from.GraphKey }').has('_sink', '{to.GraphKey}').count()"; FeedResponse <dynamic> reponse = ExecuteCommandQueryDynamic(findEdgeQuery); hasEdge = Convert.ToInt32(reponse.First()) > 0; return(hasEdge); }
public async Task <IActionResult> Run( [HttpTrigger(AuthorizationLevel.Anonymous, "put", Route = "contacts/{id}")] HttpRequest req, string id) { IActionResult returnValue = null; string requestBody = await new StreamReader(req.Body).ReadToEndAsync(); var updatedContact = JsonConvert.DeserializeObject <Contact>(requestBody); updatedContact.ContactId = id; try { QueryDefinition getContactQueryDefinition = new QueryDefinition( $"SELECT * FROM {_contactContainer.Id} c WHERE c.id = @id") .WithParameter("@id", id); FeedIterator <Contact> getResultSet = _contactContainer.GetItemQueryIterator <Contact> ( getContactQueryDefinition, requestOptions: new QueryRequestOptions() { MaxItemCount = 1 } ); while (getResultSet.HasMoreResults) { FeedResponse <Contact> response = await getResultSet.ReadNextAsync(); Contact contact = response.First(); if (contact == null) { _logger.LogError($"Couldn't find contact with {id}"); returnValue = new StatusCodeResult(StatusCodes.Status404NotFound); } var oldItem = await _contactContainer.ReadItemAsync <Contact>(id, new PartitionKey(contact.ContactType)); var replaceContact = await _contactContainer.ReplaceItemAsync(updatedContact, oldItem.Resource.ContactId, new PartitionKey(oldItem.Resource.ContactType)); returnValue = new OkObjectResult(replaceContact); } } catch (Exception ex) { _logger.LogError($"Could not update item {id}. Exception thrown: {ex.Message}"); returnValue = new StatusCodeResult(StatusCodes.Status500InternalServerError); } return(returnValue); }
/// <inheritdoc/> public async Task <int> GetMatchingWorkflowInstanceCountForSubjectsAsync(IEnumerable <string> subjects) { QueryDefinition spec = BuildFindInstanceIdsSpec(subjects, 1, 0, true); FeedIterator <int> iterator = this.Container.GetItemQueryIterator <int>(spec, null, new QueryRequestOptions { MaxItemCount = 1 }); // There will always be a result so we don't need to check... FeedResponse <int> result = await Retriable.RetryAsync(() => iterator.ReadNextAsync()).ConfigureAwait(false); return(result.First()); }
public async Task <IActionResult> Run( [HttpTrigger(AuthorizationLevel.Anonymous, "delete", Route = "TaxiPrediction/{id}")] HttpRequest req, string id) { IActionResult result = null; try { QueryDefinition getContactQueryDefinition = new QueryDefinition( $"SELECT * FROM {_taxiContainer.Id} c WHERE c.id = @id") .WithParameter("@id", id); FeedIterator <TaxiTripDTO> getResultSet = _taxiContainer.GetItemQueryIterator <TaxiTripDTO> ( getContactQueryDefinition, requestOptions: new QueryRequestOptions() { MaxItemCount = 1 } ); if (getResultSet == null) { _logger.LogInformation($"TaxiTrip {id} not found!"); result = new StatusCodeResult(StatusCodes.Status404NotFound); } while (getResultSet.HasMoreResults) { FeedResponse <TaxiTripDTO> response = await getResultSet.ReadNextAsync(); TaxiTripDTO contact = response.First(); ItemResponse <TaxiTripDTO> itemResponse = await _taxiContainer.DeleteItemAsync <TaxiTripDTO> (id : id, partitionKey : new PartitionKey(contact.VendorId)); result = new OkResult(); } } catch (Exception ex) { _logger.LogError($"Internal Server Error. Exception thrown: {ex.Message}"); result = new StatusCodeResult(StatusCodes.Status500InternalServerError); } return(result); }
public async Task DatabaseQueryIterator() { List <Cosmos.Database> deleteList = new List <Cosmos.Database>(); try { string firstDb = "Abcdefg"; string secondDb = "Bcdefgh"; string thirdDb = "Zoo"; DatabaseResponse createResponse2 = await this.cosmosClient.CreateDatabaseIfNotExistsAsync(secondDb); deleteList.Add(createResponse2.Database); DatabaseResponse createResponse = await this.cosmosClient.CreateDatabaseIfNotExistsAsync(firstDb); deleteList.Add(createResponse.Database); DatabaseResponse createResponse3 = await this.cosmosClient.CreateDatabaseIfNotExistsAsync(thirdDb); deleteList.Add(createResponse3.Database); FeedIterator <DatabaseProperties> feedIterator = this.cosmosClient.GetDatabaseQueryIterator <DatabaseProperties>( new QueryDefinition("select c.id From c where c.id = @id ") .WithParameter("@id", createResponse.Database.Id), requestOptions: new QueryRequestOptions() { MaxItemCount = 1 }); FeedResponse <DatabaseProperties> iterator = await feedIterator.ReadNextAsync(this.cancellationToken); Assert.AreEqual(1, iterator.Resource.Count()); Assert.AreEqual(firstDb, iterator.First().Id); Assert.IsFalse(feedIterator.HasMoreResults); } finally { foreach (Cosmos.Database database in deleteList) { await database.DeleteAsync(cancellationToken : this.cancellationToken); } } }
public async Task ReportsInstanceNameAndToken() { string instanceName = Guid.NewGuid().ToString(); string leaseToken = Guid.NewGuid().ToString(); List <string> ranges = new List <string>() { leaseToken }; List <DocumentServiceLeaseCore> leases = new List <DocumentServiceLeaseCore>() { new DocumentServiceLeaseCore() { LeaseToken = leaseToken, Owner = instanceName } }; Mock <FeedIterator> mockIterator = new Mock <FeedIterator>(); mockIterator.Setup(i => i.ReadNextAsync(It.IsAny <CancellationToken>())).ReturnsAsync(GetResponse(HttpStatusCode.NotModified, "0:1")); Mock <DocumentServiceLeaseContainer> mockContainer = new Mock <DocumentServiceLeaseContainer>(); mockContainer.Setup(c => c.GetAllLeasesAsync()).ReturnsAsync(leases); Func <string, string, bool, FeedIterator> feedCreator = (string partitionKeyRangeId, string continuationToken, bool startFromBeginning) => { return(mockIterator.Object); }; ChangeFeedEstimatorIterator remainingWorkEstimator = new ChangeFeedEstimatorIterator( Mock.Of <ContainerInternal>(), Mock.Of <ContainerInternal>(), mockContainer.Object, feedCreator, null); FeedResponse <ChangeFeedProcessorState> firstResponse = await remainingWorkEstimator.ReadNextAsync(default(CancellationToken)); ChangeFeedProcessorState remainingLeaseWork = firstResponse.First(); Assert.AreEqual(instanceName, remainingLeaseWork.InstanceName); Assert.AreEqual(leaseToken, remainingLeaseWork.LeaseToken); }
public async Task <ExportJobOutcome> GetExportJobByHashAsync(string hash, CancellationToken cancellationToken) { EnsureArg.IsNotNullOrWhiteSpace(hash, nameof(hash)); try { IDocumentQuery <CosmosExportJobRecordWrapper> query = _documentClientScope.Value.CreateDocumentQuery <CosmosExportJobRecordWrapper>( CollectionUri, new SqlQuerySpec( GetJobByHashQuery, new SqlParameterCollection() { new SqlParameter(HashParameterName, hash), }), new FeedOptions { PartitionKey = new PartitionKey(CosmosDbExportConstants.ExportJobPartitionKey) }) .AsDocumentQuery(); FeedResponse <CosmosExportJobRecordWrapper> result = await query.ExecuteNextAsync <CosmosExportJobRecordWrapper>(); if (result.Count == 1) { // We found an existing job that matches the hash. CosmosExportJobRecordWrapper wrapper = result.First(); return(new ExportJobOutcome(wrapper.JobRecord, WeakETag.FromVersionId(wrapper.ETag))); } return(null); } catch (DocumentClientException dce) { if (dce.StatusCode == HttpStatusCode.TooManyRequests) { throw new RequestRateExceededException(dce.RetryAfter); } _logger.LogError(dce, "Failed to get an export job by hash."); throw; } }
public async Task <IActionResult> Run( [HttpTrigger(AuthorizationLevel.Anonymous, "delete", Route = "contacts/{id}")] HttpRequest req, string id) { IActionResult returnValue = null; try { QueryDefinition getContactQueryDefinition = new QueryDefinition( $"SELECT * FROM {_contactContainer.Id} c WHERE c.id = @id") .WithParameter("@id", id); FeedIterator <Contact> getResultSet = _contactContainer.GetItemQueryIterator <Contact> ( getContactQueryDefinition, requestOptions: new QueryRequestOptions() { MaxItemCount = 1 } ); while (getResultSet.HasMoreResults) { FeedResponse <Contact> response = await getResultSet.ReadNextAsync(); Contact contact = response.First(); ItemResponse <Contact> itemResponse = await _contactContainer.DeleteItemAsync <Contact> (id : id, partitionKey : new PartitionKey(contact.ContactType)); returnValue = new OkResult(); } } catch (Exception ex) { _logger.LogError($"Couldn't delete contact. Exception thrown: {ex.Message}"); returnValue = new StatusCodeResult(StatusCodes.Status500InternalServerError); } return(returnValue); }
public async Task <ExportJobOutcome> GetExportJobByHashAsync(string hash, CancellationToken cancellationToken) { EnsureArg.IsNotNullOrWhiteSpace(hash, nameof(hash)); try { var query = _queryFactory.Create <CosmosExportJobRecordWrapper>( _containerScope.Value, new CosmosQueryContext( new QueryDefinition(GetJobByHashQuery) .WithParameter(HashParameterName, hash), new QueryRequestOptions { PartitionKey = new PartitionKey(CosmosDbExportConstants.ExportJobPartitionKey) })); FeedResponse <CosmosExportJobRecordWrapper> result = await query.ExecuteNextAsync(); if (result.Count == 1) { // We found an existing job that matches the hash. CosmosExportJobRecordWrapper wrapper = result.First(); return(new ExportJobOutcome(wrapper.JobRecord, WeakETag.FromVersionId(wrapper.ETag))); } return(null); } catch (CosmosException dce) { if (dce.IsRequestRateExceeded()) { throw; } _logger.LogError(dce, "Failed to get an export job by hash."); throw; } }
public async Task IteratorTest() { string containerName = Guid.NewGuid().ToString(); string partitionKeyPath = "/users"; ContainerResponse containerResponse = await this.cosmosDatabase.CreateContainerAsync(containerName, partitionKeyPath); Assert.AreEqual(HttpStatusCode.Created, containerResponse.StatusCode); Assert.AreEqual(containerName, containerResponse.Resource.Id); Assert.AreEqual(partitionKeyPath, containerResponse.Resource.PartitionKey.Paths.First()); HashSet <string> containerIds = new HashSet <string>(); FeedIterator <ContainerProperties> resultSet = this.cosmosDatabase.GetContainerQueryIterator <ContainerProperties>(); while (resultSet.HasMoreResults) { foreach (ContainerProperties setting in await resultSet.ReadNextAsync()) { if (!containerIds.Contains(setting.Id)) { containerIds.Add(setting.Id); } } } Assert.IsTrue(containerIds.Count > 0, "The iterator did not find any containers."); Assert.IsTrue(containerIds.Contains(containerName), "The iterator did not find the created container"); resultSet = this.cosmosDatabase.GetContainerQueryIterator <ContainerProperties>($"select * from c where c.id = \"{containerName}\""); FeedResponse <ContainerProperties> queryProperties = await resultSet.ReadNextAsync(); Assert.AreEqual(1, queryProperties.Resource.Count()); Assert.AreEqual(containerName, queryProperties.First().Id); containerResponse = await containerResponse.Container.DeleteContainerAsync(); Assert.AreEqual(HttpStatusCode.NoContent, containerResponse.StatusCode); }
private async Task ReplaceDocuments() { Container container = Shared.Client.GetContainer(_databaseName, _containerName); string sql = "SELECT VALUE COUNT(c) FROM c WHERE c.isNew = true"; FeedIterator <int> iterator1 = container.GetItemQueryIterator <int>(sql); FeedResponse <int> feedResponse1 = await iterator1.ReadNextAsync(); int count = feedResponse1.First(); WriteLine($"Documents with 'isNew' flag: {count}\n"); WriteLine("Querying for documents to be updated"); sql = "SELECT * FROM c WHERE STARTSWITH(c.name, 'New Customer') = true"; FeedIterator <dynamic> feedIterator2 = container.GetItemQueryIterator <dynamic>(sql); FeedResponse <dynamic> feedResponse2 = await feedIterator2.ReadNextAsync(); List <dynamic> documentsList = feedResponse2.ToList(); WriteLine($"Found {documentsList.Count} documents to be updated"); foreach (var document in documentsList) { document.isNew = true; dynamic result = await container.ReplaceItemAsync <dynamic>(document, (string)document.id); dynamic updatedDocument = result.Resource; WriteLine($"Updated document 'isNew' flag: {updatedDocument.isNew}"); } sql = "SELECT VALUE COUNT(c) FROM c WHERE c.isNew = true"; FeedIterator <int> feedIterator3 = container.GetItemQueryIterator <int>(sql); FeedResponse <int> feedResponse3 = await feedIterator3.ReadNextAsync(); count = feedResponse3.First(); WriteLine($"Documents with 'isNew' flag: {count}\n"); }
public async Task <String> GetLatestState(String orderId) { //TIP: Use query to reconstruct the latest state (can also support <= BeforeTimestamp) Uri collectionUri = UriFactory.CreateDocumentCollectionUri(DatabaseName, EventsCollectionName); IDocumentQuery <String> query = client.CreateDocumentQuery <OrderEvent>(collectionUri) .Where(e => e.OrderId == orderId) .OrderByDescending(e => e.EventTime) .Take(1) .Select(e => e.CurrentState) .AsDocumentQuery(); while (query.HasMoreResults) { FeedResponse <String> response = await query.ExecuteNextAsync <String>(); if (response.Count > 0) { return(response.First()); } } return(null); }
/// <summary> /// Gets metadata for the given data element. /// </summary> /// <remarks> /// Include instanceGuid to avoid a cross partition query. /// </remarks> public async Task <DataElement> GetDataElement(string dataGuid, string instanceGuid = "") { DataElement dataElement = null; Uri uri = UriFactory.CreateDocumentUri("Storage", "dataElements", dataGuid); DocumentClient client = await _clientProvider.GetDocumentClient(Program.Environment); FeedOptions options; if (!string.IsNullOrEmpty(instanceGuid)) { options = new FeedOptions { PartitionKey = new PartitionKey(instanceGuid) }; } else { options = new FeedOptions { EnableCrossPartitionQuery = true }; } IDocumentQuery <DataElement> query = client .CreateDocumentQuery <DataElement>(_dataCollectionUri, options) .Where(i => i.Id == dataGuid) .AsDocumentQuery(); FeedResponse <DataElement> result = await query.ExecuteNextAsync <DataElement>(); if (result.Count > 0) { dataElement = result.First(); } return(dataElement); }
// </ReadItemAsync> // <QueryItems> private static async Task QueryItems() { //****************************************************************************************************************** // 1.4 - Query for items by a property other than Id // // NOTE: Operations like AsEnumerable(), ToList(), ToArray() will make as many trips to the database // as required to fetch the entire result-set. Even if you set MaxItemCount to a smaller number. // MaxItemCount just controls how many results to fetch each trip. //****************************************************************************************************************** Console.WriteLine("\n1.4 - Querying for a item using its AccountNumber property"); QueryDefinition query = new QueryDefinition( "select * from sales s where s.AccountNumber = @AccountInput ") .WithParameter("@AccountInput", "Account1"); FeedIterator <SalesOrder> resultSet = container.GetItemQueryIterator <SalesOrder>( query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Account1"), MaxItemCount = 1 }); List <SalesOrder> allSalesForAccount1 = new List <SalesOrder>(); while (resultSet.HasMoreResults) { FeedResponse <SalesOrder> response = await resultSet.ReadNextAsync(); SalesOrder sale = response.First(); Console.WriteLine($"\n1.4.1 Account Number: {sale.AccountNumber}; Id: {sale.Id};"); if (response.Diagnostics != null) { Console.WriteLine($" Diagnostics {response.Diagnostics.ToString()}"); } allSalesForAccount1.Add(sale); } Console.WriteLine($"\n1.4.2 Query found {allSalesForAccount1.Count} items."); // Use the same query as before but get the cosmos response message to access the stream directly FeedIterator streamResultSet = container.GetItemQueryStreamIterator( query, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Account1"), MaxItemCount = 10, MaxConcurrency = 1 }); List <SalesOrder> allSalesForAccount1FromStream = new List <SalesOrder>(); while (streamResultSet.HasMoreResults) { using (ResponseMessage responseMessage = await streamResultSet.ReadNextAsync()) { // Item stream operations do not throw exceptions for better performance if (responseMessage.IsSuccessStatusCode) { dynamic streamResponse = FromStream <dynamic>(responseMessage.Content); List <SalesOrder> salesOrders = streamResponse.Documents.ToObject <List <SalesOrder> >(); Console.WriteLine($"\n1.4.3 - Item Query via stream {salesOrders.Count}"); allSalesForAccount1FromStream.AddRange(salesOrders); } else { Console.WriteLine($"Query item from stream failed. Status code: {responseMessage.StatusCode} Message: {responseMessage.ErrorMessage}"); } } } Console.WriteLine($"\n1.4.4 Query found {allSalesForAccount1FromStream.Count} items."); if (allSalesForAccount1.Count != allSalesForAccount1FromStream.Count) { throw new InvalidDataException($"Both query operations should return the same list"); } }
// </ReadItemAsync> // <ReadAllItems> private static async Task ReadAllItems() { //****************************************************************************************************************** // 1.3 - Read all items in a container // // NOTE: Operations like AsEnumerable(), ToList(), ToArray() will make as many trips to the database // as required to fetch the entire result-set. Even if you set MaxItemCount to a smaller number. // MaxItemCount just controls how many results to fetch each trip. //****************************************************************************************************************** Console.WriteLine("\n1.3 - Read all items with query using a specific partition key"); List <SalesOrder> allSalesForAccount1 = new List <SalesOrder>(); using (FeedIterator <SalesOrder> resultSet = container.GetItemQueryIterator <SalesOrder>( queryDefinition: null, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Account1") })) { while (resultSet.HasMoreResults) { FeedResponse <SalesOrder> response = await resultSet.ReadNextAsync(); SalesOrder sale = response.First(); Console.WriteLine($"\n1.3.1 Account Number: {sale.AccountNumber}; Id: {sale.Id};"); if (response.Diagnostics != null) { Console.WriteLine($" Diagnostics {response.Diagnostics.ToString()}"); } allSalesForAccount1.AddRange(response); } } Console.WriteLine($"\n1.3.2 Read all items found {allSalesForAccount1.Count} items."); // Use the same query as before but get the cosmos response message to access the stream directly List <SalesOrder> allSalesForAccount1FromStream = new List <SalesOrder>(); using (FeedIterator streamResultSet = container.GetItemQueryStreamIterator( queryDefinition: null, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Account1") })) { while (streamResultSet.HasMoreResults) { using (ResponseMessage responseMessage = await streamResultSet.ReadNextAsync()) { // Item stream operations do not throw exceptions for better performance if (responseMessage.IsSuccessStatusCode) { dynamic streamResponse = FromStream <dynamic>(responseMessage.Content); List <SalesOrder> salesOrders = streamResponse.Documents.ToObject <List <SalesOrder> >(); Console.WriteLine($"\n1.3.3 - Read all items via stream {salesOrders.Count}"); allSalesForAccount1FromStream.AddRange(salesOrders); } else { Console.WriteLine($"Read all items from stream failed. Status code: {responseMessage.StatusCode} Message: {responseMessage.ErrorMessage}"); } } } Console.WriteLine($"\n1.3.4 Read all items found {allSalesForAccount1FromStream.Count} items."); } if (allSalesForAccount1.Count != allSalesForAccount1FromStream.Count) { throw new InvalidDataException($"Both read all item operations should return the same list"); } }
public async Task <IEnumerable <LogChange> > getLogChangesByType(string userId, TimeLine timeLineArg = null, ActivityType typeOfDocument = ActivityType.None, int count = 100) { var options = new QueryRequestOptions { PartitionKey = new PartitionKey(userId) }; var collectionParams = new List <Tuple <string, object> >(); string sqlQuery = @"SELECT * FROM c"; bool isWhereAlready = false; TimeLine timeline = timeLineArg.StartToEnd; if (userId.isNot_NullEmptyOrWhiteSpace()) { if (!isWhereAlready) { sqlQuery += @" WHERE "; isWhereAlready = true; } else { sqlQuery += " AND "; } var sqlParam = new Tuple <string, object> ("@UserId", userId); collectionParams.Add(sqlParam); sqlQuery += @" c.UserId = @UserId "; } if (typeOfDocument != ActivityType.None && typeOfDocument.ToString().isNot_NullEmptyOrWhiteSpace()) { if (!isWhereAlready) { sqlQuery += " WHERE "; isWhereAlready = true; } else { sqlQuery += " AND "; } var sqlParam = new Tuple <string, object>("@TypeOfEvent", typeOfDocument.ToString()); collectionParams.Add(sqlParam); sqlQuery += " c.TypeOfEvent = @TypeOfEvent "; } if (timeline != null) { if (!isWhereAlready) { sqlQuery += " WHERE "; isWhereAlready = true; } else { sqlQuery += " AND "; } var sqlParamStart = new Tuple <string, object>("@JsTimeOfCreationStart", timeline.Start.ToUnixTimeMilliseconds()); collectionParams.Add(sqlParamStart); var sqlParamEnd = new Tuple <string, object>("@JsTimeOfCreationEnd", timeline.End.ToUnixTimeMilliseconds()); collectionParams.Add(sqlParamEnd); sqlQuery += " c.JsTimeOfCreation >= @JsTimeOfCreationStart and c.JsTimeOfCreation < @JsTimeOfCreationEnd "; } sqlQuery += @" order by c.JsTimeOfCreation desc "; sqlQuery += @" offset 0 limit " + count + " "; QueryDefinition queryDefinition = new QueryDefinition(sqlQuery); collectionParams.ForEach((param) => { queryDefinition = queryDefinition.WithParameter(param.Item1, param.Item2); }); var database = Client.GetDatabase(dbName); Container container = database.GetContainer(collectionName); List <LogChange> retValue = new List <LogChange>(); using (FeedIterator <LogChange> resultSet = container.GetItemQueryIterator <LogChange>( queryDefinition, requestOptions: new QueryRequestOptions() { PartitionKey = new PartitionKey("Account1"), MaxItemCount = 1 })) { while (resultSet.HasMoreResults) { FeedResponse <LogChange> response = await resultSet.ReadNextAsync(); LogChange logChange = response.First(); Console.WriteLine($"\n Log change UserId is : {logChange.UserId}; Id: {logChange.Id};"); retValue.AddRange(response); } } //var querySpec = new SqlQuerySpec //{ // QueryText = sqlQuery, // Parameters = new SqlParameterCollection( // collectionParams // ) //}; //var documentCollectionUri = UriFactory.CreateDocumentCollectionUri(dbName, collectionName); //var query = Client.CreateDocumentQuery<Document>(documentCollectionUri, querySpec, options).AsDocumentQuery(); //List<LogChange> retValue = new List<LogChange>(); //while (query.HasMoreResults) //{ // var documents = await query.ExecuteNextAsync<Document>().ConfigureAwait(false); // foreach (var loadedDocument in documents) // { // LogChange logChange = new LogChange(); // byte[] allBytesbefore = loadedDocument.GetPropertyValue<byte[]>("ZippedLog"); // logChange.ZippedLog = allBytesbefore; // logChange.Id = loadedDocument.GetPropertyValue<string>("Id"); // logChange.UserId = loadedDocument.GetPropertyValue<string>("UserId"); // logChange.TypeOfEvent = loadedDocument.GetPropertyValue<string>("TypeOfEvent"); // logChange.TimeOfCreation = loadedDocument.GetPropertyValue<DateTimeOffset>("TimeOfCreation"); // logChange.JsTimeOfCreation = loadedDocument.GetPropertyValue<ulong>("JsTimeOfCreation"); // retValue.Add(logChange); // } //} return(retValue); }
private async Task DataUpdateStepAsync(WaterfallStepContext stepContext, CancellationToken cancellationToken) { var orderData = await _orderDataAccessor.GetAsync(stepContext.Context, () => new OrderData(), cancellationToken); CosmosClient client = new CosmosClient(Startup.CosmosDbEndpoint, Startup.AuthKey); database = await client.CreateDatabaseIfNotExistsAsync(databaseId); ContainerProperties containerProperties = new ContainerProperties(containerId, partitionKeyPath: Startup.PartitionKey); // Create with a throughput of 1000 RU/s container = await database.CreateContainerIfNotExistsAsync( containerProperties, throughput : 1000); //orderNum 가져오기 try { FeedIterator <DBdata> feedIterator = container.GetItemQueryIterator <DBdata>("SELECT top 1 * FROM c order by c._ts desc"); { while (feedIterator.HasMoreResults) { FeedResponse <DBdata> result = await feedIterator.ReadNextAsync(); foreach (var item in result) { orderData.OrderNum = Int32.Parse(item.id); } } } } catch (System.Exception e) { orderData.OrderNum = 0; } //ip IPHostEntry host = Dns.GetHostEntry(Dns.GetHostName()); string ipAddr = string.Empty; for (int i = 0; i < host.AddressList.Length; i++) { if (host.AddressList[i].AddressFamily == AddressFamily.InterNetwork) { ipAddr = host.AddressList[i].ToString(); } } //Order 데이터 삽입 foreach (Sandwich tempSand in orderData.Sandwiches) { string sJson = JsonConvert.SerializeObject(tempSand); var dbData = new DBdata { id = $"{++orderData.OrderNum}", Contents = tempSand, ETag = "x", AccountNumber = ipAddr }; await container.CreateItemAsync <DBdata>(dbData, new PartitionKey(dbData.AccountNumber)); } //container 변경 containerProperties = new ContainerProperties(countContainerId, partitionKeyPath: "/AccountNumber"); // Create with a throughput of 1000 RU/s container = await database.CreateContainerIfNotExistsAsync( containerProperties, throughput : 1000); //Count 데이터 삽입 foreach (Sandwich tempSand in orderData.Sandwiches) { var CountId = 0; string sauce = ""; tempSand.Sauce.Sort(); foreach (string temp in tempSand.Sauce) { sauce += temp + " "; } //CountId 찾기 try { FeedIterator <DBcount> feedIterator = container.GetItemQueryIterator <DBcount>("SELECT top 1 * FROM c order by c._ts desc"); { while (feedIterator.HasMoreResults) { FeedResponse <DBcount> result = await feedIterator.ReadNextAsync(); foreach (var item in result) { CountId = Int32.Parse(item.id) + 1; } } } } catch (System.Exception e) { CountId = 0; } try { FeedIterator <DBcount> feedIterator = container.GetItemQueryIterator <DBcount>("SELECT * FROM c WHERE c.Sauce='" + sauce + "' and c.Bread ='" + tempSand.Bread + "' and c.Menu ='" + tempSand.Menu + "'"); { if (feedIterator.HasMoreResults) { FeedResponse <DBcount> result = await feedIterator.ReadNextAsync(); DBcount res = result.First(); var count = res.Count + 1; DBcount countData = new DBcount(); countData.id = res.id; countData.Count = count; countData.Sauce = sauce; countData.Menu = tempSand.Menu; countData.ETag = "x"; countData.AccountNumber = "0"; countData.Bread = tempSand.Bread; ItemResponse <DBcount> item = await container.DeleteItemAsync <DBcount>(partitionKey : new PartitionKey("0"), id : res.id); await container.CreateItemAsync(countData, new PartitionKey("0")); } } } catch (System.Exception e) { var countData = new DBcount { id = $"{CountId}", Count = 1, Bread = $"{tempSand.Bread}", Sauce = sauce, Menu = $"{tempSand.Menu}", ETag = "x", AccountNumber = "0" }; await container.CreateItemAsync <DBcount>(countData, new PartitionKey("0")); } } }
public async Task DatabaseQueryIterator() { List <Cosmos.Database> deleteList = new List <Cosmos.Database>(); try { string firstDb = "Abcdefg"; string secondDb = "Bcdefgh"; string thirdDb = "Zoo"; DatabaseResponse createResponse2 = await this.cosmosClient.CreateDatabaseIfNotExistsAsync(secondDb); deleteList.Add(createResponse2.Database); DatabaseResponse createResponse = await this.cosmosClient.CreateDatabaseIfNotExistsAsync(firstDb); deleteList.Add(createResponse.Database); DatabaseResponse createResponse3 = await this.cosmosClient.CreateDatabaseIfNotExistsAsync(thirdDb); deleteList.Add(createResponse3.Database); using (FeedIterator <DatabaseProperties> feedIterator = this.cosmosClient.GetDatabaseQueryIterator <DatabaseProperties>( new QueryDefinition("select c.id From c where c.id = @id ") .WithParameter("@id", createResponse.Database.Id), requestOptions: new QueryRequestOptions() { MaxItemCount = 1 })) { FeedResponse <DatabaseProperties> iterator = await feedIterator.ReadNextAsync(this.cancellationToken); Assert.AreEqual(1, iterator.Resource.Count()); Assert.AreEqual(firstDb, iterator.First().Id); Assert.IsFalse(feedIterator.HasMoreResults); } using (FeedIterator feedIterator = this.cosmosClient.GetDatabaseQueryStreamIterator( "select value c.id From c ")) { while (feedIterator.HasMoreResults) { using (ResponseMessage response = await feedIterator.ReadNextAsync(this.cancellationToken)) { response.EnsureSuccessStatusCode(); using (StreamReader streamReader = new StreamReader(response.Content)) using (JsonTextReader jsonTextReader = new JsonTextReader(streamReader)) { // Output will be: // { "_rid":"","Databases":["Zoo","Abcdefg","Bcdefgh"],"_count":3} JObject jObject = await JObject.LoadAsync(jsonTextReader); Assert.IsNotNull(jObject["_rid"].ToString()); Assert.IsTrue(jObject["Databases"].ToObject <JArray>().Count > 0); Assert.IsTrue(jObject["_count"].ToObject <int>() > 0); } } } } List <string> ids = new List <string>(); using (FeedIterator <string> feedIterator = this.cosmosClient.GetDatabaseQueryIterator <string>( "select value c.id From c ")) { while (feedIterator.HasMoreResults) { FeedResponse <string> iterator = await feedIterator.ReadNextAsync(this.cancellationToken); ids.AddRange(iterator); } } Assert.IsTrue(ids.Count >= 2); } finally { foreach (Cosmos.Database database in deleteList) { await database.DeleteAsync(cancellationToken : this.cancellationToken); } } }
/// <summary> /// Asynchronously checks the current existing leases and calculates an estimate of remaining work per leased partitions. /// </summary> /// <returns>An estimate amount of remaining documents to be processed</returns> public async Task <long> GetEstimatedRemainingWork() { await this.InitializeAsync(); long remaining = 0; ChangeFeedOptions options = new ChangeFeedOptions { MaxItemCount = 1 }; foreach (DocumentServiceLease existingLease in await this.leaseManager.ListLeases()) { options.PartitionKeyRangeId = existingLease.PartitionId; options.RequestContinuation = existingLease.ContinuationToken; IDocumentQuery <Document> query = this.documentClient.CreateDocumentChangeFeedQuery(this.collectionSelfLink, options); FeedResponse <Document> response = null; try { response = await query.ExecuteNextAsync <Document>(); long parsedLSNFromSessionToken = TryConvertToNumber(ParseAmountFromSessionToken(response.SessionToken)); long lastSequenceNumber = response.Count > 0 ? TryConvertToNumber(response.First().GetPropertyValue <string>(LSNPropertyName)) : parsedLSNFromSessionToken; long partitionRemaining = parsedLSNFromSessionToken - lastSequenceNumber; remaining += partitionRemaining < 0 ? 0 : partitionRemaining; } catch (DocumentClientException ex) { ExceptionDispatchInfo exceptionDispatchInfo = ExceptionDispatchInfo.Capture(ex); DocumentClientException dcex = (DocumentClientException)exceptionDispatchInfo.SourceException; if ((StatusCode.NotFound == (StatusCode)dcex.StatusCode && SubStatusCode.ReadSessionNotAvailable != (SubStatusCode)GetSubStatusCode(dcex)) || StatusCode.Gone == (StatusCode)dcex.StatusCode) { // We are not explicitly handling Splits here to avoid any collision with an Observer that might have picked this up and managing the split TraceLog.Error(string.Format("GetEstimateWork > Partition {0}: resource gone (subStatus={1}).", existingLease.PartitionId, GetSubStatusCode(dcex))); } else if (StatusCode.TooManyRequests == (StatusCode)dcex.StatusCode || StatusCode.ServiceUnavailable == (StatusCode)dcex.StatusCode) { TraceLog.Warning(string.Format("GetEstimateWork > Partition {0}: retriable exception : {1}", existingLease.PartitionId, dcex.Message)); } else { TraceLog.Error(string.Format("GetEstimateWork > Partition {0}: Unhandled exception", ex.Error.Message)); } } } return(remaining); }
public async Task TestJsonSerializerSettings(bool useGateway) { CosmosClient cosmosClient = TestCommon.CreateCosmosClient((cosmosClientBuilder) => { if (useGateway) { cosmosClientBuilder.WithCustomJsonSerializer(new CustomJsonSerializer(CustomSerializationTests.GetSerializerWithCustomConverterAndBinder())).WithConnectionModeGateway(); } else { cosmosClientBuilder.WithCustomJsonSerializer(new CustomJsonSerializer(CustomSerializationTests.GetSerializerWithCustomConverterAndBinder())).WithConnectionModeDirect(); } }); Container container = cosmosClient.GetContainer(databaseName, partitionedCollectionName); var rnd = new Random(); var bytes = new byte[100]; rnd.NextBytes(bytes); var testDocument = new TestDocument(new KerberosTicketHashKey(bytes)); //create and read ItemResponse <TestDocument> createResponse = await container.CreateItemAsync <TestDocument>(testDocument); ItemResponse <TestDocument> readResponse = await container.ReadItemAsync <TestDocument>(testDocument.Id, new Cosmos.PartitionKey(testDocument.Name)); AssertEqual(testDocument, readResponse.Resource); AssertEqual(testDocument, createResponse.Resource); // upsert ItemResponse <TestDocument> upsertResponse = await container.UpsertItemAsync <TestDocument>(testDocument); readResponse = await container.ReadItemAsync <TestDocument>(testDocument.Id, new Cosmos.PartitionKey(testDocument.Name)); AssertEqual(testDocument, readResponse.Resource); AssertEqual(testDocument, upsertResponse.Resource); // replace ItemResponse <TestDocument> replacedResponse = await container.ReplaceItemAsync <TestDocument>(testDocument, testDocument.Id); readResponse = await container.ReadItemAsync <TestDocument>(testDocument.Id, new Cosmos.PartitionKey(testDocument.Name)); AssertEqual(testDocument, readResponse.Resource); AssertEqual(testDocument, replacedResponse.Resource); QueryDefinition sql = new QueryDefinition("select * from r"); FeedIterator <TestDocument> feedIterator = container.GetItemQueryIterator <TestDocument>( sqlQueryDefinition: sql, requestOptions: new QueryRequestOptions() { MaxItemCount = 1, PartitionKey = new Cosmos.PartitionKey(testDocument.Name), }); FeedResponse <TestDocument> queryResponse = await feedIterator.ReadNextAsync(); AssertEqual(testDocument, queryResponse.First()); //Will add LINQ test once it is available with new V3 OM // // LINQ Lambda // var query1 = client.CreateDocumentQuery<TestDocument>(partitionedCollectionUri, options) // .Where(_ => _.Id.CompareTo(String.Empty) > 0) // .Select(_ => _.Id); // string query1Str = query1.ToString(); // var result = query1.ToList(); // Assert.AreEqual(1, result.Count); // Assert.AreEqual(testDocument.Id, result[0]); // // LINQ Query // var query2 = // from f in client.CreateDocumentQuery<TestDocument>(partitionedCollectionUri, options) // where f.Id.CompareTo(String.Empty) > 0 // select f.Id; // string query2Str = query2.ToString(); // var result2 = query2.ToList(); // Assert.AreEqual(1, result2.Count); // Assert.AreEqual(testDocument.Id, result2[0]); }