C # Call the stored procedure with input/output parameters

The previous article describes how to control thread events in the instant messaging system. This article describes how to call the stored procedure in C. The principle of reaching the peak is to try to describe the problem with a short code :)

1. Stored Procedures in SQL Server

Create procedure sp_test @ ln varchar (10), @ MSG varchar (80) Output
As
Declare @ flag int
Declare @ un varchar (80)
Set @ flag = 99

Select @ un = username from msg_userinfo where loginname = @ ln

Set @ MSG = "this message from sp_test :"
Select @ MSG = + @ MSG + @ un
 
Return @ flag
Go

2. C # Call the Stored Procedure

Private void testsp ()
{
Sqlcommand _ cmd = new sqlcommand ();
Dbbase DB = dbbase. getinstance ();
_ Cmd. Connection = dB. Conn;
_ Cmd. commandtype = commandtype. storedprocedure;


_ Cmd. commandtext = "sp_test"; // SQL Server procedure name

// Enter the login name
Sqlparameter UN = new sqlparameter ();
Un. parametername = "@ ln ";
Un. dbtype = dbtype. String;
Un. Direction = parameterdirection. input;
Un. sourceversion = datarowversion. Current;
_ Cmd. Parameters. Add (un );

_ Cmd. Parameters ["@ ln"]. value = "zsb ";

// Return_value Parameter
Sqlparameter _ return_value = new sqlparameter ();
_ Return_value.parametername = "@ return_value ";
_ Return_value.dbtype = dbtype. int32;
_ Return_value.direction = parameterdirection. returnvalue;
_ Return_value.sourceversion = datarowversion. Current;
_ Cmd. Parameters. Add (_ return_value );

// Return message
Sqlparameter outpara = new sqlparameter ();
Outpara = new sqlparameter ("@ MSG", sqldbtype. varchar, 100 );
Outpara. Direction = parameterdirection. output;
_ Cmd. Parameters. Add (outpara );
_ Cmd. executenonquery ();

Console. writeline (INT) _ cmd. Parameters ["@ return_value"]. value );

Console. writeline (_ cmd. Parameters ["@ MSG"]. value. tostring ());

 

// Username parameter

}