批量 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);
• 从数据表中选择并使用 linq 插入 postgres?不知道...

谢谢.

推荐答案

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屋！