Vb.net data center charging system-Stored Procedure

I. background

When the data center billing system bills users, You need to select a user and display the registration, recharge, and refund records. These data are used to provide the billing data, after checkout, you must update the cards, recharge, and withdraw tables at the same time. If you write SQL statements directly, it will be very complicated. These requirements can be encapsulated into a stored procedure. After clicking checkout, you only need to input parameters to execute the stored procedure.

II. Introduction to stored procedures

Stored Procedure is a set of SQL statements for specific functions. It is compiled and Stored in the database, you can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters. Stored procedures are an important object in databases. Any well-designed database application should use stored procedures.

Iii. Advantages of stored procedures:

1. encapsulate transaction rules.

2. Allow standard component programming

3. Fast execution speed (the T--SQL Statement of batch processing needs to be compiled and optimized each time it is run, and the stored procedure is pre-compiled)

4. reduces network traffic

5. Be fully utilized as a security mechanism (to avoid unauthorized user access to data)

Iv. Application in the data room Charging System

First, create a stored procedure in the database

Statement:

-- ===================================================== ====== -- Author:
 <许丹>
  
-- Create date: <June 28, 2014> -- Description:
  <结账同时更新卡表、充值表、退卡表>
   
-- ===================================================== ====== Create procedure [dbo]. [Proc_PayAccounts] -- Add the parameters for the stored procedure here @ strUserID char (6) -- User IDASBEGINupdate Card_Info set BillStatus = 'checkout' where UserID = @ strUserID and BillStatus = 'uncheckout' update ReCharge_Info set Status = 'checkout' where UserID = @ strUserID and Status = 'uncheckout' update CancelCard_Info set BillStatus = 'checkout' where UserID = @ strUserID and BillStatus = 'uncheckout' END
  
 

Layer D code:

Public Function SettleAccounts (ByVal euser As Entity. userInfo) As Integer Implements IDAL. IUser. settleAccounts Dim helper As New Helper. sqlHelper Dim strSql As String = "Proc_PayAccounts" 'declares and instantiates the Stored Procedure Dim sqlParams As SqlParameter () = {New SqlParameter ("@ strUserID", euser. userID)} 'declare and instantiate the parameter array Return helper. execAddDelUpdate (strSql, CommandType. storedProcedure, sqlParams) 'execute and return the update operation result. End Function

V. Summary:

1. The purpose of the stored procedure is to conveniently query information from the system table or complete management tasks related to updating database tables. Fast execution speed if an operation contains a large number of T-SQL statement code, were executed multiple times, then the stored procedure is much faster than the execution speed of batch processing. Because the stored procedure is pre-compiled, when a stored procedure is run for the first time, the query optimizer analyzes and optimizes the stored procedure and provides the storage plan that is finally stored in the system table. The T-SQL Statement of batch processing needs to be pre-compiled and optimized every time, so the speed is slower.

2. Using Stored procedures is to optimize the database to speed up system operation. The stored procedure is compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it. Generally, the SQL statement is compiled every time it is executed, therefore, using stored procedures can speed up database execution.

3. when performing complex operations on the database (for example, performing Update, Insert, Query, and Delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database. If these operations are completed by a program, they become SQL statements that may need to be connected to the database multiple times. Instead of storage, you only need to connect to the database once.
4. stored procedures can be reused to reduce the workload of database developers.
5. High security. You can set that only one user has the right to use the specified stored procedure.