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);
}));
}
public Task <Entry <K, V> > InsertAsync(V item)
{
return(ExecCommandAsync(async cmd => {
// Retrieve all rows
var commandStart = $"INSERT INTO {tableName} (";
var commandMiddle = ") VALUES (";
var commandEnd = ") RETURNING *";
var insertedColumnNames = new SCG.List <string>();
foreach (var property in typeof(V).GetProperties())
{
var columnName = property.Name.ToLower();
if (columnName == "id")
{
continue;
}
var propertyValue = property.GetValue(item);
var defaultPropertyValue = property.PropertyType.IsValueType ? Activator.CreateInstance(property.PropertyType) : null;
if (!Equals(propertyValue, defaultPropertyValue))
{
insertedColumnNames.Add(columnName);
var parameter = cmd.CreateParameter();
parameter.ParameterName = columnName;
parameter.Value = propertyValue;
cmd.Parameters.Add(parameter);
}
}
cmd.CommandText = commandStart +
insertedColumnNames.Join(", ") +
commandMiddle +
insertedColumnNames.Select(c => $"@{c}").Join(", ") +
commandEnd;
using (var reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false)) {
Trace.Assert(reader.HasRows);
var readSuccessful = await reader.ReadAsync().ConfigureAwait(false);
Trace.Assert(readSuccessful);
var entry = ReadToEntry(reader);
readSuccessful = await reader.ReadAsync().ConfigureAwait(false);
Trace.Assert(!readSuccessful);
return entry;
}
}));
}
