C # Gets the return and output parameter values of the stored procedure

First, do not use SQLHelper.cs and other Help class

1. Get return value
Program code
Stored Procedures
Create PROCEDURE MYSQL
@a int,
@b int
As
return @a + @b
GO
SqlConnection conn = new SqlConnection (configurationmanager.connectionstrings["LocalSqlServer"]. ToString ());
Conn. Open ();
SqlCommand mycommand = new SqlCommand ("MYSQL", conn);
myCommand.CommandType = CommandType.StoredProcedure;
MYCOMMAND.PARAMETERS.ADD (New SqlParameter ("@a", SqlDbType.Int));
mycommand.parameters["@a"]. Value = 10;
MYCOMMAND.PARAMETERS.ADD (New SqlParameter ("@b", SqlDbType.Int));
mycommand.parameters["@b"]. Value = 20;
MYCOMMAND.PARAMETERS.ADD (New SqlParameter ("@return", SqlDbType.Int));
mycommand.parameters["@return"]. Direction = ParameterDirection.ReturnValue;
Mycommand.executenonquery ();
Response.Write (mycommand.parameters["@return"). Value.tostring ());

2. Obtain output parameter values
Program code
Stored Procedures
Create PROCEDURE MYSQL
@a int,
@b int,
@c int Output
As
Set @c = @a + @b
GO
SqlConnection conn = new SqlConnection (configurationmanager.connectionstrings["LocalSqlServer"]. ToString ());
Conn. Open ();
SqlCommand mycommand = new SqlCommand ("MYSQL", conn);
myCommand.CommandType = CommandType.StoredProcedure;
MYCOMMAND.PARAMETERS.ADD (New SqlParameter ("@a", SqlDbType.Int));
mycommand.parameters["@a"]. Value = 20;
MYCOMMAND.PARAMETERS.ADD (New SqlParameter ("@b", SqlDbType.Int));
mycommand.parameters["@b"]. Value = 20;
MYCOMMAND.PARAMETERS.ADD (New SqlParameter ("@c", SqlDbType.Int));
mycommand.parameters["@c"]. Direction = ParameterDirection.Output;
Mycommand.executenonquery ();
Response.Write (mycommand.parameters["@c"). Value.tostring ());

---------------------------------------------------------------------------------------------------------------
The following code goes from the network:
C # receives the stored procedure return value:
public static int User_add (User us)
{
int iRet;
SqlConnection conn = new SqlConnection (CONN_STR);
SqlCommand cmd = new SqlCommand ("User_add", conn);
Cmd.commandtype = CommandType.StoredProcedure;
Cmd. Parameters.addwithvalue ("@UName", US. UName);
Cmd. Parameters.addwithvalue ("@UPass", US. UPass);
Cmd. Parameters.addwithvalue ("@PassQuestion", US. Passquestion);
Cmd. Parameters.addwithvalue ("@PassKey", US. passkey);
Cmd. Parameters.addwithvalue ("@Email", US. Email);
Cmd. Parameters.addwithvalue ("@RName", US. Rname);
Cmd. Parameters.addwithvalue ("@Area", US. area);
Cmd. Parameters.addwithvalue ("@Address", US. Address);
Cmd. Parameters.addwithvalue ("@ZipCodes", US. ZipCodes);
Cmd. Parameters.addwithvalue ("@Phone", US. Phone);
Cmd. Parameters.addwithvalue ("@QQ", US. QQ);
Cmd. Parameters.Add ("@RETURN_VALUE", ""). Direction = ParameterDirection.ReturnValue;
Try
{
Conn. Open ();
Cmd. ExecuteNonQuery ();
IRet = (int) cmd. parameters["@RETURN_VALUE"]. Value;
}
catch (SqlException ex)
{
Throw ex;
}
Finally
{
Conn. Close ();
}
return iRet;
}
C # receives stored procedure output parameters:
public static decimal cart_useramount (int UID)
{
Decimal IRet;
SqlConnection conn = new SqlConnection (CONN_STR);
SqlCommand cmd = new SqlCommand ("Cart_useramount", conn);
Cmd.commandtype = CommandType.StoredProcedure;
Cmd. Parameters.addwithvalue ("@UID", UID);
Cmd. Parameters.Add ("@Amount", Sqldbtype.decimal). Direction=parameterdirection.output;
Try
{
Conn. Open ();
Cmd. ExecuteNonQuery ();
IRet = (decimal) cmd. parameters["@Amount"]. Value;
}
catch (SqlException ex)
{
Throw ex;
}

Second, with SQLHelper.cs and other Help class, the same idea

such as: (Posted part of the code)

String readercode = Txt_reader_code. Text;
String bookcode = Txt_book_code. Text;
Decimal money = Convert.todecimal (txt_price. Text);
DateTime borrowdate = Convert.todatetime (DateTime.Now.ToShortDateString ());
 datatable table = new Dbutility.booktype (). Gettablebybookbarcode (Bookcode);
Double borrowday;
Double getday = 0d;
if (table! = null && table. Rows.Count > 0)
{
    if (double). TryParse (table. rows[0]["Borrowday"]. ToString (), out borrowday))
    {
          getday = Borrowday;
    }
}
Else
{
     pub.util.alertpostback (page, "failed to get book borrowing Days");
     return;
}
DateTime returndate = Convert.todatetime (DateTime.Now.AddDays (getday). ToShortDateString ());
String readername = Txt_name. Text.trim ();
String bookname = Txt_book_name. Text.trim ();

