Step by step introduction of development framework based on SqlSugar -- Design and use of framework basic classes

In the actual project development, we may encounter various project environments. Some projects need a large and comprehensive overall framework to support the development, and some small and medium-sized projects need some simple and convenient system frameworks for flexible development. At present, VPS and ABPs focus on the integration of the whole framework, but they can focus on the integration of the whole framework and ABP, which is similar to the whole framework; ABP VNext is based on the micro service architecture, and each module is developed independently. It can be integrated in one project or published separately as a micro service, and communicated uniformly through gateway processing. Whether ABP or ABP VNext framework, it is a collection NET CORE is an integration of many technologies in the field, and the basic class design is complex and has many relationships. Therefore, there is a certain threshold for development and learning, and there is a certain difficulty in the application of small and medium-sized projects. This series of essays introduces that the bottom layer uses SqlSugar as the ORM data access module to design a simple and convenient framework. This article introduces some framework contents from the foundation, and refers to some class library processing in ABP/ABP VNext to carry the processing details of similar conditional paging information, query condition processing and so on.

1. Architecture design based on SqlSugar development framework

The main design module scenarios are as follows.

 

In order to avoid dispersing dozens of projects like ABP VNext framework, we try our best to aggregate the content into one project.

1) Some of the commonly used class libraries and the base classes of the SqlSugar framework are placed in the common module of the framework.

2) The basic interfaces and general components related to Winform development are placed in the basic Winform interface library BaseUIDx project.

3) The basic core data module SugarProjectCore is mainly a project to develop the data processing and business logic required by the business. For convenience, we distinguish three directories: Interface, modal and Service to place different contents. Modal is the mapping entity of SqlSugar, Interface is to define the access Interface, and Service is to provide specific data operation implementation. Some of the framework base classes and Interface definitions in Service are also unified in the public class library.

4) WinForm Application module is mainly the WinForm interface application developed for business. For convenience, WinForm development will also put some basic components and base classes in the WinForm special interface library of BaseUIDx.

5) The web API project is based on Net core 6 project development, realize the release of relevant controller APIs by calling SugarProjectCore, and integrate Swagger release interface for other front-end interface applications to call.

6) The pure front-end can call the interface of the Web API through the API. The pure front-end module can include vue3 & element projects, EelectronJS based applications, publishing cross platform browser based application interfaces, and other apps or applets that integrate the Web API to process or display business data.

For example, back-end development can be managed in VS2022, including Winform project and Web API project.

Winform interface, we can use based on net Framework development or Net core 6, because our sugar project core project adopts net Standard mode development, compatible with both. Here, the permission module is used for demonstration and integration.

 

For pure front-end projects, we can manage and develop projects based on tools such as VSCode or {HBuilderX.

 

2. Definition and processing of framework basic classes

When developing an easy-to-use framework, the main purpose is to reduce code development, improve the universality of the interface through base classes and generic constraints, and improve the development efficiency of standard projects by combining code generation tools.

So how do we encapsulate these interfaces when we implement routine operations such as addition, deletion, modification and query of conventional data based on the ORM processing of SqlSugar.

For example, we have a simple customer information table, as shown below.

Then the SqlSugar entity class it generates is as follows.

    /// <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;
        }

        #region Property Members

        /// <summary>
        /// full name
        /// </summary>
        public virtual string Name { get; set; }

        /// <summary>
        /// Age
        /// </summary>
        public virtual int Age { get; set; }

        /// <summary>
        /// Founder
        /// </summary>
        public virtual string Creator { get; set; }

        /// <summary>
        /// Creation time
        /// </summary>
        public virtual DateTime CreateTime { get; set; }

        #endregion
    }

Among them, entity < string > is that we define a base class entity object as needed, which is mainly to define an Id attribute for processing. After all, for the processing of general table objects, SqlSugar needs the primary key definition of Id (non intermediate table processing).

    [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; }
    }

While ienity < T > defines an interface

    public interface IEntity<TPrimaryKey>
    {
        /// <summary>
        /// Unique primary key of entity class
        /// </summary>
        TPrimaryKey Id { get; set; }
    }

The above is the processing of entity classes. Generally, in order to query information, we often pass in some conditions for processing, so we need to define a general paging query object for us to accurately process the conditions.

