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

Source: Internet
Author: User

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

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.