Access to output parameter of SQL Server stored procedure in C #

Question

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 when you run the code, a NullReferenceException is thrown into the second last line of code. Where am I going wrong? Thanks in advance!

PS I am new to SQL procedures, so I called this article .

Answer 1

I suggest that you use your SqlConnection and SqlCommand to use the blocks to ensure their correct utility.

In addition, if I am not mistaken, the output parameters are available only after a complete reading of the returned dataset.

Since you don't need it at all - why not just use .ExecuteNonQuery() ? Does this 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 like you're really interested in the number of lines - why not simplify your stored procedure like this:

 ALTER PROCEDURE GetRowCount AS SELECT COUNT(*) FROM Emp WHERE age > 30; 

and then use this C # code snippet:

  con.Open(); object result = cmd.ExecuteScalar(); if(result != null) { int ans = Convert.ToInt32(result); } con.Close(); 

Answer 2

Just use ExecuteNonQuery, you cannot use ExecuteReader with out parameter in this case

 cmd.ExecuteNonQuery(); 

Or if you want to try using ExecuteScalar and ReturnValue

Answer 3

you need to indicate that this is a stored procedure, not a request

 cmd.CommandType = CommandType.StoredProcedure; 

Answer 4

You must add

 cmd.CommandType = CommandType.StoredProcedure 

before calling

Answer 5

I find the problem, its connection string. But now, in the code:

  usuary = (string)cmd.Parameters["@USUARIO"].Value; password = (string)cmd.Parameters["@CLAVE"].Value; 

compiler data whose values ​​are null ...