How to Retrieve Output Parameters from SQL Server Stored Procedures in C#?

Using stored procedure output parameters in C#

When retrieving data from a SQL Server stored procedure, you often need to access the output parameters of the stored procedure in C# code. However, understanding how to do this effectively can be a challenge.

Consider a stored procedure named usp_InsertContract that inserts new records into the Contracts table and returns the assigned primary key as an output parameter:

CREATE PROCEDURE usp_InsertContract
    @ContractNumber varchar(7),
    @NewId int OUTPUT
AS
BEGIN
    INSERT into [dbo].[Contracts] (ContractNumber)
        VALUES (@ContractNumber)
    Select @NewId = Id From [dbo].[Contracts] where ContractNumber = @ContractNumber
END

To execute this stored procedure in C#, you can use the following code snippet:

using System.Data;
using System.Data.SqlClient;

public class StoredProcedureOutputParameterExample
{
    public static void Main()
    {
        // 定义连接字符串并初始化连接。
        string connectionString = "Server=localhost;Database=MyDatabase;User Id=sa;Password=mypassword;";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // 创建命令对象。
            using (SqlCommand command = new SqlCommand("usp_InsertContract", connection))
            {
                // 将命令类型设置为StoredProcedure。
                command.CommandType = CommandType.StoredProcedure;

                // 向命令添加输入参数。
                command.Parameters.Add(new SqlParameter("@ContractNumber", SqlDbType.VarChar, 7) { Value = "ABC123" });

                // 向命令添加输出参数。
                SqlParameter newIdParameter = new SqlParameter("@NewId", SqlDbType.Int) { Direction = ParameterDirection.Output };
                command.Parameters.Add(newIdParameter);

                // 打开连接并执行命令。
                connection.Open();
                command.ExecuteNonQuery();

                // 检索输出参数值。
                int newId = (int)newIdParameter.Value;

                // 使用输出参数值。
                Console.WriteLine($"New ID: {newId}");
            }
        }
    }
}

In this code snippet, the SqlConnection and SqlCommand objects are used to establish a connection to the database and execute the stored procedure. Add the input parameter @ContractNumber to the command, and create an output parameter @NewId and set its Direction attribute to ParameterDirection.Output.

After executing the stored procedure, the output parameter value can be retrieved using the parameter's Value attribute. The retrieved value can then be used in C# code.

By following these steps, you can effectively use stored procedure output parameters in C# code to retrieve data from a SQL Server stored procedure.

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