使用Microsoft.Practices.EnterpriseLibrary.Data调用存数过程Output参数注意事项

最近拿了一个开源的源码看了下，在调试的过程中发现调用存数过程的output参数的时候一直出错，现在将问题记录下来。

问题描述：

1. 使用Microsoft.Practices.EnterpriseLibrary.Data.dll调用数据库

2. 存数过程如下：

USE [Survey]
GO
/****** Object:  StoredProcedure [dbo].[vts_spQuestionCopy]    Script Date: 08/03/2014 19:11:10 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
/*
/// <summary>
/// Copy an existing question to another survey
/// </summary>
*/
ALTER PROCEDURE [dbo].[vts_spQuestionCopy]
                @QuestionID int, 
                @NewSurveyID int,
                @DisplayOrder int,
                @PageNumber int,
                @QuestionCopyID int output
AS

BEGIN TRANSACTION CopyQuestion

INSERT INTO vts_tbQuestion  
    (ParentQuestionId, 
    SurveyID,
    LibraryID,
    SelectionModeId, 
    LayoutModeId, 
    DisplayOrder,
    PageNumber, 
    MinSelectionRequired, 
    MaxSelectionAllowed, 
    RatingEnabled,
    ColumnsNumber,
    RandomizeAnswers,
    QuestionText,
    QuestionPipeAlias,
    QuestionIDText,
    HelpText,
    Alias,
    QuestiongroupID,
    ShowHelpText)
SELECT      
    ParentQuestionId, 
    @NewSurveyID,
    null, 
    SelectionModeId, 
    LayoutModeId, 
    @DisplayOrder,
    @PageNumber, 
    MinSelectionRequired, 
    MaxSelectionAllowed, 
    RatingEnabled,
    ColumnsNumber,
    RandomizeAnswers,
    QuestionText,
    QuestionPipeAlias,
    QuestionIDText,
    HelpText,
    Alias,
    QuestionGroupID,
    ShowHelpText
FROM vts_tbQuestion WHERE QuestionId = @QuestionID

-- Check if the cloned question was created
IF @@rowCount <> 0
BEGIN
    -- Clone the question's answers
    set @QuestionCopyID = convert(int,Scope_Identity())
    INSERT INTO vts_tbMultiLanguageText(LanguageItemID, LanguageCode, LanguageMessageTypeID, ItemText)
        SELECT @QuestionCopyID as LanguageItemID, LanguageCode, LanguageMessageTypeID, ItemText
        FROM vts_tbMultiLanguageText
        WHERE LanguageItemID = @QuestionID AND LanguageMessageTypeID in(3,10,11,12)    

    exec vts_spQuestionChildsClone @QuestionID, @QuestionCopyID, @NewSurveyID
    UPDATE vts_tbQuestion SET DisplayOrder = @DisplayOrder, PageNumber = @PageNumber 
    WHERE SurveyID = @NewSurveyID AND ParentQuestionid = @QuestionCopyID

    exec vts_spAnswersCloneByQuestionId @QuestionID, @QuestionCopyID

    exec vts_spQuestionSectionOptionClone @QuestionID, @QuestionCopyID

    -- Update the display order
    UPDATE vts_tbQuestion 
    SET DisplayOrder = DisplayOrder + 1 
    WHERE 
        SurveyID = @NewSurveyID AND
        ((QuestionID<>@QuestionCopyID AND ParentQuestionID is null) OR
         (ParentQuestionID is not null AND ParentQuestionID <> @QuestionCopyID)) AND
         DisplayOrder >= @DisplayOrder
END

COMMIT TRANSACTION CopyQuestion

