The invocation of stored procedures is much used in B/s systems. The traditional method of calling is not only slow, but the code will expand with the increase of stored procedure, it is difficult to maintain. The new method solves these problems to some extent.
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
Compared 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 you are performing the first class of stored procedures, you would populate a DataSet with a DataAdapter, and then you can use the data grid control to present the results on the page, and if you are performing the second and third stored procedures, you do not need this procedure. You simply need to determine whether the operation completed successfully based on a specific return.
(1) Execute a stored procedure with no parameters as follows:
Copy Code code 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)):
Copy Code code 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;
(The code above is the same, the following is the code to be added)
Copy Code code as follows:
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 an output parameter:
Copy Code code as follows:
Sets the parameter value of the stored procedure, where @parametername is the parameter of the stored procedure.
param = new SqlParameter ("@ParameterName", sqldbtype.datetime);
Param. Direction = ParameterDirection.Output;
Param. Value = Convert.todatetime (inputdate); stored procedure parameter value;
Da. SELECTCOMMAND.PARAMETERS.ADD (param);
To get the return value of the parameters of the stored procedure:
Copy Code code as follows:
param = new SqlParameter ("@ParameterName", sqldbtype.datetime);
Param. Direction = ParameterDirection.ReturnValue;
Param. Value = Convert.todatetime (inputdate);
Da. SELECTCOMMAND.PARAMETERS.ADD (param);
Implementation: DataSet Myds=new DataSet ();
Da. Fill (myDS, "TableName");
From the above code we can see that, when the stored procedures are more or the parameters of the stored procedures for a long time, this method will greatly affect the speed of development; On the other hand, if the project is relatively large, then these functions for database logic in the future maintenance is also a great burden. So, is there an improved way to solve this problem? It is possible to invoke the corresponding stored procedure by simply passing in the name of a stored procedure when executing a stored procedure without parameters, and in the SQL Server database we can execute the stored procedure by typing the "stored Procedure name (parameter list)" string directly in the Query Analyzer. Is it possible to apply this idea to the application?
Then type the appropriate code in the compiler. The code is modified based on the code that invokes the stored procedure without parameters. The specific code is as follows:
Copy Code code 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 for the code to be more representative, the first and second arguments for the stored procedure to invoke are string types and the third parameter is an integral type. After the implementation of the discovery, can achieve the desired effect!
comparison of two invocation methods
By comparison, we can see that the second method has an obvious advantage, that is, it can improve the development speed, save development time, and the code is easy to maintain, to some extent, also reduce the system size. However, because the processing of stored procedure parameters is more general, if you want to get output parameters or get the return value of stored procedures, this method can not meet the need. However, this approach, after all, can give developers a much smaller portion of the code. If you do not need to get output parameters and return values, then almost "once and for all." Therefore, in the actual program development, this method still has a certain practical value.
A stored procedure call used to create a dataread;
Copy Code code as follows:
//database connection string
string connstr= "SERVER=LOCALHOST;DATABASE=STUIMS;UID=SA; Pwd=admin ";
//Establish connection
SqlConnection conn=new SqlConnection (ConnStr);
//Create query command
SqlCommand mycommand=new SqlCommand ("Stored Procedure name", conn);
//Call stored procedure name
Mycommand.commandtype=commandtype.storedprocedure;
//sets the parameter value of the stored procedure, where @id is the parameter of the stored procedure.
SqlParameter id=mycommand.parameters.add ("@id", Sqldbtype.ntext); The
ID. Value= stored procedure parameter values;
//execute command
SqlDataReader reader=mycommand.executereader ()//Read data
//or
SqlDataReader reader=mycommand.ex Ecutenonquery ()//Data Update
