Theory:
The following cmdobj. Is the defined sqlcommand object
1. Obtain the return value of the entire stored procedure function returned by the Return Statement in the stored procedure:
// Obtain the return value of the stored procedure, define a parameter, and specify that it is used to accept the return value
Cmdobj. Parameters. Add ("return_value", sqldbtype. INT). Direction = parameterdirection. returnvalue;
The return value is finally obtained by obtaining this parameter.
Int revalue = int. parse (cmdobj. Parameters ["return_value"]. value. tostring ());
2. Obtain the output parameter value of the stored procedure:
Define SQL parameters. The specified type is output.
Cmdobj. Parameters. Add ("@ outvalue", sqldbtype. varchar). Direction = parameterdirection. output;
Obtain the output parameter value
String outvalue = cmdobj. Parameters ["@ outvalue"]. value. tostring ();
In summary, the key point is to specify the parameter's direction attribute, which is specified using parameterdirection. Four types can be specified,
Input,
Output,
Both Input and Output,
Accept return
========================================================== ==================================
Example:
/* *
* Stored Procedure
*
Create procedure querystunamebyid
(
@ Stuid varchar (10), -- enter the Parameter
@ Stuname varchar (10) Output -- output parameter
)
As
Select @ stuname = stuname from stuinfo where stuid = @ stuid
*
*/
Using System;
Using System. Collections. Generic;
Using System. text;
Using System. Data;
Using System. Data. sqlclient;
Namespace Storeproducetest
{
Public Class Program
{
Static Void Main ( String [] ARGs)
{
Operater op = New Operater ();
String Name = Op. querystunamebyid ( " 1234 " );
Console. writeline ( String . Format ( " The name of the student whose student ID is 1234 is {0} " , Name ));
}
}
Public Class Operater
{
Private String Constr = " Server =.; database = user; uid = sa; Pwd = 1234 " ;
Private Sqlconnection sqlcon = Null ;
Private Sqlcommand sqlcomm = Null ;
Sqldatareader Dr = Null ;
Public String Querystunamebyid ( String ID)
{
String Name = "" ;
Try
{
Using (Sqlcon = New Sqlconnection (constr ))
{
Sqlcon. open ();
Sqlcomm = New Sqlcommand ( " Querystunamebyid " , Sqlcon );
// Set the command type to Stored Procedure
Sqlcomm. commandtype = Commandtype. storedprocedure;
// Set parameters
Sqlcomm. Parameters. Add ( " @ Stuid " , Sqldbtype. varchar );
// Note that the output parameter must be set. Otherwise, the default size is 0,
Sqlcomm. Parameters. Add ( " @ Stuname " , Sqldbtype. varchar, 10 );
// Set the parameter type to output parameter, which is input by default,
Sqlcomm. Parameters [ " @ Stuname " ]. Direction = Parameterdirection. output;
// Assign values to parameters
Sqlcomm. Parameters [ " @ Stuid " ]. Value = " 1234 " ;
// Run
Sqlcomm. executenonquery ();
// Obtain the value of the output parameter and assign the value to the name. Note that the object type is obtained here, and the corresponding type rotation is required.
Name = Sqlcomm. Parameters [ " @ Stuname " ]. Value. tostring ();
}
}
Catch (Exception ex)
{
Console. writeline (ex. tostring ());
}
Return Name;
}
}
}