serial number

In our daily development process, we may use numbering functions, such as sales order number, purchase order number, log number, voucher number, etc. In order to ensure that the primary keys of only some tables are either self-increasing or GUID value, or generated by the Snowflake ID algorithm. This method can basically generate a unique ID, but if the flow ID is generated in a distributed environment, the above methods may not be very useful. If there are the following scenarios

1. The serial number of the workflow

The number of the workflow will usually be in the following format, such as 2022060200001-2022060299999. On the second day, the mantissa will be 00001 and start editing. One advantage of this numbering rule is that it is very intuitive to see which day it is applied for through the workflow number. The process, about how many running water codes there are in a day. So if we use the conventional numbering rules, it is actually more difficult to complete this requirement.

2. Purchase order number

For example, in the actual business requirements, the standard purchase order should be coded with the number 5000000000-5999999999, and the subcontract purchase order should be coded with the number 4000000000-4999999999.

3. XX business receipt number

When doing a business in the office, different numbers are used according to the type of business. For example, the serial number for financial receipts is SK10000000-SK99999999, and the expenditures are ZC10000000-ZC99999999

According to the above business scenarios, if it is difficult to use conventional numbering (to ensure that the distributed environment numbering is not repeated and coded according to the flow), then HiSqla more convenient solution is provided.

This version has not officially updated nuget, please download the source code if you need to use it

• HiSql source code (github) https://github.com/tansar/HiSql

• HiSql source code (gitee) https://gitee.com/tansar/HiSql

How to turn on numbering plan

  //如果需要使用编号那么一定要开启此功能
  HiSql.Global.SnroOn = true;
  //开启编号后进行初始化  
  sqlClient.CodeFirst.InstallHisql();//仅需执行一次 如果使用的低版本的HiSql 升级引用包手需要重新初始化安装

A table is generated after the initial installation is complete. Hi_SnroThis numbering configuration table

Introduction to Numbering Configuration

Hi_Snro Table details

PreTypePre-Number Type Configuration

is PreTypean enum class

Configure the data into the table according to the above configuration rules

Configuration example

Hi_SnroThe configuration can be configured in a configuration interface by yourself. The following is the configuration and preservation through the program.

  List<Hi_Snro> list = new List<Hi_Snro>();
  ///工作流编号配置
  ///按天产生流水号 如2205061000000-2205069999999 之间
  list.Add( new Hi_Snro { SNRO = "WFNO", SNUM = 1, IsSnow = false, SnowTick = 0, StartNum = "1000000", EndNum = "9999999", Length = 7, CurrNum = "1000000", IsNumber = true, PreType=PreType.Y2MD, FixPreChar="", IsHasPre = true, CacheSpace = 5, Descript = "工作流编号" });
  ///生成销售订单编码 每分钟从0开始编号 如20220602145800001-20220602145899999
  list.Add(new Hi_Snro{ SNRO = "SALENO", SNUM = 1, IsSnow = false, SnowTick = 0, StartNum = "10000", EndNum = "99999", Length = 5, CurrNum = "10000", IsNumber = true, PreType = PreType.YMDHm, FixPreChar = "", IsHasPre = true, CacheSpace = 10, Descript = "销售订单号流水" });  ///生成另外一种销售订单编码 年的是取后两位 按每秒顺序生成 如22060214581200001-22060214581299999
  list.Add(new Hi_Snro { SNRO = "SALENO", SNUM = 2, IsSnow = false, SnowTick = 0, StartNum = "10000", EndNum = "99999", Length = 5, CurrNum = "10000", IsNumber = true, PreType = PreType.Y2MDHms, FixPreChar = "", IsHasPre = true, CacheSpace = 10, Descript = "销售订单号流水" });
  ///通过雪花ID生成
  list.Add( new Hi_Snro { SNRO = "Order", SNUM = 1, IsSnow=true, SnowTick=145444, StartNum = "", EndNum = "",Length=7, CurrNum = "", IsNumber = true, IsHasPre = false, CacheSpace = 10, Descript = "订单号雪花ID" });
  //保存配置到表中
  sqlClient.Modi("Hi_Snro", list).ExecCommand();

Id generated code environment configuration

It is recommended to make SeriNumber a local static variable

  public static SeriNumber number = null;  //定义个全局变更的流水号对象

set up connection

  //sqlClient 为数据库连接对象
  number = new SeriNumber(sqlClient);

If the application is deployed in a distributed manner, be sure to enable the following code

  HiSql.Global.RedisOn = true;//开启redis缓存
  HiSql.Global.RedisOptions = new RedisOptions { Host = "172.16.80.178", PassWord = "qwe123", Port = 6379, CacheRegion = "HRM", Database = 2 };
  HiSql.Global.NumberOptions.MultiMode= true;
  //如果部署了分布式且也要生成雪花ID 一定要配置以下当前应用的ID 多个应用间只要不重复即可0-31之间
  HiSql.Global.NumberOptions.WorkId=1;

Through the above configuration, the distributed numbering environment configuration is completed, and the numbering test can be performed below.

number test

In order to test whether the number is repeated, I built a test record table here H_nlog. After the number is generated, the data in the table can be analyzed to see if there is any repeated data. (The test environment is SqlServer) The sql code is as follows

  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  CREATE TABLE [dbo].[H_nlog](
    [Nid] [int] IDENTITY(1,1) NOT NULL,
    [Numbers] [varchar](50) NULL,
    [CreateTime] [datetime] NULL,
    [CreateName] [nvarchar](50) NULL,
    [ModiTime] [datetime] NULL,
    [ModiName] [nvarchar](50) NULL,  CONSTRAINT [PK_H_nlog] PRIMARY KEY CLUSTERED 
  (
    [Nid] 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
  ALTER TABLE [dbo].[H_nlog] ADD  CONSTRAINT [DF_H_nlog_CreateTime]  DEFAULT (getdate()) FOR [CreateTime]
  GO
  ALTER TABLE [dbo].[H_nlog] ADD  CONSTRAINT [DF_H_nlog_CreateName]  DEFAULT ('') FOR [CreateName]
  GO
  ALTER TABLE [dbo].[H_nlog] ADD  CONSTRAINT [DF_H_nlog_ModiTime]  DEFAULT (getdate()) FOR [ModiTime]
  GO
  ALTER TABLE [dbo].[H_nlog] ADD  CONSTRAINT [DF_H_nlog_ModiName]  DEFAULT ('') FOR [ModiName]
  GO
  EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'H_nlog', @level2type=N'COLUMN',@level2name=N'CreateTime'
  GO
  EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'H_nlog', @level2type=N'COLUMN',@level2name=N'CreateName'
  GO
  EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'H_nlog', @level2type=N'COLUMN',@level2name=N'ModiTime'
  GO
  EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改人' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'H_nlog', @level2type=N'COLUMN',@level2name=N'ModiName'
  GO

Generate numbers according to the numbering sequence WFNOSubnumbers1

  List<object> lst = new List<object>();  for (int i = 0; i < 1000; i++)
  {
      var num = number.NewNumber("WFNO", 1);
      lst.Add(new { Numbers = num });
      Console.WriteLine(num);
  }
    sqlClient.Insert("H_nlog", lst).ExecCommand();

Test Results

Distributed multiple applications are enabled to generate numbers for the same numbering sequence at the same time, and no duplicate numbers will be generated.