public Task PutAsync(K key, V value)
{
return(ExecCommandAsync(async cmd => {
// Retrieve all rows
string commandBegin = $"INSERT INTO {tableName} (";
string commandLeftMiddle = ") VALUES (";
string commandRightMiddle = ") ON CONFLICT (id) DO UPDATE SET (";
string commandRightRightMidle = ") = (";
string commandEnd = $") WHERE {tableName}.id=@id";
var updatedColumnNames = new SCG.List <string>();
var properties = typeof(V).GetProperties();
foreach (var p in properties)
{
var propertyValue = p.GetValue(value);
var columnName = p.Name.ToLower();
if (columnName == "id")
{
Trace.Assert(object.Equals(key, propertyValue));
}
else
{
if (columnName == "created" || columnName == "updated")
{
propertyValue = DateTime.Now;
}
var param = cmd.CreateParameter();
param.ParameterName = columnName;
param.Value = propertyValue ?? DBNull.Value;
cmd.Parameters.Add(param);
updatedColumnNames.Add(columnName);
}
}
var idParam = cmd.CreateParameter();
idParam.ParameterName = "id";
idParam.Value = key;
cmd.Parameters.Add(idParam);
cmd.CommandText = commandBegin +
updatedColumnNames.Concat("id").Join(", ") +
commandLeftMiddle +
updatedColumnNames.Concat("id").Select(x => $"@{x}").Join(", ") +
commandRightMiddle +
updatedColumnNames.Join(", ") +
commandRightRightMidle +
updatedColumnNames.Select(x => $"@{x}").Join(", ") +
commandEnd;
var rowsAffected = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
Trace.Assert(1 == rowsAffected);
}));
}
private Task UpdateByDiffHelperAsync(K key, V existing, V updated)
{
return(ExecCommandAsync(async cmd => {
// Retrieve all rows
string commandBegin = $"UPDATE {tableName} SET (";
string commandMiddle = ") = (";
string commandEnd = ") WHERE test.id=@id";
var updatedColumnNames = new SCG.List <string>();
var properties = typeof(V).GetProperties();
foreach (var p in properties)
{
var columnName = p.Name.ToLower();
if (columnName == "updated")
{
p.SetValue(updated, DateTime.Now);
}
if (object.Equals(p.GetValue(existing), p.GetValue(updated)))
{
continue;
}
var propertyValue = p.GetValue(updated);
if (columnName == "id")
{
throw new InvalidStateException();
}
else
{
var param = cmd.CreateParameter();
param.ParameterName = columnName;
param.Value = propertyValue ?? DBNull.Value;
cmd.Parameters.Add(param);
updatedColumnNames.Add(columnName);
}
}
var idParam = cmd.CreateParameter();
idParam.ParameterName = "id";
idParam.Value = key;
cmd.Parameters.Add(idParam);
cmd.CommandText = commandBegin +
updatedColumnNames.Concat("id").Join(", ") +
commandMiddle +
updatedColumnNames.Concat("id").Select(x => $"@{x}").Join(", ") +
commandEnd;
var rowsModified = await cmd.ExecuteNonQueryAsync().ConfigureAwait(false);
Trace.Assert(1 == rowsModified);
}));
}