Review Stored Procedure

Review Stored Procedure

 

Stored Procedure (Stored Procedure) if you are using a stored procedure, it is very convenient to modify the SQL statement directly on the server, instead of modifying it in the program, and then saving it, stored Procedures can ensure data security to a certain extent.

1. stored procedures greatly enhance the functions and flexibility of the SQL language. stored procedures can be written using process control statements with high flexibility.

2. stored procedures can ensure data security and integrity. Through stored procedures, users without permissions can indirectly access the database under control to ensure data security. Through the storage structure, relevant actions can be taken together to maintain the integrity of the database.

3. The stored procedure can be executed quickly. Before running the stored procedure, the database has analyzed its syntax and provided an optimization execution plan, this compiled process can greatly improve the performance of SQL statements, and most of the execution of SQL statements has been completed.

4. To a certain extent, network traffic can be reduced. If it is an SQL statement, you must pass the SQL statement to the server and then execute the SQL statement.

5. You can control SQL statements in a centralized manner. For example, when a company's rules change, you only need to change the storage process on the server. Generally, the company's rules change frequently, if you put the computing program that embodies the rules into the application, when the company rules change, you need to modify the application a lot of work, you have to modify, release and install the application. If the rules are stored in the stored procedure, when the enterprise rules change, you only need to modify the stored procedure, and the application does not need to change.

-- ===================================================== ======

-- Author: Zhang shidong

-- Createdate:

-- Description: select the latest 10 news records.

-- ===================================================== ======

Createprocedure [DBO]. [news_selectnewnews]

 

As

Begin

Select Top10 N. ID, N. Title, N. createtime, C. [name], C. ID as caid

From News n

Inner join category C on N. caid = C. ID

Order by N. createtime DESC

End

 

When a stored procedure is executed, the stored procedure created in the database is handed over to the interface layer and sent to the Dal layer for processing. As long as commandtype = storedprodure is set, the database can process the stored procedure.

Publicdatatable test (string procname)

{

Datatabledt = new datatable ();

Cmd = newsqlcommand (procname, getconn (); (open the connection and pass in the Stored Procedure)

Cmd. commandtype = commandtype. storedprocedure; (differences between SQL statements and stored procedures)

Using (SDR = cmd. executereader (commandbehavior. closeconnection ))

{

DT. Load (SDR );

}

Returnsdr;

}