Database Operations in the IDC Charging System

The second version of the data room Charging System adds the design mode and sqlhelper, and some data operations are basically completed. Compared with the first version of the simple three-tier Data room charging system, it is not much simpler, on the contrary, it is difficult to adjust between them, but it is very advantageous to think about maintainability and scalability. For example, the abstract factory solves the trouble of changing the database; the appearance mode solves the trouble of convenient expansion; sqlhelper makes the writing of layer D easier, and the repeated work is greatly reduced; the stored procedures, triggers, and views of Database Operations simplify the code logic. This blog focuses on database operations.

 

1. Stored Procedure)

1. Concept: stored procedures are in large database systems. A set of SQL statement sets for specific functions are stored in the database after compilation, you can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters.

2. Advantages: Fast execution speed, modular program design, reduced network communication volume, ensured system security, and realized reuse of code. For more information, see the title connection or the encyclopedia stored procedure.

3. For example: the charging system in the data center has many functions and requires multiple database operations on different tables. This will make the logic structure of our code slightly complex, the stored procedure can easily solve this problem. For example, to exit the system function of the data room charging system:

If you do not need a stored procedure: we need to insert this data to the work record table through transmission between three layers, and then delete the data in the table on the machine, so we need to pass it back and forth twice.

Stored Procedure: We can unmount the stored procedure by calling the insert and delete operations once:

Stored Procedure Code:

-- ===================================================== ====== -- Author: liu jiahan -- create Date: 2013/4/7 -- Description: Delete the information on the host while, insert this record to the Host record -- ============================== =============== alter procedure [DBO]. [proc_uplogin] -- add the parameters for the stored procedure here @ computerid varchar (20), @ name varchar (20), @ userlevel varchar (20), @ onlinedate varchar (20 ), @ onlinetime varchar (20), @ uplinedate varchar (20), @ uplinetime varchar (20), @ recordid varchar (20) asbegin -- set nocount on added to prevent extra result sets from -- interfering with select statements. set nocount on; -- insert statements for procedure here delete records where computerid = @ computerid insert into t_workrecord (name, userlevel, onlinetime, onlinedate, recordid, computerid, uplinedate, uplinetime) values (@ name, @ userlevel, @ onlinetime, @ onlinedate, @ recordid, @ computerid, @ uplinedate, @ uplinetime) End

Call code:

Public Function insertworkrecord (byval workrecord as SC _entity.workrecordentity) as Boolean implements limit dim sql2 as string = "select * From t_workrecord" 'query the number of data entries in the t_workrecord table dim SQL as string = "proc_uplogin" dim insertsqlhelper as new sqldbhelper dim sqlparam) as sqlparameter sqlparam (0) = insertsqlhelper. addsqlparameter ("@ name", sqldbtype. varchar, workrecord. getname) sqlparam (1) = insertsqlhelper. addsqlparameter ("@ userlevel", sqldbtype. varchar, workrecord. getuserlevel) sqlparam (2) = insertsqlhelper. addsqlparameter ("@ onlinetime", sqldbtype. varchar, workrecord. getonlinetime) sqlparam (3) = insertsqlhelper. addsqlparameter ("onlinedate", sqldbtype. varchar, workrecord. getonlinedate) sqlparam (4) = insertsqlhelper. addsqlparameter ("@ recordid", sqldbtype. varchar, insertsqlhelper. inquiredatatable (sql2, commandtype. text ). rows. count + 1) sqlparam (5) = insertsqlhelper. addsqlparameter ("@ computerid", sqldbtype. varchar, workrecord. getcomputerid) sqlparam (6) = insertsqlhelper. addsqlparameter ("@ uplinedate", sqldbtype. varchar, workrecord. getuplinedate) sqlparam (7) = insertsqlhelper. addsqlparameter ("@ uplinetime", sqldbtype. varchar, workrecord. getuplinetime) return insertsqlhelper. executenoquery (SQL, commandtype. storedprocedure, sqlparam) end Function

In this way, the implementation of a simple stored procedure is realized.

2. Trigger ):

1. Concept: A trigger is a special stored procedure. Its execution is not called by a program, nor is it manually started, but triggered by an event, for example, when you perform operations (insert, delete, update) on a table, it is activated for execution. Triggers are often used to enhance data integrity constraints and business rules.

2. function: it is mainly used to achieve complex integrity of reference and data consistency that cannot be guaranteed by primary keys and Foreign keys. Second, we also need to strengthen constraints, track changes, cascade intermodal lines, and call stored procedures. Link to the title or encyclopedia trigger
.

3. Use the trigger with caution: the trigger has powerful functions and can easily and reliably implement many complex functions. Why should we use the trigger with caution. Trigger itself is not at fault, but because of our misuse, it will cause difficulties in database and application maintenance. In database operations, we can perform data operations through relationships, triggers, stored procedures, and applications ...... Meanwhile, rules, constraints, and default values are also an important guarantee for data integrity. If we rely too much on triggers, it will inevitably affect the database structure and increase the complexity of maintenance.

4. For example: Since the storage process is usually used first, I just tested the trigger in a small example. You can see that when a data table is inserted, trigger.

Trigger code:

-- ===================================================== ====== -- Author: liu jiahan -- create Date: 2013/4/7 -- Description: trigger. When a data entry is inserted, insert a fixed data record -- ==================================== ============= alter trigger [DBO]. [deleteadd] on [DBO]. [users] After insertas begin -- set nocount on added to prevent extra result sets from -- interfering with select statements. set nocount on; insert into users (user_id, user_pwd) values ('lyun', '123') End

Insert a statement:

insert into  Users (User_ID ,User_Pwd ) values ('122','121')

View results:

3. View:

1. Concept: A view is a virtual table whose content is defined by the query. Like a real table, a view contains a series of columns and row data with names. However, a view does not exist in the database as a stored data value set. Rows and columns are used to define tables referenced by View queries and dynamically generate tables when views are referenced.

2. Role: Generally, In the design database, data redundancy is designed according to the three paradigm. However, in the query, we need to obtain several fields associated with this field through a field, however, they are distributed in different tables. In this case, we need to use the view to put all the fields to be queried in a view for query, which makes it much easier.

3. Example:

Create a view using two tables and associate the two tables with the name field. That is, create a temporary table using the existing two tables t_onworkteacher and t_user. Then, you can perform normal data operations.

The above is a brief summary of database operations by the data center charging system. All in all, we have gradually applied the previously learned database knowledge to practice. Through usage, we have gained a preliminary understanding of this, and a lot of practice is required for further study.