/// <summary>
/// SQL for updating a user preference
/// </summary>
private async Task <int> UpdateUserPreference(UserPrefDataModel userPref, UserPrefDataModel existing)
{
Log.LogDebug($"PreferenceRepository/UpdateUserPreference: user preference={JsonConvert.SerializeObject(userPref)}))')");
var upsertedCount = 0;
if (existing != null)
{
existing.PreferenceJson = userPref.PreferenceJson;
existing.SchemaVersion = userPref.SchemaVersion;
var update = $@"UPDATE UserPreference
SET Value = @PreferenceJson,
SchemaVersion = @SchemaVersion
WHERE UserPreferenceID = @UserPreferenceID";
upsertedCount = await ExecuteWithAsyncPolicy(update, existing);
Log.LogDebug($"PreferenceRepository/UpdateUserPreference: upserted {upsertedCount} rows");
}
else
{
Log.LogDebug($"PreferenceRepository/UpdateUserPreference: No user preference found to update");
}
return(upsertedCount);
}
/// <summary>
/// SQL for deleting a user preference
/// </summary>
private async Task <int> DeleteUserPreference(UserPrefDataModel userPref, UserPrefDataModel existing)
{
Log.LogDebug($"PreferenceRepository/DeleteUserPreference: user preference={JsonConvert.SerializeObject(userPref)}))')");
var upsertedCount = 0;
if (existing != null)
{
const string delete =
@"DELETE FROM UserPreference
WHERE UserPreferenceID = @UserPreferenceID";
upsertedCount = await ExecuteWithAsyncPolicy(delete, existing);
Log.LogDebug($"PreferenceRepository/DeleteUserPreference: deleted {upsertedCount} rows");
}
else
{
Log.LogDebug($"PreferenceRepository/DeleteUserPreference: No user preference found to delete");
}
return(upsertedCount);
}
/// <summary>
/// Create, update or delete a user preference.
/// </summary>
private async Task <int> UpsertUserPreference(PreferenceEventType eventType, Guid?prefKeyUID, string prefKeyName, Guid userUID, string schemaVersion = null, string prefJson = null)
{
// Look up the preference key to get the foreign key for user preferences
var prefKey = await GetPreferenceKey(prefKeyUID, prefKeyName);
if (prefKey == null)
{
return(0);
}
var userPref = new UserPrefDataModel
{
PreferenceKeyID = prefKey.PreferenceKeyID,
SchemaVersion = schemaVersion,
UserUID = userUID.ToString(),
PreferenceJson = prefJson
};
var upsertedCount = 0;
var existing = await GetUserPreference(userUID, prefKey.PreferenceKeyID);
if (eventType == PreferenceEventType.CreateUserPreferenceEvent)
{
upsertedCount = await CreateUserPreference(userPref, existing);
}
if (eventType == PreferenceEventType.UpdateUserPreferenceEvent)
{
upsertedCount = await UpdateUserPreference(userPref, existing);
}
if (eventType == PreferenceEventType.DeleteUserPreferenceEvent)
{
upsertedCount = await DeleteUserPreference(userPref, existing);
}
return(upsertedCount);
}
/// <summary>
/// SQL for creating a user preference
/// </summary>
private async Task <int> CreateUserPreference(UserPrefDataModel userPref, UserPrefDataModel existing)
{
Log.LogDebug($"PreferenceRepository/CreateUserPreference: user preference={JsonConvert.SerializeObject(userPref)}))')");
var upsertedCount = 0;
if (existing == null)
{
var insert = @"INSERT UserPreference
(UserUID, fk_PreferenceKeyID, Value, SchemaVersion)
VALUES
(@UserUID, @PreferenceKeyID, @PreferenceJson, @SchemaVersion)";
upsertedCount = await ExecuteWithAsyncPolicy(insert, userPref);
Log.LogDebug($"PreferenceRepository/CreateUserPreference: inserted {upsertedCount} rows");
}
else
{
Log.LogDebug("PreferenceRepository/CreateUserPreference: No action as user preference already exists.");
}
return(upsertedCount);
}
