Skip to main content

Curiosus.DAL.NpgSQL

NuGet Downloads Coverage

Helpers for the Npgsql PostgreSQL driver: bulk insert via binary COPY, null-aware NpgsqlDataReader accessors and LIKE/ILIKE pattern sanitizing.

Installation​

dotnet add package Curiosus.DAL.NpgSQL

Usage​

Bulk insert​

Implement IBulkInsertable and call BulkInsertViaCopyAsync on an open NpgsqlConnection:

using Curiosus.DAL.NpgSQL.BulkInsert;
using Npgsql;
using NpgsqlTypes;

public record Measurement(long SensorId, double? Value, string? Comment) : IBulkInsertable
{
    public void WriteToStream(NpgsqlBinaryImporter writer)
    {
        writer.StartRow();
        writer.Write(SensorId, NpgsqlDbType.Bigint);
        writer.WriteNullable(Value, NpgsqlDbType.Double);
        writer.WriteNullable(Comment, NpgsqlDbType.Text);
    }
}

await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync(ct);
await connection.BulkInsertViaCopyAsync(
    "COPY measurements (sensor_id, value, comment) FROM STDIN (FORMAT BINARY)",
    measurements,
    ct);

WriteToStream must start the row itself (StartRow).

Reading and LIKE patterns​

using Curiosus.DAL.NpgSQL;

int? count = reader.GetInt32("count");            // null for DBNull (or a default you pass)
string name = reader.GetString("name", "unknown");

// strips %, _, |, (, ), *, +, ?, {, }, [, ], ~, ! before building a LIKE pattern
var pattern = $"%{LikeFunctionHelpers.RemoveSpecialChars(userInput)}%";

See also​