Return Value Using SqlCommand

Question

I'm trying to get the result set and return value of a stored proc on SQL 2008 server. When I run the proc in sql management studio, I get the result set and the return value.

However, when I try to get the value in C# 4.0, the value of the parameter is null. Here is my C# code:

using (ConnectionManager<SqlConnection> cn = ConnectionManager<SqlConnection>.GetManager(CultureInfo.CurrentCulture.Name))
{
    using (SqlCommand cm = cn.Connection.CreateCommand())
    {
        cm.CommandText = "Name of proc here";
        cm.CommandType = CommandType.StoredProcedure;

        cm.Parameters.AddWithValue("@ApplicationId", ApplicationId);                                        
        cm.Parameters.AddWithValue("@Index", Index);

        if (PageSize > 0)
            cm.Parameters.AddWithValue("@PageSize", PageSize);

        cm.Parameters.Add("@ReturnValue", SqlDbType.Int);
        cm.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue;

        using (IDataReader dr = cm.ExecuteReader())
        {
            SafeDataReader sdr = new SafeDataReader(dr);
            while (sdr.Read())
            {
                UserApplicationEntity uae = new UserApplicationEntity();
                uae.UserId = sdr.GetGuid("UserId");
                uae.ExternalId = sdr.GetString("ExternalId");
                Result.Value.Collection.Add(uae);
            }

            Result.Value.TotalResults = (int)cm.Parameters["@ReturnValue"].Value;
        }
    }
}

The last line where I call Result.Value.TotalResults = (int)cm.Parameters["@ReturnValue"].Value; is where Value is null. Every tutorial or post I've found, I appear to be doing everything correctly. At this point I think I'm just missing something small and need another set of eyes. I've also tried setting the return parameter before all the others as one post I found on MS site said I needed to, but regardless of where it's at, it returns null.

Answer 1

The return value is sent last in the response from the database, so you have to read the result set completely before you can access the return value.

What does the SafeDataReader class do? Is it possible that it prevents the entire result set from being read?

Try to move the code that reads the return value outside the using block for the data reader. That may help to advance the response beyond the result set so that the return value is available.