Use stored procedures in C # (with input parameters)

1. Use stored procedures in SQL Server

Create procedure [DBO]. [sp_dia]
@ Age int,
@ Tot Int = 0 output
As
Select sage, sname
From students
Where sage = @ age;
Select @ tot = count (*)
From students
Where sage = @ age
Return @ tot

2. Create a console application project.

Using system;
Using system. Collections. Generic;
Using system. LINQ;
Using system. text;
Using system. Data;
Using system. Data. sqlclient;

Namespace callsp2
{
Class callsp2
{
Static void main (string [] ARGs)
{
Sqlconnection conn = new sqlconnection (@"
Server =./wb_james;
Integrated Security = sspi;
Database = Master ");
Try
{
Conn. open ();
Sqlcommand cmd = conn. createcommand ();
Cmd. commandtype = commandtype. storedprocedure;
Cmd. commandtext = "sp_dia ";
/////////
Sqlparameter inparm = cmd. Parameters. Add (
"@ Age", sqldbtype. INT );
Inparm. Direction = parameterdirection. input;
Inparm. value = 18;
///////////
Sqlparameter ouparm = cmd. Parameters. Add (
"@ Tot", sqldbtype. INT );
Ouparm. Direction = parameterdirection. output;
///////////////
Sqlparameter retval = cmd. Parameters. Add (
"Return_value", sqldbtype. INT );
Retval. Direction = parameterdirection. returnvalue;
///////////
Sqldatareader RDR = cmd. executereader ();
While (RDR. Read ())
{
Console. writeline ("{0} {1 }"
, RDR [0]. tostring (). padright (5), RDR [1]. tostring ());
}
RDR. Close ();

/////////
Console. writeline (
"The output parameter value is {0 }",
Cmd. Parameters ["@ age"]. value );
///////////
Console. writeline (
"The return value is {0 }",
Cmd. Parameters ["return_value"]. value );
}
Catch (system. Exception E)
{
Console. writeline (E. tostring ());
}
Finally
{
Conn. Close ();
}
}
}
}