public static async Task <BotConversationState> QueryItemsAsync(string ticketId, ILogger log)
{
try
{
//Connect to DB
await EnsureCosmosDBAsync(log);
var sqlQueryText = "SELECT * FROM c WHERE c.id = '" + ticketId + "'"; //TODO: add parameter
QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText);
FeedIterator <BotConversationState> queryResultSetIterator = botStateContainer.GetItemQueryIterator <BotConversationState>(queryDefinition);
List <BotConversationState> botConversationStates = new List <BotConversationState>();
while (queryResultSetIterator.HasMoreResults)
{
FeedResponse <BotConversationState> currentResultSet = await queryResultSetIterator.ReadNextAsync();
foreach (BotConversationState botConversationState in currentResultSet)
{
botConversationStates.Add(botConversationState);
log.LogInformation("\tRead conversation ID {0} with watermark {1}\n", botConversationState.BotConversationId, botConversationState.BotWatermark);
}
}
return(botConversationStates[0]);
}
catch (Exception ex)
{
log.LogError("Exception occurred in QueryItemsAsync: {1}", ex);
throw;
}
}
public static async Task <List <NumOfInjuredCount> > AggInjuredCount(
[HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "agg-injured--count")] HttpRequestMessage request,
[SignalR(HubName = "CounterHub")] IAsyncCollector <SignalRMessage> signalRMessages,
ILogger log)
{
cosmosClient = new CosmosClient(EndpointUri, PrimaryKey);
database = cosmosClient.GetDatabase(databaseId);
container = database.GetContainer(containerId);
List <NumOfInjuredCount> eventsCountArray = new List <NumOfInjuredCount>()
{
};
var sqlQueryText = "SELECT c.num_of_injured as label, count(c.event_id) as y from c " +
"WHERE c.is_false_alarm = 'False' " +
"GROUP BY c.num_of_injured";
Console.WriteLine("Running query: {0}\n", sqlQueryText);
QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText);
FeedIterator <NumOfInjuredCount> queryResultSetIterator = container.GetItemQueryIterator <NumOfInjuredCount>(queryDefinition);
while (queryResultSetIterator.HasMoreResults)
{
FeedResponse <NumOfInjuredCount> currentResultSet = await queryResultSetIterator.ReadNextAsync();
foreach (NumOfInjuredCount i in currentResultSet)
{
Console.WriteLine("\tRead {0}\n", i);
eventsCountArray.Add(i);
}
}
return(eventsCountArray);
}
public static async Task <List <CityCount> > AggCityCount(
[HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "agg-city-count")] HttpRequestMessage request,
ILogger log)
{
cosmosClient = new CosmosClient(EndpointUri, PrimaryKey);
database = cosmosClient.GetDatabase(databaseId);
container = database.GetContainer(containerId);
List <CityCount> eventsCountArray = new List <CityCount>()
{
};
var sqlQueryText = "SELECT c.city as name, count(c.event_id) as y from c " +
"GROUP BY c.city";
Console.WriteLine("Running query: {0}\n", sqlQueryText);
QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText);
FeedIterator <CityCount> queryResultSetIterator = container.GetItemQueryIterator <CityCount>(queryDefinition);
while (queryResultSetIterator.HasMoreResults)
{
FeedResponse <CityCount> currentResultSet = await queryResultSetIterator.ReadNextAsync();
foreach (CityCount i in currentResultSet)
{
Console.WriteLine("\tRead {0}\n", i);
eventsCountArray.Add(i);
}
}
return(eventsCountArray);
}
public static async Task <List <EventdayCount> > AggEventsEachDay(
[HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "agg-event-day")] HttpRequestMessage request,
[SignalR(HubName = "CounterHub")] IAsyncCollector <SignalRMessage> signalRMessages,
ILogger log)
{
cosmosClient = new CosmosClient(EndpointUri, PrimaryKey);
database = cosmosClient.GetDatabase(databaseId);
container = database.GetContainer(containerId);
List <EventdayCount> eventsCountArray = new List <EventdayCount>()
{
};
var sqlQueryText = "SELECT table.time as x, count(table.event_id) as y from" +
" (SELECT c.event_id as event_id, SUBSTRING(c.time, 0, 10) as time FROM c) as table" +
" GROUP BY table.time";
Console.WriteLine("Running query: {0}\n", sqlQueryText);
QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText);
FeedIterator <EventdayCount> queryResultSetIterator = container.GetItemQueryIterator <EventdayCount>(queryDefinition);
while (queryResultSetIterator.HasMoreResults)
{
FeedResponse <EventdayCount> currentResultSet = await queryResultSetIterator.ReadNextAsync();
foreach (EventdayCount i in currentResultSet)
{
Console.WriteLine("\tRead {0}\n", i);
eventsCountArray.Add(i);
}
}
eventsCountArray.Sort((x, y) => DateTime.Compare(x.x, y.x));
return(eventsCountArray);
}
public static async Task <List <Events> > GetHistoryEvents(
[HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "get_history_events")] HttpRequestMessage request,
ILogger log)
{
cosmosClient = new CosmosClient(EndpointUri, PrimaryKey);
database = cosmosClient.GetDatabase(databaseId);
container = database.GetContainer(containerId);
List <Events> eventsCountArray = new List <Events>()
{
};
var sqlQueryText = "SELECT * from c";
Console.WriteLine("Running query: {0}\n", sqlQueryText);
QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText);
FeedIterator <Events> queryResultSetIterator = container.GetItemQueryIterator <Events>(queryDefinition);
while (queryResultSetIterator.HasMoreResults)
{
FeedResponse <Events> currentResultSet = await queryResultSetIterator.ReadNextAsync();
foreach (Events i in currentResultSet)
{
Console.WriteLine("\tRead {0}\n", i);
eventsCountArray.Add(i);
}
}
return(eventsCountArray);
}
public async Task <IActionResult> Index()
{
//get countries for search list
var countries = await GetCountriesListAsync();
ViewData["CountriesList"] = countries;
//get the list of all locations from CosmosDb
var locations = new List <LocationModel>();
var iterator = docContainer.GetItemQueryIterator <LocationModel>();
while (iterator.HasMoreResults)
{
var pageOfLocations = await iterator.ReadNextAsync();
locations.AddRange(pageOfLocations.ToList());
}
//pass locations and countries
return(View(locations));
}
static async Task GetAppsAsync()
{
string query = "SELECT * FROM c";
var appsIterator = _container.GetItemQueryIterator <StoreApp>(query);
var apps = await appsIterator.ReadNextAsync();
foreach (var item in apps)
{
Console.WriteLine($"{item.Name} - {item.Version} - {item.Developer.Name} - {item.Category}");
}
}
public async Task <IEnumerable <T> > GetItemsAsync(string queryString)
{
var query = _container.GetItemQueryIterator <T>(new QueryDefinition(queryString));
List <T> results = new List <T>();
while (query.HasMoreResults)
{
var response = await query.ReadNextAsync();
results.AddRange(response.ToList());
}
return(results);
}
public async Task <CosmosListPage <T> > GetItemsAsync(IQueryable <T> queryable, QueryRequestOptions requestOptions, CosmosListOptions listOptions)
{
IQueryable <T> filteredQueryable = queryable;
if (listOptions.Search != null && listOptions.SearchOn != null)
{
filteredQueryable = ApplySearchToQueryable(listOptions.Search, listOptions.SearchOn, filteredQueryable);
}
if (listOptions.Filters.Any())
{
filteredQueryable = ApplyFiltersToQueryable(listOptions.Filters, filteredQueryable);
}
if (listOptions.Sort != null)
{
filteredQueryable = ApplySortToQueryable(listOptions, filteredQueryable);
}
QueryDefinition queryDefinition = filteredQueryable.ToQueryDefinition();
FeedIterator <T> queryResultSetIterator = _container.GetItemQueryIterator <T>(queryDefinition, listOptions.ContinuationToken, requestOptions);
List <T> results = new List <T>();
FeedResponse <T> currentResultSet = await queryResultSetIterator.ReadNextAsync();
foreach (T document in currentResultSet)
{
results.Add(document);
}
Response <int> count = await filteredQueryable.CountAsync();
return(new CosmosListPage <T>()
{
Meta = new CosmosMeta()
{
PageSize = (int)requestOptions.MaxItemCount, Total = count.Resource, ContinuationToken = currentResultSet.ContinuationToken
},
Items = results
});
}
static async Task ReadAllData()
{
var sqlQueryText = "SELECT * FROM films";
QueryDefinition queryDefinition = new QueryDefinition(sqlQueryText);
FeedIterator <Film> queryResultSetIterator = container.GetItemQueryIterator <Film>(queryDefinition);
List <Film> films = new List <Film>();
while (queryResultSetIterator.HasMoreResults)
{
FeedResponse <Film> currentResultSet = await queryResultSetIterator.ReadNextAsync();
foreach (Film f in currentResultSet)
{
films.Add(f);
Console.WriteLine($"\tRead {f.Country}\n {f.Name}\n {f.EnderDate}");
}
}
}
