Stored Procedure-Data room Charging System

1. What is a stored procedure?

Definition:

A stored procedure is a process written by flow control and SQL statements. The procedure is compiled and optimized and stored on the database server. You only need to call it when using the application.

When you enter the Registration Form of the IDC billing system, the registration form is as follows:

 

After you click Save, you need to write the student information to the t_student table, write the card information to the t_card table, and write the recharge information to the t_recharge table, if it is not applicable to stored procedures, you need to write three data entries to the table separately. Each time there is a lot of repeated code for database operations. After a stored procedure is used, you only need to write the corresponding parameters and statements into the stored procedure of the database and call them directly on the client.

2. How to use stored procedures?

The preceding registration is used as an example to create a stored procedure.

 

The database automatically pops up a stored procedure with the primary statement structure. You only need to add parameters and statements to it. The Stored Procedure Code is as follows:

USE [ChargeSystem]GO/****** Object:  StoredProcedure [dbo].[PROC_Register]    Script Date: 07/30/2014 21:30:47 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author:<Author,,Name>-- Create date: <Create Date,,>-- Description:<Description,,>-- =============================================ALTER PROCEDURE [dbo].[PROC_Register]-- Add the parameters for the stored procedure here@stuID varchar(20),@stuName varchar(20),@stuSex varchar(4),@stuDepart varchar(20),@stuGrade varchar(20),@stuClass varchar(20),@stuNote varchar(50),@cardID varchar(10),@cardType varchar(20),@remainCash varchar(4),@registerAdmin varchar(10),@isChecked varchar(20),@userID varchar(11),@rechargeCash varchar(4)ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;    -- Insert statements for procedure here                  insert into T_Student(stuID ,stuName ,stuSex ,stuDepart ,stuGrade ,stuClass ,stuNote ) values(@stuID ,@stuName ,@stuSex,@stuDepart,@stuGrade ,@stuClass ,@stuNote )    insert into T_Card (cardID,stuID ,cardType ,remainCash ,registerDate ,registerTime ,registerAdmin ,isChecked ) values(@cardID ,@stuID ,@cardType ,@remainCash ,CONVERT (varchar(20),GETDATE(),120) ,CONVERT (varchar(20),getdate(),108) ,@registerAdmin ,@isChecked )    insert into T_Recharge (cardID ,userID ,rechargeCash ,rechargeDate ,rechargeTime ) values (@cardID ,@userID ,@rechargeCash ,CONVERT (varchar(20),GETDATE(),120) ,CONVERT (varchar(20),getdate(),108)  )END

After writing, you can call it only after the execution is successful.

Dal layer call code

Public Function register (byval enstudent as entity. studententity, byval encard as entity. cardentity, byval userid as string, byval enrecharge as entity. rechargeentity) as Boolean implements iregister. register dim sqlhelper as new sqlhelper instantiate sqlhelper class dim ready type as commandtype = commandtype. storedprocedure defines the command type dim execution text as string = "proc_register" 'database execution string' to pass the parameter dim parameter as sqlparameter () parameter = {New sqlparameter ("@ userid", userid ), new sqlparameter ("@ stuname", enstudent. stuname), new sqlparameter ("@ stuid", enstudent. stuid), new sqlparameter ("@ cardid", encard. cardid), new sqlparameter ("@ stusex", enstudent. stusex), new sqlparameter ("@ studepart", enstudent. studepart), new sqlparameter ("@ stugrade", enstudent. stugrade), new sqlparameter ("@ stuclass", enstudent. stuclass), new sqlparameter ("@ stunote", enstudent. stunote), new sqlparameter ("@ cardType", encard. cardType), new sqlparameter ("@ remaincash", encard. remaincash), new sqlparameter ("@ rechargecash", enrecharge. rechargecash)} return sqlhelper. excuteadddelupdate (plain text, parameter type, parameter) 'returns the execution result, true for success; otherwise, false End Function

3. Why should stored procedures be used?

1. Compile and execute SQL statements before execution. The stored procedure is some compiled SQL statements, which can be called directly when the application needs, therefore, stored procedures are more efficient than directly using SQL statements.

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

3. For example, if a bug occurs during registration, your debugging workload will be large. If you use a stored procedure, if it is a problem with SQL statements, then you can debug SQL statements in one place.