vb.net computer room charge system--stored procedure

First, the use of the background

Computer room billing system when the demand is to select a user, at the same time display the use of registration, recharge and return card records, the data to provide checkout data, and after the checkout to update the card, recharge, return card table three tables, if directly write SQL statements will be very complex. These requirements can be encapsulated as a stored procedure, and clicking on the checkout only requires passing in the parameters to execute the stored procedure.

II. Introduction to Stored Procedures

A stored procedure (Stored Procedure) is a set of SQL statements that are compiled to perform a particular function, stored in a database, and executed by the user by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). Stored procedures are an important object in a database, and any well-designed database application should use stored procedures.

Third, the advantages of stored procedures:

1. Encapsulate transaction rules.

2. Allow standard component-type programming

3, be able to achieve a faster execution speed (batch T--sql statements are compiled and optimized each time the run, the stored procedure is precompiled)

4, can reduce network traffic

5, be used as a security mechanism to make full use of (avoid unauthorized user access to data)

Application of the charge system in the computer room

First establish a stored procedure in the database

Statement:

<span style= "FONT-SIZE:14PX;" >--=============================================--author:< Xu Dan >--Create Date: <2014 year June 28 >-- description:< checkout at the same time update card form, recharge form, return card table >--=============================================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 [email protected] and billstatus = ' not checkout ' update recharge_info set Status = ' Checked out ' whe Re UserID [email protected] and Status = ' not checkout ' update cancelcard_info set billstatus = ' checkout ' where UserID [email protected] A nd billstatus = ' no checkout ' end</span>

D-Tier code:

<span style= "FONT-SIZE:14PX;" > 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)} ' declares and instantiates an array of arguments        Return Helper. Execadddelupdate (strSQL, CommandType.StoredProcedure, sqlparams)  ' executes and returns the result of the update operation    End function</span>

Five, Summary:

< Span style= "font-size:18px" >        1. The purpose of the stored procedure is to easily query information from system tables or to complete administrative tasks related to updating database tables. Enables faster execution if an operation contains a large number of T-SQL statement code that is executed more than once, the stored procedure is much faster than the batch execution. Because stored procedures are precompiled, the query optimizer analyzes, optimizes, and gives the storage plans in the system tables that are ultimately present when a stored procedure is first run. A batch of T-SQL statements needs to be precompiled and optimized for each run, so it is slower.

< Span style= "font-size:18px" >        2. The use of stored procedures is to optimize the database, so as to improve the system running speed. stored procedures are compiled only at creation time, and each subsequent execution of the stored procedure does not need to be recompiled. While the general sql statements are compiled once per execution, so using stored procedures can improve database execution speed.

< Span style= "font-size:18px" >       3. When complex operations are performed on a database, such as when multiple tables are update,insert,query,delete, this complex operation can be encapsulated with stored procedures and used in conjunction with the transactional processing provided by the database. These operations, if done with a program, become a single SQL statement that may 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, can be set only a certain user has the right to use the specified stored procedure.