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));
}
