在 C# 中访问 SQL Server 存储过程输出参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12174399/
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
Accessing SQL Server stored procedure output parameter in C#
提问by ankit0311
I have a simple SQL Server stored procedure:
我有一个简单的 SQL Server 存储过程：
ALTER PROCEDURE GetRowCount
(
@count int=0 OUTPUT
)
AS
Select * from Emp where age>30;
SET @count=@@ROWCOUNT;
RETURN
I am trying to access the output parameter in the following C# code:
我正在尝试访问以下 C# 代码中的输出参数：
SqlConnection con = new SqlConnection();
con.ConnectionString = "Data Source=localhost\SQLEXPRESS;Initial Catalog=answers;Integrated Security=True";
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = "GetRowCount";
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@count", SqlDbType.Int));
cmd.Parameters["@count"].Direction = ParameterDirection.Output;
con.Open();
SqlDataReader reader=cmd.ExecuteReader();
int ans = (int)cmd.Parameters["@count"].Value;
Console.WriteLine(ans);
But on running the code, a NullReferenceException is being thrown at the second last line of the code. Where am I going wrong? Thanks in advance!
但是在运行代码时，在代码的倒数第二行抛出 NullReferenceException。我哪里错了？提前致谢！
P.S. I am new to SQL Procedures, so I referred this article.
PS 我是 SQL 过程的新手，所以我参考了这篇文章。
采纳答案by marc_s
I'd suggest you put your SqlConnectionand SqlCommandinto using blocks so that their proper disposal is guaranteed.
我建议你把你的SqlConnection和SqlCommand用于使用块，以便保证它们的正确处理。
Also, if I'm not mistaken, the output parameters are only available after you've completely read the resulting data set that's being returned.
另外，如果我没记错的话，输出参数只有在您完全阅读返回的结果数据集后才可用。
Since you don't seem to need that at all - why not just use .ExecuteNonQuery()instead? Does that fix the problem?
既然您似乎根本不需要那个 - 为什么不直接使用.ExecuteNonQuery()呢？这能解决问题吗？
using (SqlConnection con = new SqlConnection("Data Source=localhost\SQLEXPRESS;Initial Catalog=answers;Integrated Security=True"))
using (SqlCommand cmd = new SqlCommand("dbo.GetRowCount", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@count", SqlDbType.Int));
cmd.Parameters["@count"].Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery(); // *** since you don't need the returned data - just call ExecuteNonQuery
int ans = (int)cmd.Parameters["@count"].Value;
con.Close();
Console.WriteLine(ans);
}
Also : since it seems you're only really interested in the row count - why not simplify your stored procedure to something like this:
另外：因为您似乎只对行数真正感兴趣 - 为什么不将您的存储过程简化为这样的：
ALTER PROCEDURE GetRowCount
AS
SELECT COUNT(*) FROM Emp WHERE age > 30;
and then use this snippet in your C# code:
然后在您的 C# 代码中使用此代码段：
con.Open();
object result = cmd.ExecuteScalar();
if(result != null)
{
int ans = Convert.ToInt32(result);
}
con.Close();
回答by podiluska
You should add
你应该添加
cmd.CommandType = CommandType.StoredProcedure
before calling it
在调用它之前
回答by Aghilas Yakoub
Just use ExecuteNonQuery , you can't use ExecuteReader with out parameter in this case
只使用 ExecuteNonQuery ，在这种情况下你不能使用没有参数的 ExecuteReader
cmd.ExecuteNonQuery();
Or if you want try with ExecuteScalar and ReturnValue
或者，如果您想尝试使用 ExecuteScalar 和 ReturnValue
回答by Hassan Boutougha
you have to specify that it is a stored procedure not a query
您必须指定它是存储过程而不是查询
cmd.CommandType = CommandType.StoredProcedure;
回答by Frankwri Sz N
I find the problem, its the connection string. But now, in the code:
我找到了问题，它的连接字符串。但是现在，在代码中：
usuary = (string)cmd.Parameters["@USUARIO"].Value;
password = (string)cmd.Parameters["@CLAVE"].Value;
the compiler infomrs thats values are null...
编译器通知那些值为空...
