Simple web layer-3 architecture system (version 4) and web layer-3 (version 4)

Simple web layer-3 architecture system (version 4) and web layer-3 (version 4)

The last time I wrote the third edition,

Because you have no intention of seeing a video before, that is, to make all the SQL statements in the system into a stored procedure. The SQL Execution speed of the system can be greatly optimized. So Baidu made some details, and then I made all the SQL statements into a stored procedure.

In fact, writing a stored procedure is also very simple (it may be a simple system and does not require too complex SQL statements). You only need to create a stored procedure in the database and then call it in the program.

 

1. First, create a stored procedure:

Expand the database in use → programmability → stored procedure. Right-click to create a stored procedure.

1 set ANSI_NULLS ON 2 set QUOTED_IDENTIFIER ON 3 GO 4 -- ====================== ========================= 5 -- Author: h_F_us 6 -- Create date: December 29, 2014 09:18:01 7 -- Description: delete an employee information. ============= 9 alter procedure [dbo]. [deleteperson] 10 11 @ id int 12 13 AS14 BEGIN15 16 delete from person where id = @ id17 18 END

 

The above is the created stored procedure:

1. The name of the stored PROCEDURE is followed by alter procedure. You can select any name if it complies with the SQL naming rules.

2. AS is the parameter to be used in SQL statements.

3. Between BEGIN and END is the SQL statement to be executed.

 

 

 

This can be called in the system code.

In the personDAO class:

1 /// <summary> 2 // Delete employee information 3 /// </summary> 4 /// <param name = "id"> id of the employee to be deleted </param> 5 // <returns> returns the true value: if the deletion is successful, if the deletion is false, the deletion fails. </returns> 6 public bool delete (person p) 7 {8 bool flag = false; 9 10 SqlParameter [] paras = new SqlParameter [] 11 {12 new SqlParameter ("@ id", p. id) 13}; 14 15 string prd = "deleteperson"; 16 17 int res = sq. executeNonQuery (prd, paras, CommandType. storedProcedure); 18 19 if (res> 0) 20 {21 flag = true; 22} 23 24 return flag; 25}

 

In the SQLHelper class:

1 /// <summary> 2 // execute the add, delete, modify, and delete SQL statement with parameters 3 /// </summary> 4 /// <param name = "SQL"> Yes the executed SQL statement </param> 5 // <param name = "paras"> input parameter </param> 6 /// <returns> returns the affected number of rows </param name = "paras">/ returns> 7 public int ExecuteNonQuery (string SQL, sqlParameter [] paras, CommandType cmt) 8 {9 cmd = new SqlCommand (SQL, getcon (); 10 11 cmd. commandType = cmt; 12 13 cmd. parameters. addRange (paras); 15 16 return cmd. executeNonQuery (); 17}

 

Note: * Add the CommandType cmt in the ExecuteNonQuery method of the SQLHelper class, that is, to specify the type of the command to be executed. Then, you can replace the original SQL statement with the stored procedure, then, in the SQLHelper class, add cmd. commandType = cmt; specifies the type to be executed.

 

The preceding example uses a stored procedure to execute an SQL statement. Not too detailed, but the core code is written for later viewing.