Step by step introduction of development framework based on SqlSugar -- automatic assignment of GUID primary key in data access base class

When designing database tables, we often use string type or GUID type for primary key ID for convenience. This is very convenient for the migration of database table records, and sometimes the corresponding ID value can be obtained in advance when dealing with associated records. However, sometimes the assignment of ID is often ignored when inserting data records. In order to facilitate use or allow automatic assignment, we can automatically assign the guid primary key in the data access base class.

1. Processing of entity class primary key attribute

When we design the framework based on SqlSugar, the entity class defines a base class entity < T >, as shown in the following code.

    [Serializable]
    public abstract class Entity<TPrimaryKey> : IEntity<TPrimaryKey>
    {
        /// <summary>
        /// Unique primary key of entity class
        /// </summary>
        [SqlSugar.SugarColumn(IsPrimaryKey = true, ColumnDescription = "Primary key")]
        public virtual TPrimaryKey Id { get; set; }

Generally, you can extend string, integer and other types of entity classes.

The default Entity is defined as integer, as shown below. The self growing shaping primary key does not need to insert a value. It obtains the corresponding Id value when the record is written.

    [Serializable]
    public abstract class Entity : Entity<int>, IEntity
    {
        /// <summary>
        /// ID Self growth, primary key type
        /// </summary>
        [SqlSugar.SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
        public override int Id { get; set; }
    }

For the ID key of character type, the ID can be initialized in the constructor.

    /// <summary>
    /// Customer information
    /// Inherited from Entity,have Id key attribute 
    /// </summary>
    [SugarTable("T_Customer")]
    public class CustomerInfo : Entity<string>
    {
        /// <summary>
        /// Default constructor (those that need to initialize properties are processed here)
        /// </summary>
        public CustomerInfo()
        {
            this.Id = System.Guid.NewGuid().ToString();
            this.CreateTime = System.DateTime.Now;

        }

Or when we insert a new record into the data, we judge whether the primary key is empty, and then assign it or a unique GUID value.

Use GUID The advantage of newguid() is that it can obtain a unique GUID value, but the disadvantage is that the ID is out of order and there is no order. Sorting the ID is meaningless.

In order to solve this problem, we still introduce the rule of Abp VNext to generate an ordered GUID value. At the same time, when the database accesses the base class and inserts and updates records, judge whether the ID (primary key ID of GUID type or string type) is empty. If it is empty, assign an ordered GUID to it, which can solve the problem perfectly.

In this way, when we define the entity class, the ID value can be left blank without initialization, and the user can actively set the value or automatically process the assignment of the base class.

    /// <summary>
    /// Customer information
    /// Inherited from Entity,have Id key attribute 
    /// </summary>
    [SugarTable("T_Customer")]
    public class CustomerInfo : Entity<string>
    {
        /// <summary>
        /// Default constructor (those that need to initialize properties are processed here)
        /// </summary>
        public CustomerInfo()
        {
            this.CreateTime = System.DateTime.Now;
        }

 

2. The base class judges whether the ID is empty and assigns a value

For the ID value of GUID or string type, why set the ordered GUID, you can refer to the link below: https://github.com/abpframework/abp/blob/48c52625f4c4df007f04d5ac6368b07411aa7521/docs/zh-Hans/Guid-Generation.md

Generally, when we use SqlSugar to insert a new record, the following code is used

        /// <summary>
        /// create object
        /// </summary>
        /// <param name="input">Entity object</param>
        /// <returns></returns>
        public virtual async Task<bool> InsertAsync(TEntity input)
        {
            return await EntityDb.InsertAsync(input); 
        }

In order to judge whether the ID is empty, we need to judge the ID type, whether it is string type or GUID type. If it is empty, it will be assigned automatically. Therefore, we will make a judgment process before inserting, as shown in the following code.

        /// <summary>
        /// create object
        /// </summary>
        /// <param name="input">Entity object</param>
        /// <returns></returns>
        public virtual async Task<bool> InsertAsync(TEntity input)
        {
            SetIdForGuids(input);//If Id Empty, set ordered GUID value
            return await EntityDb.InsertAsync(input); 
        }

Where SetIdForGuids is a function that obtains the value of an ordered GUID.

        /// <summary>
        /// Set the primary key for the newly created entity object Id The values of are ordered GUID Value( GUID Type or string type (Trial)
        /// </summary>
        public virtual void SetIdForGuids(TEntity entity)
        {
            if (entity is IEntity<Guid> entityWithGuidId && entityWithGuidId.Id == Guid.Empty)
            {
                //default GUID type
                var guidType = SequentialGuidType.SequentialAsString;

                switch(this.dbContext.DbType) //According to different database types, the appropriate generation sequence mode is obtained
                {
                    case SqlSugar.DbType.SqlServer:
                        guidType = SequentialGuidType.SequentialAtEnd;
                        break;
                    case SqlSugar.DbType.MySql:
                    case SqlSugar.DbType.PostgreSQL:
                        guidType = SequentialGuidType.SequentialAsString;
                        break;
                    case SqlSugar.DbType.Oracle:
                        guidType = SequentialGuidType.SequentialAsBinary;
                        break;
                }

                var guid = GetSequentialGuid(guidType);
                entityWithGuidId.Id = guid;
            }
            else if (entity is IEntity<string> entityWithStringId && string.IsNullOrWhiteSpace(entityWithStringId.Id))
            {
                var guid = GetSequentialGuid(SequentialGuidType.SequentialAsString);
                entityWithStringId.Id = guid.ToString();
            }
        }

Different GUID values are constructed according to different database feature types. If it is the ID of a string, we uniformly adopt the method of "sequentialstring", which also supports the conventional sorting of strings. In this way, we can not only obtain a non repeated GUID value, but also sort the ID, which is sorted according to the sequence.

        /// <summary>
        /// Gets a sequence that can be generated GUID
        /// </summary>
        /// <returns></returns>
        protected Guid GetSequentialGuid(SequentialGuidType sequentialGuidType)
        {//Created using the specified sequence (generates a continuous sequence) GUID)
            //Refer to the link for details:(https://github.com/abpframework/abp/blob/48c52625f4c4df007f04d5ac6368b07411aa7521/docs/zh-Hans/Guid-Generation.md)
            var options = new AbpSequentialGuidGeneratorOptions()
            {
                DefaultSequentialGuidType = sequentialGuidType
                //SequentialAtEnd(default) be used for SQL Server.
                //SequentialAsString be used for MySQL and PostgreSQL.
                //SequentialAsBinary be used for Oracle.
            };
            return new SequentialGuidGenerator(options).Create();
        }

Add several records of dictionary type (string ID) to test.

 

You can see that the type prefix part of the ID is the same, and the subsequent changes are sorted in the positive order of the ID, which is processed according to the writing time order.

Series of articles:

<Step by step introduction to the development framework based on SqlSugar (1) -- Design and use of basic classes of the framework>

<Step by step introduction of development framework based on SqlSugar (2) -- Query Processing Based on intermediate table>

<Step by step introduction of development framework based on SqlSugar (3) -- integrated development of code generation tool Database2Sharp>

<Step by step introduction of development framework based on SqlSugar (4) -- automatic assignment of GUID primary key in data access base class>

 

Posted by Stuie_b on Tue, 17 May 2022 05:19:14 +0300