Return Value Using SqlCommand

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.

6
задан John Saunders 23 March 2011 в 23:38
поделиться