Different ways to generate the latest int type pri

Question

I am new to sql. I have added 2 new tables in database. The primary key of first is a foreign key in the other. The type of the keys is integer. Now I want to generate the keys in the code and assign it to new data so that the association between different rows of the tables is right. How do I ensure uniqueness of keys and also get the latest key from the db so that there are no errors while saving.

If I had used guids then I would have assigned a new guid to the primary key and then assigned the same to the foreign key in the other table. Also there are multiple clients and one server which is saving the data.

The data to be inserted in both the tables is decided in the c# code and is not derived from the row inserted in the primary table. Even if get the id in db then also the relation between the rows should be stored in some form from the code because after that it is lost.

Answer 1

The only viable way to do this is to use INT IDENTITY that the SQL Server database offers. Trust me on this one - you don't want to try to do this on your own!

Just use

CREATE TABLE dbo.YourTableOne(ID INT IDENTITY(1,1),  ...other columns...)

and be done with it.

Once you insert a row into your first table, you can retrieve the value of the identity column like this:

-- do the insert into the first table
INSERT INTO dbo.YourTableOne(Col1, Col2, ...., ColN)
VALUES (Val1, Val2, ...., ValN)

DECLARE @NewID INT

-- get the newly inserted ID for future use
SELECT @NewID = SCOPE_IDENTITY()

-- insert into the second table, use first table's new ID for your FK column
INSERT INTO dbo.YourTableTwo (FKColumn, ......) VALUES(@NewID, ......)

Update: if you need to insert multiple rows into the first table and capture multiple generated ID values, use the OUTPUT clause:

-- declare a table variable to hold the data
DECLARE @InsertedData TABLE (NewID INT, ...some other columns as needed......)

-- do the insert into the first table
INSERT INTO dbo.YourTableOne(Col1, Col2, ...., ColN)
OUTPUT Inserted.ID, Inserted.Col1, ..., Inserted.ColN INTO @InsertedData(NewID, Col1, ..., ColN)
VALUES (Val1, Val2, ...., ValN)

and then go from there. You can get any values from the newly inserted rows into the temporary table variable, which will then allow you to decide which new ID values to use for which rows for your second table

Answer 2

As @marc_s said using Database managed keys is more viable. But in cases there is no much load on the database, for example because there are few users who work simultanously, I will use another easier method. That's I get the last id, I try to add new record, and if I encountered error for duplicate, I will try again. I limited this to 3 trials for my application and there's a 300 ms timeout between each trial. Dont forget that this approach has serious limitations. In my application, there are very few users, the work load is very low, and the connection is a local one so this will do job well. Perhaps in other applications you need to adjust the delay, and in some cases, the approach might completely fail. Here's the code, I have two tables, Invoices and Invoices_Items the column which relates them is invoice_id:

        byte attempts = 0;

      tryagain:  //Find last invoice no

        OleDbCommand command = new OleDbCommand("SELECT MAX(invoice_id) FROM Invoices"
                 , myconnection);
            int last_invoice_id = 0;
            try
            {
                last_invoice_id = (int)command.ExecuteScalar();
            }
            catch (InvalidCastException) { };

           // text_invoice_number.Text = Convert.ToString(last_invoice_id + 1);

            try
            {
                command = new OleDbCommand(@"INSERT INTO Invoices
      (invoice_id,patient_id,visit_id,issue_date,invoice_to,doctor_id,assistant_id)
     VALUES(?,?,?,?,?,?,?)",myconnection);
   // We use last_invoice_id+1 as primary key
   command.Parameters.AddWithValue("@invoice_id",last_invoice_id+1);
            // I will add other parameters here (with the exact order in query)
                command.ExecuteNonQuery();
            }
            catch (Exception ex){
                attempts++;
                if (attempts <= 3)  // 3 attempts
                {
                    System.Threading.Thread.Sleep(300); // 300 ms second delay
                    goto tryagain;
                }
                else
                {
                    MessageBox.Show("Can not add invoice to database, " + ex.Message,   "Unexpected error!"
                        , MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
            }

        for (int i = 0; i <= listInvoiceItems.Count-1; i++)
        {
               command = new OleDbCommand(@"INSERT INTO Invoices_Items
                                 (invoice_id,quantity,product,price,amount,item_type)
                                        VALUES(?,?,?,?,?,?)",myconnection);
       // Now we use our stored last_invoice_id+1 as foreign key
             command.Parameters.AddWithValue("@invoice_id",last_invoice_id+1); 
         // Add other Invoice Items parameters here (with the exact order in query)
        command.ExecuteNonQuery();
        }