Using C code to get the return value of stored procedure

  • 2021-08-28 20:51:41
  • OfStack

Needless to say, post C # code directly to everyone.


/// <summary>
///  Executes the stored procedure and returns "  Return value "
/// </summary>
/// <param name="storedProcName"> Stored procedure name </param>
/// <param name="parameters"> Stored procedure parameters </param>
/// <returns> Return value of executing stored procedure </returns>
public static int RunProcedureWithReturn(string storedProcName, IDataParameter[] parameters)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
int result;
connection.Open();
SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
//Connection.Close();
return result;
}
}
/// <summary>
///  Create  SqlCommand  Object instance ( Used to return 1 Integer value ) 
/// </summary>
/// <param name="storedProcName"> Stored procedure name </param>
/// <param name="parameters"> Stored procedure parameters </param>
/// <returns>SqlCommand  Object instance </returns>
private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.Parameters.Add(new SqlParameter("ReturnValue",
SqlDbType.Int, 4, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
return command;
}

ps: Two types of return values in invoking stored procedures in C #


// Stored procedure 
//create proc authors_count @outrus int output
//as
//declare @authors int
//select @authors=count(*) from authors
//set @outrus=@authors
//return @authors
System.Data.SqlClient.SqlConnection sqlcon=new System.Data.SqlClient.SqlConnection("server=(local);database=pubs;uid=sa;pwd=;");
System.Data.SqlClient.SqlCommand sqlcmd=new System.Data.SqlClient.SqlCommand("authors_count",sqlcon);
sqlcmd.CommandType=System.Data.CommandType.StoredProcedure;
// sqlcmd.CommandText="authors_count";
// sqlcmd.Connection=sqlcon;
sqlcmd.Parameters.Add("@rus",System.Data.SqlDbType.Int);
sqlcmd.Parameters.Add("@outrus",System.Data.SqlDbType.Int);
sqlcmd.Parameters[0].Direction=System.Data.ParameterDirection.ReturnValue;
sqlcmd.Parameters[1].Direction=System.Data.ParameterDirection.Output;
sqlcon.Open();
//int res=(int)sqlcmd.ExecuteNonQuery();// This is not the return value of the stored procedure, but the above only returns delete,update,insert Number of rows affected 
sqlcmd.ExecuteNonQuery();
string res=sqlcmd.Parameters[0].Value.ToString();// So you can get the return value of the stored procedure 
sqlcon.Close();
this.label1.Text=" The return value of the stored procedure is :"+res.ToString();// By return  Return 
this.label2.Text=" Object returned in the stored procedure output Value :"+sqlcmd.Parameters[1].Value.ToString();// By output Return 

Related articles: