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

Insert the whole value of DataTable bulk into postgreSQL table

提问by Karan Singh

In SQL we do something like this for bulk insert to datatable

在 SQL 中，我们为批量插入数据表做这样的事情

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

Blockquote

but in PostgreSQL how to do this operation

但是在PostgreSQL中怎么做这个操作

回答by Pressacco

Simple Insert Using Parameters

使用参数的简单插入

Your project will need to reference the following assembly: Npgsql. If this reference is not visible within Visual Studio, then:

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

1. browse to the connector's installation folder
2. Execute: GACInstall.exe
3. Restart Visual Studio.

1. 浏览到连接器的安装文件夹
2. 执行： GACInstall.exe
3. 重新启动Visual Studio。

Sample Table

样品表

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;

Sample Code

示例代码

Be sure to use the following directives:

请务必使用以下指令：

using Npgsql;
using NpgsqlTypes;

Enter the following source code into your method:

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

// 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();

Thoughts On Performance

对性能的思考

The original posting made no mention of performance requirements. It was requested that the solution must:

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

1. insert using a DataTable
2. insert data without using a loop

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

If you are inserting significant amounts of data, then I would suggest that you take a look at your performance options. The Postgresdocumentation suggests that you:

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

• Disable Autocommit
• Use the COPYcommand
• Remove indexes
• Remove Foreign Key Constraints
• etc.

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

For more information about optimizing Postgres inserts, please take a look at:

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

• PostgresSql.org: Inserting Data
• PostgresSql.org: Insert + Performance Tips
• StackOverflow: How to speed up insertion performance in PostgreSQL

• PostgresSql.org：插入数据
• PostgresSql.org：插入 + 性能技巧
• StackOverflow：如何加快 PostgreSQL 中的插入性能

Also, there are a lot of other factors that can impact a system's performance. For a high level introduction, take a look at:

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

• ADO.NET SQL Server Performance bottleneck
  • This posting outlines general (i.e. non-SqlServer) strategies for optimizing performance.

• ADO.NET SQL Server 性能瓶颈
  • 这篇文章概述了优化性能的一般（即非 SqlServer）策略。

Other Options

其他选项

• Does the .NET connector support the PostgresCopycommand?
  • If not, you can download the source codefor the Npgsqlconnector and add your own BulkCopy()method. Be sure to review the source code's licensing agreement first.
• Check to see if Postgressupports Table Value Parameters.
  • This approach allows you to pass in a table into a Postgresfunction which can then insert the data directly into the destination.
• Purchase a Postgres.NET connector from a vendor which includes the required feature.

• .NET 连接器是否支持PostgresCopy命令？
  • 如果没有，你可以下载源代码的Npgsql连接器，并添加自己的BulkCopy()方法。请务必先查看源代码的许可协议。
• 检查Postgres 是否支持Table Value Parameters。
  • 这种方法允许您将表传入一个Postgres函数，然后该函数可以将数据直接插入到目标中。
• 从包含所需功能的供应商处购买Postgres.NET 连接器。

Additional References

其他参考资料

• Postgres .NET Connector- free& open source

• Postgres .NET 连接器-免费和开源

回答by komaflash

I've got the same problem a time ago. It seems there is no "ready to use" solution, till yet.

我前段时间遇到了同样的问题。到目前为止，似乎还没有“随时可用”的解决方案。

I've read thispost and build a similar solution at that time, which is in productive use till today. Its based on text querys which reads files from STDIN. It uses the ADO.NET Postgre Data Provider Npgsql. You can create a large string (or temporary file, cause of memory usage) based on your DataTable and use that one as text query with the COPY command. In our case it was much more faster than inser teach row.

我已经阅读了这篇文章并在当时构建了一个类似的解决方案，直到今天仍在生产中使用。它基于从 STDIN 读取文件的文本查询。它使用 ADO.NET Postgre 数据提供程序Npgsql。您可以根据您的 DataTable 创建一个大字符串（或临时文件，内存使用的原因），并将其用作 COPY 命令的文本查询。在我们的例子中，它比插入示教行快得多。

Maybe this isn't a complete solution, but may a good point to start and anything i know about it. :)

也许这不是一个完整的解决方案，但可能是一个很好的起点和我所知道的任何事情。:)

回答by Micha? Pilarek

I have also found, that there are no 'ready to use' solution yet. Probably you can check my other answer in which I describe a little helper I have created for this problem, making use of another helper really easy: https://stackoverflow.com/a/46063313/6654362I think that's currently the best solution. I posted the solution from the link in case the post died.

我还发现，目前还没有“随时可用”的解决方案。也许您可以查看我的其他答案，其中我描述了我为这个问题创建的一个小助手，使用另一个助手非常容易：https: //stackoverflow.com/a/46063313/6654362我认为这是目前最好的解决方案。我从链接中发布了解决方案，以防帖子死亡。

Edit: I have recently run into similar problem, but we were using Postgresql. I wanted to use effective bulkinsert, what turned out to be pretty difficult. I haven't found any proper free library to do so on this DB. I have only found this helper: https://bytefish.de/blog/postgresql_bulk_insert/which is also on Nuget. I have written a small mapper, which auto mapped properties the way Entity Framework:

编辑：我最近遇到了类似的问题，但我们使用的是 Postgresql。我想使用有效的bulkinsert，结果非常困难。我还没有找到任何合适的免费库来在这个数据库上这样做。我只找到了这个助手：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;
        }

I use it the following way (I had entity named Undertaking):

我按以下方式使用它（我有一个名为 Undertaking 的实体）：

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

I showed an example with transaction, but it can also be done with normal connection retrieved from context. undertakingsToAdd is enumerable of normal entity records, which I want to bulkInsert into DB.

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

This solution, to which I've got after few hours of research and trying, is as you could expect much faster and finally easy to use and free! I really advice you to use this solution, not only for the reasons mentioned above, but also because it's the only one with which I had no problems with Postgresql itself, many other solutions work flawlessly for example with SqlServer.

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

回答by Tony

Do you really want to use DataTable for this? If you could use EF Code First then you could use code from this article. Idea of the article is to use Npgsql COPY as a basis + fetch metadata from DB itself, merge it with metadata from model classes and dynamically generate code for writing data.

您真的要为此使用 DataTable 吗？如果您可以使用 EF Code First，那么您可以使用本文中的代码。文章的思路是以Npgsql COPY为基础+从DB本身获取元数据，将其与来自模型类的元数据合并，动态生成写入数据的代码。

Usage is like this:

用法是这样的：

var uploader = new NpgsqlBulkUploader(context);
var data = GetALotOfData();
uploader.Insert(data);
// OR
uploader.Update(data);