Original address: http://www.51la.org/webjx/htmldata/2005-12-24/1135405777.html
Abstract Stored procedure calls are commonly used in b/s systems. The traditional calling method is not only slow, but the code will continue to expand as the number of stored procedures increases, making it difficult to maintain. The new method solves these problems to a certain extent.
Keywords asp.net; stored procedures In the process of using .net, database access is a very important part, especially in the construction process of b/s system, database operation has almost become an essential operation. Calling stored procedures to implement database operations is a method used by many programmers, and most programmers use stored procedures if they can, and rarely use SQL statements directly, so stored procedures are very useful and important.
Introduction to stored procedures
Simply put, a stored procedure is an encapsulated procedure composed of some SQL statements and control statements. It resides in the database and can be called by a client application or from another procedure or trigger. Its parameters can be passed and returned. Similar to function procedures in an application, stored procedures can be called by name, and they also have input and output parameters.
According to the different return value types, we can divide stored procedures into three categories: stored procedures that return recordsets, stored procedures that return numerical values (also called scalar stored procedures), and behavioral stored procedures. As the name implies, the execution result of a stored procedure that returns a record set is a record set. A typical example is to retrieve records that meet one or several conditions from the database; a stored procedure that returns a numerical value returns a value after execution, for example, in the database Execute a function or command with a return value; finally, the behavioral stored procedure is only used to implement a certain function of the database without a return value, such as update and delete operations in the database.
Benefits of using stored procedures
Compared with directly using SQL statements, directly calling stored procedures in an application has the following benefits:
(1) Reduce network traffic. There may not be a big difference in network traffic between calling a stored procedure with a small number of rows and directly calling SQL statements. However, if the stored procedure contains hundreds of SQL statements, its performance is definitely better than calling SQL statements one by one. much higher.
(2) Execution speed is faster. There are two reasons: First, when the stored procedure is created, the database has already parsed and optimized it once. Secondly, once the stored procedure is executed, a copy of the stored procedure will be retained in the memory, so that the next time the same stored procedure is executed, it can be called directly from the memory.
(3) Stronger adaptability: Since stored procedures access the database through stored procedures, database developers can make any changes to the database without changing the stored procedure interface, and these changes will not affect the database. Applications have an impact.
(4) Distributed work: The coding work of application and database can be carried out independently without suppressing each other.
From the above analysis, we can see that it is necessary to use stored procedures in applications.
Two Different Methods of Calling Stored Procedures
In order to highlight the advantages of the new method, let's first introduce the "official" method of calling stored procedures in .net. In addition, all sample programs in this article work on the sqlserver database. Other situations are similar and will not be explained one by one in the future. All examples in this article are in C# language.
To access the database in an application, the general steps are: first declare a database connection sqlconnection, and then declare a database command sqlcommand to execute sql statements and stored procedures. With these two objects, you can use different execution methods to achieve your goals according to your own needs. What needs to be added is that don’t forget to add the following reference statement to the page: using system.data.sqlclient.
As far as executing stored procedures is concerned, if the first type of stored procedure is executed, then a dataadapter must be used to fill the results into a dataset, and then the data grid control can be used to present the results on the page; if the executed If it is the second and third stored procedures, this procedure is not needed. You only need to determine whether the operation is successfully completed based on the specific return.
(1) The code to execute a stored procedure without parameters is as 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 just choose the appropriate way to perform the process here for different purposes.
(2) The code to execute a stored procedure with parameters is as follows (we can declare the function that calls the stored procedure as exeprocedure(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 above code is the same, the following is the code to be added)
param = new sqlparameter("@parametername", sqldbtype.datetime);
param.direction = parameterdirection.input;
param.value = convert.todatetime(inputdate);
da.selectcommand.parameters.add(param);
This adds an input parameter. If you need 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 get the return value of a parameter stored procedure:
param = new sqlparameter("@parametername", sqldbtype.datetime);
param.direction = parameterdirection.returnvalue;
param.value = convert.todatetime(inputdate);
da.selectcommand.parameters.add(param);
From the above code, we can see that when there are many stored procedures or there are many parameters in the stored procedures, this method will greatly affect the development speed; on the other hand, if the project is relatively large, then these functions for database logic will It will also be a big burden in future maintenance. So, is there an improved way to solve this problem? I thought that when executing a stored procedure without parameters, we only need to pass in the name of a stored procedure to call the corresponding stored procedure, and in the sqlserver database, we can directly type in the "stored procedure name (parameter list)" in the query analyzer. Stored procedures can be executed with such strings. So, can this idea be applied to applications?
So type the corresponding code in the compiler. These codes are modified based on the code that calls the stored procedure without parameters. The specific 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('para1','para2',para3)";
da.selectcommand.commandtype = commandtype.storedprocedure;
In order to make the code more representative, the first and second parameters of the stored procedure to be called are of string type, and the third parameter is of integer type. After execution, I found that the expected results can be achieved!
Comparison of the two calling methods Through comparison, we can see that the second method has an obvious advantage, that is, it can increase the development speed, save development time, and the code is easy to maintain, and it also reduces the system size to a certain extent. However, since the processing of stored procedure parameters is relatively general, if you want to obtain output parameters or get the return value of a stored procedure, this method cannot meet the needs. Even so, this method can allow developers to save a large part of the code. If you don't need to get the output parameters and return values, you can almost do it "once and for all". Therefore, in actual program development, this method still has certain practical value.