Problem reading out parameter from stored procedur

Question

I just come across a strange problem where i cannot retrieve the sql stored procedure out parameter value. I struck with this problem for nearly 2 hours.

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;

        }

the @total value is always null. But when i execute the query generated through profiler in query analyzer , it returns fine.

Finally i found this is due to the SQL Connection.

It works fine if i close the connection before reading the out param

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

WT.., i just cant figure out why it behaves like this.. anybody have an idea?

Answer 1

Parameter values are returned at the end of the TDS stream (since you could change it at the end of your query, after selecting data). You must be sure to consume all the TDS data (or at least, cause the buffers to be flushed, which Close() does for you) in order to get the updated parameter values, for example:

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

The same goes for SQL errors raised late in the query. You could also try adding a using; this may also be sufficient:

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

Answer 2

To add to Marc's answer, you can just close the reader (not the connection) to get the results.

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