sqlparameter[] Parameters = {
New SqlParameter ("@readerBarCode", SqlDbType.VarChar),
New SqlParameter ("@bookBarCode", SqlDbType.VarChar),
New SqlParameter ("@hire", Sqldbtype.money),
New SqlParameter ("@borrowDate", Sqldbtype.datetime),
New SqlParameter ("@returnDate", Sqldbtype.datetime),
New SqlParameter ("@readerName", SqlDbType.VarChar),
New SqlParameter ("@bookName", SqlDbType.VarChar),
New SqlParameter ("@return", SqlDbType.Int)//Add a return parameter
};
Parameters[0]. Value = Readercode;
PARAMETERS[1]. Value = Bookcode;
PARAMETERS[2]. Value = money;
PARAMETERS[3]. Value = borrowdate;
PARAMETERS[4]. Value = returndate;
PARAMETERS[5]. Value = Readername;
PARAMETERS[6]. Value = BookName;
PARAMETERS[7]. Direction = ParameterDirection.ReturnValue; Declares that this parameter is a return type

DBUtility.SQLHelper.ExecuteNonQuery (DBUtility.SQLHelper.BookConn, CommandType.StoredProcedure, "P_bookborrow", parameters);

int num = Convert.ToInt32 (parameters[7]. Value.tostring ());//Extract the value of the stored procedure return parameter, success is 0, unsuccessful is-1

if (num = = 0)
{Pub.Util.AlertPostBack (page, "borrowing success");
}
Else
Pub.Util.AlertPostBack (page, "borrowing failed");

Stored Procedures

CREATE PROCEDURE P_bookborrow (@readerBarCode varchar, @bookBarCode varchar, @hire money, @borrowDate datetime,@ returndate datetime, @bookName varchar (in), @readerName varchar (20))
As
BEGIN Tran
INSERT into T_reader_book values (@readerBarCode, @bookBarCode, @borrowDate, @returnDate, @bookName, @readerName)
if (@ @error <>0)
Begin
Rollback Tran
Return-1
End
Update t_readerinfo set Borrownum=borrownum+1,[money]=[money][email protected] where Readerbarcode = @readerBarCode
if (@ @error <>0)
Begin
Rollback Tran
Return-1
End
Update T_bookinfo set stock=stock-1 where Bookbarcode[email protected]
if (@ @error <>0)
Begin
Rollback Tran
Return-1
End
INSERT into logs values (GetDate (), ' Barcode number: '[email protected]+ ' readers, borrowing the book Number: '[email protected]+ ' books ')
if (@ @error <>0)
Begin
Rollback Tran
Return-1
End
Commit Tran
if (@ @error <>0)
Return-1
Else
return 0

GO

C # Gets the return and output parameter values of the stored procedure