CODEWITHSUNDEEP
c#winformspostgresqlnpgsql
Ben Cox
Ben Cox

Reputation: 51

How to generate next Primary Key value

I'm using Visual Studio 2010 PostgreSQL 9.x Npgsql

I'm trying to insert data from fields on a C# WinForms app. I just can't figure out how to generate/retrieve the next primary key value on the fly. Here are my column names:

epiphanyKey [PK] bigserial transaction numeric license character dateOfActv date time time

I need the insert command to be "session-safe" since multiple people could be entering 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 code above the NpgsqlCommand cmd = ... statement doesn't work correctly because I don't know the next primary key value for the primary key value epiphanyKey.

Any ideas or code sniplets to generate the next primary key value when sending the query to the db?

Upvotes: 5

Views: 3893

Answers (2)

Guffa
Guffa

Reputation: 700312

You can use the returning keyword to make the query return the id that was just created. Example from the docs:

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

The code that you posted is not complete, or even possible to compile, so I can't give you a complete example how to use it in your case, but here is a start:

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

int id = cmd.ExecuteScalar();

Upvotes: 5

Sergii Kudriavtsev
Sergii Kudriavtsev

Reputation: 10512

You should use Sequences for generating your primary ID automatically inside database when inserting new objects.

For more elaborate answer (code) I'll need to know the wsmsmrs210 table structure.

Generally, if your datatable is created with:

CREATE SEQUENCE sequence_name;

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

then your code should look like:

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 stored function in PLPGSQL that will accept your field values as input parameters, insert the value to the table, get last insert id and return it.

Upvotes: 4

Related Questions