Notes for using Microsoft. Practices. enterpriselibrary. Data to call the output parameter of the stored data process

Recently, I took a look at an open-source code. During the debugging process, I found that the output parameter in the calling of the stored data process was always incorrect. Now I will record the problem.

Problem description:

1. Use Microsoft. Practices. enterpriselibrary. Data. DLL to call the database

2. the stored data process is as follows:

USE [Survey]GO/****** Object:  StoredProcedure [dbo].[vts_spQuestionCopy]    Script Date: 08/03/2014 19:11:10 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER ONGO/*/// <summary>/// Copy an existing question to another survey/// </summary>*/ALTER PROCEDURE [dbo].[vts_spQuestionCopy]                @QuestionID int,                 @NewSurveyID int,                @DisplayOrder int,                @PageNumber int,                @QuestionCopyID int outputASBEGIN TRANSACTION CopyQuestionINSERT 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,    ShowHelpTextFROM vts_tbQuestion WHERE QuestionId = @QuestionID-- Check if the cloned question was createdIF @@rowCount <> 0BEGIN    -- 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 >= @DisplayOrderENDCOMMIT TRANSACTION CopyQuestion

3. The Calling process in the Code is as follows:

        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());        }

 

Let's analyze this code: an error is always prompted during the call.

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

Is empty. When output is used, sqlvalue is null. What should we do now?

I tried to give it a default value and changed the code

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

New problems occur:

An exception of Type 'System. invalidoperationexception' occurred in Microsoft. Practices. incluiselibrary. 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)

What does this error mean? This means that the number of parameters is inconsistent. After the query, we know that the original Params pass the value. When the output has the default value, the input parameter is five, but the stored procedure only accepts four. What should I do now?

Try to modify

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

But it still reports the same error. Try to remove the default value. Use the above method to run successfully, but if you want to get the return value, it will be canceled and cannot be obtained. Swollen?

 

Try executenonquery (dbcommand command) and modify the Code as follows:

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

Run successfully. Get the output return value.

 

Note: In executenonquery (string storedprocedurename, Params object [] parametervalues), Params actually transmits the values in sqlparameter. If you want to obtain the output return value, there may be problems. Currently, I use executenonquery (dbcommand command) to obtain the returned value. If you have other methods, please stay. Thank you.