Problem reading parameter from stored procedure using C #

Question

I just run into some strange problem when I cannot get the value of the sql stored procedure parameter. I ran into this problem for almost 2 hours.

The code is very simple.

using (var con = new SqlConnection(connectionString)) { con.Open(); SqlCommand cmd = new SqlCommand("sp_mgsearach", con); cmd.CommandType = CommandType.StoredProcedure; SqlParameter param1 = new SqlParameter("@SearchTerm", SqlDbType.VarChar); param1.Value = searchTerm; param1.Direction = ParameterDirection.Input; cmd.Parameters.Add(param1); SqlParameter param2 = new SqlParameter("@start", SqlDbType.Int); param2.Value = start; param2.Direction = ParameterDirection.Input; cmd.Parameters.Add(param2); SqlParameter param3 = new SqlParameter("@end", SqlDbType.Int); param3.Value = end; param3.Direction = ParameterDirection.Input; cmd.Parameters.Add(param3); SqlParameter param4 = new SqlParameter("@total", SqlDbType.Int); param4.Direction = ParameterDirection.InputOutput; param4.Value = 0; cmd.Parameters.Add(param4); var reader = cmd.ExecuteReader(); LoadHits(reader); if (lstHits.Count > 0) total = Convert.ToInt32(cmd.Parameters["@total"].Value); else total = 0; } 

@total is always null. But when I execute the query generated through the profiler in the query analyzer, it returns a penalty.

Finally, I found this to be related to SQL Connection.

It works fine if I close the connection before reading the out parameter

  LoadHits(reader); con.close() if (lstHits.Count > 0) total = Convert.ToInt32(cmd.Parameters["@total"].Value); else total = 0; 

WT .., I just can't understand why he is behaving like this. Does anyone have an idea?

Answer 1

Parameter values ​​are returned to the end of the TDS stream (since you can change it at the end of your query after selecting the data). You should definitely use all the TDS data (or at least cause a flush of buffers that Close() for you) to get updated parameter values, for example:

 do { while(reader.Read() {} } while (reader.NextResult()); 

The same applies to SQL errors that occur at the end of a query. You can also try adding using ; this may also be sufficient:

 using(var reader = cmd.ExecuteReader()) { LoadHits(reader); } 

Answer 2

To add to Marc's answer, you can simply close the reader (rather than the connection) to get the results.

This is well documented ("Closing DataReader"): http://msdn.microsoft.com/en-us/library/haa3afyz(v=VS.100).aspx