logo标签列表

将 DataTable 的所有值批量插入到 postgreSQL 表中。

c#asp.net-mvcpostgresqldatatable
23

在SQL中，我们通常会像这样对数据表进行批量插入操作：

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

Blockquote

但在PostgreSQL中如何执行此操作

https://github.com/npgsql/Npgsql/wiki/User-Manual#user-content-fast-bulk-data-copy-into-a-table - Vivek S.
非常棒的文章，@wingedpanther先生。 我仍然不明白那个想法， 因为要求是将“DataTable”数据直接插入表中， 而不需要在C# MVC中使用循环。 DataTable包含超过1万条记录。 - Karan Singh
对于在PostgreSQL数据库中进行批量插入，您可以使用COPY命令，将数据导入到csv文件中（避免使用datatable），然后再从csv文件中使用COPY命令将它们复制到表中。 - Vivek S.
1
你可能想将它转换为 CSV 格式，然后插入到所需的位置。或者不这样做？ - Amirhossein Mehrvarzi
3个回答
8

使用参数的简单插入

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

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

示例表格

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. 使用DataTable进行插入
  2. 在不使用循环的情况下插入数据

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

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

有关优化Postgres插入的更多信息，请参见：

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

其他选项

  • .NET连接器是否支持PostgresCopy命令？
    • 如果不支持，则可以下载Npgsql连接器的源代码并添加自己的BulkCopy()方法。请确保首先查看源代码的许可协议。
  • 检查Postgres是否支持表值参数。
    • 这种方法允许您将一个表传递到Postgres函数中，该函数可以直接将数据插入目标。
  • 从供应商处购买Postgres.NET连接器，其中包括所需的功能。

其他参考资料

我认为这是最好的解决方案。但同时设置dataAdapter.UpdateBatchSize并开始一个事务（connection.BeginTransaction()）。对于Mysql而言，这可以提供巨大的性能提升，约为30倍。 - Konstantin
InvalidOperationException: 当传递带有已修改行的 DataRow 集合时，更新需要有效的 UpdateCommand。我遇到了这个异常，你有什么想法吗？ - Anonymous Creator
好的。我尝试使用newOrders.Merge(dt)添加行，但它没有起作用（出现了上面评论中的异常）。所以我仍然需要循环遍历数据表来添加行。 - Anonymous Creator
3
我以前也遇到过同样的问题。似乎现在还没有“可用”的解决方案。
我阅读了this帖子，并在那时构建了一个类似的解决方案，至今仍在生产中使用。它基于从STDIN读取文件的文本查询。它使用ADO.NET Postgre数据提供程序Npgsql。您可以根据DataTable创建一个大字符串（或临时文件，因为内存使用）并将其作为文本查询与COPY命令一起使用。在我们的情况下，它比插入每行更快。
也许这不是一个完整的解决方案，但可能是一个很好的起点和我所知道的任何内容。 :)
登录是必需的。 - rmpt
是的，看起来这篇文章已经被删除了。 - devmb
1
如果您有的话，能否提供新的链接呢？ - HamidKhan
@HamidKhan 抱歉，我不能。这是由Alexander Kuznetsov在2013年在SQLblog.com上发布的文章系列，现已被删除。我猜它已经过时了。 - devmb
2

我也发现，目前还没有“即用型”解决方案。也许你可以查看我的另一个答案，在其中我描述了一个小助手，可以轻松地利用另一个助手来解决这个问题：https://dev59.com/DW025IYBdhLWcg3wjGtO#46063313 我认为这是目前最好的解决方案。 我在帖子中发布了链接中的解决方案，以防帖子失效。

编辑： 最近我遇到了类似的问题，但我们使用的是Postgresql。我想使用有效的批量插入，结果证明这相当困难。我没有找到任何适用于此数据库的免费库。我只找到了这个助手： https://bytefish.de/blog/postgresql_bulk_insert/ 它也在Nuget上。我编写了一个小映射器，自动映射属性的方式就像Entity Framework：

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;
        }

我是这样使用它的（我有一个名为Undertaking的实体）：
var undertakingHelper = BulkMapper.CreateHelper<Model.Undertaking>("dbo", nameof(Model.Undertaking));
undertakingHelper.SaveAll(transaction.UnderlyingTransaction.Connection as Npgsql.NpgsqlConnection, undertakingsToAdd));

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

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

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接