A front-end and back-end general calling method based on table value parameters

Abstract

The application discloses a front and rear end general calling method based on a table value parameter and belongs to the technical field of software development and data interaction. The application takes the table value parameter as a unified data interaction carrier, encapsulates six kinds of standardized general calling sub-functions, covers more than 99% business scenes, realizes complete decoupling of front and rear ends, zero SQL of the front end, unified system calling, high code reuse, has the characteristics of architecture neutrality, cross-platform, cross-database, seamless extension to API and micro services, fundamentally eliminates the security injection risk, greatly improves the development efficiency, reduces the maintenance cost, and prolongs the system life cycle. The application has been engineered and landed in an industrial ERP / MES system and can be widely applied to various information systems and has high practicability and industry popularization value.

Description

Technical Field

[0001] This invention relates to the fields of software development, data interaction, and system architecture. Specifically, it relates to a unified front-end and back-end calling method based on table-valued parameters (TVP), which is completely decoupled from the front-end and back-end, universally applicable across all scenarios, highly secure, and highly reusable. It can be widely applied to various software development and data interaction scenarios such as enterprise information systems, industrial ERP / MES, database stored procedure interaction, API interface calls, microservice communication, and distributed service architecture. It solves common industry problems in traditional development, such as chaotic interfaces, redundant code, low security, difficult maintenance, and poor scalability. Background Technology

[0002] In the current development and maintenance of various information systems, industrial software, and enterprise management systems, the interaction and calls between the application layer, service layer, and data layer generally suffer from the following underlying architectural defects that are difficult to resolve through local optimization: The front-end code is highly redundant and has extremely poor reusability: for each interface and each stored procedure, it is necessary to repeatedly write underlying code such as database connection, parameter construction, execution call, exception handling, and resource release, resulting in a huge amount of code and extremely high maintenance costs.

[0003] The calling methods are chaotic and lack a unified standard: the system contains multiple calling modes, such as pure execution, single parameter input, multiple parameter input, single value return, result set return, and status prompt return. The interface definitions are inconsistent, the format is non-standard, and the integration is difficult.

[0004] Significant security risks: Many business processes use direct concatenation of SQL strings on the front end to perform queries and operations, which can easily lead to high-level security risks such as SQL injection, unauthorized access, illegal tampering, and data leakage, and does not meet the requirements of industrial system security level protection.

[0005] Weak batch data interaction capability: Traditional single-parameter parameter passing cannot efficiently support large-scale data processing, the advantages of table-valued parameters have not been standardized and utilized, and there is a lack of a unified encapsulation system.

[0006] The architecture is heavily tied to the database and has poor scalability: traditional calling methods are only suitable for single database scenarios and cannot be seamlessly migrated to APIs, microservices, and distributed architectures. The system has high technical iteration costs and short lifecycles.

[0007] Low development efficiency: New business logic needs to be written from scratch, which cannot be quickly reused, resulting in long system iteration cycles and slow deployment, making it difficult to meet the rapid digitalization needs of enterprises.

[0008] Currently, the industry lacks a universal front-end and back-end calling standard solution that covers all business scenarios, is secure and compliant, has a neutral architecture, and allows for one-time encapsulation and reuse across the entire system, thus failing to address the aforementioned pain points from the ground up. Summary of the Invention

[0009] The purpose of this invention is to overcome the shortcomings of existing technologies and provide a universal front-end and back-end calling method based on table-valued parameters. It uses table-valued parameters as a unified data carrier, standardized functions as a unified entry point, and security and controllability as the core principles. It encapsulates 6 types of universal calling sub-functions covering more than 99% of business scenarios, achieving unified calling across the entire system, zero code redundancy, security without injection, and scalable architecture. It encapsulates once, reuses all business functions, and adapts to all scenarios.

[0010] (1) Overall Design Rules Unified data carrier: Batch data, business parameters, and query conditions all use structured table-valued parameters (TVP) as the sole interaction format.

[0011] Unified naming and structure: The call identifier, parameter structure, return message, and status code are standardized across the entire system.

[0012] Unified status return: Execution results, exception information, and business prompts are output using fixed fields in a unified manner.

[0013] Automatic resource management: Database connections, data objects, and memory resources are all automatically created, released, and reclaimed.

[0014] Absolute security control: The front end is prohibited from concatenating any SQL statements at all times, and all data access is executed in a closed manner through standardized functions.

[0015] High coverage positioning: Covers more than 99% of conventional business scenarios, and can be lightweightly extended on the basis of this architecture for extremely special and complex scenarios.

[0016] (2) Core technical solution: 6 types of standardized general calling sub-functions This invention encapsulates six types of standard atomic call functions that cannot be further simplified, covering all common scenarios such as CRUD operations, batch processing, data loading, status return, and UI binding:

[0017] It executes business logic without any input parameters, dataset, or result return, and is suitable for scenarios such as initialization, refresh, and state reset.

[0018] It takes a table-valued parameter dataset as input, has no status output, and returns no result. It is suitable for pure write operations such as batch addition, batch modification, and batch deletion.

[0019] Pass in table-valued parameters and execute to return a standard result set, which is automatically bound to the front-end display controls. The front-end requires zero SQL and zero processing logic.

[0020] When a table-valued parameter is passed in, the system only returns the execution status, the number of successful entries, and error information after execution, without returning a result set. This is suitable for status-based business processes such as review, submission, and cancellation.

[0021] It takes table-valued parameters as input and returns a result set, execution status, and exception information, making it suitable for integrated processing of complex business processes.

[0022] By simply inputting the business identifier and filtering conditions, it automatically routes to the corresponding data logic, performs automatic queries, and automatically binds to the interface, completely replacing the front-end SQL concatenation and achieving the highest level of secure querying.

