How to Retrieve a Primary Key from a Stored Procedure's Output Parameter in C#?

Using stored procedure output parameters in C#

When retrieving data from a stored procedure, you often need to access output parameters to obtain additional information. In this example, a C# program encounters problems while trying to retrieve the primary key returned by the usp_InsertContract stored procedure.

The stored procedure is slightly modified to use SCOPE_IDENTITY(), as shown below:

<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>

The C# code has also been adjusted to correctly retrieve the output value:

<code class="language-csharp">// 打开连接并执行存储过程
conn.Open();
cmd.ExecuteNonQuery();

// 从@NewId读取输出值
int contractID = Convert.ToInt32(cmd.Parameters["@NewId"].Value);
conn.Close();</code>

This modification should solve the problem by ensuring that the primary key is correctly returned as an output parameter and accessible in the C# program.

Note: Be sure to verify that the Contracts table has columns with the IDENTITY attribute configured correctly. Missing IDENTITY columns may also make it difficult to obtain the primary key value.

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