C#: Auto-incrementing alphanumeric invoice number

Article
1/17/2024

Introduction

When working with applications, which deal with orders and invoices there, needs to be a method to track them, which may be a numeric value or an alphanumeric value. Presented are two simple methods which alphanumeric values to represent an order/invoice value stored in SQL-Server using Entity Framework Core in a Windows Form application.

Rather than creating sequences or triggers in a database the alphanumeric value generation are based on C# code in tangent with reading values from a table which has one record for each customer e.g. John’s carpet cleaners has AA prefix, Annie’s shoes has BB prefix. A fallacy with this method, two orders placed for the same customer at one time, which in most cases when taking an order a sale representative is responsible for John’s carpet cleaners and another sale representative would not work with John’s carpet cleaners etc. Otherwise, use a trigger or computed column.

For a variation of this see Entity Framework Core HasValueGenerator which works on customer alphanumeric value, here it's for order numbers without using HasValueGenerator which may be easier for new developers to Entity Framework Core (and will work with Entity Framework classic which does not have HasValueGenerator).

Schema for implementing alphanumeric number

CustomerSequence table contains one row per customer linked to Customers table by primary key. The column SequencePreFix in this case is two characters which could be one or more than two which uniquely identifies a customer, CurrentSequenceValue is a numeric value incremented using C# code using Entity Framework Core (conventional data access methods can be used in place of EF Core)

Sequence generation

A class project (add this project to a Visual Studio solution and reference in a forms or class project) which has one method, IncrementAlphaNumericValue(string value). For generating a customer invoice the following method reads Customer and the related table CustomerSequence to obtain the prefix and current sequence value. For the first time current sequence will be null and assigned 0001, otherwise the current sequence value is passed to IncrementAlphaNumericValue method to increment.

As presented if a mocked version of AB0001 is passed and iterated 200,000 times the last value would be HT001 which means there needs to be logic to reset the value once it reaches AB999 (see remarks in code to implement). This can be done by simply asserting if the value is AB999 similar to how an assertion is done for the first usage which is against a null value. The other option is to reset back to null annually if orders are less than 999 while performing assertion for AB999 would be a better solution. Of course another idea is to not have a prefix for each customer and use the alphanumeric value from IncrementAlphaNumericValue for all customers.

Walk through generating order numbers

Keeping code simple to learn from, a ListBox shows customers read from the database using the following method in this class..

/// <summary>
/// Get all customer names and id for loading into in this case a ListBox
/// </summary>
/// <returns></returns>
public static  async Task<List<CustomerItem>> GetCustomers()
{
  List<CustomerItem> result = new  List<CustomerItem>();
 
  using (var context = new AccountContext())
  {
    await Task.Run(async () =>
    {
      result = await context.Customers.AsNoTracking().Select(customer => new  CustomerItem
      {
        Id = customer.CustomerIdentifier,
        Name = customer.CompanyName
      }).ToListAsync();
    });
  }
 
  return result;
}

Form code to load the ListBox.

/// <summary>
/// Load customers name and primary key
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private async void MainForm_Shown(object sender, EventArgs e)
{
  var items = await DataOperations.GetCustomers();
  CustomersListBox.DataSource = items;
}

Each time the selected customer item changes in the ListBox the current sequence value is presented in a TextBox.

/// <summary>
/// Show current customer's sequence value
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void  listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
  if (CustomersListBox.SelectedItem == null) return;
  var currentCustomer = (CustomerItem)CustomersListBox.SelectedItem;
 
  SequenceTextBox.Text = DataOperations.GetCustomerCurrentSequenceValue(currentCustomer.Id);
}

Adding a new order with a incrementing sequence via button click. In a real application there would be an entire process to add items to an order which has no baring on sequence generation.

private void  NewOrderButton_Click(object sender, EventArgs e)
{
 
  if (CustomersListBox.SelectedItem == null) return;
 
  var currentCustomer = (CustomerItem)CustomersListBox.SelectedItem;
 
  if (Question($"Add new order for '{currentCustomer.Name}'"))
  {
    /*
     * In a real app this may be displayed in a DataGridView,
     * ListView etc.
     */
    var order = DataOperations.NewOrder(currentCustomer.Id);
 
    SequenceTextBox.Text = DataOperations
      .GetCustomerCurrentSequenceValue(currentCustomer.Id);
  }
}

To create a new order with a incrementing sequence the following method creates the order calling GetCustomerNextSequenceValue passing in the current customer identifier.

/// <summary>
/// Add a new order for customer
/// </summary>
/// <param name="customerIdentifier">Existing customer</param>
/// <remarks>
/// For a real application return the order or simply the order key
/// </remarks>
public static  Order NewOrder(int  customerIdentifier)
{
  using (var context = new AccountContext())
  {
    var order = new  Order()
    {
      CustomerIdentifier = customerIdentifier,
      InvoiceNumber = GetCustomerNextSequenceValue(customerIdentifier),
      OrderDate = DateTime.Now
    };
 
    context.Orders.Add(order);
    context.SaveChanges();
 
    return order;
  }
}

This method used in NewOrder method

Retrieves the current customer by primary key along with the associated record in the sequence table.
Asserts there is a current sequence value
1. Value is null, assign a default value
2. Value is not null, pass the current value to IncrementAlphaNumericValue method
Assign value to current sequence record
Save changes back to the database.

public static  string GetCustomerNextSequenceValue(int customerIdentifier)
{
  using (var context = new AccountContext())
  {
    Customers result = context.Customers
      .Include(customer => 
        customer.CustomerSequence)
      .FirstOrDefault(customer => 
        customer.CustomerIdentifier == customerIdentifier);
 
    CustomerSequence customerSequence = result?.CustomerSequence.FirstOrDefault();
    var sequenceValue = "";
 
 
    if (string.IsNullOrWhiteSpace(customerSequence?.CurrentSequenceValue))
    {
      sequenceValue = $"{customerSequence?.SequencePreFix}0001";
    }
    else
    {
      sequenceValue = StringHelpers
        .IncrementAlphaNumericValue($"{customerSequence.CurrentSequenceValue}");
    }
 
 
    customerSequence.CurrentSequenceValue = sequenceValue;
    context.SaveChanges();
    return sequenceValue;
  }
 
}

Summary

Code has been presented to create alphanumeric sequence values used in this case for order numbers where each customer has a unique prefix. The underlying code responsible for generating sequence values can be used without a unique prefix in which case given a value such as A0001 can increment the character and numeric value e.g. AZ999 for instance.

01/08/2023 - see a slimmed down version using SqlClient and EF Core 7 here

Source code

Download or clone the following GitHub repository followed by creating the databases and tables using the two scripts provided at the root of the Visual Studio solution.

Share via