Getting out parameter of stored procedure with C# only works when I put a breakpoint in my code

Question

I have database connection setup with Entity Framework. I've created multiple stored procedures and one of them has an output parameter that I need in my application.

Procedure in c# :

public virtual ObjectResult<Nullable<System.Guid>> AjouterProfesseur(string prenom, string nom, ObjectParameter identity)
{
        var prenomParameter = prenom != null ?
            new ObjectParameter("prenom", prenom) :
            new ObjectParameter("prenom", typeof(string));

        var nomParameter = nom != null ?
            new ObjectParameter("nom", nom) :
            new ObjectParameter("nom", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Nullable<System.Guid>>("AjouterProfesseur", prenomParameter, nomParameter, identity);
}

To retrieve the output parameter I use following (this is also where I have to put my breakpoint and step over it for it to work):

public static Guid AddProfesseur(string prenom, string nom)
{
        using (ExamenProjetIntegrationEntities db = new ExamenProjetIntegrationEntities())
        {
            ObjectParameter objParam = new ObjectParameter("identity", typeof(Guid));
            var resultToReturn = db.AjouterProfesseur(prenom, nom, objParam);
            return  Guid.Parse(objParam.Value.ToString());
        }
}

Then I have a business layer who calls that method again :

public static Guid addProfesseur(string prenom, string nom)
{
        try
        {
            var data = Data.AddProfesseur(prenom, nom);
            return data;
        }
        catch (Exception e)
        {
            var sqlex = e.InnerException as SqlException;

            if (sqlex != null)
            {
                switch (sqlex.Number)
                {
                    default:
                        throw new Exception(sqlex.Number + " - " + sqlex.Message);
                }
            }

            throw e;
        }
}

And finally in my API controller I'm using following statement :

var idProfesseur = BL.addProfesseur(professeur.prenom, professeur.nom);

I call the method with ajax in my ASP.NET MVC view.

Does anyone has any idea why this is and how I can solve this issue?

EDIT :

Following link is exactly what I'm doing : Executing SQL Stored Procedure with Output Parameter from Entity Framework .But my problem is the fact that I need to step through it for it to work

I may be off base here, but shouldn't you refer to `resultToReturn` somehow? — 500 - Internal Server Error, Dec 16 '19 at 21:06
I mean the output parameter is written in my SQL stored procedure. Entity framework created the first method based on that stored procedure. I don't know if that helps — Jeremy, Dec 16 '19 at 21:17
The return value from your AddProfesseur seems wrong. You're looking at the right value in the debugger, but returning the wrong value from the method (it seems). Something like return Guid.Parse(resultToReturn.Value.ToString()); — JP Alioto, Dec 16 '19 at 21:24
Does this answer your question? [Executing SQL Stored Procedure with Output Parameter from Entity Framework](https://stackoverflow.com/questions/22068027/executing-sql-stored-procedure-with-output-parameter-from-entity-framework) — Jeremy Lakeman, Dec 17 '19 at 00:04
@JeremyLakeman It doesn't, because it does exactly what I'm doing. My problem is not that I don't get the Guid because I do, but only when I step through my function.. — Jeremy, Dec 17 '19 at 07:20

score 1 · Accepted Answer · answered Dec 17 '19 at 17:02

Output parameters are not available until the return result has been read completely, or the underlying DbDataReader is closed.

See the Parameters section of ObjectContext.ExecuteFunction, and the second sentence of this section on DataReaders

You can force this by evaluating the result with LINQ.

public static Guid AddProfesseur(string prenom, string nom)
{
        using (ExamenProjetIntegrationEntities db = new ExamenProjetIntegrationEntities())
        {
            ObjectParameter objParam = new ObjectParameter("identity", typeof(Guid));
            var resultToReturn = db.AjouterProfesseur(prenom, nom, objParam).Count();
            return  Guid.Parse(objParam.Value.ToString());
        }
}

You could also use .ToList(), .FirstOrDefault(), or whatever method you prefer to read the underlying result.

score 0 · Answer 2 · answered Dec 17 '19 at 00:04

0

Using the underlying SqlParameter would require .Direction = ParameterDirection.Output. ObjectParameter doesn't have an equivalent setting. Instead I believe the parameter direction is configured in your model.

answered Dec 17 '19 at 00:04

Jeremy Lakeman

9,515
25
29

score 0 · Answer 3 · answered Aug 25 '23 at 08:32

This might not be the solution for all but I found if I disposed the ObjectResult returned from the stored procedure call, the output parameter values became available. Would need extra logic if the values in the ObjectResult were still needed.

ObjectParameter outputParam= new ObjectParameter("outputparam",typeof(string)); 

ObjectResult spResult = db.sp_check_dup_hostname_or_ips(param1, param2, param3, param4, outputParam);

spResult.Dispose();
           
string myOutputString = Convert.ToString(outputparam.Value);

Getting out parameter of stored procedure with C# only works when I put a breakpoint in my code

3 Answers3