如何从 C# 函数的存储过程返回多个输出参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12884461/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to return multiple output parameters from stored procedure for C# function
提问by Yasser
I'm using output parameters to get values from my database.
我正在使用输出参数从我的数据库中获取值。
This is my stored procedure:
这是我的存储过程：
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
I'm not sure about the returns at the end:
我不确定最后的回报：
RETURN (@SubscriptionPIN)
RETURN (@SignupDate)
RETURN (@ProductCount)
On the other side, here is the c# code :
另一方面，这里是 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;
}
}
}
}
When I run this code, I get error:
当我运行此代码时，出现错误：
System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.
at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
System.InvalidOperationException: String[1]:
在 System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
在 System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection parameters)
at System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC& rpc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
在 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
在 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
在 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
I don't know what is the correct way to send many output parameters from stored procedure, can someone help please?
我不知道从存储过程发送许多输出参数的正确方法是什么，有人可以帮忙吗？
采纳答案by Guffa
You need to specify the maximum length for the nvarcharparameters:
您需要指定nvarchar参数的最大长度：
SqlParameter SubscriptionPIN = new SqlParameter("@TheCustomerID", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output };
cmd.Parameters.Add(SubscriptionPIN);
SqlParameter SignupDate = new SqlParameter("@SignupDate", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output };
cmd.Parameters.Add(SignupDate);
Remove the returnstatements from the stored procedure. You don't need to do anything for the output parameters to be returned. (Also, you can only use one returnstatement, and you can only return integer values. You would use a parameter with the direction ReturnValueto get that returned value.)
return从存储过程中删除语句。您不需要为要返回的输出参数做任何事情。（此外，您只能使用一个return语句，并且只能返回整数值。您将使用带有方向的参数ReturnValue来获取该返回值。）
回答by Yasser
Procedure execution ends after your first RETURN which "Exits unconditionally from a query or procedure."
过程执行在您的第一个“无条件退出查询或过程”的 RETURN 后结束。
Consider returning both values as one recordset with
考虑将两个值作为一个记录集返回
SELECT @SubscriptionPIN AS SubsPIN , @SignupDate AS SignUpDate, @ProductCount AS ProdCount
at the end of the procedure.
在程序结束时。
回答by Indranil
Here is what I tried and it is working fine
这是我尝试过的并且工作正常
**Stored Procedures**
STORED PROCEDURE 1
create procedure spLoginCount
@userid nvarchar(50),
@password nvarchar(50),
@count int out
as
Begin
select @count=count(userid) from users where userid=@userid and pswd=@password
End
**STORED PROCEDURE 2**
create procedure spLoginData
@userid nvarchar(50),
@usertype nvarchar(20) out,
@lastlogin nvarchar(100) out
as
Begin
select @usertype=usertype,@lastlogin=lastlogin from users where userid=@userid
End
**ASP.NET code which will get values of two output Parameters**....
protected void btnLogin_Click(object sender, EventArgs e)
{
string uid="", psw="";
uid = txtUserName.Text;
psw = txtPassword.Text;
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection scon = new SqlConnection(cs))
{
SqlCommand scmd = new SqlCommand("spLoginCount", scon);
scmd.CommandType = System.Data.CommandType.StoredProcedure;
scmd.Parameters.AddWithValue("@userid",uid);
scmd.Parameters.AddWithValue("@password", psw);
SqlParameter outparameter = new SqlParameter();
outparameter.ParameterName = "@count";
outparameter.SqlDbType = System.Data.SqlDbType.Int;
outparameter.Direction = System.Data.ParameterDirection.Output;
scmd.Parameters.Add(outparameter);
scon.Open();
scmd.ExecuteScalar();
string count = outparameter.Value.ToString();
if (count == "1")
{
SqlCommand scmd1= new SqlCommand("spLoginData", scon);
scmd1.CommandType = System.Data.CommandType.StoredProcedure;
scmd1.Parameters.AddWithValue("@userid", uid);
/*SqlParameter outUserType = new SqlParameter();
outUserType.ParameterName = "@usertype";
outUserType.SqlDbType = System.Data.SqlDbType.NText;
outUserType.Direction = System.Data.ParameterDirection.Output;
scmd1.Parameters.Add(outUserType);
*/
SqlParameter outUserType = new SqlParameter("@usertype", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output };
scmd1.Parameters.Add(outUserType);
SqlParameter outLastLogin = new SqlParameter("@lastlogin", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output };
scmd1.Parameters.Add(outLastLogin);
scmd1.ExecuteNonQuery();
scon.Close();
string usertype,lastlogin;
usertype = outUserType.Value.ToString();
lastlogin = outLastLogin.Value.ToString();
}
}
}
回答by Yashwant Software Developer
>Try this its working fine for the multiple output parameter:
>试试这个它的多输出参数工作正常：
using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["conStringEndicia"].ConnectionString)){
using (var sqlCmd = new SqlCommand("endicia.credentialLookup", sqlConnection))
{
sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
sqlCmd.Parameters.AddWithValue("@accountNumber", accountNumber);
SqlParameter outLogin = new SqlParameter("@login", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output };
sqlCmd.Parameters.Add(outLogin);
SqlParameter outPassword = new SqlParameter("@password", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output };
sqlCmd.Parameters.Add(outPassword);
sqlConnection.Open();
sqlCmd.ExecuteNonQuery();
string login, password;
login = outLogin.Value.ToString();
password = outPassword.Value.ToString();
}
}
回答by Jamal
SqlParameter SubscriptionPIN = new SqlParameter("@TheCustomerID", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output }; cmd.Parameters.Add(SubscriptionPIN); SqlParameter SignupDate = new SqlParameter("@SignupDate", DbType.String, 100) { Direction = ParameterDirection.Output }; cmd.Parameters.Add(SignupDate);
SqlParameter SubscriptionPIN = new SqlParameter("@TheCustomerID", SqlDbType.NVarChar, 100) { Direction = ParameterDirection.Output }; cmd.Parameters.Add(SubscriptionPIN); SqlParameter SignupDate = new SqlParameter("@SignupDate", DbType.String, 100) { Direction = ParameterDirection.Output }; cmd.Parameters.Add(注册日期);
must give type and size
必须给出类型和大小
