sql server - Problem reading out parameter from stored procedure using c# -

I just came across a strange problem where I can not retrieve the SQL stored procedure from the parameter value. I hit with this problem for about 2 hours.

The code is very simple

 using  (var con = new SqlConnection (connectionString)) {con.Open (); SqlCommand CMD = New SQL Commands ("sp_mgsearach", Con?); Cmd.CommandType = CommandType.StoredProcedure; SqlParameter param1 = New SqlParameter ("@SearchTerm", SqlDbType.VarChar); Param1.Value = searchTerm; Param1.Directation = Parameter Direction.Input; Cmd.Parameters.Add (param1); SqlParameter param2 = New SqlParameter ("Start", SqlDbType.Int); Param2.Value = start; Param2.Direction = Parameter Direction.Input; Cmd.Parameters.Add (param2); SqlParameter param3 = New SqlParameter ("@ and", SqlDbType.Int); Param3.Value = end; Param3.Directation = Parameter Direction.Input; Cmd.Parameters.Add (param3); SqlParameter param4 = New SqlParameter ("Total", SqlDbType.Int); Param4.Direction = Parameter Direction. InputApptup; Param4.Value = 0; Cmd.Parameters.Add (param4); Var Reader = CMD. Spare reader (); LoadHits (reader); If (lstHits.Count & gt; 0) Total = Convert.Tont32 (cmd.Parameters ["@ Total"]. Value); Other total = 0; }  

The @total value is always empty but when I execute the query generated through the profiler in the Query Analyzer, it returns exactly.

Finally I came to know that this is due to the SQL connection.

If I turn off the connection before reading it altogether

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

WT .., I just do not know why it behaves like this .. Anyone have an idea?

Parameter values ​​are returned to expiration of TDS stream (since you have returned You can change at the end of your query, after selecting the data) You should make sure that all TDS data (or at least, the reason for flushing buffers, which is Close () for you parameter parameters, for example:

  do {while (reader.Read () {}} while updating (reader.extResult ());  

Lets go to the late SQL errors in the same query. You can also try to add by using ; It can also be sufficient: < / P>

  (var reader = cmd.ExecuteReader ()) {LoadHytes (Reader);}