Generate an object class with * * * PageDto, as shown below.

    /// <summary>
    /// It is used for paging query according to conditions, DTO object
    /// </summary>
    public class CustomerPagedDto : PagedAndSortedInputDto, IPagedAndSortedResultRequest
    {
        /// <summary>
        /// Default constructor 
        /// </summary>
        public CustomerPagedDto() : base() { }

        /// <summary>
        /// Parameterized constructor
        /// </summary>
        /// <param name="skipCount">Number of skipped</param>
        /// <param name="resultCount">Maximum number of result sets</param>
        public CustomerPagedDto(int skipCount, int resultCount) : base(skipCount, resultCount)
        {
        }

        /// <summary>
        /// Initialization using paging information SkipCount and MaxResultCount
        /// </summary>
        /// <param name="pagerInfo">Paging information</param>
        public CustomerPagedDto(PagerInfo pagerInfo) : base(pagerInfo)
        {
        }

        #region Property Members

        /// <summary>
        /// Of objects not included ID,Used to exclude corresponding records during query
        /// </summary>
        public virtual string ExcludeId { get; set; }

        /// <summary>
        /// full name
        /// </summary>
        public virtual string Name { get; set; }

        /// <summary>
        /// Age-start
        /// </summary>
        public virtual int? AgeStart { get; set; }
        /// <summary>
        /// Age-end
        /// </summary>
        public virtual int? AgeEnd { get; set; }

        /// <summary>
        /// Creation time-start
        /// </summary>
        public DateTime? CreateTimeStart { get; set; }
        /// <summary>
        /// Creation time-end
        /// </summary>
        public DateTime? CreateTimeEnd { get; set; }

        #endregion
    }

Pagedandsortedinputdto and ipagedandsortedresultrequest refer to the processing methods from ABP/ABP VNext, so that we can facilitate the query processing operation of data access base class.

Next, we define a base class MyCrudService and pass the Relevant generic constraints, as shown below

    /// <summary>
    /// be based on SqlSugar Base class object for database access operations
    /// </summary>
    /// <typeparam name="TEntity">Define mapped entity classes</typeparam>
    /// <typeparam name="TKey">Type of primary key, such as int,string etc.</typeparam>
    /// <typeparam name="TGetListInput">Or the condition object of paging information</typeparam>
    public abstract class MyCrudService<TEntity, TKey, TGetListInput> : 
        IMyCrudService<TEntity, TKey, TGetListInput>
        where TEntity : class, IEntity<TKey>, new()
        where TGetListInput : IPagedAndSortedResultRequest

Let's ignore the implementation details of the base class interface first. Let's see how to use MyCrudService and IMyCrudService.

First, we define an application layer interface ICustomerService, as shown below.

    /// <summary>
    /// Customer information service interface
    /// </summary>
    public interface ICustomerService : IMyCrudService<CustomerInfo, string, CustomerPagedDto>, ITransientDependency
    {

    }

Then implement its interface in CustomerService.

    /// <summary>
    /// Application layer service interface implementation
    /// </summary>
    public class CustomerService : MyCrudService<CustomerInfo, string, CustomerPagedDto>, ICustomerService

In this way, the interface of a specific Customer is defined in ICustomer, and the standard interface can directly call the base class.

The base class MyCrudService provides two important interfaces for subclasses to be rewritten to facilitate accurate condition processing and sorting, as shown in the following code.

    /// <summary>
    /// be based on SqlSugar Base class object for database access operations
    /// </summary>
    /// <typeparam name="TEntity">Define mapped entity classes</typeparam>
    /// <typeparam name="TKey">Type of primary key, such as int,string etc.</typeparam>
    /// <typeparam name="TGetListInput">Or the condition object of paging information</typeparam>
    public abstract class MyCrudService<TEntity, TKey, TGetListInput> : 
        IMyCrudService<TEntity, TKey, TGetListInput>
        where TEntity : class, IEntity<TKey>, new()
        where TGetListInput : IPagedAndSortedResultRequest
    {
        /// <summary>
        /// The subclass is left to implement the processing of filtering conditions
        /// </summary>
        /// <returns></returns>
        protected virtual ISugarQueryable<TEntity> CreateFilteredQueryAsync(TGetListInput input)
        {
            return EntityDb.AsQueryable();
        }
        /// <summary>
        /// Default sort, by ID Sort
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        protected virtual ISugarQueryable<TEntity> ApplyDefaultSorting(ISugarQueryable<TEntity> query)
        {
            if (typeof(TEntity).IsAssignableTo<IEntity<TKey>>())
            {
                return query.OrderBy(e => e.Id);
            }
            else
            {
                return query.OrderBy("Id");
            }
        }        
    }

