public async Task <IEnumerable <string> > GlobalUsers__Save(IEnumerable <string> newEmails, IEnumerable <string> oldEmails, int databaseId, bool returnEmailsForCreation = false) { var result = new List <string>(); var conn = await GetConnectionAsync(); using (var cmd = conn.CreateCommand()) { // Parameters var newEmailsTable = RepositoryUtilities.DataTable(newEmails.Select(e => new StringListItem { Id = e })); var newEmailsTvp = new SqlParameter("@NewEmails", newEmailsTable) { TypeName = $"[dbo].[StringList]", SqlDbType = SqlDbType.Structured }; var oldEmailsTable = RepositoryUtilities.DataTable(oldEmails.Select(e => new StringListItem { Id = e })); var oldEmailsTvp = new SqlParameter("@OldEmails", oldEmailsTable) { TypeName = $"[dbo].[StringList]", SqlDbType = SqlDbType.Structured }; cmd.Parameters.Add(newEmailsTvp); cmd.Parameters.Add(oldEmailsTvp); cmd.Parameters.Add("@DatabaseId", databaseId); cmd.Parameters.Add("@ReturnEmailsForCreation", returnEmailsForCreation); // Command cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = $"[dal].[{nameof(GlobalUsers__Save)}]"; // Execute and load if (returnEmailsForCreation) { using var reader = await cmd.ExecuteReaderAsync(); while (await reader.ReadAsync()) { result.Add(reader.String(0)); } } else { await cmd.ExecuteNonQueryAsync(); } } return(result); }
/// <summary> /// Takes a list of (Id, State, Error), and updates the state of every email with a given Id to the given state. /// It also marks [StateSince] to the current time and persists the given Error in the Error column if the state is negative /// </summary> public async Task Notifications_Emails__UpdateState(int tenantId, IEnumerable <IdStateErrorTimestamp> updates, CancellationToken cancellation = default) { updates ??= new List <IdStateErrorTimestamp>(); if (!updates.Any()) { return; } // Prep connection string connString = await _shardResolver.GetConnectionString(tenantId, cancellation); using var conn = new SqlConnection(connString); // Command and parameters using var cmd = new SqlCommand($"[dal].[{nameof(Notifications_Emails__UpdateState)}]", conn) { CommandType = CommandType.StoredProcedure }; var updatesTable = RepositoryUtilities.DataTable(updates); SqlParameter updatesTvp = new SqlParameter("@Updates", updatesTable) { TypeName = $"[dbo].[{nameof(IdStateErrorTimestamp)}List]", SqlDbType = SqlDbType.Structured }; cmd.Parameters.Add(updatesTvp); // Execute the Query await conn.OpenAsync(cancellation); await ExponentialBackoff(cancellation, async() => { await cmd.ExecuteNonQueryAsync(cancellation); }, nameof(Notifications_Emails__UpdateState)); }