Return last inserted ID in postgresql with c#

Question

I a function that inserts a record in a table, and i want to get the insered ID. How can I do? I have to make a new query?

        using (NpgsqlConnection conn = new NpgsqlConnection(ConfigurationManager.ConnectionStrings["AxWaveConnection"].ToString()))
        {

            conn.Open();
            NpgsqlTransaction transaction = conn.BeginTransaction();


            String query =
            "INSERT INTO Video (youtubeidvideo, title, rating, viewcount, duration, dataadded, errorflag, schedulingflag, schedulingdate, convertdate) " +
            "VALUES ('" + youtubeIdVideo + "','" + title + "'," + rating.ToString().Replace(',', '.') + "," + viewCount.ToString() +  "," +
            duration.ToString() + ", now(), false, false, null, null); ";

            NpgsqlCommand cmd = new NpgsqlCommand(query, conn, transaction);

            try
            {
                cmd.ExecuteNonQuery();


            }
            catch (Exception e)
            {
                scopeID = -1;        //Duplicate Record
            }

            transaction.Commit();
            conn.Close();
        }

In order to select the last identity inserted you need to use: currval(sequencename) I would also suggest that you refactor the Insert Statement to utilize Parameterized Query looking at the way that the query is setup you could experience errors with the now() see if your NpgsqlCommand supports Parameters.AddWithValues here is a good stack overflow link as well NpgSqlCommand Return Last Inserted Record — MethodMan
– MethodMan, Commented Feb 13, 2013 at 10:46

CodeGuru · Accepted Answer · 2013-02-13 10:46:46Z

4

Can you try this:

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

You need to supply the table name and column name of course.

This will be for the current session / connection

http://www.postgresql.org/docs/8.3/static/functions-sequence.html

You can check this also

See the RETURNING clause of the INSERT statement. Basically, the INSERT doubles as a query and gives you back the value that was inserted.

answered Feb 13, 2013 at 10:46

CodeGuru

2,8137 gold badges39 silver badges53 bronze badges

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

Collectives™ on Stack Overflow

Return last inserted ID in postgresql with c#

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related