3. 代码中的调用过程如下：

        public int CopyQuestionById(int questionId, int targetSurveyId, int targetDisplayOrder, int targetPageNumber)
        {
            //SqlParameter[] commandParameters = new SqlParameter[] 
            //{ new SqlParameter("@QuestionId", questionId), 
            //    new SqlParameter("@NewSurveyId", targetSurveyId), 
            //    new SqlParameter("@DisplayOrder", targetDisplayOrder), 
            //    new SqlParameter("@PageNumber", targetPageNumber), 
            //    new SqlParameter("@QuestionCopyId", SqlDbType.Int) 
            //};
            //commandParameters[4].Direction = ParameterDirection.Output;

            ArrayList commandParameters = new ArrayList();
            {
                commandParameters.Add(new SqlParameter("@QuestionId", questionId).SqlValue);
                commandParameters.Add(new SqlParameter("@NewSurveyId", targetSurveyId).SqlValue);
                commandParameters.Add(new SqlParameter("@DisplayOrder", targetDisplayOrder).SqlValue);
                commandParameters.Add(new SqlParameter("@PageNumber", targetPageNumber).SqlValue);
                commandParameters.Add(new SqlParameter("@QuestionCopyId", SqlDbType.Int) { Direction = ParameterDirection.Output}.SqlValue);
            }

            DbConnection.db.ExecuteNonQuery("vts_spQuestionCopy", commandParameters);
            return int.Parse(commandParameters[4].ToString());
        }

咱们来分析一下这段代码：在调用的使用一直提示一个错误

new SqlParameter("@QuestionCopyId", SqlDbType.Int) { Direction = ParameterDirection.Output}.SqlValue

为空。原来当为Output时，sqlvalue就会为空，现在我们应该怎么办呢？

我尝试给其一个默认值，将代码修改为

new SqlParameter("@QuestionCopyId", SqlDbType.Int) { Direction = ParameterDirection.Output, Value = 0}.SqlValue

新的问题出现了：

 An exception of type 'System.InvalidOperationException' occurred in Microsoft.Practices.EnterpriseLibrary.Data.dll but was not handled in user code

Additional information: The number of parameters does not match number of values for stored procedure. 

ExecuteNonQuery(string storedProcedureName, params object[] parameterValues)

这个错误是什么意思呢？意思是说参数个数不一致。查询后得知，原来 params 传的是值，当Output有默认值的时候，传入参数就为5个，可是存储过程只接受4个。现在应该怎么办呢？

尝试修改

DbConnection.db.ExecuteNonQuery("vts_spQuestionCopy", commandParameters.ToArray());

但是还是会报错，同样的错误。尝试去掉默认值，使用上述方法，成功运行，但是想取到返回值就懵了，取不到。肿么办？

尝试使用ExecuteNonQuery(DbCommand command)，将代码修改如下：

        public int CopyQuestionById(int questionId, int targetSurveyId, int targetDisplayOrder, int targetPageNumber)
        {
            SqlParameter[] commandParameters = new SqlParameter[] 
            { new SqlParameter("@QuestionId", questionId), 
                new SqlParameter("@NewSurveyId", targetSurveyId), 
                new SqlParameter("@DisplayOrder", targetDisplayOrder), 
                new SqlParameter("@PageNumber", targetPageNumber), 
                new SqlParameter("@QuestionCopyId", SqlDbType.Int) 
            };
            commandParameters[4].Direction = ParameterDirection.Output;

            SqlCommand vts_spQuestionCopy = new SqlCommand("vts_spQuestionCopy");
            vts_spQuestionCopy.CommandType = CommandType.StoredProcedure;
            vts_spQuestionCopy.Parameters.AddRange(commandParameters);
            DbConnection.db.ExecuteNonQuery(vts_spQuestionCopy);

            var result =int.Parse(vts_spQuestionCopy.Parameters["@QuestionCopyID"].Value.ToString());
            return result;
        }

运行成功，取到output返回值。

Note：ExecuteNonQuery(string storedProcedureName, params object[] parameterValues)中的params其实是将SqlParameter中的值传入。想要取到output返回值的时候，可能会存在问题。目前我是使用ExecuteNonQuery(DbCommand command)来得到返回值，各位如果有其它方法，敬请留下，谢谢。