c# - ExecuteNonQuery 不更新输出参数

Question

我正在使用 ExecuteNonQuery 在数据库中插入一条记录并返回新记录的 ID。它插入正常，但输出参数未更新。

尝试在 SSMS 中执行存储过程并且运行良好。只有在使用 ExecuteNonQuery 执行它时才会出现问题。

包括用于尝试插入的所有代码。

        public int InsertPurchaseOrder(IPurchaseOrder po)
        {
            int returnId = 0;
            int returnCount = 0;

            List<IParam> parms = ParameterFactory.CreateListInstance();

            parms.Add(ParameterFactory.CreateParameter("@Timestamp", po.Timestamp, SqlDbType.Timestamp, ParameterDirection.Output));
            parms.Add(ParameterFactory.CreateParameter("@Status", po.Status, SqlDbType.Int, ParameterDirection.Input));
            parms.Add(ParameterFactory.CreateParameter("@Subtotal", po.Subtotal, SqlDbType.Money, ParameterDirection.Input));
            parms.Add(ParameterFactory.CreateParameter("@Taxes", po.Taxes, SqlDbType.Money, ParameterDirection.Input));
            parms.Add(ParameterFactory.CreateParameter("@Total", po.Total, SqlDbType.Money, ParameterDirection.Input));
            parms.Add(ParameterFactory.CreateParameter("@EmployeeId", po.EmployeeId, 8, SqlDbType.VarChar, ParameterDirection.Input));

            parms.Add(ParameterFactory.CreateParameter("@PONumOut", 12345, SqlDbType.Int, ParameterDirection.Output));

            try
            {
                returnCount = dataAccess.ExecuteNonQuery(SPROC_INSERT_PO, CommandType.StoredProcedure, parms);
                if (returnCount >= 0)
                {
                    po.Timestamp = (byte[])parms[0].Value;
                    returnId = Convert.ToInt32(parms[6].Value);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return returnId;
        }

 public static IParam CreateParameter(string name, object value, SqlDbType type, ParameterDirection direction)
        {
            return new Param(name, value, type, direction);
        }

        public Param(string name, object value, SqlDbType type, ParameterDirection direction)
        {
            Name = name;
            Value = value;
            Size = 0;
            DataType = type;
            Direction = direction;
        }

        public int ExecuteNonQuery(string cmdText, CommandType cmdType, List<IParam> parms)
        {
            SqlCommand cmd = CreateCommand(cmdText, cmdType, parms);

            using (cmd.Connection)
            {
                cmd.Connection.Open();
                return cmd.ExecuteNonQuery();
            }
        }

        public SqlCommand CreateCommand(string cmdText, CommandType cmdType, List<IParam> parms)
        {
            SqlConnection conn = new SqlConnection(
                Properties.Settings.Default.cnnString);

            SqlCommand cmd = new SqlCommand(cmdText, conn);
            cmd.CommandType = cmdType;

            if (parms != null)
            {
                foreach (IParam p in parms)
                {
                    cmd.Parameters.Add(new SqlParameter(p.Name,
                        p.DataType, p.Size));
                    cmd.Parameters[p.Name].Value = p.Value;
                    cmd.Parameters[p.Name].Direction = p.Direction;
                }
            }

            return cmd;
        }

ALTER PROCEDURE [dbo].[spInsertPO]
    @Timestamp timestamp OUTPUT,
    @Status Int,
    @Subtotal money,
    @Taxes money,
    @Total money,
    @EmployeeId varchar(8),
    @PONumOut Int OUTPUT
AS
BEGIN
    BEGIN TRY
        SET NOCOUNT OFF
        INSERT INTO
            PurchaseOrder(Status,Subtotal,Taxes,Total,EmployeeId)
        VALUES
            (@Status, @Subtotal, @Taxes, @Total, @EmployeeId)

        SET @PONumOut = @@IDENTITY;
    END TRY
    BEGIN CATCH
        THROW
    END CATCH
END

Answer 1

解决了问题。

需要将我的 ExecuteNonQuery() 方法修改为以下内容。

        public int ExecuteNonQuery(string cmdText, CommandType cmdType, List<IParam> parms)
        {
            SqlCommand cmd = CreateCommand(cmdText, cmdType, parms);

            using (cmd.Connection)
            {
                cmd.Connection.Open();
                int ret = cmd.ExecuteNonQuery();
                UnloadParms(parms, cmd);
                return ret;
            }
        }

并添加以下方法。

private static void UnloadParms(List<IParam> parms, SqlCommand cmd)
        {
            if (parms != null)
            {
                for (int i = 0; i <= parms.Count - 1; i++)
                {
                    IParam p = parms[i];
                    p.Value = cmd.Parameters[i].Value;
                    parms[i] = p;
                }
            }
        }