[0023] (3) Cross-architecture scalability This invention is architecture-neutral, not tied to any technology stack, and can be seamlessly extended to all modern architectures: Table-valued parameters ↔ API request message / request body Stored procedure identifier ↔ Interface address / Service name Status output ↔ HTTP response code / prompt message Result set ↔ List of data returned by the interface The same calling logic can be reused directly in traditional C / S architecture, B / S architecture, microservices, distributed systems, HarmonyOS native applications, and multi-terminal cross-platform applications.

[0024] (4) Decoupling of front-end and back-end core design The front end is extremely thin: it is only responsible for data entry, interface rendering, parameter construction, and calling standard functions, and does not carry any business logic, process data, or concatenate SQL.

[0025] The backend is extremely stable: all data validation, transaction control, permission judgment, and exception handling are encapsulated in the backend and common functions.

[0026] One-time encapsulation, reuse throughout the entire system: New business functions do not require modification of the calling framework; development can be completed simply by passing in the parameters. Beneficial effects

[0027] Extremely high coverage: One method covers more than 99% of routine business operations, completely eliminating duplicate code.

[0028] Universally applicable: It is suitable for all information systems regardless of industry, architecture, or database.

[0029] Architecture-level security: Zero SQL on the front end, eliminating SQL injection, unauthorized access, and data leakage at the source.

[0030] Standardized and unified: The calling method, return format, exception handling, and resource management are all fully standardized.

[0031] Development efficiency is doubled: no need to write underlying call code for new business functions, shortening the development cycle by more than 80%.

[0032] Automatic UI binding: Data is automatically loaded and controls are automatically adapted, greatly simplifying front-end development.

[0033] Seamless cross-architecture scaling: It can be directly migrated to APIs, microservices, and distributed architectures, extending the system lifecycle by more than 10 years.

[0034] High stability and easy maintenance: centralized logic, quick problem localization, and maintenance costs approaching zero. Detailed Implementation

[0035] Define a globally unified general table-valued parameter structure for the entire system as the standard for data interaction across the entire system.

[0036] It encapsulates 6 types of standardized general-purpose call sub-functions, integrating connection management, parameter conversion, transaction execution, exception handling, and automatic resource release.

[0037] The front-end page retains only the visual interface, and all business operations are completed by passing TVP parameters through a unified function.

[0038] All queries, additions, modifications, deletions, approvals, and synchronizations are executed through a unified entry point, returning results in a unified format.

[0039] When extending to microservices / APIs, keep the function logic unchanged and simply convert the data carrier to standard JSON messages for seamless adaptation.

Claims

1. A general front-end and back-end calling method based on table-valued parameters, characterized in that, Using Table Valued Parameters (TVP) as the unified data interaction carrier between the front end and the back end, it achieves unified and secure interaction between the application layer, service layer and data layer by encapsulating 6 types of standardized general calling sub-functions, covering more than 99% of common business scenarios, and can be seamlessly extended to API interfaces, microservices and distributed architectures.

2. The method according to claim 1, characterized in that, The following unified design rules apply: Batch data uses structured table-valued parameters for interaction; call identifiers, parameter structures, and return formats are standardized; business status and exception information are returned uniformly through fixed output items; database connections and system resources employ automatic creation and release mechanisms. The entire process prohibits the front-end from concatenating SQL statements, thus eliminating the risk of injection at the architectural level.

3. The method according to claim 1, characterized in that, The six types of standardized general-purpose call subfunctions include: (1) A pure execution function with no input parameters, no dataset, and only executes business logic; (2) Batch operation functions that take a dataset as input, have no state output, and return no result; (3) Input the dataset, execute the function, return the result set, and automatically bind the query function of the front-end control; (4) A status return function that takes in a dataset, returns the execution status and exception information, and does not return a result set; (5) A composite business processing function that takes a dataset as input and returns a result set and execution status; (6) A general standardized query function that only requires inputting identifiers and conditions, automatically completes queries and binds them to the interface.

4. The method according to claim 1, characterized in that, The front-end is only responsible for interface rendering, data entry, parameter construction, and standard function calls. It does not carry business logic, handle data calculations, or concatenate any SQL statements, thus achieving a high degree of decoupling between the front-end and back-end.

5. The method according to claim 1, characterized in that, All data interactions, execution logic, exception handling, and resource management are encapsulated within general-purpose functions. New business logic can be developed by simply reusing these functions without having to rewrite the underlying code.

6. The method according to claim 1, characterized in that, This method can be seamlessly adapted and extended to API and microservice architectures: structured table-valued parameters correspond to interface request messages; stored procedures or business identifiers correspond to service interface identifiers; status output items correspond to interface response codes and prompts; and result sets correspond to the interface return data bodies.

7. The method according to claim 1, characterized in that, By unifying the calling standards, return formats, exception handling, and resource management, the system achieves high reusability, low redundancy, easy maintenance, and easy expansion, significantly reducing development and iteration costs.

8. A general front-end and back-end calling system based on table-valued parameters, characterized in that, include: A unified data interaction layer uses table-valued parameters as the standard transmission format. A general function encapsulation layer implements the six types of standardized calling functions described in claim 3; The front-end lightweight interaction layer is only responsible for interface display and parameter construction. Extend the adaptation layer to support unified calls and seamless switching of database stored procedures, API interfaces, and microservices.

9. The system according to claim 8, characterized in that, The method and system described have been engineered and implemented in an integrated industrial ERP / MES system, and can operate stably for a long time. They are applicable to all scenarios of front-end and back-end interaction in various information systems.