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.