locked
How to generate series number for every record during save in mvc RRS feed

  • Question

  • User1878001738 posted
    Hi,

    This is the scenario. I am developing an application wherein I need to generate series number for every record saved. For example this is the format of series number:
    PSE-2019-000001 wherein PSE is the prefix, the current year, and the 000001 is the count of record that is saved in the database. For every record saved, the count will increment so as to determine the what is the last record count and to know the particular record based on its series number. So lets say if i saved three records in the database, the series number would be PSE-2019-000001, PSE-2019-000002, and PSE-2019-000003. It would also better if i use date and time instead of just the current year but I think that will still do.
    Can someone help me through this? I need to do this in code-level, not in the sql database.
    Thank you in advance.
    Sunday, June 16, 2019 11:30 AM

Answers

  • User1120430333 posted

    1) go get the max record in the database table, take the number part add 1 to it and write a new record, which is not very good in a multi user environment, becuase there can be collision on duplicated numbers being applied.

    2) use an auto-incremented number controlled by the DB engine, becuase once the number has been used, there can be no duplicate situation.

    3) You keep the count record that is not data based related in a file, which is not good since only one user can have the file open at any given time and  again is not a good situation in a multi-user environment.

    I have always used option 2 and never had a problem doing it.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 16, 2019 4:17 PM
  • User475983607 posted

    I recommend at least 3 columns.  One for  PSE, year, and numeric series.  

    I need to do this in code-level, not in the sql database.

    I don't recommend do so unless the application has a single user.  Otherwise, you'll need to implement a wait/retry process when dirty data is detected.  Remember a web application is disconnected for SQL.  You can easily have several users with the same "Next Number".

    Generating the sequence in the database during an INSERT is by far the more reliable method.  A TRIGGER would work well.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 16, 2019 6:41 PM
  • User1520731567 posted

    Hi loraine26,

    I suggest you could query your max Id and set your series number based on rule in controller,

    For example:

     public ActionResult Create()
            {
                Employee emp = new Employee();
                //sort the employee and get the last insert employee.
                var maxId= db.XXX.OrderByDescending(c => c.ID).FirstOrDefault();
                if (lastemployee == null)
                {
                    emp.EmployeeID = "PSE-2019-000001";
                }
                else
                {
                    emp.EmployeeID = "PSE-"+DateTime.Now.Year.ToString()+"-"+(maxId.ID + 1).ToString("D6");
                }
                ...
                return View(emp);
            }

    In VIEW:

             ....
            @Html.HiddenFor(model=> model.EmployeeID)
    
    
            <div class="form-group">
                @Html.LabelFor(model => model.EmployeeName, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.EmployeeName)
                    @Html.ValidationMessageFor(model => model.EmployeeName)
                </div>
            </div>

    ....

    In post action:

      [HttpPost]
            [ValidateAntiForgeryToken]
            public ActionResult Create([Bind(Include = "xxxx,xxxx...)] Model model)
            {
                if (ModelState.IsValid)
                {
                    db.Employees.Add(model);
                    db.SaveChanges();
                    return RedirectToAction("Index");
                }
    
                return View(model);
            }

    More details,you could refer to this reply:

    https://forums.asp.net/post/6167155.aspx

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 17, 2019 9:59 AM

All replies

  • User1120430333 posted

    1) go get the max record in the database table, take the number part add 1 to it and write a new record, which is not very good in a multi user environment, becuase there can be collision on duplicated numbers being applied.

    2) use an auto-incremented number controlled by the DB engine, becuase once the number has been used, there can be no duplicate situation.

    3) You keep the count record that is not data based related in a file, which is not good since only one user can have the file open at any given time and  again is not a good situation in a multi-user environment.

    I have always used option 2 and never had a problem doing it.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 16, 2019 4:17 PM
  • User475983607 posted

    I recommend at least 3 columns.  One for  PSE, year, and numeric series.  

    I need to do this in code-level, not in the sql database.

    I don't recommend do so unless the application has a single user.  Otherwise, you'll need to implement a wait/retry process when dirty data is detected.  Remember a web application is disconnected for SQL.  You can easily have several users with the same "Next Number".

    Generating the sequence in the database during an INSERT is by far the more reliable method.  A TRIGGER would work well.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, June 16, 2019 6:41 PM
  • User1520731567 posted

    Hi loraine26,

    I suggest you could query your max Id and set your series number based on rule in controller,

    For example:

     public ActionResult Create()
            {
                Employee emp = new Employee();
                //sort the employee and get the last insert employee.
                var maxId= db.XXX.OrderByDescending(c => c.ID).FirstOrDefault();
                if (lastemployee == null)
                {
                    emp.EmployeeID = "PSE-2019-000001";
                }
                else
                {
                    emp.EmployeeID = "PSE-"+DateTime.Now.Year.ToString()+"-"+(maxId.ID + 1).ToString("D6");
                }
                ...
                return View(emp);
            }

    In VIEW:

             ....
            @Html.HiddenFor(model=> model.EmployeeID)
    
    
            <div class="form-group">
                @Html.LabelFor(model => model.EmployeeName, htmlAttributes: new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.EmployeeName)
                    @Html.ValidationMessageFor(model => model.EmployeeName)
                </div>
            </div>

    ....

    In post action:

      [HttpPost]
            [ValidateAntiForgeryToken]
            public ActionResult Create([Bind(Include = "xxxx,xxxx...)] Model model)
            {
                if (ModelState.IsValid)
                {
                    db.Employees.Add(model);
                    db.SaveChanges();
                    return RedirectToAction("Index");
                }
    
                return View(model);
            }

    More details,you could refer to this reply:

    https://forums.asp.net/post/6167155.aspx

    Best Regards.

    Yuki Tao

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 17, 2019 9:59 AM
  • User1878001738 posted

    Hi All,

    This is what I have done.

    I created a table with a computed column, so every time a new record is created, there is a corresponding series number based on the identity id of the table. 

    I just need to check and test this if there are concurrent users, and it would not duplicate the series number upon saving concurrently.

    CREATE table [ir].[saved_pse_number_ref]
    (
    	id int identity(1,1) not null,
    	incident_report_id uniqueidentifier not null,
    	date_today datetime not null,
    	pse_number as ('PSE' + '-' + CAST(YEAR(date_today) as varchar(4)) + '-' + RIGHT('0000000' + CAST(id as varchar(7)), 7)) persisted
    )

    Thank you for all your replies and suggestions. It helps me a lot and I really appreciate it.

    Tuesday, June 18, 2019 1:31 AM
  • User1120430333 posted

    Once the number has be selected from the Identity count, it can't be selected again and plus 1 is added to the count. Even if you put the whole logic in a System.Transaction scope in getting the count number and persisting the data for the main record using the count, if the solution blows up and you do a rollback, the count number that was given by the Identity count is gone and it cannot be used again, becuase the Identity count doesn't rollback.

    I have used the technique you are using for counters for regional counts when inserting a record into the database using EF in an ASP.NET MVC solution. Every time a record was inserted into the database, I inserted a new record into the regional count table for  a given region that had a primary-key of ID using Identity. Ef return the ID (a count record ID) of the dummy count record inserted into the count table, and I populated the count number to a property in the main object that holds the assigned count and persisted the object to the database. I had four reginal counters that started at different count numbers a range of count numbers for each region based on specifications given by the client. It woeked like a charm in the multi user environment.  

    Tuesday, June 18, 2019 8:48 AM