private IReadOnlyList <object> bulkInsert(string tableName, IReadOnlyList <IEntityBase> entities, bool insertPrimaryKey)
{
if (entities.Count == 0)
{
throw new ArgumentException("No entities to insert.");
}
log.Describe($"{nameof(bulkInsert)} into: {tableName}", new
{
insertPrimaryKey,
entities.Count
});
tableName = ResolveTableName(tableName);
var type = entities[0].GetType();
var columns = ReflectionUtil.GetColumnSchemas(type);
var columnNames = new List <string>(columns.Count);
var values = new List <object>(columns.Count * entities.Count);
var query = new StringBuilder(columns.Count * 32);
var pos = 0;
PostgreSqlColumnSchema pk = null;
foreach (var column in columns)
{
// don't insert PK if specified
if (column.PrimaryKey)
{
pk = column;
if (!insertPrimaryKey)
{
continue;
}
}
columnNames.Add('"' + column.Name + '"');
}
query.Write($"INSERT INTO ")
.WriteLine(tableName)
.WriteTabbedLine(1, $"({string.Join(", ", columnNames)})")
.WriteTabbedLine(1, "VALUES");
foreach (var entity in entities)
{
if (!type.GetTypeInfo().IsInstanceOfType(entity))
{
throw new Exception($"Mixed up entities in bulk insert. ({type.Name} != {entity.GetType().Name})");
}
query.WriteTabbed(2, "(");
foreach (var column in columns)
{
// don't insert PK if specified
if (column.PrimaryKey && !insertPrimaryKey)
{
continue;
}
var value = column.GetValue(entity);
values.Add(value);
query.Append('@').Append(pos++).Append(',');
}
query.TrimEnd(',').Append("),").NewLine();
}
query.TrimEndLineBreaks()
.TrimEnd(',')
.NewLine();
if (pk == null)
{
var affected = db.Execute(query.ToString(), values);
return(new object[0]);
}
else
{
var insertedIds = new List <object>();
query.WriteTabbedLine(1, $"RETURNING \"{pk.Name}\"");
using (var reader = db.Query(query.ToString(), values))
{
Debug.Assert(reader.VisibleFieldCount == 1);
while (reader.Read())
{
var id = reader.GetValue(0);
insertedIds.Add(id);
Debug.Assert(id != null);
}
}
return(insertedIds);
}
}
private object Insert(string tableName, IEntityBase entity, bool insertPrimaryKey, bool upsert, bool merge)
{
log.Describe($"{nameof(Insert)} into: {tableName}", new
{
insertPrimaryKey,
upsert,
merge,
entity
});
var type = entity.GetType();
var columns = ReflectionUtil.GetColumnSchemas(type);
var columnNames = new List <string>(columns.Count);
var valuePlaceholders = new List <string>(columns.Count);
var values = new List <object>(columns.Count);
var query = new StringBuilder(columns.Count * 32);
var pos = 0;
PostgreSqlColumnSchema pk = null;
foreach (var column in columns)
{
// don't insert PK if specified
if (column.PrimaryKey)
{
pk = column;
if (!insertPrimaryKey)
{
continue;
}
}
var value = column.GetValue(entity);
var defaultValue = value?.GetType().GetDefault();
if (column.Nullable && (merge && value == defaultValue))
{
continue;
}
columnNames.Add('"' + column.Name + '"');
valuePlaceholders.Add("@" + pos++);
values.Add(value);
}
query.Write($"INSERT INTO ")
.WriteLine(ResolveTableName(tableName))
.WriteTabbedLine(1, $"({string.Join(", ", columnNames)})")
.WriteTabbedLine(1, $"VALUES ({string.Join(", ", valuePlaceholders)})");
if (upsert && pk != null)
{
query.WriteTabbedLine(2, "ON CONFLICT (\"" + pk.Name + "\") DO UPDATE SET ");
for (var i = 0; i < valuePlaceholders.Count; i++)
{
query.WriteTabbed(3, columnNames[i])
.Write(" = ")
.Write(valuePlaceholders[i])
.WriteIf(i < valuePlaceholders.Count - 1, ",")
.NewLine();
}
}
if (!insertPrimaryKey && pk != null)
{
query.WriteTabbedLine(2, "RETURNING \"" + pk.Name + '"');
}
var result = db.QuerySingleValue(query.ToString(), values);
return(result);
}