Simple Web three-tier architecture system "fourth Edition"

The last time I wrote a third edition,

Because before inadvertently saw a video, that is, all the SQL statements in the system are made into stored procedures. The SQL execution speed of the system can be optimized to a large extent. So Baidu has a little detail problem, then I put all the SQL statements, have been made into a stored procedure.

In fact, the write stored procedure is also very simple (perhaps a simple system, do not need too complex SQL statements), only need to establish a stored procedure in the database, and then called in the program in the line.

1. First establish the stored procedure:

Expand the database used → programmability → stored procedures. Then right-click and create a new stored procedure.

1 SetAnsi_nulls on2 SetQuoted_identifier on3 GO4 -- =============================================5 --Author:h_f_us6 --Create date:2014 December 29 09:18:017 --Description: Delete an employee information8 -- =============================================9 ALTER PROCEDURE [dbo].[Deleteperson]Ten  One     @id int     A  -  as - BEGIN the  -     Delete  fromPersonwhereId= @id -  - END

The above is a well-established stored procedure:

1. Where ALTER PROCEDURE is followed by the name of the stored procedure, it can be arbitrarily selected as long as it conforms to the SQL naming specification.

2.as above, the parameters to be used in the SQL statement.

3.between BEGIN and END is the SQL statement to execute.

This can be called in the system code.

In the Persondao class:

1         /// <summary>2         ///Delete Employee Information3         /// </summary>4         /// <param name= "id" >to delete an employee's ID</param>5         /// <returns>return True or False value: If the deletion is true, the deletion is unsuccessful if it is a fake display</returns>6          Public BOOLDelete (person p)7         {8             BOOLFlag =false;9 TenSqlparameter[] Paras =Newsqlparameter[] One             { A                 NewSqlParameter ("@id", P.id) -             }; -  the             stringPRD ="Deleteperson"; -  -             intres =Sq. ExecuteNonQuery(PRD, paras, commandtype.storedprocedure); -  +             if(Res >0) -             { +Flag =true; A             } at  -             returnFlag; -}

In the SqlHelper class:

1         /// <summary>2         ///execute the SQL statement with parameter additions and deletions3         /// </summary>4         /// <param name= "SQL" >the SQL statement to execute</param>5         /// <param Name= "Paras" >parameters passed in</param>6         /// <returns>returns the number of rows affected</returns>7          Public intExecuteNonQuery (stringSQL, Sqlparameter[] paras, commandtype CMT)8         {9cmd =NewSqlCommand (SQL, Getcon ());Ten  OneCmd.commandtype =CMT; A  -Cmd. Parameters.addrange (paras); the  -             returncmd. ExecuteNonQuery (); -}

Note: * Added to the ExecuteNonQuery method in the SqlHelper class, CommandType CMT, that is, to indicate what type to execute, then you can replace the original SQL statement with the stored procedure, and then in the SqlHelper class, add the cmd . CommandType = CMT; Specifies the type to perform.

The above is the execution of SQL statements using stored procedures. The writing is not too detailed, but the core code is written out for easy viewing later.

Simple Web three-tier architecture system "fourth Edition"