准备语句和批处理在npgsql中的应用

Question

准备语句和批处理在npgsql中的应用

5

文档中的Simple Preparation示例（https://www.npgsql.org/doc/prepare.html#simple-preparation）展示了一个在准备命令后设置参数的示例。

var cmd = new NpgsqlCommand(...);
cmd.Parameters.Add("param", NpgsqlDbType.Integer);
cmd.Prepare();
// Set parameters
cmd.ExecuteNonQuery();
// And so on

问题

参数是如何设置的？
如果使用指定了 NpgsqlDbType 的 AddWithValue(String, NpgsqlDbType, Object) 方法，是否可以使用 AddWithValue 替代 Add -- 文档中说“不支持设置值”？
如果同一命令中存在多个语句，这将如何工作？

这个答案 (https://stackoverflow.com/a/53268090/10984827) 显示可以在单个字符串中准备多个命令，但不清楚如何创建此 CommandText 字符串。

编辑：我认为我已经接近成功了，但不确定如何创建和执行批量查询字符串。以下是我尝试使用 StringBuilder 构建批量查询的天真方法。这行不通。我该如何正确地做到这一点？

using System;
using System.Collections.Generic;
using System.Text;
using Npgsql;
using NpgsqlTypes;

class Model
{
    public int value1 { get; }
    public int value2 { get; }

    public Model(int value1, int value2)
    {
        this.value1 = value1;
        this.value2 = value2;
    }
}

class Program
{
    static void Main(string[] args)
    {
        var dataRows = new List<Model>();
        dataRows.Add(new Model(3,2));
        dataRows.Add(new Model(27,-10));
        dataRows.Add(new Model(11,-11));

        var connString = "Host=127.0.0.1;Port=5432;Username=postgres;Database=dbtest1";

        // tabletest1
        // ----------
        //   id        SERIAL PRIMARY KEY
        // , value1    INT NOT NULL
        // , value2    INT NOT NULL

        using (var conn = new NpgsqlConnection(connString))
        {
            conn.Open();

            var cmd = new NpgsqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = $"INSERT INTO tabletest1 (value1,value2) VALUES (@value1,@value2)";
            var parameterValue1 = cmd.Parameters.Add("value1", NpgsqlDbType.Integer);
            var parameterValue2 = cmd.Parameters.Add("value2", NpgsqlDbType.Integer);
            cmd.Prepare();

            var batchCommand = new StringBuilder();

            foreach (var d in dataRows)
            {
                parameterValue1.Value = d.value1;
                parameterValue2.Value = d.value2;
                batchCommand.Append(cmd.CommandText);
                batchCommand.Append(";");
            }
            Console.WriteLine(batchCommand.ToString());
            // conn.ExecuteNonQuery(batchCommand.ToString());
        }
    }
}

- Mike Ekim

2个回答

3

在NpgSQL 6.0中，添加了批处理/流水线技术batching/pipelining。

以下是一个更新的示例：

await using var connection = new NpgsqlConnection(connString);
await connection.OpenAsync();
    
var batch = new NpgsqlBatch(connection);
    
const int count = 10;
const string parameterName = "parameter";
for (int i = 0; i < count; i++)
{
    var batchCommand = new NpgsqlBatchCommand($"SELECT @{parameterName} as value");
    batchCommand.Parameters.Add(new NpgsqlParameter(parameterName, i));
    batch.BatchCommands.Add(batchCommand);
}

await batch.PrepareAsync();

var results = new List<int>(count);
await using (var reader = await batch.ExecuteReaderAsync())
{
    do
    {
        while (await reader.ReadAsync())
        {
            results.Add(await reader.GetFieldValueAsync<int>("value"));
        }
    } while (await reader.NextResultAsync());
}
Console.WriteLine(string.Join(", ", results));

- SteveHansen

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Shay Rojansky · Accepted Answer

1) 只需捕获从Add()返回的NpgsqlParameter，然后设置其Value属性：

var p = cmd.Parameters.Add("p", NpgsqlDbType.Integer);
cmd.Prepare();
p.Value = 8;
cmd.ExecuteNonQuery();

2) 您可以以相同的方式使用 AddWithValue()，但如果您正在准备该命令以便多次重复使用它，则这样做就没有意义。因为首先要添加一个没有值的参数，然后准备，然后多次执行，每次设置值。

3) 您可以准备一个多语句命令。目前的工作方式是，命令中的所有语句将共享相同的参数列表（位于 NpgsqlCommand 上）。因此，同样的模式适用：使用 SQL 和参数创建命令，然后准备它，然后设置参数值并执行。命令中的每个单独语句都将运行已准备好的状态，并从性能提高中受益。

下面是一个包含两个语句的示例批处理：

cmd.CommandText = "INSERT INTO tabletest1 (value1,value2) VALUES (@v1,@v2); INSERT INTO tabletest1 (value1, value2) VALUES (@v3,@v4)";
var v1 = cmd.Parameters.Add("v1", NpgsqlDbType.Integer);
var v2 = cmd.Parameters.Add("v2", NpgsqlDbType.Integer);
var v3 = cmd.Parameters.Add("v3", NpgsqlDbType.Integer);
var v4 = cmd.Parameters.Add("v4", NpgsqlDbType.Integer);
cmd.Prepare();

while (...) {
    v1.Value = ...;
    v2.Value = ...;
    v3.Value = ...;
    v4.Value = ...;
    cmd.ExecuteNonQuery();
}

然而，如果目标是高效地插入大量数据，请考虑使用COPY - 它比批量插入操作更快速。(查看详细信息)

最后，为了完善这个话题，在INSERT语句中你可以一次包含多行：

INSERT INTO tabletest1 (value1, value2) VALUES (1,2), (3,4)

您还可以重新参数化实际值，并准备该命令。这类似于批处理两个INSERT语句，应该更快（虽然仍比COPY慢）。