在C＃中访问SQL Server存储过程的输出参数 [英] Accessing SQL Server stored procedure output parameter in C#

问题描述

我有一个简单的SQL Server存储过程：

I have a simple SQL Server stored procedure:

ALTER PROCEDURE GetRowCount

(
@count int=0 OUTPUT
)

AS
Select * from Emp where age>30;
SET @count=@@ROWCOUNT;

RETURN

我试图访​​问输出参数，下面的C＃代码：

I am trying to access the output parameter in the following C# code:

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);

但在运行的代码，一个NullReferenceException正在以代码的第二个最后一行抛出。我在哪里去了？在此先感谢！

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!

P.S。我是新来的SQL程序，所以我提到这篇文章。

P.S. I am new to SQL Procedures, so I referred this article.

推荐答案

我建议你把你的的SqlConnection 和的SqlCommand 成块的使用，使他们妥善处置的保证。

I'd suggest you put your SqlConnection and SqlCommand into using blocks so that their proper disposal is guaranteed.

另外，如果我没有记错，输出参数唯一可用后，你完全读取的返回结果数据集

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.

既然你似乎并不需要，在所有 - 为什么不使用 .ExecuteNonQuery（）呢？ ？这是否解决问题。

Since you don't seem to need that at all - why not just use .ExecuteNonQuery() instead? Does that fix the problem?

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;

，然后在你的C＃代码中使用此片段：

and then use this snippet in your C# code:

    con.Open();

    object result = cmd.ExecuteScalar();

    if(result != null)
    {
        int ans = Convert.ToInt32(result);
    }

    con.Close();

这篇关于在C＃中访问SQL Server存储过程的输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！