使用 Npgsql 绑定数组 [英] array binding with Npgsql

问题描述

有没有办法像使用 ODP.NET 数组绑定功能一样使用 npgsql 进行批量操作?我知道单个命令中的批处理/流水线/多个 SQL 语句(在单个往返中执行)，但据我所知，您不能以相同的方式使用它，每个语句都有不同的参数.如果可以完成，我会很高兴有人能提供如何将下面的代码转换为 Npgsql 的示例.

Is there a way to do bulk operations with npgsql like with ODP.NET array binding feature ? I am aware of the Batching/Pipelining/multiple SQL statements in a single command (executing in a single roundtrip) but as far as I understand you can't use it in the same way, with different parameters for every statement. If it can be done, I would be happy if someone could provide an example how to convert the code below to Npgsql.

List<dto> listDTO; // list containing up to 1000 dtos

OraCommand = db.GetOracleCommand();
OraCommand.CommandText = "INSERT INTO TABLE (ID, FIELD1, FIELD2) VALUES (:ID, :FIELD1, :FIELD2)";

object[] FIELD1 = new object[listDTO.Count];
object[] FIELD2 = new object[listDTO.Count];

for (int i = 0; i <= listDTO.Count - 1; i++)
{
    ID[i] = listDTO.Id;
    FIELD1[i] = listDTO.Field1;
    FIELD2[i] = listDTO.Field2;                    
}
OraCommand.ArrayBindCount = listDTO.Count;

OraCommand.Parameters.Add(":ID", OracleDbType.Decimal, ID, System.Data.ParameterDirection.Input);
OraCommand.Parameters.Add(":FIELD1", OracleDbType.Varchar2, 10, FIELD1, System.Data.ParameterDirection.Input);
OraCommand.Parameters.Add(":FIELD2", OracleDbType.Varchar2, 32, FIELD2, System.Data.ParameterDirection.Input);

db.DoSqlPrepareCommand(OraCommand);

这就是我认为最好使用 Npgsql 完成的方式:

This is how I think it should best be done using Npgsql:

NpgsqlConnection conn = new NpgsqlConnection("connString");
conn.Open();    
NpgsqlCommand command = new NpgsqlCommand();    
string CommandText = "";

for (int i = 0; i <=5 ; i++)
{
    CommandText = CommandText + "INSERT INTO testtbl (id, field1) VALUES (@ID_" + i + " , @FIELD1_" + i + ");";

    command.Parameters.Add(new NpgsqlParameter("ID_" + i, i));
    command.Parameters.Add(new NpgsqlParameter("FIELD1_" + i, "FIELD1" + i));
}
command.CommandText = CommandText;
command.Connection = conn;
int result = command.ExecuteNonQuery();

推荐答案

如果您要查找的是多行的批量插入，您应该查看 二进制复制 - 这绝对是最有效的方法.

If what you're looking for is bulk insert of many rows, you should look at binary COPY - this is definitely the most efficient method.

否则，绝对有可能准备您的 INSERT 语句，然后在同一往返中使用不同的参数批量/流水线执行它.这将产生非常好的性能，尽管仍然不如二进制 COPY.

Otherwise it's definitely possible to prepare your INSERT statement and then batch/pipeline its execution with different parameters in the same roundtrip. This will yield very good performance, although still not as good as binary COPY.

这篇关于使用 Npgsql 绑定数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！