如何从C#函数的存储过程返回多个输出参数
我正在使用输出参数从我的数据库中获取值.
这是我的存储过程: ALTER PROCEDURE [dbo].[sp_GetCustomerMainData] -- Add the parameters for the stored procedure here @Reference nvarchar(100),@SubscriptionPIN nvarchar(100) OUTPUT,@SignupDate nvarchar(100) OUTPUT,@ProductCount int OUTPUT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SET @SubscriptionPIN = 'N/A' SET @SignupDate = 'N/A' SET @ProductCount = 0 -- Insert statements for procedure here IF EXISTS(SELECT [SubscriptionPIN] FROM [Norton].[dbo].[Customers] WHERE [Reference] = @Reference) BEGIN SELECT TOP 1 @SubscriptionPIN = [SubscriptionPIN],@SignupDate = SignUpDate FROM [Norton].[dbo].[ProductList] WHERE [Reference] = @Reference SET @ProductCount = (SELECT COUNT(*) FROM [Norton].[dbo].[ProductList] WHERE [Reference] = @Reference) END RETURN (@SubscriptionPIN) RETURN (@SignupDate) RETURN (@ProductCount) END 我不确定最后的回报: RETURN (@SubscriptionPIN) RETURN (@SignupDate) RETURN (@ProductCount) 另一方面,这是c#代码: using (SqlConnection con = new SqlConnection(connectionInfo)) { using (SqlCommand cmd = new SqlCommand("sp_GetCustomerMainData",con) { CommandType = CommandType.StoredProcedure }) { cmd.Parameters.Add("@Reference",SqlDbType.NVarChar).Value = CustomerReferenceID; SqlParameter SubscriptionPIN = new SqlParameter("@TheCustomerID",SqlDbType.NVarChar) { Direction = ParameterDirection.Output }; cmd.Parameters.Add(SubscriptionPIN); SqlParameter SignupDate = new SqlParameter("@SignupDate",SqlDbType.NVarChar) { Direction = ParameterDirection.Output }; cmd.Parameters.Add(SignupDate); SqlParameter ProductCount = new SqlParameter("@ProductCount",SqlDbType.Int) { Direction = ParameterDirection.Output }; cmd.Parameters.Add(ProductCount); con.Open(); try { cmd.ExecuteNonQuery(); if (cmd.Parameters["@TheCustomerID"].Value.ToString() != "N/A") { aStatus.SubscriptionPIN = cmd.Parameters["@TheCustomerID"].Value.ToString(); aStatus.SignupDate = cmd.Parameters["@SignupDate"].Value.ToString(); aStatus.ProductCount = int.Parse(cmd.Parameters["@ProductCount"].Value.ToString()); aStatus.Result = "0: Reference ID Found"; } else { aStatus.Result = "1: Reference ID does not exists"; return aStatus; } } catch (SqlException sqlExc) { foreach (SqlError error in sqlExc.Errors) { aStatus.Result = string.Format("{0}: {1}",error.Number,error.Message); return aStatus; } } } } 当我运行此代码时,我收到错误:
我不知道从存储过程发送许多输出参数的正确方法是什么,有人可以帮忙吗? 解决方法
您需要指定nvarchar参数的最大长度:
SqlParameter SubscriptionPIN = new SqlParameter("@TheCustomerID",SqlDbType.NVarChar,100) { Direction = ParameterDirection.Output }; cmd.Parameters.Add(SubscriptionPIN); SqlParameter SignupDate = new SqlParameter("@SignupDate",100) { Direction = ParameterDirection.Output }; cmd.Parameters.Add(SignupDate); 从存储过程中删除return语句.您无需为返回的输出参数执行任何操作. (另外,您只能使用一个return语句,并且只能返回整数值.您可以使用带有ReturnValue方向的参数来获取返回的值.) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |