Accessing SQL Server stored procedure output param

问题:

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

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

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. I am new to SQL Procedures, so I referred this article.

回答1:

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.

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;

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

回答2:

you have to specify that it is a stored procedure not a query

cmd.CommandType = CommandType.StoredProcedure;

回答3:

Just use ExecuteNonQuery , you can't use ExecuteReader with out parameter in this case

cmd.ExecuteNonQuery(); 

Or if you want try with ExecuteScalar and ReturnValue

回答4:

You should add

cmd.CommandType = CommandType.StoredProcedure 

before calling it

回答5:

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...