Read the information from the database to build a view field to facilitate program code generation

In many cases, we need to develop a rapid code generation tool to improve the development efficiency, code generation tool is a lot of information is read the database table, view and other metadata to improve the object table information, with this information, we can add attribute fields in the ordinary entity class code in Chinese comments, Or in the WinForm or web interface of the rapid generation, can be in the query box or interface when editing, as a tag hint and other processing information. However, in general, the comment information of the view is not, but there is a certain relationship between the view and the table, although the different database systems for their information correspondence is not necessarily consistent, but we can find their corresponding relationship can be added to the View field comments, this is the idea and practice described in this article, This article introduces the processing implementation of the Code generation tool Databae2sharp How to get the memo information for the fields in the view.

1. SQL Server database View information processing

To demonstrate the processing of the view, we need to create two tables, as well as the view information, first we create a customer information table, as shown below.

We want the creator field in this T_customer table to be associated with the user table, and then we add a view with the view code as shown below.

SELECT     dbo. T_customer.id, dbo. T_customer.name, dbo. T_customer.age, dbo. T_customer.creator, dbo. T_customer.createtime, dbo. Users.usernamefrom         JOIN= dbo. Users.id   

That is, the field that lists T_customer, and the Username field in a users table, corresponds to a view that has two tables associated with it, and the data for that table is shown below.

Well, here we are basically ready for the prerequisites, the rest is how to get the information of these views.

In SQL Server, if we need to get information about the user view, we can get it through the view of the system, as shown below.

This is the name of the view and the view SQL code, if we need to get a detailed view of the field information, which is not enough, we can further list the user view and the specific table based on the view information, as shown below.

From we can see this view field and the table field of the one by one correspondence, that is, can pass the specific table field information, including our here very concerned about the note information.

For example, based on these principles, our view extracts the note information directly from the database table fields, thus realizing the automatic extraction of the view Memo field and reducing the tedious and inconvenient of manual setting.

2. Processing of Oracle Database view information

The processing of view fields in Oracle is different from SQL Server because it is difficult to find the view information for the one by one correspondence between the view fields and the table field.

In Oracle we first prepare a t_customer table, as well as basic information such as views.

Creating a view at the same time as a user table is associated with SQL Server-like practices, as shown below.

As= u.id;

We can get field information for the view through Oracle's system view All_tab_columns or Dba_tab_columns, as shown below.

Unfortunately, the information of this view does not have any intersection with the View Association table, cannot get the corresponding table information, then how to get the information of the view corresponding table, the answer is can be obtained by all_dependencies this view, as shown in.

But it's just getting to the corresponding table, not associating with the field, but basically it's pretty much the same, because we can get the view field, and the associated table contained in the view, then basically the field information is locked, unless a lot of the two tables have the same name and are difficult to handle.

With the following SQL, we can get the table and field information, as shown below.

SelectDISTINCT table_name, COLUMN_NAMEFrom All_tab_columnsWHERE table_nameInch(Select Referenced_nameFrom All_dependencieswhere name=‘View_customer "and type =  ' view" =  " Table ' ) and column_name Span style= "COLOR: #808080" >in (select column_name from All_tab_columns where table_name=  "view_customer") order by table_name;        

This way we can match the comment information of the view corresponding to the table's field information, and finally we look at the Oracle-based implementation.

From what we can see, the Code tool Database2sharp, according to the above-mentioned logical relationship, automatically from the table corresponding to the field to match the corresponding memo information, thereby reducing the manual setting of the View field notes information cumbersome work, for the View Code generation and interface generation increased speed and efficiency.

The above view notes the extraction logic of the information, we can apply it in their own business processing inside, to achieve a more extensive application.

The above view note information extraction requirements, from a customer's advice, very good, here to thank him for the contribution of this detail.

Reproduced

Read the information from the database to build a view field to facilitate program code generation