For Customer specific business objects, we need to implement specific query details and sorting conditions. After all, when our parent class does not have constraints to determine the attributes of the entity class, it is most appropriate to leave these to the child class.

    /// <summary>
    /// Application layer service interface implementation
    /// </summary>
    public class CustomerService : MyCrudService<CustomerInfo, string, CustomerPagedDto>, ICustomerService
    {
        /// <summary>
        /// Custom condition processing
        /// </summary>
        /// <param name="input">query criteria Dto</param>
        /// <returns></returns>
        protected override ISugarQueryable<CustomerInfo> CreateFilteredQueryAsync(CustomerPagedDto input)
        {
            var query = base.CreateFilteredQueryAsync(input);

            query = query
                .WhereIF(!input.ExcludeId.IsNullOrWhiteSpace(), t => t.Id != input.ExcludeId) //Exclude ID
                .WhereIF(!input.Name.IsNullOrWhiteSpace(), t => t.Name.Contains(input.Name)) //If exact matching is required, use Equals
                                                                                             //Age range query
                .WhereIF(input.AgeStart.HasValue, s => s.Age >= input.AgeStart.Value)
                .WhereIF(input.AgeEnd.HasValue, s => s.Age <= input.AgeEnd.Value)

                //Create date range query
                .WhereIF(input.CreateTimeStart.HasValue, s => s.CreateTime >= input.CreateTimeStart.Value)
                .WhereIF(input.CreateTimeEnd.HasValue, s => s.CreateTime <= input.CreateTimeEnd.Value)
                ;

            return query;
        }

        /// <summary>
        /// Custom sorting processing
        /// </summary>
        /// <param name="query">Searchable LINQ</param>
        /// <returns></returns>
        protected override ISugarQueryable<CustomerInfo> ApplyDefaultSorting(ISugarQueryable<CustomerInfo> query)
        {
            return query.OrderBy(t => t.CreateTime, OrderByType.Desc);

            //Sort by first field and then by second field
            //return base.ApplySorting(query, input).OrderBy(s=>s.Customer_ID).OrderBy(s => s.Seq);
        }
    }

Through the precise condition processing of CreateFilteredQueryAsync, we can clarify the query condition processing of entity classes. Therefore, for customerpageddo, it can be processed by the base class of the client and the service back end.

For example, the paging condition query function GetListAsync of the base class is processed according to this. Its implementation code is as follows.

        /// <summary>
        /// Get list by criteria
        /// </summary>
        /// <param name="input">Paging query criteria</param>
        /// <returns></returns>
        public virtual async Task<PagedResultDto<TEntity>> GetListAsync(TGetListInput input)
        {
            var query = CreateFilteredQueryAsync(input);
            var totalCount = await query.CountAsync();

            query = ApplySorting(query, input);
            query = ApplyPaging(query, input);

            var list = await query.ToListAsync();

            return new PagedResultDto<TEntity>(
               totalCount,
               list
           );
        }

The # ApplySorting is to decide whether to select the default sorting implemented by subclasses according to conditions.

        /// <summary>
        /// Record sorting processing
        /// </summary>
        /// <returns></returns>
        protected virtual ISugarQueryable<TEntity> ApplySorting(ISugarQueryable<TEntity> query, TGetListInput input)
        {
            //Try to sort query if available
            if (input is ISortedResultRequest sortInput)
            {
                if (!sortInput.Sorting.IsNullOrWhiteSpace())
                {
                    return query.OrderBy(sortInput.Sorting);
                }
            }

            //IQueryable.Task requires sorting, so we should sort if Take will be used.
            if (input is ILimitedResultRequest)
            {
                return ApplyDefaultSorting(query);
            }

            //No sorting
            return query;
        }

For obtaining a single object, we generally provide an ID primary key.

        /// <summary>
        /// according to ID Get single object
        /// </summary>
        /// <param name="id">Primary key ID</param>
        /// <returns></returns>
        public virtual async Task<TEntity> GetAsync(TKey id)
        {
            return await EntityDb.GetByIdAsync(id);
        }

It can also be processed according to the user's Express condition. We define many Express condition processing in the base class to facilitate the subclass to call the condition processing. For example, for deletion, you can specify either ID or condition.

        /// <summary>
        /// Delete assignment ID Object of
        /// </summary>
        /// <param name="id">record ID</param>
        /// <returns></returns>
        public virtual async Task<bool> DeleteAsync(TKey id)
        {
            return await EntityDb.DeleteByIdAsync(id);
        }
/// <summary>
        /// Deletes the collection based on the specified criteria
        /// </summary>
        /// <param name="input">Expression condition</param>
        /// <returns></returns>
        public virtual async Task<bool> DeleteAsync(Expression<Func<TEntity, bool>> input)
        {
            var result = await EntityDb.DeleteAsync(input);
            return result;
        }

