CODEWITHSUNDEEP
c#sql-servert-sqlstored-procedures
ankit0311
ankit0311

Reputation: 735

Accessing SQL Server stored procedure output parameter in C#

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.

Upvotes: 3

Views: 26725

Answers (5)

Frankwri Sz N
Frankwri Sz N

Reputation: 1

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

Upvotes: 0

Aghilas Yakoub
Aghilas Yakoub

Reputation: 28970

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

Upvotes: 1

marc_s
marc_s

Reputation: 754488

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

Upvotes: 12

Hassan Boutougha
Hassan Boutougha

Reputation: 3919

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

cmd.CommandType = CommandType.StoredProcedure;

Upvotes: 1

podiluska
podiluska

Reputation: 51494

You should add

cmd.CommandType = CommandType.StoredProcedure

before calling it

Upvotes: 0

Related Questions