CODEWITHSUNDEEP
c#postgresqldappernpgsql
ubi
ubi

Reputation: 4399

Npgsql + Dapper ExecuteReader

Not sure if this a bug in Npgsql or Dapper or I'm doing it wrong.

I'm trying insert an array of records and return the inserted rows to get the inserted ids. The records are passed as a Postgres array parameter

c# class

public class test
{
    public int id { get; set; }
    public string name { get; set; }
}

It is mapped to the following table

// id is PK sequence
create table test (
    id int,
    name text
)

Here's the insertion code

var sql = @"
with t as (
    insert into public.test t (id, name)
    select i.id, i.name
    from unnest(@items) i left outer join t
    on i.name = t.name
    where i.id is null
    returning *
)";

var items = new[]
{
    new NpgsqlParameter("items", new []
    {
        new test() {name = "x"},
        new test() {name = "y"},
    }
};

// Not using Dapper for command exec as it doesn't support PG composite types
// Only using the RowParser
IList<T> result = new List<test>();
using (var connection = new NpgsqlConnection(...))
{
    connection.MapComposite<test>("public.test");
    using (NpgsqlCommand cmd = new NpgsqlCommand())
    {
        cmd.Connection = connection;
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = sql;
        cmd.Parameters.Add(parameters);

        using (var reader = await cmd.ExecuteReaderAsync())
        {
            var hasRows = reader.HasRows; // true
            var rowParser = reader.GetRowParser<test>(typeof(test)); // <== Dapper parser
            result.Add(rowParser(reader)); // <== throws the exception below
        }
    }
}

result.Add(parser(reader)) throws the following exception (IRowParser is a Dapper interface)

{System.InvalidOperationException: No row is available
   at Npgsql.NpgsqlDataReader.CheckRow()
   at Npgsql.NpgsqlDataReader.GetValue(Int32 ordinal)

Any ideas what's wrong here?

Upvotes: 2

Views: 2691

Answers (1)

Michael Miller
Michael Miller

Reputation: 365

Try adding a reader.Read() call.

using (var reader = await cmd.ExecuteReaderAsync())
{
    reader.Read() // <== ADD THIS LINE
    var hasRows = reader.HasRows; // true
    var rowParser = reader.GetRowParser<test>(typeof(test)); // <== Dapper parser
    result.Add(rowParser(reader)); // <== throws the exception below
}

Thank, your code was useful for me as I wanted to know how I could use Dapper and PostgreSQL composite types.

Upvotes: 2

Related Questions