Auto-increment primary key ID Design

I. Introduction

When using the ORM framework, a table must have a primary key. If there is no primary key, there is no way to update a record uniquely. It is easy to set a self-increasing primary key in the SQL Server database and MySQL database. It is complicated to set a self-increasing primary key in the Oracle database. This article does not discuss the auto-growth of a single table in the database. It discusses the design of a unique ID for auto-Growth of multiple tables.
What should I do if you have a requirement for a unique ID for the auto-Growth of multiple tables?

 

II. Introduction of guid

Some people may think of The GUID in. net. First, test the guid.

 public void GuidTest()        {            string guid = Guid.NewGuid().ToString();            Console.WriteLine(guid);            Console.WriteLine(guid.Length);        }

Guid Test

The output result is as follows:

C8eb1c81-eafa-423b-a1bf-15fd5df829c4
36

We can see that this GUID is a string with a length of 36 characters. It is really long enough. If you want to reduce the number of digits when using it, you can remove the horizontal bar with four digits missing, there are 32 digits left, but it is still quite long.
From my observations, if someone designs the primary key of the database as the guid, it is actually a second product :)
There are three reasons: 1) It is too long to occupy space in the database.
2) inconvenient sorting
3) The string retrieval efficiency is less than the integer
It seems that there are too many guids.
 

Iii. Self-increasing ID implementation for standalone Edition

For the unique ID of auto-increment under multiple tables, the first thought is time. You can solve the problem by formatting the time into an integer. To reduce the number of digits, remove 20 of the year of the date, and take two digits in milliseconds, 15 digits in total.
To prevent duplication, if the ID generated by the program is later than the ID of the current time, it will be saved in XML. The next time the program starts, check the saved ID in XML and the ID in the current time. This is done to prevent the program from restarting and generating duplicate IDs. Of course, to ensure multi-threaded security, we can easily lock it. The Code is as follows:

Public class idgenerator {Private Static string idxml = "Id. XML "; Private Static long current = 0; Private Static object OBJ = new object (); Private Static string format =" yymmddhhmmssfff "; // 15-bit static idgenerator () {var xdoc = xdocument. load (idxml); long last = 0; long. tryparse (xdoc. root. value, out last); // check whether the last generated ID is greater than the ID of the current time at each startup long now = long. parse (datetime. now. tostring (Format); If (last> now) {current = last;} else {current = now;} public static long GETID () {lock (OBJ) {current + = 1; // if the current generated ID is greater than the ID of the current time, it is saved if (current> long. parse (datetime. now. tostring (Format) {var xdoc = xdocument. load (idxml); xdoc. root. value = current. tostring (); xdoc. save (idxml) ;}return current ;}}}

Idgenerator

Iv. Auto-increment ID implementation in distributed mode
In a real environment, a project may have multiple websites or applications using this self-increasing ID. In this case, we need to consider the distributed situation. Here we implement distributed use of the WCF technology, the WCF implementation communicates with the server locally through the contract. After abstraction, calling the server code is like calling the local code.
First, define the contract:

namespace WCFServiceTest{    [ServiceContract]    public interface IIdGenContract    {        [OperationContract]        long GetIdByWCF();    }}

Iidgencontract

This interface has a method getidbywcf, which must be implemented by the server.

namespace WCFServiceTest{    [ServiceBehavior(IncludeExceptionDetailInFaults = true, InstanceContextMode = InstanceContextMode.Single, ConcurrencyMode = ConcurrencyMode.Multiple, MaxItemsInObjectGraph = Int32.MaxValue)]    public class IdGenService : IIdGenContract    {        public static IdGenService Instance = new IdGenService();        public long GetIdByWCF()        {            return IdGenerateHelper.IdGenerator.GetId();        }    }}

Idgenservice

The next step is to enable this service.

Namespace wcfservicetest {class program {static void main (string [] ARGs) {servicehost host = new servicehost (idgenservice. instance); host. open (); console. writeline (typeof (idgenservice) + "opened"); console. writeline ("service address:" + host. description. endpoints [0]. listenuri); console. read ();}}}

Program

The configuration file is required. If the website is Web. config and the desktop program is app. config, the configuration file is detailed in the Demo code below.
The server is complete. The following is the code of the client to call the service. This interface must also be implemented when the client communicates with the server through a contract (interface.

namespace ConsoleApplication2{    public class IdService : ClientBase<IIdGenContract>, IIdGenContract    {        public long GetIdByWCF()        {            return base.Channel.GetIdByWCF();        }    }}

Clientidservice

The next step is to test the code.

Namespace consoleapplication2 {// <summary> // generate the auto-increment ID by calling the WCF Service /// </Summary> class program {static void main (string [] ARGs) {long id = new clientidservice (). getidbywcf (); console. writeline (ID); console. read ();}}}

Program

 

Iv. End
Execute everything as planned and output a unique ID. This complete demo is downloaded.

Auto-increment primary key ID Design