[C #] implement serial number generator

for database applications, a proper serial number generator is very useful, with this function, we can conveniently assign values to the ID field (primary key) of the newly added record. Of course, we can also use the primary key of the Identity type. However, if there is a parent-child relationship record at the same time, therefore, assigning parent_id to a sub-record is inconvenient. We must first Insert the parent record into the database, then obtain the automatically generated ID from the database, and then assign a value to the parent_id field of the sub-record. undoubtedly, if we can assign values to the ID of the parent record directly, the problem will be much simpler.
however, for a distributed application, duplicate IDs often occur without careful consideration. we use the field number in existing products of our company to solve this problem, that is to say, assign each user a long enough range (such as 10000000) as the optional ID, the ID of each user using an independent interval generally does not cause problems, But as a perfectionist, we can't help but consider an extreme situation. What if the range overlaps? In addition, this allocation method is undoubtedly a great waste of ID resources. Is there a better solution?
a few days ago, I read Martin Fowler's masterpiece , which mentioned a good solution, even if the key table) A typical key table has two columns: name and the next valid ID (nextid ). if you use a unique database key, this table has only one record, for example, "DB, 1". If you use a unique table key, each database table has a row of data corresponding to it, for example: "users, 11", "roles, 31", etc. to obtain a new available ID, we only need to obtain the nextid value from a suitable row, and then store nextid + incrementby to the database, here, we add an incrementby each time to reduce database operations, avoid wasting valuable database connection resources, and reduce the possibility of conflict.
let's take a look at my location. net implementation (imitating the masterpiece of Martin Fowler):

Using System;
Using System. diagnostics;
Using System. Data;
Using System. Data. Common;

Namespace Myproject. Core
{
Public   Class Keygenerator
{
Private Members # Region Private Members

Private Idbconnection _ connection =   Null ;
Private   String _ Keyname;
Private   Int _ Incrementby;
Private   Long _ Nextid =   0 ;
Private   Long _ Maxid =   0 ;

# Endregion

Public Keygenerator (idbconnection Conn, String Keyname, Int Incrementby)
{
_ Connection=Conn;
_ Keyname=Keyname;
_ Incrementby=Incrementby;
}

Public   Long Nextkey
{
Get
{
If (_ Nextid = _ Maxid)
{
Reserveids ();
}
Return _ Nextid ++ ;
}
}

Private   Void Reserveids ()
{
Idbtransaction TX = _ Connection. begintransaction ();
Idbcommand cmd = _ Connection. createcommand ();
Cmd. Transaction = TX;
Try
{
Cmd. commandtext =   String . Format (
" Update keys set nextid = nextid + {0} Where name = \ '{1 }\' " ,
_ Incrementby, _ keyname );
Int Result = Cmd. executenonquery ();
Debug. Assert (Result =   1 , _ Keyname +   " Not exists " );
Cmd. commandtext =   String . Format ( " Select nextid from keys where name = \ '{0 }\' " ,
_ Keyname );
_ Maxid = Convert. toint64 (CMD. executescalar ());
_ Nextid = _ Maxid - _ Incrementby;
TX. Commit ();
}
Catch (Exception ex)
{
TX. rollback ();
ThrowEx;
}
Finally
{
Cmd. Dispose ();
}
}
}
}

The method is also very simple, as shown below: Oledbconnection Conn =   New Oledbconnection ();
Conn. connectionstring =
" Provider = sqloledb; Data Source = (local); initial catalog = GCS; trusted_connection = Yes " ;
Conn. open ();

Keygenerator gen =   New Keygenerator (Conn, " Users " , 10 );

For ( Int I =   0 ; I <   10 ; I ++ )
{
Console. writeline (gen. nextkey );
}

Conn. Close ();

Appendix: SQL statement used to create a key table

Create   Table   [ Keys ] (
[ Name ]   [ Varchar ] ( 32 ) Collate chinese_prc_ci_as Not   Null ,
[ Nextid ]   [ Int ]   Null ,
Primary   Key Clustered
(
[ Name ]
) On   [ Primary ]
) On   [ Primary ]