Data room charging system-Use of Stored Procedures

In the "checkout" section of the data center billing system, select an operator and click "checkout, change the record-filling, recharge, and card-return operations handled by the operator to "checked ". Note that swap, recharge, and back-to-card are recorded in three tables. Assume that according to the traditional method, you need to write three functions at the Dal layer, and update the ischeck of each table to "true ", not to mention how much code is written and how much effort is required. This reduces the execution speed of the system and facilitates errors.

In the restructuring of the charging system for the individual version of the data room, We don't just need to implement the functions, but become "lazy" and say no to repeated work, the stored procedure is introduced here to solve the problem. A stored procedure is a module written using SQL statements and flow control statements. After compilation and optimization, it is stored in the database of the database server. It can be called during use.

1. Create a stored procedure:

Select "programmability" under the stored procedure to be used, click "+", select "Stored Procedure", and right-click "create stored procedure" in the shortcut menu ", the following interface is displayed. A lot of code is actually a template. We just need to fill it out.

Note: The data type must be specified after the number of bytes, which is consistent with the field type in the database.

2. Use the stored procedure's D-Layer Code:

'After checkout, change the user-operated business [Recharge, refund, and note card] ischeck to true public function updateischeck (ecard as card) as Boolean implements icheckout. updateischeck dim sqlhelper as new sqlhelper. sqlhelper instantiate sqlhelper class dim primitive type as <strong> commandtype </strong> = commandtype. storedprocedure 'defines the command type. Stored Procedure <strong> dim runtime text as string = "proc_checkout" </strong> 'database running string' is passed to mongodim Params as sqlparameter () params = {New sqlparameter ("@ ischeck", ecard. proischeck), new sqlparameter ("@ userid", ecard. prouserid)} return sqlhelper <strong>. executenoquery (plain text, partition type, Params) </strong> end Function

3. SQL statement code:

Public Function ModifyPwd(euser As User) As Integer Implements IPwd.ModifyPwd        Dim strSQL As String = "update T_User set userPwd [email protected] where [email protected]"        Dim params() As SqlParameter = {New SqlParameter("@pwd", euser.ProuserPwd), New SqlParameter("@userID", euser.ProuserID)}        Dim helper As New SqlHelper.sqlHelper        Dim result = helper.ExecuteNoQuery(strSQL, CommandType.Text, params)        Return result    End Function

Through the comparison above, it is found that the use of stored procedures is very easy. You only need to change commandtype to storedprocedure, instead of carefully writing SQL statements, but instead of writing the name of the stored procedure.

Compared with using SQL statements directly, calling stored procedures in applications has the following advantages: reduces the workload of programming and reduces network traffic. The network traffic for calling a stored procedure with few rows of data may not be very different from that for directly calling SQL statements. However, assume that the stored procedure includes hundreds of rows of SQL statements, therefore, the performance of an SQL statement is much higher. This is because the database has been parsed and optimized during the creation of the stored procedure. Once a stored procedure is run, a stored procedure is retained in the memory, so that it can be called directly from the memory when the same stored procedure is run again next time, thus improving the running speed. At the same time, SQL functions and flexibility are enhanced to indirectly implement security control.

This is the first time to use the stored procedure and will be continuously learned and used in the future. After taking the first step, the road ahead will be easy.