2

I'm trying to use Dapper, Npgsql to connect to a postgres database from a .net application. After an insert, I want to know the id of inserted row. I have read and tried options discussed here. I have tried using returning id like so:

id = connection.Execute("insert into document_tag (tag) values (@tag) returning id;",
            new { tag },
            transaction);

but I get only '1' in id.

I also tried to use currval like so:

id = connection.Execute("insert into document_tag (tag) values (@tag); select currval(pg_get_serial_sequence('document_tag','id'));",
            new { tag },
            transaction);

Even in this case, I get only '1' in id.

In both cases, the insert was successful and the id column in the database had a valid serial number other than '1'.

Is my expectation wrong or is there an alternative?

KSK
  • 666
  • 4
  • 22

2 Answers2

2

To insert a row and get back a database-generated column, use:

INSERT INTO document_tag (tag) VALUES (@tag) RETURNING id

Note that there is no semicolon before "RETURNING" - it's just a clause of the INSERT statement, as you can see in the PostgreSQL docs.

In addition, once you add a RETURNING clause, the INSERT statement returns a resultset containing the requested information, just as if you executed a SELECT statement. Dapper's Execute() is meant to execution without resultsets, so you will need to switch to Query(), and probably add a Single() method to extract the single value you need. An alternative would be ExecuteScalar().

Shay Rojansky
  • 15,357
  • 2
  • 40
  • 69
0

As suggested by @Shay: using connection.QueryFirst<int> instead of connection.Exeucte and got it working. Updated working code:

id = connection.QueryFirst<int>("insert into document_tag (tag) values (@tag) returning id;",
        new { tag },
        transaction);
KSK
  • 666
  • 4
  • 22