C#中的SQL输出参数 [英] SQL output parameters in C#
问题描述
我正在尝试从Microsoft SQL Server stored procedure
获取值.总共我需要返回两个值.正如您在下面看到的那样,其中一个可以通过return访问,而另一个可以通过一个输出参数访问.我在WPF(C#)中运行此代码:
I'm trying to get a value from Microsoft SQL Server stored procedure
. Totally I need to return two values. As you see below one of them is accessible by return and the other is accessible by a output parameter. I run this code in WPF(C#):
public int InsertCustomerEntranceLogWithOptions(String cardNumber,int buy, int score, out int logID)
{
SqlCommand command = new SqlCommand(
@"
--declare @id int, @logID int
exec @res = Insert_CustomerEntranceLog_WithOptions
@cardNumber, @buy, @score, @logID
--set @logID = @id",
myConnection);
command.CommandType = CommandType.Text;
command.Parameters.Add("@cardNumber", SqlDbType.NVarChar).Value = cardNumber;
command.Parameters.Add("@buy", SqlDbType.Int).Value = buy;
command.Parameters.Add("@score", SqlDbType.Int).Value = score;
command.Parameters.Add("@logID", SqlDbType.Int).Value = 0;
command.Parameters["@logID"].Direction = ParameterDirection.Output;
command.Parameters.Add("@res", SqlDbType.Int).Value = 0;
command.Parameters["@res"].Direction = ParameterDirection.Output;
int res = (int)RunNonQuery(command);
logID = (int)command.Parameters["logID"].Value;
return res;
}
和RunNonQuery方法是:
and RunNonQuery Method is:
public Object RunNonQuery(SqlCommand command, String returnVariableName = "@res")
{
Object result = null;
if (openConnection())
{
try
{
command.ExecuteNonQuery();
if (returnVariableName.Length != 0)
{
result = command.Parameters[returnVariableName].Value;
//command.Dispose();
}
else
{
//command.Dispose();
return 1;
}
}
catch (TimeoutException ex)
{
//Log
}
catch (Exception ex)
{
Utility.LogExceptionError(ex, 1, 1, 1);
//throw ex;
}
closeConnection();
}
return result;
}
我确定我的RunNonQuery
正常工作.我测试了很多.但是,当我使用InsertCustomerEntranceLogWithOptions
方法时,却收到此错误:
I'm sure that my RunNonQuery
works properly. I test it a lot. But When I InsertCustomerEntranceLogWithOptions
method, I Get this error:
An unhandled exception of type 'System.IndexOutOfRangeException'
occurred in System.Data.dll
Additional information: An SqlParameter with ParameterName 'logID'
is not contained by this SqlParameterCollection.
似乎我没有添加SqlParameter
,但是如您所见,已插入LogID
.怎么了?
我还删除了SQL命令中的注释,然后运行它,但是我看到了错误.
It seems that I do not add SqlParameter
but as you see LogID
is inserted. what is wrong?
I also remove comments in SQL command and then run it but steel I see the error.
推荐答案
@
丢失,请按照以下说明进行更新
@
is missing, update like the following
logID = (int)command.Parameters["@logID"].Value;
这篇关于C#中的SQL输出参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!