C # two different stored procedure calls and Comparison

Stored Procedure calls are often used in the B/S system. Traditional calling methods are not only slow, but the code will continue to expand as the storage process increases, making it difficult to maintain them. The new method solves these problems to some extent.
　
In the process of using. NET, database access is a very important part, especially during the construction of the B/S system, database operations have almost become an essential operation. Calling stored procedures to implement database operations enables many programmers to use the methods. Most programmers use stored procedures when they can use stored procedures, and seldom directly use SQL statements, therefore, stored procedures are very useful and important.

Stored Procedure Overview
Simply put, stored procedures are encapsulated by SQL statements and control statements. They reside in databases and can be called by customer applications, it can also be called from another process or trigger. Its parameters can be passed and returned. Similar to function procedures in applications, stored procedures can be called by name, and they also have input and output parameters.
Depending on the type of the returned value, we can divide the stored procedure into three types: the stored procedure of the returned record set, the stored procedure of the returned value (also known as the scalar stored procedure), and the behavior stored procedure. As the name suggests, the execution result of the stored procedure that returns the record set is a record set. A typical example is to retrieve records that meet one or more conditions from the database; after the stored procedure of the returned value is executed, a value is returned. For example, a function or command with a returned value is executed in the database. Finally, the stored procedure is only used to implement a function of the database, there is no return value, for example, update or delete operations in the database.

Benefits of using Stored Procedures
Compared with using SQL statements directly, calling stored procedures in applications has the following benefits:
(1) reduce network traffic. The network traffic for calling a stored procedure with few rows of data may not be significantly different from that for directly calling SQL statements. However, if the Stored Procedure contains hundreds of rows of SQL statements, therefore, its performance is definitely much higher than that of a single SQL statement.
(2) Faster execution. There are two reasons: first, the database has been parsed and optimized when the stored procedure was created. Second, once the stored procedure is executed, a stored procedure will be retained in the memory, so that the next time you execute the same stored procedure, you can directly call it from the memory.
(3) better adaptability: Because stored procedures access databases through stored procedures, therefore, database developers can make any changes to the database without modifying the stored procedure interface, without affecting the application.
(4) protocol work: the coding work of applications and databases can be performed independently without mutual suppression.
From the analysis above, we can see that it is necessary to use stored procedures in applications.

Two different stored procedure calling methods
To highlight the advantages of the new method, first introduce the "official" Method for calling the stored procedure in. NET. In addition, all the sample programs in this article work on the SqlServer database. Other situations are similar and will not be described in detail later. All examples in this article use the C # language.
To access the database in an application, the general steps are: first declare a database to connect to 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 as needed. Do not forget to add the following reference Statement on the page: using System. Data. SqlClient.
For the execution of the stored procedure, if the first type of stored procedure is executed, you must use a DataAdapter to fill the result into a DataSet, then you can use the data grid control to display the results on the page. If the second and third stored procedures are executed, this process is not required, you only need to determine whether the operation is successful based on the specific response.
(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, you only need to select an appropriate method to execute this process for different purposes.
(2) The code for executing a stored procedure with parameters is as follows (we can declare the function that calls the 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, and the code to be added is as follows)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 );

In this way, an input parameter is added. To add output parameters:Copy codeThe Code is as follows: // set the parameter value of the stored procedure. @ ParameterName indicates 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 obtain the return value of the parameter store process: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 );
Run DataSet myds = new DataSet ();
Da. Fill (myds, "tableName ");

From the code above, we can see that when there are many stored procedures or a large number of stored procedure parameters, this method will greatly affect the development speed. On the other hand, if the project is relatively large, therefore, the functions used for database logic will be a huge burden for future maintenance. Is there an improvement solution to this problem? You can call a stored procedure by passing in the name of a stored procedure when executing a stored procedure without parameters, in addition, in the SqlServer database, we can directly input the "Stored Procedure name (parameter list)" string in the query analyzer to execute the stored procedure, can this idea be applied to applications?
Therefore, type the corresponding code in the compiler. These codes are modified based on the code that calls the stored procedure without parameters. The 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;

To make the code more representative, the first and second parameters of the stored procedure to be called are string types, and the third parameter is an integer type. After the execution, the expected results can be fully achieved!

Comparison of the two call Methods
Through comparison, we can see that the second method has a very obvious advantage, that is, it can improve the development speed, save development time, and the code is easy to maintain, to a certain extent, the system size is also reduced. However, since the processing of stored procedure parameters is general, this method cannot meet the requirements if you want to obtain output parameters or get the returned values of stored procedures. Even so, this method can, after all, reduce a lot of code for developers. If you do not need to obtain the output parameters and return values, you can "once and for all ". Therefore, in actual program development, this method has some practical value.
Call the stored procedure used to create 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;
// Set 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 Value;
// Execute the command
SqlDataReader reader = myCommand. ExecuteReader (); // read data
// Or
SqlDataReader reader = myCommand. ExecuteNonQuery (); // data update