Sharing the data collected from. Net to. net over the past year-stored procedures in B/S (II)

Stored Procedures in B/S

In use. NetIn the process, database access is a very important part, especially inB/SDuring system construction, database operations have almost become an essential operation. Many database operations are performed by calling stored procedures.ProgramIn addition, most programmers use stored procedures when they can use stored procedures.SQLTherefore, stored procedures are very useful and important.

Stored Procedure Overview

Simply put, the stored procedure is composedSQLA encapsulated process composed of statements and control statements. It resides in the database and can be called by the customer application or by another process or trigger. Its parameters can be passed and returned. Similar to function procedures in applications, stored procedures can be called by name, and they also have input and output parameters.

Depending on the type of returned values, we can divide stored procedures into three types: stored procedures that return record sets, Return the Stored Procedure (also called a scalar Stored Procedure) of the numeric value and the stored procedure of the behavior. As the name suggests, the execution result of the stored procedure that returns the record set is a record set. A typical example is to retrieve records that meet one or more conditions from the database; after the stored procedure of the returned value is executed, a value is returned. For example, a function or command with a returned value is executed in the database. Finally, the stored procedure is only used to implement a function of the database, there is no return value, for example, update or delete operations in the database.

Benefits of using Stored Procedures

compared with direct use SQL statement, directly calling stored procedures in applications has the following benefits:

(1) reduces network traffic. Call a stored procedure with few rows and directly call SQL the network traffic of a statement may not be significantly different, however, if the Stored Procedure contains hundreds of rows SQL statement, therefore, its performance is definitely better than that of a single call SQL the statement is much higher.

(2)The execution speed is faster. There are two reasons: first, the database has been parsed and optimized when the stored procedure was created. Second, once the stored procedure is executed, a stored procedure will be retained in the memory, so that the next time you execute the same stored procedure, you can directly call it from the memory.

(3)Better adaptability: Because stored procedures access databases through stored procedures, database developers can make any changes to the database without modifying the Stored Procedure interfaces, these changes do not affect the application.

(4)Protocol work: the coding work of applications and databases can be performed independently without mutual suppression.

From the analysis above, we can see that it is necessary to use stored procedures in applications.

Two different stored procedure calling methods

to highlight the advantages of the new method, we will first introduce it in . net " official " method. In addition, all the sample programs in this article work in sqlserver similar to other databases, I will not describe them one by one later. All examples in this article use C # language.

to access the database in an application, the general steps are as follows: first declare a database connection sqlconnection , then declare a database command sqlcommand , run SQL statements and stored procedures. With these two objects, you can use different execution methods as needed. Do not forget to add the following reference Statement on the page: using system. data. sqlclient .

For the execution of a stored procedure, if the first type of stored procedure is executed,DataadapterFill in the result toDatasetAnd then you can use the data grid control to display the results on the page. If the second and third stored procedures are executed, this process is not required, you only need to determine whether the operation is successful based on the specific response.

(1)Execute a stored procedure without ParametersCodeAs follows:

Sqlconnection conn = new sqlconnection ( " Connectionstring " );
Sqldataadapter da = new sqldataadapter ();
Da. selectcommand = new sqlcommand ();
Da. selectcommand. Connection = conn;
Da. selectcommand. commandtext = "nameofprocedure ";
Da. selectcommand. commandtype = commandtype. storedprocedure;
Then, you only need to select an appropriate method to execute this process for different purposes.

(2)The code for executing a stored procedure with parameters is as follows (we can declare the function that calls the stored procedureExeprocedure (string inputdate)):

Sqlconnection conn = new sqlconnection ( " Connectionstring " );
Sqldataadapter da = new sqldataadapter ();
Da. selectcommand = new sqlcommand ();
Da. selectcommand. Connection = conn;
Da. selectcommand. commandtext = "nameofprocedure ";
Da. selectcommand. commandtype = commandtype. storedprocedure;
(The code above is the same, and the code to be added is as follows)
Param = new sqlparameter ("@ parametername", sqldbtype. datetime );
Param. Direction = parameterdirection. input;
Param. value = convert. todatetime (inputdate );
Da. selectcommand. Parameters. Add (PARAM );
In this way, an input parameter is added. To add output parameters:

Param = new sqlparameter ("@ parametername", sqldbtype. datetime );
Param. Direction = parameterdirection. output;
Param. value = convert. todatetime (inputdate );
Da. selectcommand. Parameters. Add (PARAM );
To obtain the return value of the parameter store process:

Param = new sqlparameter ("@ parametername", sqldbtype. datetime );
Param. Direction = parameterdirection. returnvalue;
Param. value = convert. todatetime (inputdate );
Da. selectcommand. Parameters. Add (PARAM );
From the code above, we can see that when there are many stored procedures or a large number of stored procedure parameters, this method will greatly affect the development speed. On the other hand, if the project is relatively large, therefore, the functions used for database logic will be a huge burden for future maintenance. Is there an improvement solution to this problem? You can call a stored procedure by passing in the name of a stored procedure when executing a stored procedure without parameters. Sqlserver In the database, we can directly input " Stored Procedure name (parameter list) " The sample string can be used to execute the stored procedure. Can this idea be applied to applications?

Therefore, type the corresponding code in the compiler. These codes are modified based on the code that calls the stored procedure without parameters. The Code is as follows:

Sqlconnection conn = new sqlconnection ( " Connectionstring " );
Sqldataadapter da = new sqldataadapter ();
Da. selectcommand = new sqlcommand ();
Da. selectcommand. Connection = conn;
Da. selectcommand. commandtext = "nameofprocedure ('Para 1 ' ,'Para 2 ' , Para3)";
Da. selectcommand. commandtype = commandtype. storedprocedure;
To make the code more representative, the first and second parameters of the stored procedure to be called are string types, and the third parameter is an integer type. After the execution, the expected results can be fully achieved!

Comparison of the two call Methods

Through comparison, we can see that the second method has a very obvious advantage, that is, it can improve the development speed, save development time, and the code is easy to maintain, to a certain extent, the system size is also reduced. However, since the processing of stored procedure parameters is general, this method cannot meet the requirements if you want to obtain output parameters or get the returned values of stored procedures. Even so, this method can, after all, reduce a lot of code for developers. If you do not need to obtain the output parameters and return values, you can almost " Once and for all " . Therefore, in actual program development, this method has some practical value. Write by xingbaifang