How to Retrieve Output Parameter Values from Stored Procedures in ADO.NET?

Accessing Output Parameter Values in ADO.NET

This guide demonstrates how to easily retrieve output parameter values from stored procedures within ADO.NET. The process involves these key steps:

1. Define the Output Parameter: Create a SqlParameter object, setting its Direction property to ParameterDirection.Output. Ensure the parameter's name and data type (SqlDbType) precisely match those defined in your stored procedure (e.g., @ID INT OUT).

2. Add to Command Parameters: Add this SqlParameter object to the Parameters collection of your SqlCommand object.

3. Execute the Stored Procedure: Use SqlCommand.ExecuteNonQuery() to execute the stored procedure.

4. Retrieve the Output Value: After execution, access the output parameter's value using the Value property of your SqlParameter object. Remember to cast the retrieved value to the correct data type to prevent errors.

Here's a practical code example illustrating this process:

// Assuming a stored procedure 'sproc' with an output parameter '@ID'
// and a connection string 'connectionString'

using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("sproc", conn))
{
    SqlParameter outputParam = new SqlParameter("@ID", SqlDbType.Int) { Direction = ParameterDirection.Output };
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(outputParam);

    conn.Open();
    cmd.ExecuteNonQuery();

    int retrievedId = (int)outputParam.Value; //Retrieve and cast the output integer value

    conn.Close();
}

The output value is readily available from the SqlParameter object itself. It's crucial to ensure the data type in your SqlParameter matches the stored procedure's output parameter definition. Accurate data type casting during retrieval is essential to avoid exceptions.

The above is the detailed content of How to Retrieve Output Parameter Values from Stored Procedures in ADO.NET?. For more information, please follow other related articles on the PHP Chinese website!