Resolving methods to invoke stored procedures in asp.net

in use. NET, database access is a very important part, especially in the process of the construction of B/s system, database operation has become almost an essential operation. Stored procedures are useful and important when invoking stored procedures to implement database operations that are used by many programmers, and most programmers use stored procedures for the purpose of storing procedures and rarely use SQL statements directly.

Introduction to Stored Procedures

simply put, a stored procedure is a encapsulated process consisting of SQL statements and control statements that resides in a database, can be invoked by a client application, or invoked from another procedure or trigger. Its parameters can be passed and returned. Similar to function procedures in an application, stored procedures can be invoked by name, and they also have input and output parameters.

depending on the type of return value, we can divide a stored procedure into three categories: a stored procedure that returns a recordset, a stored procedure that returns a value (also known as a scalar stored procedure), and a behavior stored procedure. As the name suggests, the execution result of a stored procedure that returns a Recordset is a recordset, a typical example is retrieving a record from a database that meets one or more criteria, and returns a value after the stored procedure that returns the value, such as executing a function or command with a return value in the database; A behavioral stored procedure is used only to implement a function of a database without returning a value, such as an update and delete operation in a database.

benefits of using stored procedures

as opposed to using SQL statements directly, calling stored procedures directly in your application has the following benefits:

(1) Reduce the amount of network traffic. Calling a stored procedure with a low number of rows may not be very different from the network traffic that invokes the SQL statement directly, but if the stored procedure contains hundreds of rows of SQL statements, its performance is definitely much higher than that of a single calling SQL statement.

(2) faster execution speed. There are two reasons: first, the database has been parsed and optimized by the time the stored procedure was created. Second, once the stored procedure is executed, it retains a copy of the stored procedure in memory so that it can be invoked directly from memory the next time the same stored procedure is executed.

(3) Stronger adaptability: Because stored procedures access the database through stored procedures, database developers can make any changes to the database without altering the stored procedure interface, and these changes do not affect the application.

(4) Cloth type work: The coding work of the application and the database can be independently carried out separately, but will not suppress each other.

As you can see from the above analysis, it is necessary to use stored procedures in your application.

two different methods of stored procedure invocation

in order to highlight the advantages of the new method, first introduced in the. NET, the "official" method of invoking a stored procedure. In addition, all of the sample programs in this article work on the SQL Server database, and others are similar, and are no longer one by one descriptive. All examples in this article are in C # language.

The general step in accessing a database in an application is to declare a database connection SqlConnection first, and then declare a database command SqlCommand to execute the SQL statements and stored procedures. With these two objects, you can use different execution methods according to your own needs to achieve the goal. You need to add that you do not forget to include the following reference statement on the page: using System.Data.SqlClient.

In the case of executing a stored procedure, if the first type of stored procedure is executed, then a DataAdapter is used to populate the dataset, and then the data grid control can be used to present the result on the page, and if the second and third stored procedures are executed, You do not need this procedure, just to determine whether the operation completed successfully based on a specific return.

(1) Execute a stored procedure with no parameters 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 select the appropriate way to execute the process here for different purposes.

(2) The code that executes a stored procedure with parameters is as follows (we can declare the function that invokes 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 code above 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);

Total 2 page: previous 1 [2] Next page