批量C#数据表到Postgresql表 [英] Bulk C# datatable to postgresql table
问题描述
我有一个包含数千条记录的数据表。
我有一个与数据表相同字段的postgres表。
我想每天截断该表并再次填充数据表的数据。我已经看过sql批量复制,但是在postgres上不可用。
那么,哪一种是最有效的方法?
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?
- 每条记录一个插入
- 多次插入:插入表值(1,1),(1,2),(1,3),(2,1);
- 从数据表中选择并插入postgres与linq?不知道...
谢谢。
推荐答案
PostgreSQL确实确实具有大容量副本(实际上称为 copy
),并且它具有.NET的漂亮包装。如果要加载,则要使用 NpgsqlCopyIn
,如果要提取数据,则可以使用 NpgsqlCopyOut。
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.
您的问题在细节上有点含糊-我不知道您的数据表中的字段或有关您实际数据库的任何内容,因此以该示例为例使用C#/ PostgreSQL将数据批量插入表中:
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:
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();
-编辑8/27/2019-
-- Edit 8/27/2019 --
Npgsql的构造已完全更改。下面是使用二进制导入的上述示例的样板(也提供文本):
The construct for Npgsql has completely changed. Below is a boilerplate for the same example above, using binary import (text is also available):
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();
}
这篇关于批量C#数据表到Postgresql表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!