How to Retrieve a Return Value from a Stored Procedure in C# using ADO.NET?

Call a stored procedure with return value in C#

A common need when accessing a database is to call stored procedures from an application, especially those that return numeric values. This article demonstrates how to use ADO.NET to call a stored procedure that returns an integer value and retrieve the result in a C# application.

Stored Procedure

Consider the stored procedure usp_GetNewSeqVal which retrieves the next value of a sequence given a sequence name. It receives the @SeqName parameter as input and returns the new sequence value.

C# code

In order to call this stored procedure and retrieve the return value, we can use the following code:

using (SqlConnection conn = new SqlConnection(getConnectionString()))
using (SqlCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = parameterStatement.getQuery();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@SeqName", "SeqNameValue");

    // @ReturnVal 可以是任何名称
    var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
    returnParameter.Direction = ParameterDirection.ReturnValue;

    conn.Open();
    cmd.ExecuteNonQuery();
    var result = returnParameter.Value;
}

Key details

• SqlCommand Configure with the correct command text and type.
• Input parameters@SeqName are added to the command.
• Add a new parameter @ReturnVal with its direction set to ParameterDirection.ReturnValue. This instructs the command to create a variable in the stored procedure and assign the return value to it.
• The connection is opened and the query is executed using ExecuteNonQuery.
• Finally, the return value can be retrieved from the returnParameter.Value attribute.

The above is the detailed content of How to Retrieve a Return Value from a Stored Procedure in C# using ADO.NET?. For more information, please follow other related articles on the PHP Chinese website!