将DataTable批量的整个值插入postgreSQL表

Question

在SQL我们做这样的事情批量插入到可更新

SqlBulkCopy copy = new SqlBulkCopy(sqlCon);
copy.DestinationTableName = strDestinationTable;            
copy.WriteToServer(dtFrom);

Blockquote

但在PostgreSQL中，如何执行此操作

Answer 1

我还发现，目前还没有现成的解决方案。也许你可以查看我的另一个答案，我在其中描述了我为这个问题创建的一个小助手，使使用另一个助手变得非常简单：https://stackoverflow.com/a/46063313/6654362我认为这是目前最好的解决方案。我在链接上发布了解决方案，以防帖子死掉。

编辑：我最近遇到了类似的问题，但我们使用的是Postgreql。我想使用有效的保鲜膜，结果是相当困难的。我还没有找到任何适当的免费库这样做在这个数据库。我只找到了这个助手：https://bytefish.de/blog/postgresql_bulk_insert/也在Nuget上。我写了一个小映射器，它自动映射实体框架的方式属性：

public static PostgreSQLCopyHelper<T> CreateHelper<T>(string schemaName, string tableName)
        {
            var helper = new PostgreSQLCopyHelper<T>("dbo", "\"" + tableName + "\"");
            var properties = typeof(T).GetProperties();
            foreach(var prop in properties)
            {
                var type = prop.PropertyType;
                if (Attribute.IsDefined(prop, typeof(KeyAttribute)) || Attribute.IsDefined(prop, typeof(ForeignKeyAttribute)))
                    continue;
                switch (type)
                {
                    case Type intType when intType == typeof(int) || intType == typeof(int?):
                        {
                            helper = helper.MapInteger("\"" + prop.Name + "\"",  x => (int?)typeof(T).GetProperty(prop.Name).GetValue(x, null));
                            break;
                        }
                    case Type stringType when stringType == typeof(string):
                        {
                            helper = helper.MapText("\"" + prop.Name + "\"", x => (string)typeof(T).GetProperty(prop.Name).GetValue(x, null));
                            break;
                        }
                    case Type dateType when dateType == typeof(DateTime) || dateType == typeof(DateTime?):
                        {
                            helper = helper.MapTimeStamp("\"" + prop.Name + "\"", x => (DateTime?)typeof(T).GetProperty(prop.Name).GetValue(x, null));
                            break;
                        }
                    case Type decimalType when decimalType == typeof(decimal) || decimalType == typeof(decimal?):
                        {
                            helper = helper.MapMoney("\"" + prop.Name + "\"", x => (decimal?)typeof(T).GetProperty(prop.Name).GetValue(x, null));
                            break;
                        }
                    case Type doubleType when doubleType == typeof(double) || doubleType == typeof(double?):
                        {
                            helper = helper.MapDouble("\"" + prop.Name + "\"", x => (double?)typeof(T).GetProperty(prop.Name).GetValue(x, null));
                            break;
                        }
                    case Type floatType when floatType == typeof(float) || floatType == typeof(float?):
                        {
                            helper = helper.MapReal("\"" + prop.Name + "\"", x => (float?)typeof(T).GetProperty(prop.Name).GetValue(x, null));
                            break;
                        }
                    case Type guidType when guidType == typeof(Guid):
                        {
                            helper = helper.MapUUID("\"" + prop.Name + "\"", x => (Guid)typeof(T).GetProperty(prop.Name).GetValue(x, null));
                            break;
                        }
                }
            }
            return helper;
        }

我使用它的方式如下（我有一个名为promise的实体）：

var undertakingHelper = BulkMapper.CreateHelper<Model.Undertaking>("dbo", nameof(Model.Undertaking));
undertakingHelper.SaveAll(transaction.UnderlyingTransaction.Connection as Npgsql.NpgsqlConnection, undertakingsToAdd));

我展示了一个带有事务的示例，但它也可以通过从上下文检索的正常连接来完成。undertakingsToAdd是可枚举的普通实体记录，我想将其批量插入数据库。

经过几个小时的研究和尝试，我得到了这个解决方案，正如你所期望的那样，它更快，最终易于使用和免费！我真的建议你使用这个解决方案，不仅是因为上面提到的原因，而且因为它是唯一一个我对Postgresql本身没有问题的解决方案，许多其他解决方案都可以完美地工作，例如SqlServer。

Answer 2

我之前也有同样的问题。到目前为止，似乎还没有“现成的”解决方案。

