c# - 在 C# 中使用 SqlTransaction，如何从存储过程中获取输出参数并插入到另一个表中？

Question

我有两个表:表 1 和表 2。我需要新插入的 ID(来自 Table1)作为输出参数，它应该被插入到 Table2 中。我必须在此处使用 SqlTransaction 或 TransactionScope。

存储过程:

    CREATE PROC InsertTable1
    (
        @Name nvarchar(50),
        @OutputParam int out
    )
    AS
    BEGIN
        INSERT INTO Table1(Name) 
        VALUES(@Name)

        SELECT @OutputParam = SCOPE_IDENTITY() 
    END

C#代码:

SqlTransaction transaction = db.sqlconn.BeginTransaction();

SqlParameter[] param = new SqlParameter[2];
param[0] = new SqlParameter("@Name", "TestName");
param[1] = new SqlParameter("@OutputParam", SqlDbType.Int);
param[1].Direction = ParameterDirection.Output;

SqlHelper.ExecuteNonQuery(transaction, "InsertTable1", param);

int outputId = (int)param[1].Value;

当我运行和调试项目时出现错误

Object reference not set to an instance of an object

刚执行完

int outputId = (int)param[1].Value;

我认为交易正在关闭连接或其他东西。如果我不使用交易，它会起作用。但无论如何我都必须使用交易。我对实现相同结果的任何替代方法持开放态度。

Answer 1

首先，SqlHelper.ExecuteNonQuery 的过载您使用的参数数组 values，而不是 SqlParameter 数组。它只能编译和运行，因为类型是object。因此它将通过 SqlCommandBuilder 派生自己的参数，并尝试从您的参数参数中设置它们的值，但当然 SqlParameter 不能将 SqlParameter 作为其 .Value .然而，这一点没有实际意义，因为对源代码的注释(下面)明确指出您无法从此方法访问 OUTPUT 或 RETURN 值。

    /// <summary>
    /// Execute a stored procedure via a SqlCommand (that returns no resultset) against the specified 
    /// SqlTransaction using the provided parameter values.  This method will query the database to discover the parameters for the 
    /// stored procedure (the first time each stored procedure is called), and assign the values based on parameter order.
    /// </summary>
    /// <remarks>
    /// This method provides no access to output parameters or the stored procedure's return value parameter.
    /// 
    /// e.g.:  
    ///  int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36);
    /// </remarks>
    /// <param name="transaction">a valid SqlTransaction</param>
    /// <param name="spName">the name of the stored procedure</param>
    /// <param name="parameterValues">an array of objects to be assigned as the input values of the stored procedure</param>
    /// <returns>an int representing the number of rows affected by the command</returns>
    public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
    {
        //if we receive parameter values, we need to figure out where they go
        if ((parameterValues != null) && (parameterValues.Length > 0))
        {
            //pull the parameters for this stored procedure from the parameter cache (or discover them & populate the cache)
            SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);

            //assign the provided values to these parameters based on parameter order
            AssignParameterValues(commandParameters, parameterValues);

            //call the overload that takes an array of SqlParameters
            return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
        }
        //otherwise we can just call the SP without params
        else
        {
            return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
        }
    }