locked
Stored proceedure does not execute using _context.Database.ExecuteSqlRawAsync (@"EXECUTE @ParamOut RRS feed

  • Question

  • User-973886032 posted

    hi guys

    I am using the same thing I used in my MVC applications to execute a SP in my razor pages, it does not throw any errors, but funny enough does not execute, I removed the parameters and tried just executing an empty sp that updates a table and nothing executes

    any advise ?

     var xyz = _context.Database
                    .ExecuteSqlRawAsync  
                                 (@"EXECUTE @ParamOut = [USP_Shops_Tbl_Shopping_Basket2]  {0}", outParam);
    
                var T1 = outParam;
                var T = xyz;
    
    
                var x = _context.Database
                    .ExecuteSqlRawAsync  
                                 (@"EXECUTE @ParamOut = [USP_Shops_Tbl_Shopping_Basket] 
                                    {0} , {1}, {2} , {3} , {4} , {5} "//, {6} , {7},{8} , {9}, {10}"
                 , model.Username, model.ProductID, model.Quantity, model2.IP, model2.Authentication, outParam);
    
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER procedure [dbo].[USP_Shops_Tbl_Shopping_Basket2](@return int output)
    
    as 
    
    
    update [dbo].[Tbl_Shopping_Basket] set Quantity = Quantity - 1  
    
     
    
     
    set @return = 777
    return @return

    Saturday, September 5, 2020 8:22 AM

Answers

All replies

  • User753101303 posted

    Hi,

    In short you need as well to tell expliicity this is an output parameter from the C# code. See for example https://stackoverflow.com/questions/60382040/how-to-utilize-output-parameters-in-ef-core-3-0-using-executesqlinterpolatedasyn

    Return values are procesed the same way with their own direcrion. The number of affected rows is retuned out of the box. Do you want an output paramter or return value or your plan will be really to use all 3 mechanisms ?

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 5, 2020 9:40 AM
  • User-2054057000 posted

    See your stored procedure is not having any parameters yet you are passing it with EF core code. Kindly refer - Execute SQL Stored Procedures using FromSqlRaw() & ExecuteSqlRawAsync() methods in Entity Framework Core

    Your code line is wrong.

     var x = _context.Database
                    .ExecuteSqlRawAsync  
                                 (@"EXECUTE @ParamOut = [USP_Shops_Tbl_Shopping_Basket] 
                                    {0} , {1}, {2} , {3} , {4} , {5} "//, {6} , {7},{8} , {9}, {10}"
                 , model.Username, model.ProductID, model.Quantity, model2.IP, model2.Authentication, outParam);

    Sunday, September 6, 2020 5:01 AM
  • User711641945 posted

    Hi afrika,

    Try to use the following code:

    var outParam = new SqlParameter("@return", 5);
    var xyz = _context.Database
        .ExecuteSqlRawAsync
                    ("EXEC USP_Shops_Tbl_Shopping_Basket2 @return", outParam);

    Best Regards,

    Rena

    Monday, September 7, 2020 8:25 AM
  • User-973886032 posted

    Thanks a lot guys

    I found out the problem, my connection string was pointing to the old database. Hence why nothing was being updated. 

    I am now getting an error with static files, I will post a new thread shortly. thanks

    Monday, September 7, 2020 8:57 AM