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

dip s

5y1.1kLearn iOS Programming

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.

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rijwan Ansari5y ago
Hi,

1. use transaction while saving and do max+1 just before save.
2. make the bollNo column unique in database.
0
Varun Setia5y ago
Hi,

To avoid overwriting of data follow the steps below:
1. Insert ID and LastUpdateTimestamp.
2. Get ID and LastUpdateTimestamp in currentSession.
3. On Update if (current session LastUpdateTimestamp!=database LastUpdateTimestamp).
4. Generate new ID i.e. max+1 and Insert Data .
5. Else insert the data with existing ID.

This will allow you handling the data using Optimistic concurreny approach. Instead of throwing concurrency exception you can generate new id and insert data in that record, but remember to update the new id in bill in UI.
+2
dip s5y ago
@ sachin singh thank you for your reply.
I am already saving latest bill no in a table(e.g. XYZ table) in database. When user clicks on new bill. On billing screen I am retrieving Bill no from this table and showing on screen. But if more than 1 user opens billing screen then they all can see same bill no. When save bill button is clicked. I am saving data to bill table and also incrementing Bill no by 1 and saving in table XYZ. So that every time latest bill no gets displayed to user.
But if more users are making bill then what i want is like the record will get inserted in bill table then bill no is incremented by 1 and get updated in XYZ table. then for next billing this latest bill no should get inserted in bill table again bill no will increment by 1 and updated in XYZ table and so.

I am alreading using begin trans and commit trans for this.
// Code to insert data in bill table
1. Begin trans
2. Get Bill no from XYZ table.
3. Insert record in bill table with bill no from XYZ table.
4. Update XYZ table set Bill no = Bill no + 1
5. Commit trans.
+1
Sachin Singh5y ago
two users mean two sessions, before displaying bill no to users, save the bill no to the database and then while generating bills retrieve bill_No, at the same time use transaction and thus you will get different bill no for different users.
Or Method-2
in the session_start event of Global.asax , create one application variable, increment the variable with each session and then retrieve that and add that to the Max(bill_No) instead of adding 1.
ret
+1