Why stored procedure out parameter is not getting read by entity framework's ExecuteSqlCommand method?

c# entity-framework entity-framework-6 sql sql-server

Question

I am trying to read the OUT parameters value returned by stored procedure but it throws error DBNULL. Even the procedure is returning the value.

Code:

   int? outParam= 0;

   SqlParameter sqlParam= new SqlParameter("@model", user.Name+" Model");
   SqlParameter sqlParamOut = new SqlParameter("out", outParam);
   sqlParamOut.DbType = System.Data.DbType.Int32;
   sqlParamOut.Direction = System.Data.ParameterDirection.Output;

   UsersPhonesDBContext.Database.ExecuteSqlCommand("dbo.InsertPhones @model, @out", sqlParam, sqlParamOut);

   outParam = Convert.ToInt32(sqlParamOut.Value);

SP:

alter PROCEDURE InsertPhones (

    @model varchar(100),
    @out int output
)
AS
BEGIN
        Insert into Phones (Model, Manufacturer)
        Values 
        (
            @model,
            'Hunain Phone'
        )

        set @out= SCOPE_IDENTITY();
        Select @out
END
GO
1
1
1/17/2019 8:22:04 AM

Popular Answer

out parameters in SQL Server are fun. You have to repeat yourself everywhere.

Specifically, here, you've forgotten to mention it in the parameter list when calling the stored proc. dbo.InsertPhones @model, @out should be dbo.InsertPhones @model, @out out. There may also be a way to omit the parameter list from this string and instruct SQL Server that you're giving it a stored procedure name, not arbitrary query text, but I'm not sure how you would do that specifically here (with an SqlCommand object, it would be via the CommandType property).

If you're using an out parameter, the select at the end of the stored procedure is unnecessary, but I assume that was an attempt at a fix.

3
1/17/2019 8:24:55 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow