Two different stored procedure calls and comparisons in c
- 2020-05-19 04:35:23
- OfStack
Stored procedure calls are used a lot on B/S systems. Not only is the traditional method of invocation slow, but the code will swell with the number of stored procedures, making it difficult to maintain. The new method solves these problems to a certain extent.
In the process of using.NET, database access is a very important part, especially in the construction of B/S system, database operation has almost become an indispensable operation. Calling stored procedures to implement database operations makes many programmers use methods, and most programmers are able to use stored procedures on the use of stored procedures, rarely directly using SQL statements, so stored procedures are very useful and important.
Introduction to stored procedures
Simply put, a stored procedure is an encapsulated procedure consisting of a set of SQL statements and control statements that reside in a database and can be called by a client application or from another procedure or trigger. Its arguments can be passed and returned. Like 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 classify stored procedures into three categories: stored procedures that return a recordset, stored procedures that return a numeric value (also known as a scalar stored procedure), and behavioral stored procedures. As the name implies, the stored procedure that returns a recordset is executed with a result of one recordset. A typical example is to retrieve a record that meets one or more criteria from a database. A stored procedure that returns a value returns a value after execution, such as executing a function or command with a return value in the database. Finally, the behavior stored procedure is only used to implement a function of the database and does not return a value, such as update and delete operations in the database.
Benefits of using stored procedures
Instead of using SQL statements directly, calling stored procedures directly in your application has the following benefits:
(1) reduce network traffic. Calling a small number of lines of stored procedure may not be much different from the network traffic of calling SQL statements directly, but if the stored procedure contains hundreds of lines of SQL statements, its performance is definitely much better than calling SQL statements one by one.
(2) faster execution. There are two reasons: first, at the time the stored procedure is created, the database has parsed and optimized it once. Second, once a stored procedure 1 is executed, a copy of the procedure is kept in memory so that the next time the same stored procedure is executed, it can be called directly from memory.
(3) greater flexibility: because stored procedure access to the database is done through stored procedures, database developers can make any changes to the database without changing the stored procedure interface without affecting the application.
(4) distributed work: the coding of the application and the database can be done independently, without suppressing each other.
As you can see from the analysis above, it is necessary to use stored procedures in your applications.
Two different stored procedure invocation methods
To highlight the advantages of the new method, I'll first introduce the "official" method for calling stored procedures in.NET. In addition, all of the sample programs in this article work on the SqlServer database, but other situations are similar and will not be covered later. All the examples in this article are in the C# language.
To access the database in your application, the general steps are: first declare a database connection 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 to achieve your goals according to your own needs. As a side note, don't forget to add the following reference statement to the page: using System.Data.SqlClient.
In the case of executing a stored procedure, if you are executing a class 1 stored procedure, you will populate the result into an DataSet with an DataAdapter, and then you will be able to render the result on the page using a data grid control. If you are performing the second and third stored procedures, you do not need this procedure; you simply need to determine whether the operation has completed successfully based on the specific return.
(1) the code for executing a stored procedure without parameters is as follows:
You can then simply choose the appropriate way to perform this procedure for a different purpose.
(2) the code for executing a stored procedure with parameters is as follows (we can declare the function calling the stored procedure as ExeProcedure(string inputdate)) :
(the above code is the same, the following is the code to add)
This adds an input parameter. If output parameters need to be added:
To obtain the return value of the reference process:
As we can see from the above code, this approach can greatly affect the speed of development when there are more stored procedures or more stored procedure parameters. On the other hand, if the project is large, the functions used for the database logic will also be a big burden in the future maintenance. So, is there an improved way to solve this problem? Think no parameters in the implementation of the stored procedures only need to pass into a stored procedure can invoke the name of the corresponding stored procedure, and in the SqlServer database we can directly type in the query analyzer "stored procedure name (parameter list)" kind of string can execute stored procedures, so, if I can apply this idea to the application?
Type the corresponding code into the compiler. This code is based on the code that calls a stored procedure with no arguments. The specific code is as follows:
To make the code more representative, the first and second arguments of the stored procedure to be called are of string type, and the third argument is of integer type. After the implementation of the discovery, can completely achieve the desired effect!
A comparison of the two calling methods
By comparison, we can see that the second method has one obvious advantage, that is, it can improve the development speed, save the development time, the code is easy to maintain, and it also reduces the system size to a certain extent. However, because of the general treatment of stored procedure parameters, this method is not sufficient if you want to get the output parameters or the return value of the stored procedure. Still, this approach can save developers a significant portion of their code. If you don't need to get the output parameters and return values, you can almost do "1 for good." Therefore, in the actual program development, this method still has 1 definite practical value.
Stored procedure call to create DataRead;
In the process of using.NET, database access is a very important part, especially in the construction of B/S system, database operation has almost become an indispensable operation. Calling stored procedures to implement database operations makes many programmers use methods, and most programmers are able to use stored procedures on the use of stored procedures, rarely directly using SQL statements, so stored procedures are very useful and important.
Introduction to stored procedures
Simply put, a stored procedure is an encapsulated procedure consisting of a set of SQL statements and control statements that reside in a database and can be called by a client application or from another procedure or trigger. Its arguments can be passed and returned. Like 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 classify stored procedures into three categories: stored procedures that return a recordset, stored procedures that return a numeric value (also known as a scalar stored procedure), and behavioral stored procedures. As the name implies, the stored procedure that returns a recordset is executed with a result of one recordset. A typical example is to retrieve a record that meets one or more criteria from a database. A stored procedure that returns a value returns a value after execution, such as executing a function or command with a return value in the database. Finally, the behavior stored procedure is only used to implement a function of the database and does not return a value, such as update and delete operations in the database.
Benefits of using stored procedures
Instead of using SQL statements directly, calling stored procedures directly in your application has the following benefits:
(1) reduce network traffic. Calling a small number of lines of stored procedure may not be much different from the network traffic of calling SQL statements directly, but if the stored procedure contains hundreds of lines of SQL statements, its performance is definitely much better than calling SQL statements one by one.
(2) faster execution. There are two reasons: first, at the time the stored procedure is created, the database has parsed and optimized it once. Second, once a stored procedure 1 is executed, a copy of the procedure is kept in memory so that the next time the same stored procedure is executed, it can be called directly from memory.
(3) greater flexibility: because stored procedure access to the database is done through stored procedures, database developers can make any changes to the database without changing the stored procedure interface without affecting the application.
(4) distributed work: the coding of the application and the database can be done independently, without suppressing each other.
As you can see from the analysis above, it is necessary to use stored procedures in your applications.
Two different stored procedure invocation methods
To highlight the advantages of the new method, I'll first introduce the "official" method for calling stored procedures in.NET. In addition, all of the sample programs in this article work on the SqlServer database, but other situations are similar and will not be covered later. All the examples in this article are in the C# language.
To access the database in your application, the general steps are: first declare a database connection 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 to achieve your goals according to your own needs. As a side note, don't forget to add the following reference statement to the page: using System.Data.SqlClient.
In the case of executing a stored procedure, if you are executing a class 1 stored procedure, you will populate the result into an DataSet with an DataAdapter, and then you will be able to render the result on the page using a data grid control. If you are performing the second and third stored procedures, you do not need this procedure; you simply need to determine whether the operation has completed successfully based on the specific return.
(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;
You can then simply choose the appropriate way to perform this procedure for a different purpose.
(2) the code for executing a stored procedure with parameters is as follows (we can declare the function calling 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 above code is the same, the following is the code to add)
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 output parameters need to be added:
// Sets the parameter values of the stored procedure , Among them @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 obtain the return value of the reference process:
param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction = ParameterDirection.ReturnValue;
param.Value = Convert.ToDateTime(inputdate);
da.SelectCommand.Parameters.Add(param);
Perform: DataSet myds=new DataSet();
da.Fill(myds,"tableName");
As we can see from the above code, this approach can greatly affect the speed of development when there are more stored procedures or more stored procedure parameters. On the other hand, if the project is large, the functions used for the database logic will also be a big burden in the future maintenance. So, is there an improved way to solve this problem? Think no parameters in the implementation of the stored procedures only need to pass into a stored procedure can invoke the name of the corresponding stored procedure, and in the SqlServer database we can directly type in the query analyzer "stored procedure name (parameter list)" kind of string can execute stored procedures, so, if I can apply this idea to the application?
Type the corresponding code into the compiler. This code is based on the code that calls a stored procedure with no arguments. The specific 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 arguments of the stored procedure to be called are of string type, and the third argument is of integer type. After the implementation of the discovery, can completely achieve the desired effect!
A comparison of the two calling methods
By comparison, we can see that the second method has one obvious advantage, that is, it can improve the development speed, save the development time, the code is easy to maintain, and it also reduces the system size to a certain extent. However, because of the general treatment of stored procedure parameters, this method is not sufficient if you want to get the output parameters or the return value of the stored procedure. Still, this approach can save developers a significant portion of their code. If you don't need to get the output parameters and return values, you can almost do "1 for good." Therefore, in the actual program development, this method still has 1 definite practical value.
Stored procedure call to create DataRead;
// Database connection string
string connStr="Server=localhost;database=stuIMS;uid=sa;pwd=admin";
// Establish a connection
SqlConnection conn=new SqlConnection(connStr);
// Create query command
SqlCommand myCommand=new SqlCommand(" Stored procedure name ",conn);
// Call the stored procedure name
myCommand.CommandType=CommandType.StoredProcedure;
// Sets the parameter values of the stored procedure , Among them @id Is the parameter of the stored procedure .
SqlParameter id=myCommand.Parameters.Add("@id",SqlDbType.NText);
id.Value= Stored procedure parameter values ;
// Execute the command
SqlDataReader reader=myCommand.ExecuteReader();// Read the data
// or
SqlDataReader reader=myCommand.ExecuteNonQuery();// Data update