How to parse the data access layer to operate databases

There are several types of database operations at the data access layer: concatenated strings, basic SQL statements with construction parameters, stored procedures, views, and so on. Because this system is mainly used to promote our learning, I tried these methods in this IDC charging system.

 

For example:

A. When querying the on-premise and On-premise records based on the conditions, the SQL statement with parameters is used. The preceding offline record table, student information table, and on-board card information table are used as the basic table for joint query.

B. When adding checkout information, querying recharge records, and card balances, a stored procedure with input or output parameters is used. When creating a stored procedure, you also try to use control statements and built-in functions, especially when adding checkout information.

C. When querying the basic information of students, the view mode is involved. The view created by the on-board card and student information table is queried in the form of Concatenated character creation.

 

Below, I will compare their advantages and disadvantages based on my experience in using these methods:

 

1. concatenating strings is definitely not advisable. Although you can customize SQL statements on the client at any time as needed, it is very dangerous, such as the SQL statement injection vulnerability.

2. Using the basic SQL statement of the constructor to deal with simple queries is also acceptable and safe. If it is a complicated query, it will drive people crazy (for example, joint query of 7 or 8 tables)

For example:

This is an SQL statement with parameters that I used to query the on/off server records. Three tables are involved here and internal connections are used.

Selectoor_cardid, stu_stuname, stu_department, stu_grade, oor_ondate, oor_ontime, oor_computer

Fromtb_onoffrecord inner join tb_cardinfo

On oor_cardid = cif_cardid and oor_cardid = @ cardid and cif_state = @ cardstate and oor_offdate is null and oor_state = @ cardstate

Inner jointb_studentinfo on cif_stuid = stu_pk_stuid

Because the database considers the joint query, the prefix is added before the field. If there is no prefix, then some fields, such as the table name before cardid, are more complicated. (This also reflects the benefits of the field prefix)

3. The direct difference between views and basic SQL statements is that SQL statements are transmitted over multiple networks, while views are views that directly execute databases. The same is true for stored procedures. This is also the reason why stored procedures are fast. That is to say, you only need to transmit one name for views and stored procedures, while SQL statements may need to transmit a large number of strings, views and stored procedures obviously increase the burden on the server database.

4. The difference between a stored procedure and a view is mainly reflected in that a view is a visual joint query, while a stored procedure is not, but a view is more intuitive, and you can query a view like a query table, you do not need to consider the statements such as left join and right join. For very complex chain tables (if seven or eight tables are connected together, it is not a simple single link), writing SQL statements will drive your brain crazy, view can greatly reduce the work difficulty.

In another way, views and stored procedures reduce the coupling between applications and physical tables. The stored procedure is described in strings, and the view is more intuitive, so the effect to be achieved is similar.

The method to use depends on the specific application scenario. Obviously, directly concatenating strings is not advisable. If the execution efficiency is taken into account and the server database pressure is not considered, it is very easy to return datatable using views and stored procedures. If the project is small, client resources can be used at will. you can directly use the basic SQL statement that constructs parameters. When several tables are involved, the client retrieves data from multiple returned datatables after obtaining data, it is also good to generate a new datatablee using code. The specific problems should be analyzed in detail to grasp the balance.