0

I am developing a complaint management in which I have to generate unique serial number for each complaint like 00001/20 {Serial number/year}.

I am using repository pattern and i am generating this complaint number using the following code snippet but problem is if two user try to lodge a complaint at the same time it will generate a same complaint no and that thrown an error as I am keeping a serial number in a separate table which is also mentioned below for reference. Let me know the best way to achieve this 

 int serialNo = repository.serialNo.Find(c => c.Year == DateTime.Now.Year).FirstOrDefault().TicketCounter;
string complaintNo = string.Format("{0}", serialNo.ToString().PadLeft(5, '0'));

model.Id = repository.complaintRepo.GetMaxPK(c => c.Id);

I am using repository pattern.

Muhammad Waqas
  • 11
  • 4
  • Will this help? https://stackoverflow.com/questions/36868254/how-do-i-get-db-generated-ids-back-when-using-repository-pattern – user2316116 Apr 22 '20 at 10:21
  • Yes it will but as you can see I have two column Id & complaint no both I want unique and Id i can make it auto generated but complaint no is the problem. That's why I am manually getting the max value and incremented. – Muhammad Waqas Apr 22 '20 at 10:49
  • well, true but the point is that the model id should be generated (or reserved) on the db level - then it will remain unique. I think you have couple of solutions here, for example: change table to generate custom id by default, ex.: https://dba.stackexchange.com/questions/222617/how-to-create-custom-id-in-sql-server-with-sequence (then you will not need that code above at all, it will be done by database when new record is created). Alternatively, use existing code but when you get an error on adding it to the database, assign next # again and try to save again. Is SN unique within all years? – user2316116 Apr 22 '20 at 11:02
  • serial number will change by year so how you are suggesting to generate a complaint no. Id i will generate on db level but what about complaint no?? – Muhammad Waqas Apr 22 '20 at 11:07
  • if you use sql server, you can setup your table so that it generates a sequence that will automatically assign row ID as 00001/20, 00002/20, ... 00003/21 when you insert new rows in the table. – user2316116 Apr 22 '20 at 11:11
  • can you tell me how as currently I am using entity framework with repository pattern – Muhammad Waqas Apr 22 '20 at 11:20

1 Answers1

0

I guess, one of the solutions is to setup the table so that it generates required ID automatically on every new row. This ensures that the ID is always unique.

CREATE SEQUENCE MySequence
AS int
START WITH 1
INCREMENT BY 1;

CREATE TABLE Complaint
(
    Id    char(8) CONSTRAINT [DF_Complaint_ID]
                  DEFAULT FORMAT((NEXT VALUE FOR MySequence), '0000#')
                  +'/'+RIGHT(YEAR(GETDATE()),2),
    Foo   int,
    Bar   int,

    CONSTRAINT [PK_MyTable] PRIMARY KEY (Id)
);

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=18a5d0fec80a3985e30cef687d3c8e49

So there will be no need to assign the id manually and your code could look like 

var c = repository.Insert(new model
{
   Foo = ...
   Bar = ...,
   ...
});
repository.Save();

// you can get id after inserting data in the database
string id = c.Id;
user2316116
  • 6,726
  • 1
  • 21
  • 35
  • Thanks this will really help me alot, just one confusion my sequence is based on year so every year sequence will restart from one. Another question is if i have master detail and I wanna refer primary key to detail table and then after setting up all values I wanna hit the database for insertion. – Muhammad Waqas Apr 26 '20 at 10:48
  • What is the sense to refer primary key to detail table if this key does not exist in the database yet and can (as you already experienced) be same for concurrent users at the same time? – user2316116 Apr 26 '20 at 19:32
  • Reason is to avoid any discrepancy if i just insert record master table data and then get the identity value to assign it in foreign table what if it fails to save and throw an error. That's why currently what i am doing is i am generating primary key manually and assign it in foreign table and after setting all value i am calling SaveChanges() method. – Muhammad Waqas Apr 27 '20 at 07:48
  • Such approach does not help to avoid a "discrepancy" and every concurrent user at the same time can get same id. The easiest way to fix it will be to save id in the database so you can always know if id is already in use, or assign it on inserting the data in the database. – user2316116 Apr 27 '20 at 08:22
  • so what you suggest first save the primary table data and get the id and save the detail table data? – Muhammad Waqas Apr 27 '20 at 09:31
  • This is the easiest way as it ensures the id will be unique. At the same time it depends on the need, e.g. if the process of creating the details can be cancelled i.e. you will get an orphaned id in the primary table and this should not be the case, then you can keep all as-is and simply check for error on saving and generate next id so data from concurrent user will be saved with next available id. – user2316116 Apr 27 '20 at 09:58