If it is judged whether it exists, it is handled in the same way

        /// <summary>
        /// Judge whether there are records with specified conditions
        /// </summary>
        /// <param name="id">ID Primary key</param>
        /// <returns></returns>
        public virtual async Task<bool> IsExistAsync(TKey id)
        {
            var info = await EntityDb.GetByIdAsync(id);
            var result = (info != null);
            return result;
        }

        /// <summary>
        /// Judge whether there are records with specified conditions
        /// </summary>
        /// <param name="input">Expression condition</param>
        /// <returns></returns>
        public virtual async Task<bool> IsExistAsync(Expression<Func<TEntity, bool>> input)
        {
            var result = await EntityDb.IsAnyAsync(input);
            return result;
        }

I'm writing about the processing of Web API< The encapsulation of database access processing based on SqlSugar is in Developing application on Web API of net6 framework >There is also an introduction in the, which is mainly to do it first net6 development environment, and then carry out relevant project development.

According to the needs of the project, we have defined some base classes of controllers to realize different functions.

 

Where ControllerBase is net core Web API, from which we derive a LoginController for login authorization, while BaseApiController handles the user identity information of the conventional interface, while BusinessController encapsulates the basic interfaces such as standard addition, deletion, modification and query. When we actually develop, we only need to develop and write a base class similar to CustomerController.

BaseApiController has nothing to introduce, but to encapsulate and obtain the user's identity information.

The Id of the interface user can be obtained through the following code

        /// <summary>
        /// Current user identity ID
        /// </summary>
        protected virtual string? CurrentUserId => HttpContext.User.FindFirst(JwtClaimTypes.Id)?.Value;

Businesseacontroller inherits this controller. Pass in relevant object information through generic constraints.

    /// <summary>
    /// This controller base class is a base class specially designed for accessing data business objects
    /// </summary>
    /// <typeparam name="TEntity">Define mapped entity classes</typeparam>
    /// <typeparam name="TKey">Type of primary key, such as int,string etc.</typeparam>
    /// <typeparam name="TGetListInput">Or the condition object of paging information</typeparam>
    [Route("[controller]")]
    [Authorize] //Login access is required
    public class BusinessController<TEntity, TKey, TGetListInput> : BaseApiController
        where TEntity : class, IEntity<TKey>, new()
        where TGetListInput : IPagedAndSortedResultRequest
    {
        /// <summary>
        /// General basic operation interface
        /// </summary>
        protected IMyCrudService<TEntity, TKey, TGetListInput> _service { get; set; }

        /// <summary>
        /// Constructor, initializing the basic interface
        /// </summary>
        /// <param name="service">General basic operation interface</param>
        public BusinessController(IMyCrudService<TEntity, TKey, TGetListInput> service)
        {
            this._service = service;
        }

....

This base class receives an object that conforms to the definition of the base class interface as the interface object of processing methods such as addition, deletion, modification and query of the base class. The definition processing in the specific CustomerController is as follows.

    /// <summary>
    /// Controller object for customer information
    /// </summary>
    public class CustomerController : BusinessController<CustomerInfo, string, CustomerPagedDto>
    {
        private ICustomerService _customerService;

        /// <summary>
        /// Constructor and inject the underlying interface object
        /// </summary>
        /// <param name="customerService"></param>
        public CustomerController(ICustomerService customerService) :base(customerService)
        {
            this._customerService = customerService;
        }
    }

In this way, basic related operations can be realized. If you need a special interface implementation, you can define the method implementation.

The controller processing code in a similar dictionary project is shown below. Define HTTP methods and routing information.

        /// <summary>
        /// According to dictionary type ID Gets the list collection of all dictionaries of this type(Key Is the name, Value Is a value)
        /// </summary>
        /// <param name="dictTypeId">Dictionary type ID</param>
        /// <returns></returns>
        [HttpGet]
        [Route("by-typeid/{dictTypeId}")]
        public async Task<Dictionary<string, string>> GetDictByTypeID(string dictTypeId)
        {
            return await _dictDataService.GetDictByTypeID(dictTypeId);
        }

        /// <summary>
        /// Get the dictionary list collection of all this type according to the dictionary type name(Key Is the name, Value Is a value)
        /// </summary>
        /// <param name="dictTypeName">Dictionary type name</param>
        /// <returns></returns>
        [HttpGet]
        [Route("by-typename/{dictTypeName}")]
        public async Task<Dictionary<string, string>> GetDictByDictType(string dictTypeName)
        {
            return await _dictDataService.GetDictByDictType(dictTypeName);
        }

 

Posted by definewebsites on Sat, 14 May 2022 08:22:52 +0300