Two different types of stored procedure calls and comparisons in C #

Introduction to Stored Procedures
Simply put, a stored procedure is a encapsulated process consisting of some SQL statements and control statements that reside in a database, can be called by a client application, or can be called 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.
Depending on the type of return value, we can divide the 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 implies, the execution result of a stored procedure that returns a Recordset is a recordset, a typical example of retrieving a record from a database that matches one or several conditions; Returns a value after the stored procedure for the value is executed, such as executing a function or command with a return value in the database; The behavior stored procedure is used only to implement a function of the database, and there is no return value, such as update and delete operations in the database.

benefits of using stored procedures
Invoking a stored procedure directly in an application with respect to directly using SQL statements has the following benefits:
(1) Reduce network traffic. Calling a stored procedure with a low number of rows may not be very different from the network traffic that calls the SQL statement directly, but if the stored procedure contains hundreds of rows of SQL statements, the performance is definitely much higher than a single call to the SQL statement.
(2) Execution speed is faster. There are two reasons: first, when the stored procedure is created, the database has been parsed and optimized once. Second, once the stored procedure is executed, a copy of the stored procedure is kept in memory so that the next time the same stored procedure is executed, it can be called directly from memory.
(3) Stronger adaptability: Because the stored procedure accesses the database through stored procedures, the database developer can make any changes to the database without altering the stored procedure interface, and these changes do not affect the application.
(4) Fabric work: The coding of applications and databases can be done independently, without suppressing each other.
As you can see from the above analysis, it is necessary to use stored procedures in your application.

two different methods for calling stored procedures
To highlight the advantages of the new method, first introduce the. NET calls the "official" method of the stored procedure. In addition, all of the sample programs in this article work on the SQL Server database, others are similar and are no longer one by one explained. All examples in this article are in the C # language.
To access the database in an application, the general step 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 to achieve the goal according to your own needs. It should be added that you should not forget to add 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 type of stored procedure, you populate the result with a DataAdapter into a dataset, and then you can use the DataGrid control to present the results to the page, and if you are performing a second and third stored procedure, you do not need this procedure. You only need to determine whether the operation completed successfully based on a specific return.
(1) The code for executing a stored procedure without parameters is as follows:

Copy CodeThe 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";
Da.SelectCommand.CommandType = CommandType.StoredProcedure;

Then just choose the appropriate way to perform the procedure here for different purposes.
(2) The code to execute a stored procedure with parameters is as follows (we can declare a function that invokes a stored procedure as exeprocedure (String inputdate)):

Copy CodeThe 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";
Da.SelectCommand.CommandType = CommandType.StoredProcedure;

(The code above is the same, the following is the code to be added)

Copy CodeThe code is 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 CodeThe code is 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 values;
Da. SELECTCOMMAND.PARAMETERS.ADD (param);

To get the return value of the stored procedure:

Copy CodeThe code is as follows:
param = new SqlParameter ("@ParameterName", sqldbtype.datetime);
Param. Direction = ParameterDirection.ReturnValue;
Param. Value = Convert.todatetime (inputdate);
Da. SELECTCOMMAND.PARAMETERS.ADD (param);
Execute: DataSet myds=new DataSet ();
Da. Fill (myDS, "tableName");

From the above code, we can see that when the stored procedure is more or the parameters of the stored procedures are relatively long, 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 thought that the stored procedure can be called only by passing in the name of a stored procedure when executing a stored procedure without parameters, and we can execute the stored procedure directly in the SQL Server database by typing the "stored Procedure name (parameter list)" Kind of string in the query parser. Is it possible to apply this idea to the application?
Then type the appropriate code in the compiler. The code is changed based on the code that calls the stored procedure without parameters. The specific code is as follows:

Copy CodeThe 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;

For the code to be more representative, the first and second parameters of the stored procedure to be called are string types, and the third argument is an integral type. After the implementation of the discovery, can achieve the desired effect!

comparison of two calling methods
By comparison we can see that the second method has a clear advantage, that is, can improve the development speed, save development time, and the code is easy to maintain, to a certain extent, also reduce the system size. However, because the processing parameters of the stored procedure is more general, if you want to get the output parameters or get the return value of the stored procedure, this method can not satisfy the need. Nonetheless, this approach can, after all, allow developers to have a large part of the code. If you do not need to get output parameters and return values, you can almost "once and for all". So in the actual program development, this method still has certain practical value.
The stored procedure call used to create the dataread;

Copy CodeThe code is as follows:
Database connection string
String connstr= "Server=localhost;database=stuims;uid=sa;pwd=admin";
Establish a connection
SqlConnection conn=new SqlConnection (CONNSTR);
Create a query command
SqlCommand mycommand=new SqlCommand ("Stored Procedure name", conn);
Call the 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);
Id. Value= stored procedure parameter values;
Execute command
SqlDataReader Reader=mycommand.executereader ();//Read data
Or
SqlDataReader reader=mycommand.executenonquery ();//Data update

Two different types of stored procedure calls and comparisons in C #