Comparison of stored procedures and SQL statements and calling methods of stored procedures in C #

As an SQL stored procedure and. net newbie, the following guidance is still very useful. I have just come into contact with these things and collected some related things, which makes it easier for newbie to get started. Of course,
To be proficient and skilled in application, you still need to look at more and deeper information. Please do not laugh.
The following content is from the network for reference only:
--------
Storage
Resentment between the stored procedures and SQL statements

The programmer said: stored procedures are still SQL statements, which is a problem.
Currently, Jianghu has three schools: SQL
Statements flow, advocating the use of SQL statements, one school is the storage process School, advocating the use of stored procedures to speak, and the other school is the moderate School, emphasizing harmony, so the first two schools were so loud that the moderate school was busy. Now
The SQL School has a large number of ORM AIDS led by nhibloud. Recently, the store process team has its own killer secret. The two schools have been fighting for each other for a long time.
Storage
The pursuit speed of the storage process School is the greatest cost for the people in the storage process school. Another secret of the SQL statement school attacked by the stored procedure is security.
All, but no one is connected to the dynamic SQL of the string, which will become invalid.
SQL statement schools are well-qualified schools, but most of them are new users.
Process dispatching. Many developers of Java help use SQL statements, while those of stored procedures use Microsoft statements.
However, there are also some weaknesses in stored procedures. portability is one of them, and scalability is another.
Scalability, because generally large Web applications use multiple Web servers, and then use dual-database servers for Hot Standby. One of them is turned on, but is idle, this is only to solve the single point of failure problem,
It is a situation where multiple web application servers and single database servers are formed. If all operations are implemented using stored procedures, the database server will be overloaded and the Web server load will be insufficient,
The expansion of the database server is very troublesome, and the investment is large, the program needs to be modified, and the investment in Web servers is relatively cheap and easier to expand.
Where are people in the rivers and lakes?
I am more inclined to put more burden into the program, and reduce the burden on the database, because as long as the database can survive, it is easier to add Web servers, but once
When the database server fails, we have to cry. For general reports, I use stored procedures for packaging some complex result sets. I can find an ORM for general queries and cuid operations.

In fact, it is mainly about the weakness of the stored procedure.
1) In terms of deployment, the stored procedure is more difficult than SQL
2) In terms of performance, the stored procedure is higher than SQL
3)
From security, stored procedures are more vulnerable than SQL injection.
The process can reduce the amount of network data transmitted and put all the computing on the server.

Front-end programs can directly access data
Database tables can be read and written directly to the database tables in SQL language;

For security, you can program and use stored procedures to read and write data tables.
Front-end programs directly access these stored procedures, rather than database tables;

A safer method is to add a database access layer between the front-end program and the database.
Data read/write requests are submitted to the middle layer. The middle layer is connected to the database for actual operations and the results are sent to the front end. This method can avoid the possibility that the front-end machine can directly connect to the database, and the database is more secure.
All.

C # two different methods for calling stored procedures

========================================================== ================

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
In the SQL Server 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 the application, the general step is: first declare a database to connect to sqlconnection, and then declare a database command
Sqlcommand, used 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 a stored procedure, if the first type of stored procedure is executed,
Dataadapter fills in the result to a dataset, and then you can use the data grid control to display the result on the page. If you are executing the second and third stored procedures, you do not need
In this process, 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:

Param = new sqlparameter ("@ parametername", sqldbtype. datetime );
Param. Direction
= Parameterdirection. output;
Param. value =
Convert. todatetime (inputdate );
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 );
　
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, then these are used for data
The database logic functions are also a huge burden for future maintenance. Is there an improvement solution to this problem? When you want to execute a stored procedure without parameters, you only need to pass in a stored procedure
You can call the corresponding stored procedure by name. In the sqlserver database, you can directly enter the "Stored Procedure name (parameter list)" in the query analyzer) the string can be saved.
During the storage process, 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 strings.
Type. The third parameter is an integer. After the execution, the expected results can be fully achieved!

SQL Server
2005 examples and instructions on the use of stored procedure return values in the help document

Return
The statement unconditionally terminates the query, stored procedure, or batch processing. The statements after the return statement in the stored procedure or batch processing are not executed.

When a return statement is used in a stored procedure, this statement can specify the integer that is returned to the calling application, batch processing, or process. If return
If no value is specified, the stored procedure returns 0.

Most stored procedures use the Returned Code as usual to indicate that the Stored Procedure succeeded or failed. If no error occurs, the stored procedure returns 0. Any non-zero value indicates that an error has occurred. For example:

Use
Adventureworks;
Go
-- Create a procedure that takes one input
Parameter
-- And returns one output parameter and a return code.
Create
Procedure sampleprocedure @ employeeidparm int,
@ Maxtotal int
Output
As
-- Declare and initialize a variable to hold @ error.
Declare
@ Errorsave int;
Set @ errorsave = 0;
-- Do a select using
Input parameter.
Select C. firstname, C. lastname, E. Title
From
HumanResources. employee as E
Join person. Contact as C on E. contactid =
C. contactid
Where employeeid = @ employeeidparm;
-- Save any
Nonzero @ error value.
If (@ error <> 0)
Set @ errorsave =
@ Error;
-- Set a value in the output parameter.
Select @ maxtotal
= Max (totaldue)
From sales. salesorderheader;
If (@ error <>
0)
Set @ errorsave = @ error;
-- Returns 0 if neither select
Statement had
-- An error, Otherwise returns the last error.
Return
@ Errorsave;
Go

The Returned Code can be retrieved to an integer variable in the transact-SQL batch processing or stored procedure that executes the stored procedure:

Declare
@ Returnstatus int;
Declare @ maxtotalvariable int;
Declare
@ Maxtotal int;
Execute @ returnstatus = sampleprocedure
@ Employeeidparm = 65, @ maxtotal = @ maxtotalvariable output;
Print'
';
Print 'Return code = '+ Cast (@ returnstatus as char (10 ));
Print
'Maximum order total = '+ Cast (@ maxtotalvariable as char (15 ));
Go

Applications that call stored procedures can bind the parameter tags corresponding to the returned code to integer variables.