我读了这篇文章，并在当时构建了一个类似的解决方案，直到今天，这个解决方案仍在有效使用。它基于文本查询，从标准文本中读取文件。它使用ADO。NET Postgre数据提供程序Npgsql。您可以基于数据表创建一个大字符串（或临时文件，这是内存使用的原因），并使用COPY命令将其用作文本查询。在我们的例子中，它比inser teach row快得多。

也许这不是一个完整的解决方案，但可能是一个很好的起点，以及我所知道的一切。：）

Answer 3

您的项目将需要引用以下程序集：Npgsql。如果此引用在Visual Studio中不可见，则：

1. 浏览到连接器的安装文件夹

CREATE TABLE "OrderHistory"
(
  "OrderId" bigint NOT NULL,
  "TotalAmount" bigint,
  CONSTRAINT "OrderIdPk" PRIMARY KEY ("OrderId")
)
WITH (
  OIDS=FALSE
);
ALTER TABLE "OrderHistory"
  OWNER TO postgres;
GRANT ALL ON TABLE "OrderHistory" TO postgres;
GRANT ALL ON TABLE "OrderHistory" TO public;
ALTER TABLE "OrderHistory" ALTER COLUMN "OrderId" SET (n_distinct=1);

GRANT SELECT("OrderId"), UPDATE("OrderId"), INSERT("OrderId"), REFERENCES("OrderId") ON "OrderHistory" TO public;
GRANT SELECT("TotalAmount"), UPDATE("TotalAmount"), INSERT("TotalAmount"), REFERENCES("TotalAmount") ON "OrderHistory" TO public;

请务必使用以下指令：

using Npgsql;
using NpgsqlTypes;

在方法中输入以下源代码：

// Make sure that the user has the INSERT privilege for the OrderHistory table.
NpgsqlConnection connection = new NpgsqlConnection("PORT=5432;TIMEOUT=15;POOLING=True;MINPOOLSIZE=1;MAXPOOLSIZE=20;COMMANDTIMEOUT=20;COMPATIBLE=2.2.4.3;DATABASE=test;HOST=127.0.0.1;PASSWORD=test;USER ID=test");

connection.Open();

DataSet dataSet = new DataSet();

NpgsqlDataAdapter dataAdapter = new NpgsqlDataAdapter("select * from OrderHistory where OrderId=-1", connection);
dataAdapter.InsertCommand = new NpgsqlCommand("insert into OrderHistory(OrderId, TotalAmount) " +
                        " values (:a, :b)", connection);
dataAdapter.InsertCommand.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Bigint));
dataAdapter.InsertCommand.Parameters.Add(new NpgsqlParameter("b", NpgsqlDbType.Bigint));
dataAdapter.InsertCommand.Parameters[0].Direction = ParameterDirection.Input;
dataAdapter.InsertCommand.Parameters[1].Direction = ParameterDirection.Input;
dataAdapter.InsertCommand.Parameters[0].SourceColumn = "OrderId";
dataAdapter.InsertCommand.Parameters[1].SourceColumn = "TotalAmount";

dataAdapter.Fill(dataSet);

DataTable newOrders = dataSet.Tables[0];
DataRow newOrder = newOrders.NewRow();
newOrder["OrderId"] = 20;
newOrder["TotalAmount"] = 20.0;

newOrders.Rows.Add(newOrder);
DataSet ds2 = dataSet.GetChanges();
dataAdapter.Update(ds2);
dataSet.Merge(ds2);
dataSet.AcceptChanges();

connection.Close();

最初的帖子没有提到绩效要求。要求解决方案必须：

1. 使用数据表插入
2. 不使用循环插入数据

如果要插入大量数据，我建议您查看性能选项。Postgres文档建议您：

• 禁用自动提交
• 使用COPY命令
• 删除索引
• 删除外键约束
• 等

有关优化Postgres插入的更多信息，请查看：

• PostgresSql。组织：插入数据
• PostgresSql。组织：插入绩效提示
• StackOverflow：如何在PostgreSQL中提高插入性能

此外，还有许多其他因素会影响系统的性能。有关高级介绍，请参阅：

• 阿多。NET SQL Server性能瓶颈
  • 本文概述了优化性能的一般（即非SqlServer）策略
  • 是的。NET连接器是否支持PostgresCopy命令？
  • 这种方法允许您将表传递到一个Postgreshtml" target="_blank">函数中，然后该函数可以将数据直接插入到目标中。
  • 博士后。网络连接器-免费