批量 C# 数据表到 postgresql 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38652832/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Bulk C# datatable to postgresql table
提问by Za7pi
I have got a datatable with thousands of records. I have got a postgres table with the same fields of the datatable. I want everyday to truncate this table and fill again with the data of the datatable. I have seen sql bulk copy, but it is not avalaible on postgres. So, which one is the most effective way?
我有一个包含数千条记录的数据表。我有一个具有相同数据表字段的 postgres 表。我想每天截断这个表并再次填充数据表的数据。我见过 sql 批量复制，但它在 postgres 上不可用。那么，哪一种是最有效的方法呢？
- One insert per record
- Multiple insert: insert into table values (1,1),(1,2),(1,3),(2,1);
- Select from datatable and insert into postgres with linq? no idea...
- 每条记录插入一个
- 多次插入：插入表值(1,1),(1,2),(1,3),(2,1)；
- 从数据表中选择并使用 linq 插入 postgres？不知道...
Thanks.
谢谢。
回答by Hambone
PostgreSQL definitely does have a bulk copy (it's actually called copy), and it has a nice wrapper for .NET. If you are loading, you want to use the NpgsqlCopyIn, and if you are extracting data you can use NpgsqlCopyOut.
PostgreSQL 确实有一个大容量副本（它实际上被称为copy），并且它有一个很好的 .NET 包装器。如果你正在加载，你想使用NpgsqlCopyIn，如果你正在提取数据，你可以使用NpgsqlCopyOut.
Your question is a little vague on details -- I don't know the fields in your datatable or anything about your actual database, so take this as a brief example on how to bulk insert data into a table using C#/PostgreSQL:
你的问题在细节上有点含糊——我不知道你的数据表中的字段或你的实际数据库的任何信息，所以把它作为一个关于如何使用 C#/PostgreSQL 将数据批量插入表的简要示例：
NpgsqlCopyIn copy = new NpgsqlCopyIn("copy table1 from STDIN WITH NULL AS '' CSV;",
conn);
copy.Start();
NpgsqlCopySerializer cs = new NpgsqlCopySerializer(conn);
cs.Delimiter = ",";
foreach (var record in RecordList)
{
cs.AddString(record.UserId);
cs.AddInt32(record.Age);
cs.AddDateTime(record.HireDate);
cs.EndRow();
}
cs.Close();
copy.End();
-- Edit 8/27/2019 --
-- 编辑 8/27/2019 --
The construct for Npgsql has completely changed. Below is a boilerplate for the same example above, using binary import (text is also available):
Npgsql 的构造已经完全改变。下面是上面相同示例的样板，使用二进制导入（文本也可用）：
using (var writer = conn.BeginBinaryImport(
"copy user_data.part_list from STDIN (FORMAT BINARY)"))
{
foreach (var record in RecordList)
{
writer.StartRow();
writer.Write(record.UserId);
writer.Write(record.Age, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(record.HireDate, NpgsqlTypes.NpgsqlDbType.Date);
}
writer.Complete();
}
回答by Micha? Pilarek
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/6654362
也许您可以查看我的其他答案，其中描述了我为这个问题创建的一个小助手，使用另一个助手非常容易：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 does:
编辑：我最近遇到了类似的问题，但我们使用的是 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>(schemaName, "\"" + tableName + "\"");
var properties = typeof(T).GetProperties();
foreach(var prop in properties)
{
var type = prop.PropertyType;
if (Attribute.IsDefined(prop, typeof(KeyAttribute)))
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 Jonathan Magnan
There is some option to bulk insert into PostgreSQL.
有一些选项可以批量插入 PostgreSQL。
By example, in my library, I'm using the SQL Copy
例如，在我的库中，我使用的是SQL Copy
COPY TableName (Column1, Column2, Column3) FROM STDIN BINARY
Disclaimer: I'm the owner of the project Bulk-Operations.NET
免责声明：我是Bulk-Operations.NET项目的所有者
This library make it very easy to perform any kind of bulk operations:
这个库可以很容易地执行任何类型的批量操作：
- BulkInsert
- BulkUpdate
- BulkDelete
- BulkMerge
- 批量插入
- 批量更新
- 批量删除
- 批量合并
In multiple database provider including PostgreSQL
在包括 PostgreSQL 在内的多个数据库提供程序中
// Easy to use
var bulk = new BulkOperation(connection);
bulk.BulkInsert(dt);
bulk.BulkUpdate(dt);
bulk.BulkDelete(dt);
bulk.BulkMerge(dt);
回答by Tony
As said in other answers there is no built-in solution for that and just some helper libraries (free and non-free), personally I come up with my own solution. Advantages of this are
正如其他答案中所说，没有内置的解决方案，只有一些帮助库（免费和非免费），我个人提出了自己的解决方案。这样做的好处是
- Free, easy to use
- Doesn't demand extra setup for mappings, it reuses metadata from DB itself and from EF DbContext
- Uses dyamic code building for performance
- 免费，易于使用
- 不需要额外的映射设置，它重用来自 DB 本身和来自 EF DbContext 的元数据
- 使用动态代码构建来提高性能
Usage is like this:
用法是这样的：
var uploader = new NpgsqlBulkUploader(context);
var data = GetALotOfData();
uploader.Insert(data);
// OR
uploader.Update(data);
I described it there
我在那里描述过
