The best way to generate a new sequence number is to ulilize an auto-increment field in the database. There is one drawback though, this number will be only generated after the record was added to the database. There situations though, when this new invoice number needs to be displayed on the page before the record is actually saved.
The problem with this approach is that several users may open Invoice page at the same time and get the same number. To avoid this kind of collision, in our Invoice template we use a separate table with the latest invoice number. Here is the PHP code that we use there:
$rs = DB::Select("invoice_number");
$data = $rs->fetchAssoc();
$inv = $data['invnumber'] + 1;
$values['invoice_number'] = $inv;
DB::Exec("update invoice_number set invnumber=".$inv);This solution is 99.99% safe as it only takes a few milliseconds to read this number, increment it and write it back. However, if two users hit 'Add new invoice' as exactly rhe same time, milliseconds-wise, it may result in a duplicate invoice number. To make this solution 100% bulletproof we can involve LOCK/UNLOCK TABLES technique. This kind of solution is available in all modern databases, below is an example for MySQL.
DB:Exec("LOCK TABLE invoice_number WRITE, invoice_number AS i READ");
$rs = DB::Select("invoice_number");
$data = $rs->fetchAssoc();
$inv = $data['invnumber'] + 1;
$values['invoice_number'] = $inv;
DB::Exec("update invoice_number set invnumber=".$inv);
DB:Exec("UNLOCK TABLES");And here is the C# code that does the same:
dynamic inv = null;
DB.Exec("LOCK TABLE invoice_number WRITE, invoice_number AS i READ");
rs = XVar.Clone(DB.Select(new XVar("invoice_number")));
data = XVar.Clone(rs.fetchAssoc());
inv = XVar.Clone(data["invnumber"] + 1);
values.InitAndSetArrayItem(inv, "invoice_number");
DB.Exec((XVar)(MVCFunctions.Concat("update invoice_number set invnumber=", inv)));
DB.Exec("UNLOCK TABLES");