Computer room charge System--stored procedure

In this room charge system to learn something really many. E-r diagrams, views, stored procedures, triggers, and so on. These things have been learned before, this time in practice, the harvest doubled.

Benefits of using Stored procedures

1. 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.

2. 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.

3. The process can be reused to reduce the workload of the Database developer

4, full-height, can be set only some users have the use of the specified stored procedures.

Practical application of stored procedures

In the card back I used the stored procedure, the following is the return card as an example.

Create a stored procedure

<span style= "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;" >create  Procedure Proc_closecard  --Create stored procedure @cardid varchar, @closeCardUserId varchar (10)-- Defining the parameters asbegin--inserting data into the return card table insert into T_closecard (cardid,closecash,registrydate,registrytime,ischecked, Registryuserid) Select CardId, Balance, Date,time, isChecked, UserId  from T_card where cardId [email protected]--will be in the card table Related cards Delete the delete from t_card where [email protected]--update the return card table updates T_closecard set closeuserid [email protected]  where cardId [email protected] end</span>

Return card D layer call to stored procedure

<span style = "FONT-FAMILY:KAITI_GB2312;FONT-SIZE:18PX;"    > ' <summary> ' Add return card information ' </summary> ' <param name= ' _enclosecardentity ' > Return card entity class </param> ' <returns> insert successfully returns TRUE, failed to return false</returns> public Function Addclosecard (ByVal enclosecardentity as  Closecardentity) as Boolean Dim sqlHelper As New sqlHelper ' instantiate sqlHelper ' SQL statement Dim cmdtext As String        = "Proc_closecard" ' defines command type, stored procedure Dim cmdtype as CommandType cmdtype = CommandType.StoredProcedure ' Define parameter array Dim sqlparams as SqlParameter () ' assigns a value to the parameter array sqlparams = {New SqlParameter ("@cardId", enclose Cardentity.cardid), New SqlParameter ("@closeCardUserId", Shareduserid.userid)} Return SqlHelper . Executenoquery (Cmdtext, Cmdtype, sqlparams) End Function</span> 

The result of the operation with the stored procedure is the same as the result of the SQL statement. Just a SQL statement written on the database side, one written on the compiler side.

Once you've used it, you'll find that the stored procedure is actually quite simple. But listen to Master said that the stored procedure is process-oriented, not conform to the idea of object-oriented. It will be less used in the future. But now in the learning phase, use is also good, at least to use. In the practice of continuous improvement.

Computer room charge System--stored procedure