Rebuilding the charging system of vb.net Data Center-Use of the stored procedure

Rebuilding the charging system of vb.net Data Center-Use of the stored procedure

During layer-7 login, I came into contact with the concept of the stored procedure, but it was not used yet. In the formal refactoring process, add the stored procedure to the refactoring. To understand the stored procedure.

First

[Concept of Stored Procedure]

Stored Procedure is a set of SQL statements for specific functions. Compiled and stored in the database. You can specify the name of the stored Procedure and provide parameters for execution.

Stored Procedures can contain logical control statements and data manipulation statements. They can accept parameters, output parameters, return one or more result sets, and return values.

[Use of stored procedures]

First, open the database-Stored Procedure

Right-click to create a stored procedure

 

Create procedure <stored procedure Name> -- Add the parameters for the stored PROCEDURE here <@ Param1, sysname, @ p1>
 
  
=
  
   
, <@ Param2, sysname, @ p2>
   
    
=
    
     
ASBEGIN -- set nocount on added to prevent extra result sets from -- interfering with SELECT statements. set nocount on; -- Insert statements for procedure hereSELECT <@ Param1, sysname, @ p1>, <@ Param2, sysname, @ p2> ENDGO
    
   
  
 

 

For example, the storage process of registering a new user in the IDC Charging System

 

CREATE PROCEDURE [dbo].[pro_Register]-- Add the parameters for the stored procedure here@Cardno char(12),@Studentno char(12),@Studentname char(12),@Sex char(12),@Department char(12),@Grade char(12),@Class char(12),@Explain varchar(50),@IsCheck char(12),@Type char(12),@Cash numeric(18,1),@Date date,@Time time(0),@UserID char(12),@Status char(12)ASBEGINinsert into T_Student(Cardno,Studentname,Studentno,Sex,Department,Grade,Class,Cash,Explain,IsCheck,UserID,Type,Status)values(@Cardno ,@Studentname,'12',@Sex,@Department,@Grade,@Class,@Cash,@Explain,@IsCheck,@UserID,@Type,@Status)insert into T_Recharge(Cardno,Studentno,Addmoney,Date,Time,UserID,Status)values(@Cardno,@Studentno,@Cash,@Date,@Time,@UserID,@Status)END

 

 

Stored Procedure name: pro_Register

Called at Layer D

 

Public Function SelectStudent (ByVal student As Entity. studentEntity, ByVal recharge As Entity. rechargeEntity) As Boolean Implements IDAL. IStudent. selectStudent Dim sqlparams As SqlParameter () = {New SqlParameter ("@ Cardno", student. cardno), New SqlParameter ("@ Studentname", student. studentName), New SqlParameter ("@ Studentno", student. studentno), New SqlParameter ("@ Sex", student. sex), New SqlParameter ("@ Department", student. department), New SqlParameter ("@ Grade", student. status), New SqlParameter ("@ Class", student. clas), New SqlParameter ("@ Cash", student. cash), New SqlParameter ("@ Explain", student. explain), New SqlParameter ("@ IsCheck", "unpaid"), New SqlParameter ("@ UserID", student. userID), New SqlParameter ("@ Type", student. type), New SqlParameter ("@ Status", student. status), New SqlParameter ("@ Date", recharge. dater), New SqlParameter ("@ Time", recharge. timer)} Dim strSql = "pro_Register" Dim helper As New SqlHelper Dim dt As New Integer dt = helper. exeAddDelUpdate ("pro_Register", CommandType. storedProcedure, sqlparams) If dt> 0 Then Return True Else Return False End If

 

[Advantages of stored procedures]

Stored procedures allow standard component-based programming

1. stored procedures allow standard component programming

After a stored procedure is created, it can be called and executed multiple times in the program without re-writing the SQL statement of the stored procedure. Database professionals can modify the stored procedure at any time, but it has no impact on the application source code, which greatly improves the program portability.

2. Fast execution of Stored Procedures

If an operation contains a large number of T-SQL statement codes that are executed multiple times, the stored procedure is much faster than the batch execution. 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 will be slower.

In the data room charging system, because the data volume and users are relatively small, the difference between batch processing and storage process may not be realized. This method has obvious advantages when you have a large amount of data.

3. storage process to reduce network traffic

For the same database object operation, if the T-SQL statements involved in this operation are organized into a stored procedure, when the stored procedure is called on the client, only this call statement is passed in the network; otherwise, multiple SQL statements are passed. This reduces network traffic and network load.

This advantage is that when the data volume is large, you can obviously experience the advantages of the stored procedure.

4. stored procedures can be fully utilized as a security mechanism

The system administrator can restrict the permissions of a stored procedure to prevent unauthorized users from accessing data and ensure data security.