This satisfaction is called a stored procedure, similar to a function in other programming languages, which contains the SQL statement that can be combined with the programming configuration such as if, while, to be stored in the database. The stored procedure can be used to write code that handles transactions in the database.
It can do all things that SQL can do! ! !
Its unique features are:
1. You can accept the input parameters to return multiple values to the modified process in the form of an output parameter.
2, you can return the status value to the callout process or batch, indicate success or failure.
3. Other stored procedures can be called and they can be called.
4. Allow modular programming, that is, you can create them independently of the application, you can modify them without recompiling applications.
5. Allow execution at a faster speed. In the customer-server system, the use of the stored procedure can greatly improve performance.
6, reduce network traffic.
7, you can use the security mechanism.
In the program, StoredProcedure is stored separately. But it is associated with the database.
We can use the Visual Studio Server Explorer to view StoredProcedure.
When the CommandType property is set to Storedure, the CommandText property should be set to the name of the stored procedure. This command will perform this stored procedure when the "Execute) method is called.
cmd.commandtype = commandtype.storedProcedure;
cmd.comMandtext = "sp_select_allemployees";
Remember these two code? This is common in the source file of Components.
Although the stored procedure can be called by the stored procedure name prior to the parameter argument before the SQL statement, if the PARAMETERS collection of the ADO.NET Command object can be explicitly defined, the stored procedure parameters can be explicitly defined and the output parameters and return values can be explicitly defined. .
Parameter objects can be created using the Parameter constructor, or create it by calling the Add method of the Parameters collection of COMMAND. Parameters.Add uses the constructor parameter or an existing parameter object as an input.
For parameters other than the input parameter, you must set the parameterDirection property to specify the parameter type is InputOutput, Output or ReturnValue. By default, it is an input parameter.
See a actual code
Sqlcommand samplecmd = new sqlcommand ("SampleProc", NWINDCONN);
Samplecmd.commandtype = commandtype.storedProcedure;
SQLParameter Sampparm = Samplecmd.Parameters.Add ("Return_Value", SqldbType.InT);
Sampparm.direction = parameterDirection.ReturnValue;
SAMPPARM = Samplecmd.Parameters.Add ("@ INPUTPARM", SqldbType.nvarchar, 12);
Sampparm.Value = "Sample Value";
SAMPPARM = Samplecmd.Parameters.Add ("@ outputparm", sqldbtype.nvarchar, 28);
SAMPPARM.DIRECTION = parameterdirection.output; nwindconn.open ();
SqlDataReader SampReader = Samplecmd.executeReader ();
Console.writeLine ("{0}, {1}", SampReader.getname (0), SampReader.getname (1));
While (SampReader.Read ())
{
Console.writeline ("{0}, {1}", SampReader.getInt32 (0), SampReader.getstring (1));
}
SampReader.close ();
Nwindconn.close ();
Console.writeline ("@outputparm: {0}", samplecmd.parameters ["@ outputparm"]. Value);
Console.Writeline ("Return_Value: {0}", SampleCmd.Parameters ["Return_Value"]. Value);
ReturnValue: Used to capture the returnced value of StoredProcedure.
The stored procedure for returning the value using the Output parameter is very useful, they can be used to retrieve a few information from the database, but the information is not related to each other, or information is obtained in the result.
Learn about the application of StoredProcedure, they are generally divided into two parts:
1, StoredProcedure:
Alter procedure sp_dates_byemployeeid
(
@Employeeid Int,
@EDATE DATETIME OUTPUT,
@LDate DateTime Output
)
AS
/ * SET NOCOUNT ON * /
SELECT @ edate = min (orderdate)
From Orders
Where Employeeid = @ EmployeeID
Select @ ldate = max (orderdate)
From Orders
Where Employeeid = @ EmployeeID
Return
2, the application of the application:
cmd.commandtype = commandtype.storedProcedure;
cmd.commandtext = "sp_dates_byemployeid";
SQLParameter Parinput = cmd.parameters.add ("@ EmployeeID", SqldbType.It);
Parinput.direction = parameterdirection.input;
Parinput.Value = Convert.Toint32 (TXTEMPID2.TEXT);
SQLParameter Paroutput2 = cmd.parameters.add ("@ ldate", sqldbtype.datetime);
Paroutput2.direction = parameterDirection.output;
SQLParameter Paroutput1 = cmd.Parameters.Add ("@ edate", sqldbtype.datetime);
