How to avoid data concurrency while insert/update in table using c#

Question

Hi,

I am using angular 11 for UI and asp.net mvc 5 as backend coding in my web application.

I have a bill table in my sql 8 which has coulmns like 

BillID - Primary key , auto generated

BillNo  

ClientID and some other columns.

 

 For every client Bill no starts with 1.

Now, BillID is primary key and auto generated but BillNo is getting calculated through coding. I am taking max bill No. for that client and add 1 to it to show next/latest bill No to user of that client.

 

e.g. If a client has two user  A and B. Both A and B are from billing department and making bills at the same time.

Both can see latest Bill no on billing screen. e.g. bill no -25. When they both clicks save bill at the same time,

in the databse Bill no -25 goes for both the bills.  BillID goes different because BillID is primary key and auto generated. But BillNo goes same. How Can I handle this Bill No duplication issue?

 

This is happening with other cases also like payment receipts/refund receipts in which receipts nos are going same.

 

How can I handle these issues.

Thank you in advance.

Be Safe.