Stored Procedure | data | Database one. 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 used as a security mechanism to make full use of
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 nodes in Server Explorer, as shown in Figure 1.
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
The code template above conforms to the simplified syntax rules for creating stored procedures, and the complete syntax rules are as follows:
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 to run the following diagram:
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 with input publisher information pops up in the IDE (as shown in Figure 3). 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.
Five To create a database application for a simple stored procedure:
Here we use the above stored procedure without parameters to a database application, which also uses the SqlDataAdapter object and DataSet object in Ado.net. The SqlDataAdapter object is linked together as a bridge between the SQL Server database and the DataSet object. The SqlDataAdapter object contains two commonly used methods: the Fill () method and the update () method. The fill () method can fetch the corresponding data from the database and populate it into the DataSet object, and the update () method is the name of the update dataset. Before calling the fill () method, we must set the SelectCommand property of the SqlDataAdapter object, which is actually a SqlCommand object. The SelectCommand property contains a valid SQL statement that can then be fetched from the database and populated into the DataSet object.
First, we create a Windows Forms application, the programming language is C #. After you create a new project in Visual Studio.NET, add a new class-publishers class to the project that encapsulates the business logic for connecting to the background database and getting the DataSet object. The steps are as follows:
1. Add the necessary namespace reference: using System.Data.SqlClient;
2. Add the following necessary variables to the class:
Private SqlConnection cnpubs;
Private SqlCommand cmdpubs;
Private SqlDataAdapter dapubs;
Private DataSet dspubs;
3. Complete the connection to the backend database in the constructor of the class, and obtain business logic such as SqlDataAdapter object:
Public Publishers ()
{
Try
{
Create a database Connection object
cnpubs = new SqlConnection ("server=localhost;integrated security=true;database=pubs");
Creates a SqlCommand object and indicates that its command type is stored procedure
Cmdpubs = new SqlCommand ();
Cmdpubs.connection = cnpubs;
Cmdpubs.commandtype = CommandType.StoredProcedure;
Cmdpubs.commandtext = "Up_getpublisherinfo";
Creates a SqlDataAdapter object that sets its SelectCommand property to the SqlCommand object above
Dapubs = new SqlDataAdapter ();
Dapubs.selectcommand = cmdpubs;
Create a DataSet object
dspubs = new DataSet ();
}
catch (Exception) {}
}
4. Finally, the class is provided with a getpublisherinfo () method that populates the DataSet object with the SqlDataAdapter object and returns the filled DataSet object. The method is as follows (it is noteworthy that the SqlDataAdapter object implicitly opens the database connection and closes the connection implicitly after fetching the data, which means that the DataSet object works in connectionless mode.) And when you explicitly open a database connection and get the data, the SqlDataAdapter object does not close the connection:
Public DataSet Getpublisherinfo ()
