How to Retrieve Output Parameters from a Stored Procedure in C#?

Retrieving Output Parameters from Stored Procedures in C#

Many database interactions utilize stored procedures to efficiently manage data. A common requirement is retrieving output parameters, especially when dealing with automatically generated primary keys. This example demonstrates how to correctly handle output parameters in C# when working with SQL Server stored procedures.

The problem often arises when trying to capture the newly generated primary key after an INSERT operation. The following modified stored procedure provides a solution:

<code class="language-sql">CREATE PROCEDURE usp_InsertContract
(
  @ContractNumber varchar(7),
  @NewId int OUTPUT
)
AS
BEGIN
  INSERT INTO [dbo].[Contracts] (ContractNumber)
  VALUES (@ContractNumber);

  SELECT @NewId = SCOPE_IDENTITY();
END;</code>

This stored procedure accepts a contract number and returns the newly generated ID via the @NewId output parameter using SCOPE_IDENTITY().

Here's the updated C# code to retrieve this output parameter:

<code class="language-csharp">// Establish database connection and command object
using (SqlConnection conn = new SqlConnection(pvConnectionString))
using (SqlCommand cmd = new SqlCommand("dbo.usp_InsertContract", conn))
{
  cmd.CommandType = CommandType.StoredProcedure;

  // Define and add parameters
  cmd.Parameters.AddWithValue("@ContractNumber", contractNumber);
  cmd.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output;

  // Open connection and execute the stored procedure
  conn.Open();
  cmd.ExecuteNonQuery();

  // Retrieve the output parameter value
  int contractID = Convert.ToInt32(cmd.Parameters["@NewId"].Value);
  conn.Close();
}</code>

This C# code connects to the database, executes the stored procedure, and efficiently retrieves the @NewId output parameter, storing it in the contractID variable. The newly generated primary key is now available for further processing within your application.

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