C # Call the SQL stored procedure (example with input and output parameters)

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;
}
}
}