[Summary] There are many modes of storage procedures in the B / S system. The traditional calling method is not only slow, but the code will continue to expand as the stored procedure has increased, it is difficult to maintain. The new method solves these problems to some extent. Key words ASP.NET; stored procedures In the process of use .NET, database access is a very important part, especially during the construction of the B / S system, database operations are almost an essential operation. Calling a stored procedure To implement the database operation, how many programmers are used, and most of the programmers can use the stored procedure to use the stored procedure, rarely use the SQL statement, so the stored procedure is useful and important. Introduction to the stored procedure, the stored procedure is a packaged process consisting of some SQL statements and control statements, which resides in the database, can be called by the client application, or can be called from another process or trigger. Its parameters can be passed and returned. Similar to the function process in the application, the stored procedure can be called by name, and they also have input parameters and output parameters. Depending on the type of return value, we can divide the stored procedure into three categories: return the memory process of the record set, return the number of stored procedures (also known as scalar stored procedures), and behavior stored procedures. As the name suggests, the execution result of the stored procedure returns the record set is a recordset. The typical example is to retrieve a record that meets one or several conditions in the database; the stored procedure for returning value is completed, for example in the database Perform a function or command with the return value; Finally, the behavior stored procedure is just a feature used to implement the database, and there is no return value, such as updates and delete operations in the database. The benefits of using the stored procedure are directly invoked directly in the application directly using the SQL statement, and there is the following benefits: (1) Reduce network traffic. Calling a number of stored procedures with a number of rows and a network traffic that directly calls the SQL statement may not have a big difference, but if the stored procedure contains a hundred lines of SQL statements, its performance is definitely more than one call SQL statement Much higher. (2) Faster execution speed. There are two reasons: First, when the stored procedure is created, the database has been parsed and optimized. Second, once the stored procedure is executed, a stored procedure will be retained in the memory so that the same stored procedure is performed next time, it can be called directly from memory. (3) Strong adaptability: Since the stored procedure is performed by the stored procedure, the database developer can make any changes to the database without changing the stored procedure interface, and these changes will not The application has an impact. (4) Put work: The encoding operation of the application and database can be done independently without compression. It can be seen from the above analysis that it is necessary to use the stored procedure in the application. Two different stored procedure calling methods In order to highlight the advantages of a new method, first introduce the "official" method of calling the stored procedure in .NET. In addition, all sample programs in this paper work on the SQL Server database, and other situations are similar, and will not be described later. All examples of this article use C # language. To access the database in the application, the general step is: First declare a database connection SQLCONNECTION, then declare a database command SQLCommand, used to execute the SQL statement and stored procedures. With these two objects, you can use different implementations according to your needs. Need to add, don't forget to add the following reference statement on the page: use system.data.sqlclient.
In order to perform the stored procedure, if the first class stored procedure is executed, then use a DataAdapter to populate the result into a DataSet, then you can use the data grid control to present the result on the page; if executed It is the second and third stored procedures, then this process is not required, and only the operation is required to be successfully completed according to a particular return determination. (1) performing a code 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 select this process as long as you do this, for different purposes. (2) Performing a parameter stored procedure as follows (we can declare the function "of the stored procedure as ExEProcedure (SQLCONNECTION CONN = New SqlConnection); SqlDataAdapter Da = New SqlDataAdapter (); da.SelectCommand = new SqlCommand (); da.SelectCommand.Connection = conn; da.SelectCommand.CommandText = "NameOfProcedure"; da.SelectCommand.CommandType = CommandType.StoredProcedure; (same as above code, the following code is 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.
