How to handle primary key violation in C#?

Question

con.Open();

SqlCommand cmd = new SqlCommand(@"INSERT INTO donorinfo (donorid,name,fathersname,age,sex,cnicno,mobileno,dateofbirth,city,address,eyecolor,bloodgroup,dateofdonation) VALUES('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + comboBox1.Text + "','" + textBox6.Text + "','" + textBox7.Text + "','" + dateofbirth.Text + "','" + textBox9.Text + "','" + textBox10.Text + "','" + comboBox2.Text + "','" + comboBox3.Text + "','" + dateofdonation.Text + "')", con);

cmd.ExecuteNonQuery();
con.Close();

MessageBox.Show("Succesfully saved", "info");
clear();

Display();

I am working on blood bank management system here is my insert button code.

Please don't build your queries by concating strings - that makes you vulnerable to SQL Injection. Use prepared statements instead. — Manfred Radlwimmer, Mar 24 '18 at 20:17
a primary key violation? You really should never get one. The database should be auto-generating the IDs. Or if it's a table representing a many-many relationship (in which case a violation is potentially a more realistic scenario) then either a) rather than allowing it to occur, check in advance that a record for that combination of values doesn't already exist, or b) catch the specific exception and ask the user what to do instead - maybe it needs to be an update of an existing record instead of a new insert. — ADyson, Mar 24 '18 at 20:21
You are passing `textBox1.Text` as `donorid` so you're most likely inserting duplicates there — Camilo Terevinto, Mar 24 '18 at 20:22
Unclear what you are asking here. Do you still want to insert an item even though an item with the same PK exists in the db. Or handle the exception? `try..catch`? — FortyTwo, Mar 24 '18 at 20:23
Please see [How can I add user-supplied input to an SQL statement?](https://stackoverflow.com/q/35163361/1260204) — Igor, Mar 24 '18 at 20:24

Transcendent · Answer 1 · 2018-03-24T20:44:33.073

Primary keys are not meant to be generated manually due to the fact that after you have inserted millions of records, you cannot tell if an ID that you are manually selecting or creating is duplicate or not. This means that PKs must be always auto-generated either by the database or by your code. In case you want it to be generated by the database you can use the IDENTITY keyword and then select a seed (example: IDENTITY(1,1)) or you can create a SEQUENCE in the database. However, another option is to use a GUID as the key. If you want to generate GUID type PKs in database (and particularly a sequential GUID) you can set your primary key type as UNIQUEIDENTIFIER ROWGUIDCOL and you can use the NEWSEQUENTIALID() function to create a sequential GUID as default value. On the other hand, you can generate GUID in C# using the following code:

var id = Guid.NewGuid(); // or Guid.NewGuid().ToString();

However, I recommend you not use a GUID PK and instead go for a numerical primary key since that needs less space, and it would make insert/update/delete or cascade operations much faster.

How to handle primary key violation in C#?

1 Answers1