SQL TOP 1 query when generating random ID with C# application

Question

I have a C# application which creates an entry in the SQL database to table_customer_invoice and table_customer_invoice_detail, when a user sells a particular item from the application, as soon as the user enters the amount tendered, an insert in the customer_invoice table is done where ID is an identity column which I use that ID by reading the last entered ID by (SELECT TOP 1 ID FROM CUSTOMERINVOICE ORDER BY ID DESC) to insert in the table_customer_invoice_detail.

It works well the only problem is when two users sell an item together at the exact same time from different computers than the (SELECT TOP 1 ID) clashes and both users get the same ID as it looks for the last ID created. Is there any other way I can get the exact ID for the particular user?

This is the code in the application


SqlCommand cmd3 = new SqlCommand("insert into CUSTOMERINVOICE(INVOICEDATE,INVOICESTATUS,ISDEBTORINVOICE,ISLAYBYEINVOICE,INVOICETYPE,INVOICETOTAL,salesrep,dayended)" + "values(GETDATE(),0,0,0,1,'" + Convert.ToDecimal(lblTotalAmount.Text.ToString()) + "','" + txtSalesRep.Text + "',0)", conn);
cmd3.ExecuteNonQuery();

SqlDataAdapter ada2 = new SqlDataAdapter("SELECT TOP 1 ID FROM CUSTOMERINVOICE ORDER BY ID DESC", conn);
  DataTable dt2 = new DataTable();
  ada2.Fill(dt2);
  DataRow dr2 = dt2.Rows[0];
  CUSTID = Int32.Parse(dr2["ID"].ToString());

                        foreach (DataGridViewRow row in dgSaleBasket.Rows)
                        {

                            SqlDataAdapter ada5 = new SqlDataAdapter("SELECT itemcode,onhand,costincl FROM stmast where itemcode ='" + row.Cells[1].Value + "'", conn);
                            DataTable dt5 = new DataTable();
                            ada5.Fill(dt5);
                            int quantityPurchased = Int32.Parse(row.Cells[4].Value.ToString());
                            for (int i = 0; i < dt5.Rows.Count; i++)
                            {
                                DataRow dr5 = dt5.Rows[i];

                                double SellPrice = Convert.ToDouble(row.Cells[5].Value.ToString());
                                costinc = Convert.ToDouble(dr5["costincl"].ToString());
                                profit = (SellPrice - costinc) * quantityPurchased;
                                totalprofit = profit + totalprofit;
                                SqlCommand cmd4 = new SqlCommand("insert into CUSTOMERINVOICEDETAIL(INVOICEID,ITEMCODE,DESCRIPTION,QUANTITY,PRICE,profit,refund)" + "values(" + CUSTID + ",'" + row.Cells[1].Value.ToString() + "','" + row.Cells[2].Value.ToString() + "'," + row.Cells[4].Value.ToString() + ",'" + Convert.ToDecimal(row.Cells[5].Value.ToString()) + "'," + profit + ",0)", conn);
                                cmd4.ExecuteNonQuery();


                                SqlCommand cmd6 = new SqlCommand("UPDATE stmast SET onhand =onhand-" +quantityPurchased + ", lastSold =GETDATE() , lastSoldPrice=" + Convert.ToDecimal(row.Cells[5].Value.ToString()) + ",totalQtySold=totalQtySold+" + quantityPurchased + " WHERE itemcode ='" + row.Cells[1].Value + "'", conn);
                                cmd6.ExecuteNonQuery();

                                SqlCommand cmd2 = new SqlCommand("UPDATE customerinvoice set invoiceprofit=" + totalprofit + " WHERE id =" + CUSTID, conn);
                                cmd2.ExecuteNonQuery();


                            }

What is salesrep here? if that's the user who creates the records, then use that as a filter when you try to get the ID — Thangadurai, Nov 26 '19 at 09:24
@PeterB thanks peter seems like something i can try i will post the outcome — Abrar, Nov 26 '19 at 09:25
@Thangadurai thats a good suggestion but a user can enter into both PC's at once so it wont work. — Abrar, Nov 26 '19 at 09:30
You can use transactions for this (if you want to keep it way you are doing) to prevent others from Reading/writing untill your code is completed. — Mr Zach, Nov 26 '19 at 09:42
Perhaps time to switch to an ORM, You will insert object in database the object itself will have the Id property changed, with the correct value. — Drag and Drop, Nov 26 '19 at 09:43
And btw you may want to check [SQL injection and parameterized queries](https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements). — Drag and Drop, Nov 26 '19 at 09:46

score 0 · Answer 1 · answered Nov 26 '19 at 09:43

Maybe you can try by using INSERTED temporary table example :

CREATE DATABASE TEST

USE TEST

CREATE TABLE client
(
id INT IDENTITY(1,1),
name varchar(20)
)

DECLARE @tempTable TABLE(id INT,
                         [name] varchar(20))
INSERT client(name)
OUTPUT INSERTED.* INTO @tempTable
VALUES ('Marc');
SELECT * FROM @tempTable
-- Return |1|Marc|

SQL TOP 1 query when generating random ID with C# application

1 Answers1