Application of stored procedures in. NET database applications

I. Preface:

Stored Procedure is a set of SQL statements for specific functions. It is compiled and stored in the database. You can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters. Stored procedures are an important object in databases. Any well-designed database application should use stored procedures. In general, stored procedures have the following advantages:

◆ Standard component programming is allowed for stored procedures.

◆ The stored procedure can achieve fast execution speed.

◆ Stored procedures can reduce network traffic.

◆ Stored procedures can be fully utilized as a security mechanism.

The author of this article will introduce it to you. NET database application stored procedures, and how to associate it with ADO.. Net sqldataadapter object, DataSet object, and so on. the overall performance of the. NET database application.

Ii. system requirements:

Development tools: Visual Studio. NET

Database Management System: SQL Server 2000 (which includes the pubs database used by the sample program)

3. Create a simple stored procedure:

Here I will introduce how to use Visual Studio. NET ide To create a stored procedure. Use Visual Studio. net IDE is very easy and intuitive to create a stored procedure. As long as you direct to the pubs database in the server resource manager and expand nodes, you will find various database objects including the stored procedure.

Right-click the Stored Procedure node to bring up a menu containing the "Create stored procedure" command. After a stored procedure is created, the following code template is displayed in the code editing window of IDE:

 

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. 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]

The meaning of each parameter is not described here. If you are interested, you can refer to the information about the SQL Server 2000 database management system.

Below I will give a brief introduction to each syntax component in this code template. Create procedure declares the creation of a stored procedure, followed by the name of the stored procedure. "/*…… */"Is the parameter of the stored procedure, which can include input and output parameters. The content after the as keyword is the main 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 an integer status value can be returned to the caller. Next we will create a simple stored procedure without parameters and use it:

 

CREATE PROCEDURE dbo.up_GetPublisherInfoAS SELECT pub_id, pub_name, city, state, countryFROM publishersRETURN

After creating the stored procedure, save it. After saving, the node corresponding to the stored procedure appears in the server resource manager. Note that the create keyword in the code editing window is changed to the alter keyword, which is used to change any existing stored procedure. To run the stored procedure, click its node and select "Run stored procedure" from the context menu ".

4. Create a stored procedure with parameters:

We have created a simple stored procedure without parameters. Many stored procedures with parameters are often used in actual applications. Stored Procedures with parameters are generally used to update or insert data. We can use the same operation method to create a stored procedure with parameters:

 

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

In the above Code for creating a stored procedure, we add a "@" sign before the name to declare the local variable-parameter of the stored procedure, and also declare the type of each parameter, determine the direction value of each parameter, that is, whether the parameter is input, output, input, output, or return value. You can call the stored procedure by using the corresponding stored procedure name and correct and valid parameters. In addition, you can use the output keyword to add output parameters to parameters. For more information, see the preceding syntax rules. Output parameters can be returned to the caller.

The above stored procedure can update the information of the corresponding publisher in the publishers table. You can click the node of the stored procedure and select "Run stored procedure" from the context menu to execute it. Once executed, a dialog box for entering publisher information is displayed in IDE. In this dialog box, enter correct and valid update information. Note that the pub_id value must exist in the original table. Then, click "OK" to update the data.

5. Create a database application for a simple stored procedure:

The following describes how to use the stored procedure without parameters to create a database application. The sqldataadapter object and DataSet object in ADO. NET are also used. The sqldataadapter object serves as a bridge between the SQL Server database and the DataSet object. The sqldataadapter object contains two common methods: the fill () method and the update () method. The fill () method can obtain the corresponding data from the database and fill it into the DataSet object. The update () method, as its name implies, is used to update the dataset. Before calling the fill () method, we must set the selectcommand attribute of the sqldataadapter object, which is actually a sqlcommand object. The selectcommand attribute contains valid SQL statements, which can be obtained from the database and filled into the DataSet object.

First, we create a Windows Forms Application with the programming language C #. Create a new project in Visual Studio. NET and add a new publishers class to the project. This class encapsulates the business logic of connecting to the background database and obtaining dataset objects. The procedure is 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 background database in the constructor of this class to obtain the business logic such as the sqldataadapter object:

 

