CODEWITHSUNDEEP
c#sql-serverinsertado.net
Mr ASD
Mr ASD

Reputation: 123

Add a non existing Primary Key in a SQL server table

I want to insert data in a table that has integers as primary keys. The primary keys will be added by a wide range of random numbers. First I want to check if the primary key exists and then insert it.

Here is my code:

public static void Insert()
    {
       bool a=true;

        while (a != false)
        {


            try
            {
                db.test.Add(new test() //insert into test table new data
                {

                    Id = rand.Next(1, 10),
                    name = "test",
                    surname = "test"
                });

                db.SaveChanges();
                a=false;
            }
            catch (Exception)
            {

                Console.WriteLine("Duplicate");

            }


        }
    }

In the first iteration if the number is not duplicate I can successfully insert the new row in database. If in the first iteration it is duplicate entry after db.SaveChanges();i jump directly in catch. But the problem arises when in the second iteration the random number is not duplicate it still jumps to catch exception without executing the a=false line of code.

Any Idea?

Note: I am not interested on using GUIDs or any other data type.

Upvotes: 1

Views: 326

Answers (2)

Marc Gravell
Marc Gravell

Reputation: 1064184

The issue here is that once a duplicate key has been generated, it will fail to insert, so it will keep trying to insert it each time; to quote OP:

Yes exactly, when it detects a duplicate it keeps trying it even though the next number may not be a duplicate.

There are 3 main ways of solving this:

  1. don't generate your own keys; sorry, but this is the simplest, most obvious, and most supportable mechanism here; IDENTITY is your friend
  2. if you fail to insert something : remove that same something from your ORM context so you don't keep failing; there is presumably a db.test.Remove method that will suffice; you may also be able to just change the value in the last row, abusing the fact that you know it was the last row that failed: db.test.Last().Id = newId; - this is very odd though; everything gets weird if you change identity keys
  3. or alternatively, don't keep re-using the data context after failure; assuming that db is now unusable after the exception, and create a new one for any subsequent operations; IMO this should be done by the same code that originally created the db-context, in a "redo from start" way

Upvotes: 2

Billy Ferguson
Billy Ferguson

Reputation: 1439

I might have an easier solution for you if you don't NEED it to be a random number.

CREATE TABLE Persons (
    ID int IDENTITY(1,1) PRIMARY KEY,
    Surname varchar(255) NOT NULL,
    Name varchar(255)
); 

INSERT INTO Persons (Name,Surname)
VALUES ('Lars','Monsen');

This way you don't even need to do anything with the PRIMARY KEY, SQL Server will handle it for you!

Upvotes: 2

Related Questions