C # How to call a stored procedure

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:
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 )):
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)
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:

// 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:
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:
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;

[Csharp]
<Span style = "font-size: 12px;"> // 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 </span>

Author: newd_2011