// https://stackoverflow.com/questions/18901545/return-result-from-select-query-in-stored-procedure-to-a-list public static async Task <IList <ParticipantTableEntity> > GetParticipantsFromSqlAsync(string sqlConnectionString) { var testList = new List <ParticipantTableEntity>(); using (var SqlConnection = new SqlConnection(sqlConnectionString)) { var command = new SqlCommand("GetParticipants", SqlConnection); command.CommandType = System.Data.CommandType.StoredProcedure; await SqlConnection.OpenAsync(); var reader = await command.ExecuteReaderAsync(); while (await reader.ReadAsync()) { var test = new ParticipantTableEntity(); //test.ID = int.Parse(reader["ID"].ToString()); //test.Name = reader["Name"].ToString(); testList.Add(test); } SqlConnection.Close(); } return(testList); }
public static async Task RunAsync( [ServiceBusTrigger("participants", Connection = "AzureWebJobsServiceBus")] ParticipantTableEntity myQueueItem, ILogger log, ExecutionContext context) { config = Configuration.InitializeConfiguration(config, context); KeyVaultExtensions.InitializeConfiguration(config); keyVaultClient = KeyVaultExtensions.GetInstance(); logging = log; var tableStorageAccountConnectionString = await KeyVaultExtensions.GetSecretAsync(Constants.KeyVaultConstants.AzureWebjobsStorageKey); cloudTableClient = CloudTableClientExtensions.InitializeCloudTableClientAsync(cloudTableClient, tableStorageAccountConnectionString); logging.LogInformation($"C# ServiceBus queue trigger function processed message: {myQueueItem.DisplayName}"); var table = cloudTableClient.GetTableReference("CascadiaGamersParticipants"); var query = new TableQuery <ParticipantTableEntity>().Where(TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.Equal, myQueueItem.RowKey));; Guid.TryParse(myQueueItem.RowKey, out var participantId); if (!participants.ContainsKey(participantId)) { await AddParticipantToCacheAsync(table, query); } table = cloudTableClient.GetTableReference("CascadiaGamersDonations"); var donationQuery = new TableQuery <DonationTableEntity>() .Where(TableQuery.GenerateFilterCondition("ParticipantId", QueryComparisons.Equal, myQueueItem.ParticipantId.ToString())); await AddDonationToCacheAsync(table, donationQuery, participants[participantId]); logging.LogInformation($"Getting donations for {myQueueItem.DisplayName} at: {DateTime.Now}"); var donorDriveClient = new DonorDriveClient(); var donations = await donorDriveClient.GetDonorParticipantsAsync(uint.Parse(myQueueItem.ParticipantId)); logging.LogInformation($"Retrieved {donations.Count()} donations for {myQueueItem.DisplayName} at: {DateTime.Now}"); var participantDto = participants[participantId]; var newDonations = new List <Donor>(); foreach (var donation in donations) { var donationid = participantDto.CreateGuidFromSHA256Hash(donation); if (!participantDto.Donations.ContainsKey(donationid)) { participantDto.Donations.Add(donationid, donation); newDonations.Add(donation); } } var donationsForTableStorage = AutoMapper.Mapper.Map <List <DonationTableEntity> >(donations); donationsForTableStorage.Select( d => { d.ParticipantUniqueIdentifier = participantDto.CreateGuidFromSHA256Hash().ToString(); return(d); } ).ToList(); logging.LogInformation($"Updating or Inserting {donationsForTableStorage.Count} donations for {myQueueItem.DisplayName} to Table Storage at: {DateTime.Now}"); await TableStorageExtensions <DonationTableEntity> .WriteToTableStorageAsync(cloudTableClient, donationsForTableStorage, "CascadiaGamersDonations"); logging.LogInformation($"Updating or Inserting {donationsForTableStorage.Count} donations for {myQueueItem.DisplayName} to Sql Server at: {DateTime.Now}"); var sqlConnectionString = await KeyVaultExtensions.GetSecretAsync(Constants.KeyVaultConstants.SqlConnectionString); await SqlExtensions <DonationTableEntity> .WriteDonationsToSqlAsync(donationsForTableStorage, sqlConnectionString); logging.LogInformation($"Completed upserting {donationsForTableStorage.Count} donations for {myQueueItem.DisplayName} to Sql Server at: {DateTime.Now}"); if (newDonations.Count() > 0) { var donationsForNewDonationsQueue = AutoMapper.Mapper.Map <List <DonationTableEntity> >(newDonations); var queueConnectionString = await KeyVaultExtensions.GetSecretAsync(Constants.KeyVaultConstants.ServiceBusConnectionString); var queueName = "newdonations"; queueClient = QueueClientExtensions.InitializeQueueClient(queueClient, queueConnectionString, queueName); logging.LogInformation($"Adding {donationsForNewDonationsQueue.Count} new donations for {myQueueItem.DisplayName} to Service Bus at: {DateTime.Now}"); await QueueExtensions <DonationTableEntity> .WriteToQueueAsync(queueClient, donationsForNewDonationsQueue, queueName); } }