Retrieve serial ID with Npgsql when inserting with ExecuteScalar

c# .net npgsql
Cheluis picture Cheluis · Apr 7, 2011 · Viewed 15.3k times · Source

I'm trying to insert a row into a PostgreSQL table with a serial primary key and I need to retrieve this column after it was inserted. I got something like this:

The table "pais" has 3 columns: id, pais, capital; id is a serial column and is its primary key.

NpgsqlCommand query = new NpgsqlCommand("insert into pais(nombre, capital) values(@nombre, @capital)", conn);
query.Parameters.Add(new NpgsqlParameter("nombre", NpgsqlDbType.Varchar));
query.Parameters.Add(new NpgsqlParameter("capital", NpgsqlDbType.Varchar));
query.Prepare();
query.Parameters[0].Value = this.textBox1.Text;
query.Parameters[1].Value = this.textBox2.Text;
Object res = query.ExecuteScalar();
Console.WriteLine(res);

It inserts the row on the table but "res" value is null. If I insert with the nexval('table_sequence') also returns null.

Any idea of how can I return the id of the table? Am I missing something?

Thanks in advance

Answer

hsmiths picture hsmiths · Apr 23, 2011

Is that thread safe?
What if another insert happens between your insert and select? Why not use:

INSERT INTO table (fieldnames) VALUES (values) RETURNING idcolumn?

Related questions

INSERT data from Textbox to Postgres SQL

I just learn how to connect C# and PostgresSQL. I want to INSERT data from tb1(Textbox) and tb2 to database. But I don't know how to code My previous code is SELECT from database. this is my code private …

Read More
Could not load file or assembly 'System.Memory, Version=4.0.1.' in Visual Studio 2015

For a couple of months i had no issue about generating the model from DB by deleting it and recreating it . After a pull from git, an issue has been occurred while trying to make the same process . After the …

Read More
How to make an HTTP POST web request

Canonical How can I make an HTTP request and send some data using the POST method? I can do a GET request, but I have no idea of how to make a POST request.

Read More