How to get the last inserted row number?

Question

This question may seem very very easy (Select COUNT(Id), but in my case is different. I developed a web application with 15 to 25 concurrent users. every time they open the program, I am checking the last row inserted into the database and in C# I created a serial number based on the last row inserted + 1. So say for example below entry:

 

User1 opens up the program:

      DB count = 1

      C# Series = Ser-00001 (displayed in UI)

      User1 inserts a row. success

 

User2 opens up the program:

      DB count = 2

      C# Series = Ser-00002 (displayed in UI)

      User2 inserts a row. success

 

User3 opens up the program:

      DB count = 3

      C# Series = Ser-00003 (displayed in UI)

      User3 inserts a row. success

 

No issue right? But, there will be a time that 2 or more users will open the program at the same time. Now, below is the scenario

 

 

User7 opens up the program:

      DB count = 4

      C# Series = Ser-00004 (displayed in UI)

      User7 inserts a row. success

 

User8 opens up the program:

      DB count = 4

      C# Series = Ser-00004 (displayed in UI)

      User8 inserts a row. failed

 

User9 opens up the program:

      DB count = 4

      C# Series = Ser-00004 (displayed in UI)

      User9 inserts a row. failed

 

This time a big problem suddenly occur! 

 

Now my question is, how do you do it to avoid this type of duplication? Thank you in advance for any posible solution.