Retrieve Stored Procedure Output Parameters in Ent

Question

C# code

public List<searchProducts_Result> GetProducts()
{
            var nameParam = new ObjectParameter("numbers", typeof(int));
            List<searchProducts_Result> listobjs = new List<searchProducts_Result>();
            ObjectResult<searchProducts_Result> objResult = null;
            searchProducts_Result outParam = new searchProducts_Result();
            using (var db = new SPWebEntities())
            {
                objResult = db.searchProducts("asd", 2, 5, "15", nameParam);
                if (nameParam.Value != null)
                    outParam.UserID = nameParam.Value.ToString();
                else
                    outParam.UserID = "0";
               listobjs.Add(outParam);
                foreach (searchProducts_Result sr in objResult)
                {
                    listobjs.Add(sr);
                }
            }

            return listobjs;
}

My stored procedure:

[searchProducts]
    @productName varchar(50),
    @pageStart int=2,
    @pageEnd int=4,
    @result varchar(MAX),
    @numbers int output
as
   select @numbers = COUNT(*)  
   from products 
   where productName like @productName 

   select *
   from (select 
            *,
            ROW_NUMBER() over (order by ID) as row 
         from products 
         where productName like @productName) a 
   where a.row between @pageStart and @pageEnd

nameParam.Value It's always return null value

When I execute the stored procedure in SQL Server Mgmt Studio, it appears that it has a value but in c# its always null

Answer 1

You have to enumerate your result in order to actually execute the stored procedure. Try this:

public List<searchProducts_Result> GetProducts()
{
   var nameParam = new ObjectParameter("numbers", typeof(int));
   List<searchProducts_Result> listobjs = new List<searchProducts_Result>();

   // List<searchProducts_Result> objResult = null;

   searchProducts_Result outParam = new searchProducts_Result();
   using (var db = new SPWebEntities())
   {
      // by calling ToList() you execute the SP
      List<searchProducts_Result> objResult =
          db.searchProducts("asd", 2, 5, "15", nameParam).ToList();

      if (nameParam.Value != null)
         outParam.UserID = nameParam.Value.ToString();
      else
         outParam.UserID = "0";
      listobjs.Add(outParam);

      foreach (searchProducts_Result sr in objResult)
      {
         listobjs.Add(sr);
      }
   }

   return listobjs;
}