Different Ways To Generate The Latest Int Type Primary Foreign Key In Code

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

Solution 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

CREATETABLE dbo.YourTableOne(ID INTIDENTITY(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 tableINSERTINTO dbo.YourTableOne(Col1, Col2, ...., ColN)
VALUES (Val1, Val2, ...., ValN)

DECLARE@NewIDINT-- get the newly inserted ID for future useSELECT@NewID= SCOPE_IDENTITY()

-- insert into the second table, use first table's new ID for your FK columnINSERTINTO 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 dataDECLARE@InsertedDataTABLE (NewID INT, ...some other columns as needed......)

-- do the insert into the first tableINSERTINTO 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

Solution 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 delaygoto 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();
        }