Preparing statements and batching in npgsql

Question

The Simple Preparation example in the docs (https://www.npgsql.org/doc/prepare.html#simple-preparation) shows an example where parameters are set after the command is prepared.

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

Questions

1. How are the parameters set?
2. Is it possible to use AddWithValue instead of Add if the AddWithValue(String, NpgsqlDbType, Object) method which specifies NpgsqlDbType is used -- docs say "setting the value isn't support"?
3. How does this work if multiple statements exist in the same command?

This answer (https://stackoverflow.com/a/53268090/10984827) shows that multiple commands in a single string can be prepared together but it's not clear how this CommandText string is created.

---

Edit: I think I'm almost there but I'm not sure how to create and execute the batched the query string. Here's my naive attempt at building a batched query using a StringBuilder. This doesn't work. How do I do this correctly?

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

Answer 1

1) Simply capture the NpgsqlParameter returned from Add(), and then set its Value property:

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

2) You can use AddWithValue() in the same way, but if you're preparing the command in order to reuse it several times, that makes less sense. The idea is that you first add the parameter without a value, then prepare, then execute it several times, setting the value each time.

3) You can prepare a multi-statement command. As things work now, all statements in the command will share the same parameter list (which lives on NpgsqlCommand). So the same pattern holds: create your command with your SQL and parameters, prepare it, and then set parameter values and execute. Each individual statement within your command will run prepared, benefiting from the perf increase.

Here's an example with two-statement batching:

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

However, if the objective is to efficiently insert lots of data, consider using COPY instead - it will be faster than even batched inserts.

Finally, to complete the picture, for INSERT statements specifically you can include more than one row in a single statement:

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

You can also again parameterize the actual values, and prepare this command. This is similar to batching two INSERT statements, and should be faster (although still slower than COPY).

Answer 2

In NpgSQL 6.0 there has been the addition of batching/pipelining.

Here is an updated example:

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