Public Publishers () {try {// create a database connection object cnpubs = new sqlconnection ("Server = localhost; Integrated Security = true; database = pubs "); // create a sqlcommand object and specify its command type as stored procedure export pubs = new sqlcommand (); export pubs. connection = cnpubs; Publish pubs. commandtype = commandtype. storedprocedure; implements pubs. commandtext = "up_getpublisherinfo"; // create a sqldataadapter object and set its selectcommand attribute to the above sqlcommand object dapubs = new sqldataadapter (); dapubs. selectcommand = synchronized pubs; // create a DataSet object dspubs = new dataset ();} catch (exception ){}}

4. finally, a getpublisherinfo () method is provided for this class. This method fills the DataSet object with the sqldataadapter object and returns the filled DataSet object. The method is as follows (it is worth noting that: the sqldataadapter object implicitly opens the database connection and closes the connection after obtaining data. This means that the DataSet object works in non-connection mode. When you explicitly open a database connection and obtain data, the sqldataadapter object does not close the connection ):

 

Public dataset getpublisherinfo () {// call the fill () method of the sqldataadapter object and return the DataSet object dapubs. Fill (dspubs); Return dspubs ;}

After the publishers class is designed, we add a DataGrid Control to the main form and use it to display data in the DataSet object. First, add the following member variables to the main form class:

 

Private publishers pubs; private dataset Ds; then, modify the constructor of the main form class as follows: public form1 () {//// required // initializecomponent (); // todo: after initializecomponent calls, add Any constructor code // pubs = new publishers (); DS = pubs. getpublisherinfo (); datagrid1.datasource = Ds. tables [0];}

As soon as the application starts the DataGrid control of the main form, the corresponding data obtained from the pubs database using the stored procedure without parameters described above is displayed.

6. Create Database applications with parameter stored procedures

We have created a stored procedure application without parameters. Next we will create a more complex database application. In actual database applications, we often need to obtain data and update, insert, or delete data. In this case, we need to use the stored procedure with parameters. When using the sqldataadapter object, we will call its update () method. The Update () method automatically performs operations based on the changes in each record in the able object in the DataSet object. The sqldataadapter object also contains attributes such as updatecommand, insertcommand, and deletecommand. These attributes are actually sqlcommand objects. The Update () method selects the corresponding Attribute Based on the operation type.

When using stored procedures with parameters to create database applications, we generally use the sqlparameter class, which encapsulates various attributes and methods related to SQL parameters. The attributes include parametername, sqldbtype, direction, size, value, sourcecolumn, and sourceversion. Among them, parametername, sqldbtype, direction, size and other attributes are used to match the parameters defined in the stored procedure. For example, the sqlparameter object defined below is used to match the "@ pub_id" parameter in the previously defined up_updatepublisherinfo stored procedure.

Sqlparameter updparam = new sqlparameter ("@ pub_id", sqldbtype. Char, 4 );

In the above definition, although the direction attribute is not explicitly given, its default value is input, which meets our needs. If the direction attribute of a sqlparameter object is InputOutput, output, or returnvalue, the direction attribute must be explicitly stated, for example, the following code clearly states that the direction attribute of a sqlparameter object is output.

Oparam. Direction = parameterdirection. output;

The sourcecolumn attribute is used to match the datacolumn object in a able object. This matching can implicitly import the required sqlparameter object when the update () method is called to update the datatable object. If this attribute is not declared during definition, you must explicitly describe the sourcecolumn attribute of the sqlparameter object in the code.

The default value of the sourceversion attribute is the current value in the corresponding field of the datarow object, that is, the value to be updated to the database. Of course, the sourceversion attribute can also point to the original value in the corresponding field of the datarow object, that is, the initial value obtained from the database. In the database transaction processing system, data synchronization is very important. Next we will establish a storage process that can detect data synchronization.

 

CREATE PROCEDURE dbo.up_UpdatePublisherName (@pub_id char(4),@pub_name varchar(40), @Original_pub_name varchar(40) ) AS if exists(select pub_id 　from publishers where (pub_id = @pub_id) AND (pub_name = @Original_pub_name)) Begin　UPDATE publishers SET pub_name = @pub_name 　WHERE (pub_id = @pub_id)EndRETURN

Next, we call the stored procedure in the above application to update the name of the publisher. First, improve the business logic class-publishers class on the basis of the original application:

1. Add a new sqlcommand object, which can be used as the updatecommand attribute of the sqldataadapter object:

Private sqlcommand cmdupdpubs;

2. Update the constructor Publishers () of this class and add the following content:

 

// Create another sqlcommand object, which references the Stored Procedure cmdupdpubs = new sqlcommand (); cmdupdpubs. connection = cnpubs; cmdupdpubs. commandtype = commandtype. storedprocedure; cmdupdpubs. commandtext = "up_updatepublishername"; // Add the necessary parameter cmdupdpubs for the above sqlcommand object. parameters. add ("@ pub_id", sqldbtype. char, 4, "pub_id"); cmdupdpubs. parameters. add ("@ pub_name", sqldbtype. varchar, 40, "pub_name"); sqlparameter updparam = new sqlparameter ("@ original_pub_name", sqldbtype. varchar, 40, "pub_name"); updparam. sourceversion = datarowversion. original; cmdupdpubs. parameters. add (updparam); 3. specify the updatecommand attribute of the sqldataadapter object as the above-defined sqlcommand object: dapubs. updatecommand = cmdupdpubs; 4. add method updatepublishername (): Public void updatepublishername (Dataset dschanges) {// update all changes to dapubs. update (dschanges );}

After the business logic class of the application is complete, add a button named "Update dataset" on the main form, and add the Event Response Function of this button as follows:

 

private void button1_Click(object sender, System.EventArgs e) { if( ds.HasChanges() ) { pubs.UpdatePublisherName( ds.GetChanges() ); ds.Clear(); ds = pubs.GetPublisherInfo(); } }

So far, the application's business logic class and main form class have been updated, and the current application can update the relevant content in the database based on the user's switch. After updating the data, you can open the corresponding table in SQL Server and verify whether the data has been updated successfully.

VII. Summary:

This article introduces the basic knowledge of stored procedures and how to build data-driven applications in. NET database applications by combining sqldataadapter objects and dataset objects. In this article, we have applied two types of stored procedures: one is a simple stored procedure without parameters, and the other is a stored procedure with parameters, sqlparameter objects must also be used to call this type of stored procedure. At the same time, it is not difficult to find that the data update business logic is encapsulated in the storage process as a good design method, which can improve the manageability, scalability and database security of applications. Similarly, the business logic for inserting and deleting data can also be encapsulated in the stored procedure and used in applications in a similar way. Finally, I hope this article will help you a lot.

 

Reference from: http://9host.cn/asp.net/20074730313.html