Transaction numbers and invoice numbers that are alpha numeric are commonly used and in this code sample/article a method to produce alpha numeric is shown along with using a computed column.
Code to produce alpha numeric can be used in any type of project while the computed column is specific to SQL-Server.
Method for incrementing
This method accepts a string like AQW-23-10 and return AQW-23-11 which incremented the last series of numbers
public class Helpers
{
/// <summary>
/// Given a string which ends with a number, increment the number by 1
/// </summary>
/// <param name="sender">string ending with a number</param>
/// <returns>string with ending number incremented by 1</returns>
public static string NextValue(string sender)
{
string value = Regex.Match(sender, "[0-9]+$").Value;
return sender[..^value.Length] + (long.Parse(value) + 1)
.ToString().PadLeft(value.Length, '0');
}
}
The following overload accepts an int value to increment by rather than increment by 1 as in the above method.
public static string NextValue(string sender, int incrementBy)
{
string value = Regex.Match(sender, "[0-9]+$").Value;
return sender[..^value.Length] + (long.Parse(value) + incrementBy)
.ToString().PadLeft(value.Length, '0');
}
Example
Real life examples, taking an order at a restaurant where the order number consist of the waiter’s identifier, transaction date/time, a transaction number and an incrementing value.
Perhaps the model might be
public class Transaction
{
public string UserIdentifier { get; set; }
public DateTime DateTime { get; set; }
public int Id { get; set; }
public int RegisterNumber { get; set; }
public override string ToString() => $"{UserIdentifier},{RegisterNumber},{Id}";
}
By having unique segments like waiter’s identifier there is no chance of issues in the backend database.
The same would be true for other examples like a person calls in an order to a store, the person taking the order has a unique identifier.
EF Core
In this example the following tables are used
| Table | Description | Note |
|---|---|---|
| Customers | Has customer details | |
| CustomerSequence | Has one record per customer to store thier sequence and a prefix to compose the final sequence | AA1 first sequence for first customer |
| 🔹 | I kept the prefix short but can be whatever you want | |
| Ordrers | Customer orders |
Here SequencePreFix is a unique string value to represent a specific customer and CurrentSequenceValue is the sequence, combine both together to produce an, in this case a invoice number for an order.
public static bool EntityFrameworkExample2(int customerId)
{
using var context = new Context();
// Get customer to add a new order
var customer = context.CustomerSequence.FirstOrDefault(x => x.CustomerIdentifier == customerId);
if (customer is not null)
{
var prefix = customer.SequencePreFix;
var sequenceValue = customer.CurrentSequenceValue;
/*
* If this is the first order for a customer start the sequence, otherwise increment
* the sequence
*/
sequenceValue = string.IsNullOrWhiteSpace(sequenceValue) ?
$"{prefix}{Helpers.NextValue("0")}" :
Helpers.NextValue(sequenceValue);
// update the sequence
customer.CurrentSequenceValue = sequenceValue;
// add a new order
Orders order = new() { CustomerIdentifier = customer.Id, InvoiceNumber = sequenceValue, OrderDate = DateTime.Now };
context.Orders.Add(order);
return context.SaveChanges() == 2;
}
else
{
return false;
}
}
Example code to binary file
The model used, a simple invoice which uses protobuf-net 1 NuGet package to store information in a binary file.
[ProtoContract]
public class Invoice
{
[ProtoMember(1)]
public int Id { get; set; }
[ProtoMember(2)]
public string Number { get; set; }
}
In the Operation.cs class for the first use we mock-up data with various formats to show the NextValue method works on different formats.
public static List<Invoice> Invoices => new()
{
new() { Id = 1, Number = "F1124" },
new() { Id = 2, Number = "1278-120"},
new() { Id = 3, Number = "3999/IKL/VII/21"},
new() { Id = 4, Number = "0001"},
new() { Id = 5, Number = "AA0001"},
new() { Id = 6, Number = "BB0200"},
new() { Id = 7, Number = $"BB-{Now.Year}-{Now.Month:D2}-0200"},
// id, register number, transaction date/time, transaction number
new() { Id = 8, Number = $"KP,22,{Now:MM/dd/yyyy hh:mm},01"},
};
Then to increment the value this is done in DataOperations.Save() method.
public static void Save(List<Invoice> list)
{
for (int index = 0; index < list.Count; index++)
{
var newValue = Helpers.NextValue(list[index].Number);
list[index].Number = newValue;
}
SerializeInvoices(list);
}
In Program class (under the Classes folder), if the binary file does not create then create the file with data from above.
👉uncomment Operations.Reset() to delete the binary file to start over (or simply delete the file in Explorer)
[ModuleInitializer]
public static void Init()
{
// use to reset invoices back to original state
//Operations.Reset();
if (!File.Exists(Operations.FileName))
{
Operations.CreateReadInvoice();
}
}
Increment with a SQL-Server computed column
The following example is a start for incrementing a sequence in table via computed column.
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE dbo.SampleComputed
(
Id NUMERIC(18, 0) IDENTITY(1, 1) NOT NULL,
ComputedIdentifier AS CASE
WHEN Id < 9999 THEN
'A' + RIGHT('000' + CONVERT(VARCHAR, Id, 10), 4)
ELSE
'A' + CONVERT(VARCHAR, Id, 10)
END PERSISTED,
CustomerName NVARCHAR(50) NULL,
CONSTRAINT PK_SampleComputed
PRIMARY KEY CLUSTERED (Id ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY];
GO
For more on computed columns see the following repository.
Note
What has been presented has been intentionally kept'd basic so that the reader can use as is or modify to suit their specific needs.
Source code
Clone the following GitHub repository
Summary
Using NextValue method with a properly thought out schema for data storage provide you with auto-incrementing alpha numeric values.
If there is a need for performing this with more assertions check out my Microsoft TechNet article Auto-incrementing alphanumeric invoice number with source code working with Entity Framework Core,
-
protobuf-net is a contract based serializer for .NET code, that happens to write data in the "protocol buffers" serialization format engineered by Google. The API, however, is very different to Google's, and follows typical .NET patterns (it is broadly comparable, in usage, to XmlSerializer, DataContractSerializer, etc). It should work for most .NET languages that write standard types and can use attributes. ↩
Top comments (4)
How would you handle concurrency scenarios where you are pressed to generate multiple ID's that use the same sequence? As it is shown here, I see a problem with this: If the sequence is not centrally located, and anyone can give you a number to increment, the code can certainly encounter collisions.
Great question, as stated at the end of the post I kept it basic which excludes concurrency violations.
When working with EF Core there is the ConcurrencyCheck attribute with the following code decisions can be made
github.com/karenpayneoregon/ef-cor...
For SqlClient
learn.microsoft.com/en-us/sql/conn...
And we can keep going...
Greate article
But for many reasons maybe fall in more than one issue for that reason I implement the twitter's algorithm named SnowflakId in C# you can read see the code from my repo here github.com/Shoogn/SnowflakeId or you can install it using NuGet package: Hussien.SnowflakId --Version 2.0.0
This library mange all things that related to create a sequence Id and get the datetime from id.
Your library does not auto-increment alpha-numeric values which is the whole point of this article.