Return ID of newly inserted row on a PostgreSQL database using C# and Npgsql?

Question

I'm building a WinForms project in C# using a PostgreSQL database and the Npgsql framework.

For inserting a record, I need to return the ID of the new record. This SO question says to add SELECT SCOPE_IDENTITY() to the query string passed to cmd. So my query string looks like this:

string insertString = "INSERT INTO sometable (company_name, category, old_value, old_desc, new_value, new_desc, reference1, reference2) VALUES (@comp, @cat, @oldValue, @oldDesc, @newValue, @newDesc, @ref1, @ref2); SELECT SCOPE_IDENTITY();";

and then get the ID with something like

int modified = cmd.ExecuteNonQuery();

But that's likely SQL Server-specific. If I use that method, I get an exception at the above line saying, "fuction scope_identity() does not exist".

I wasn't able to find anything that seemed to address this on the Npgsql documentation.

Per the linked SO question and Denis' suggestions I've tried adding both

RETURNING id;

and

CURRVAL(pg_get_serial_sequence('my_tbl_name','id_col_name'))

to the query string, replacing SELECT SCOPE_IDENTITY(); with those statements in the code above. In both cases they work as intended in DBeaver on an insert, but in my C# code in my WinForm project, modified was set to "1".

NOTE: I re-titled the question and added more information about what I've done.

There is no SCOPE_IDENTITY function in PostgreSQL. See this topic: stackoverflow.com/questions/2944297/… — Denis Stukalov
– Denis Stukalov, Commented Apr 6, 2020 at 18:15
@DenisStukalov, thanks for the link. I added RETURNING id; to the query string and left the ExecuteNonQuery line alone, expecting it to put the id in modified, but when I ran it, modified's value was "1". I tried the query in DBeaver and it returned the ID of the new row, so I'm just not certain how to access that value in my C# code. — marky
– marky, Commented Apr 6, 2020 at 18:59
I think, you better use this expression - SELECT CURRVAL(pg_get_serial_sequence('my_tbl_name','id_col_name')) and then user cmd.ExecuteScalar() for filling 'modified' variable — Denis Stukalov
– Denis Stukalov, Commented Apr 6, 2020 at 19:25
I just tried that and am still getting a 1 for the modified variable. — marky
– marky, Commented Apr 6, 2020 at 19:35
CURRVAL also worked in DBeaver with an insert, but like RETURNING, in my WinForm C# code, modified is just set to 1. — marky
– marky, Commented Apr 6, 2020 at 19:55

user3567816 · Accepted Answer · 2021-10-20 08:13:26Z

9

Add "returning idcolumn" to the end of the sql query, then run the command with the ExecuteScalar() method instead of ExecuteNonQuery(). It should return with an int.

string insert = "insert into table1 (col1) values (something) returning idcol";
int id = cmd.ExecuteScalar();

answered Oct 20, 2021 at 8:13

user3567816

1661 gold badge2 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jason Roos · Accepted Answer · 2022-04-14 19:30:07Z

1

All the comments above were almost nearly spot on and got me to a solution but didn't exactly wrap it in a bow -- so I thought i'd post my implementation that works (with silly fake example tables of course).

        private int? InsertNameIntoNamesTable(string name)
        {
            int? id = null;
            
            using (var dbcon = new NpgsqlConnection(_connectionString))
            {
                dbcon.Open();

                StringBuilder sb = new StringBuilder();

                var sql = $@"                    
                    insert into names_table
                        (name)
                    values
                        ({name})
                        returning id;
                ";

                sb.Append(sql);

                using (var cmd = new NpgsqlCommand(sql, dbcon))
                {
                    id = (int)cmd.ExecuteScalar();
                }

                dbcon.Close();
            }

            return id;          
        }

answered Apr 14, 2022 at 19:30

Jason Roos

815 bronze badges

1 Comment

KeithL Over a year ago

I would suggest to use parameters instead of directly "injecting" a string. There is sql injection safety with parameters.

Mahi · Accepted Answer · 2023-04-10 06:24:27Z

1

If you want to read multiple values after inserting you might use command.ExecuteReader(). command.ExecuteScalar() working for single column.

string insertCommand = "Insert into tableX (lastName, firstName,) values ('abc', 'efg') returning id, autoIncrementedClumn";
command.CommandText = insertCommand;
var reader = command.ExecuteReader();
if (reader.Read())
{
  int id = Convert.ToInt32(reader["id"].ToString());
  int autoIncrementedClumn=Convert.ToInt32(reader["autoIncrementedClumn"].ToString());
{

answered Apr 10, 2023 at 6:24

Mahi

1,20713 silver badges22 bronze badges

Collectives™ on Stack Overflow

Return ID of newly inserted row on a PostgreSQL database using C# and Npgsql?

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related