首页 > 代码库 > 使用Microsoft.Practices.EnterpriseLibrary.Data调用存数过程Output参数注意事项
使用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 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. 代码中的调用过程如下：
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 = http://www.mamicode.com/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)来得到返回值，各位如果有其它方法，敬请留下，谢谢。
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。