How to create the following primary key value

Question

I am using Visual Studio 2010 PostgreSQL 9.x Npgsql

I am trying to insert data from fields in a C # WinForms application. I just can't figure out how to generate / get the next primary key value on the fly. Here are the names of my columns:

epiphanyKey [PK] bigserial transaction number license symbol dateOfActv date time

I need the insert command to be "session safe", as several people can enter data into the database at the same time.

NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=postgres;Password=password;Database=epiphany;"); // postgres 8.3 on my test system conn.Open(); // opens the connection NpgsqlCommand cmd = new NpgsqlCommand("INSERT INTO wsmsmrs210 (epiphanyKey,transaction,license,dateOfActv,time, conn); NpgsqlDataReader dr = cmd.ExecuteReader(); 

In the above code, the NpgsqlCommand cmd = ... operator does not work correctly because I do not know the next primary key value for the epiphanyKey primary key value.

Any ideas or sniplets code to generate the following primary key value when sending a request in db?

Answer 1

You can use the returning keyword so that the query returns the identifier that was just created. Example from the docs :

 INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') RETURNING did; 

The code you posted is not complete or can even be compiled, so I cannot give you a complete example of how to use it in your case, but here is the beginning:

 NpgsqlCommand cmd = new NpgsqlCommand( "INSERT INTO wsmsmrs210 " + "(epiphanyKey,transaction,license,dateOfActv,time, conn) " + "VALUES (...) " + "RETURNING epiphanyKey"); int id = cmd.ExecuteScalar(); 

Answer 2

You should use Sequences to automatically create your primary identifier inside the database when inserting new objects.

For a more detailed answer (code), I need to know the structure of the wsmsmrs210 table.

Generally, if your datatable is created using:

 CREATE SEQUENCE sequence_name; CREATE TABLE wsmsmrs210 ( epiphanyKey bigint default nextval('sequence_name'), ... ) 

then your code should look like this:

 NpgsqlConnection conn = new NpgsqlConnection("Server=localhost;Port=5432;User Id=postgres;Password=password;Database=epiphany;"); conn.Open(); // opens the connection NpgsqlCommand cmd = new NpgsqlCommand("INSERT INTO wsmsmrs210 (transaction,license,dateOfActv,time) VALUES(:a,:b,:c,:d)", conn); ... // add parameters :a, :b, :c, :d via cmd.Parameters.Add(...) here cmd.ExecuteNonQuery(); // Add next two lines if you need last inserted id in code cmd = new NpgsqlCommand("SELECT CURRVAL('sequence_name')", conn); var id = cmd.ExecuteScalar(); conn.Close(); 

If you want to have maximum reliability, then you should write a saved function in PLPGSQL that will take your field values ​​as input parameters, insert the value into the table, get the last insert identifier and return it.