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.SearchU开发者_如何学运维ser("", outputParameter);
Response.Write(outputParameter.Value);
}
However outputParameter.Value
always is null.
Could anybody tell me why?
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.
We had a simular issue due to defered excecution our unit tests failed. In short if you have a stored proc that does NOT return anything you need to be sure to set the response type as 'None' when set as 'None' it will be excecuted when called and not defered.
In case you return anything (E.g. Scalar type of String results) it will excecute it when you use the result even if that .Count() or .ToList() is outside of the method that contains the function call.
So try not to force excecution if not need, when needed it should excecute but be sure to declare it correctly or it might not work.
I have same problem before. The main reason I think that the entities framework has the bug in case the user stored procedure has output parameter and return a result set. For example:
ALTER PROCEDURE [dbo].[SearchTest]
(
@RowTotal INT = 0 OUTPUT,
@RowCount INT = 0 OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM SomeThing
SELECT @RowTotal = 1233, @RowCount = 5343
END
However if you change the user stored procedure as following, you can get the output params
ALTER PROCEDURE [dbo].[SearchTest]
(
@RowTotal INT = 0 OUTPUT,
@RowCount INT = 0 OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
SELECT @RowTotal = 1233, @RowCount = 5343
END
You can workaround as following:
ALTER PROCEDURE [dbo].[SearchTest]
AS
BEGIN
DECLARE @RowTotal INT, @RowCount INT
SET NOCOUNT ON
SELECT @RowTotal = 1233, @RowCount = 5343
SELECT @RowTotal AS RowTotal, @RowCount AS RowCount, s.*
FROM SomeThing s
END
If anybody has better solution, please tell me
精彩评论