Application of SQL stored procedures in the. NET Database

The author of this article will show you how to use the Visual Studio.NET IDE to create stored procedures and. NET database application, and how to use it in conjunction with SqlDataAdapter objects, dataset objects, and so on in ado.net to improve the overall performance of. NET database applications.

A Objective

A stored procedure (Stored Procedure) is a set of SQL statements that are compiled and stored in a database in order to complete a specific function. The user executes it by specifying the name of the stored procedure and giving the parameter (if the stored procedure has parameters). Stored procedures are an important object in a database, and any well-designed database application should use stored procedures. In general, stored procedures have the following advantages:

Stored procedures allow standard component programming.

Stored procedures can achieve faster execution speed.

Stored procedures can reduce network traffic.

Stored procedures can be fully exploited as a security mechanism.

The author of this article will introduce to you. NET database application, and how to use it in conjunction with SqlDataAdapter objects, dataset objects, and so on in ado.net to improve the overall performance of. NET database applications.

Two System Requirements

Development tools: Visual Studio.NET

Database management system: SQL Server 2000 (which contains the pubs database used by the sample program)

Three To create a simple stored procedure

Here I'll show you how to use the Visual Studio.NET IDE to create stored procedures. Using the Visual Studio.NET IDE to create stored procedures is very easy and intuitive, and you will find a variety of database objects, including stored procedures, as long as you are directed to the pubs database and expand the nodes in Server Explorer.

Right-click on the stored Procedure node to pop up a menu that contains the command for new stored procedure. After you create a new stored procedure, the Code editing window in the IDE appears with the following code template:

CREATE PROCEDURE dbo.StoredProcedure1
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT )
*/
AS
/* SET NOCOUNT ON */
RETURN
上面的代码模板符合简化的创建存储过程的语法规则，完整的语法规则如下：
CREATE PROC [ EDURE ] procedure_name [ ; number ]
[ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]

Limited to space, the meaning of each parameter is not much described here, interested readers can refer to the SQL Server 2000 database management System information.

Here's a brief introduction to the various grammatical components in the code template. The CREATE PROCEDURE declaration creates a stored procedure followed by the name of the stored procedure. The ingredient in "/*......*/" is the parameter of the stored procedure, which can include input parameters and output parameters. The AS keyword follows the body part of the stored procedure, which is any number and type of SQL statements contained in the stored procedure. The return keyword indicates that the stored procedure ends and returns an integral state value to the caller. Here we'll create a simple stored procedure with no parameters and use it:

CREATE PROCEDURE dbo.up_GetPublisherInfo
AS
SELECT pub_id, pub_name, city, state, country
FROM publishers
RETURN

After you create the above stored procedure, save the. Once saved, the node that corresponds to the stored procedure appears in Server Explorer. Also, note that the Create keyword in the Code editing window becomes the ALTER keyword, which is used to change any existing stored procedure. To run the stored procedure above, simply click on its node and select "Run Stored Procedures" in the right-click pop-up menu.

Four To create a stored procedure with parameters

We have created a simple stored procedure with no parameters, and many stored procedures with parameters are often used in practical applications. Stored procedures with parameters are typically used to update data or to insert data. Here's how we can create a stored procedure with parameters using the same procedure:

CREATE PROCEDURE dbo.up_UpdatePublisherInfo
(
@pub_id char (4),
@pub_name varchar (40),
@city varchar (20),
@state char (2),
@country varchar (30)
)
AS
UPDATE publishers
SET pub_name = @pub_name, city = @city, state = @state,
　 country = @country
WHERE ( pub_id = @pub_id )
RETURN

In the above code to create the stored procedure, we declare the local variable-parameter of the stored procedure by adding a "@" flag before the name. It also declares the types of each parameter, determines the directional value of each parameter, and indicates whether the parameter is an input or output type or a return value type. The user can invoke the stored procedure name and the correct valid parameters. Also, you can add output parameters to parameters by using the Output keyword, refer to the syntax rules above. Output-type parameters can return information to the caller.

The above stored procedure can update the information of the corresponding publisher in the publishers table. You can execute it by clicking on the node of the stored procedure and selecting "Run Stored Procedures" in the right-click pop-up menu. Once executed, a dialog box for entering publisher information pops up in the IDE. Fill in the correct and valid update information in the dialog box, note that the value of pub_id must exist in the original table, and then click OK to update the data.