Exemple #1
0
        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);
            }
        }
Exemple #2
0
        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);
        }