Search code examples
stored-procedureslinq-to-entities

I cannot get the output parameter when use function import by Entity Framework


Here's my SQL Server stored procedure :

ALTER PROCEDURE [dbo].[SearchUser]
  (@Text NVARCHAR(100),  
   @TotalRows INT = 0 OUTPUT)   
AS
BEGIN 
   SELECT @TotalRows=1000
   SELECT * from Users
END

And my C# code

using (var context = new TestDBEntities())
{
    var outputParameter = new ObjectParameter("TotalRows", typeof(Int32));
    context.SearchUser("", outputParameter);
    Response.Write(outputParameter.Value);
}

However outputParameter.Value always is null.

Could anybody tell me why?


Solution

  • Output parameters filled by its actual values during the execution of the stored procedure.

    But table-valued stored procedure actually get executed only in moment when you're trying to iterate resulting recordset, but not calling a wrapper method.

    So, this DOES'T work:

    using (var context = new TestDBEntities()) 
    { 
        var outputParameter = new ObjectParameter("TotalRows", typeof(Int32)); 
        context.SearchUser("", outputParameter); 
    
        // Paremeter value is null, because the stored procedure haven't been executed
        Response.Write(outputParameter.Value); 
    
    } 
    

    This DOES:

    using (var context = new TestDBEntities()) 
    { 
        var outputParameter = new ObjectParameter("TotalRows", typeof(Int32)); 
    
        // Procedure does not executes here, we just receive a reference to the output parameter
        var results = context.SearchUser("", outputParameter);
    
        // Forcing procedure execution
        results.ToList();
    
        // Parameter has it's actual value
        Response.Write(outputParameter.Value); 
    
    } 
    

    When you're working with stored procedures what don't return any recordset, they execute immediately after a method call, so you have actual value in output parameter.