C# 使用 SqlHelper.ExecuteDataSet() 执行 SQL Server 存储过程并获取返回值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13265981/
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
Execute SQL Server Stored Procedure with SqlHelper.ExecuteDataSet() and Get Return Value
提问by Willy Lazuardi
I have an SQL Server Stored Procedure. This procedure have an output parameter.
On my C#.NETAppliaction, I execute this procedure via SqlHelper.ExecuteDataSet()and it returns a query result DataSet.
How can I get the output parameter from the stored procedure procedure while using SqlHelper.ExecuteDataSet().
Some article said that I need to use SqlHelper.ExecuteNonQuery()but I need the DataSettoo.
我有一个SQL Server Stored Procedure. 这个过程有一个输出参数。在我的C#.NETAppliaction 上，我通过执行此过程SqlHelper.ExecuteDataSet()并返回一个查询结果DataSet。如何在使用SqlHelper.ExecuteDataSet(). 有些文章说我需要使用，SqlHelper.ExecuteNonQuery()但我也需要DataSet。
This is my code:
这是我的代码：
public DataSet GetDataPerTable(string spName, string a, string b, out int c)
{
try
{
c = 0;
SqlParameter[] spParameter = new SqlParameter[3];
spParameter[0] = new SqlParameter("@a", SqlDbType.Char, 4);
spParameter[0].Direction = ParameterDirection.Input;
spParameter[0].Value = a;
spParameter[1] = new SqlParameter("@b", SqlDbType.Char, 1);
spParameter[1].Direction = ParameterDirection.Input;
spParameter[1].Value = b;
spParameter[2] = new SqlParameter("@c", SqlDbType.Int);
spParameter[2].Direction = ParameterDirection.ReturnValue;
c = Convert.ToInt32(spParameter[2].Value);
return SqlHelper.ExecuteDataset(Configuration.MyConnectionString, CommandType.StoredProcedure, spName, spParameter);
}
catch (Exception ex)
{
throw ex;
}
}
My cvariable always return 0. Any Idea? Thanks in advance :)
我的c变量总是返回 0。有什么想法吗？提前致谢 ：）
My Procedure is about like this:
我的程序大概是这样的：
CREATE PROCEDURE [dbo].SPR_MyProcedure (@a Char(4), @bChar(1), @c Int Output)
SELECT *
FROM MyTable
Set @c = 1
采纳答案by podiluska
@cisn't the a return value, it's an output parameter
@c不是返回值，而是输出参数
Change
改变
spParameter[2].Direction = ParameterDirection.ReturnValue;
to
到
spParameter[2].Direction = ParameterDirection.Output;
Also, you need to set your cvariable after the call to ExecuteDataseteg:
此外，您需要c在调用后设置变量，ExecuteDataset例如：
DataSet dataset = SqlHelper.ExecuteDataset(Configuration.MyConnectionString, CommandType.StoredProcedure, spName, spParameter);
c=(int)spParameter[2];
return dataset;
回答by Frantisek Bachan
You have to use ParameterDirection.Outputfor parameter c.
see Get output parameter value in ADO.NET
您必须使用ParameterDirection.Outputfor 参数c。请参阅在 ADO.NET 中获取输出参数值
ParameterDirection.ReturnValueis a return value of the whole stored procedure, 0 by default or a value specified in RETURN statement.
ParameterDirection.ReturnValue是整个存储过程的返回值，默认为 0 或 RETURN 语句中指定的值。
回答by Annie
SqlHelper.ExecuteDataSet doesnt return output parameter. see this link
SqlHelper.ExecuteDataSet 不返回输出参数。看到这个链接
for output parameter in every example they r using ExecuteNonQuery see these aticles for output prameter
对于他们使用 ExecuteNonQuery 的每个示例中的输出参数，请参阅这些输出参数的文章
http://forums.asp.net/t/360456.aspx/1
http://forums.asp.net/t/360456.aspx/1
http://www.codeproject.com/Articles/15666/Data-Access-Application-Block-NET-2-0-Get-Return-V
http://www.codeproject.com/Articles/15666/Data-Access-Application-Block-NET-2-0-Get-Return-V
i hope it can help u
我希望它可以帮助你
