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