Use EntityFramework Core to call a MSSQL StoredProcedure and show the data returned --

asp.net-core-webapi ef-migrations entity-framework entity-framework-core sql-server

Question

Well, the question is self explanatory. So I won't unnecessary expand it. Here is my SP --

    CREATE procedure [dbo].[sp_TotalSalesByCustomerID]
    @customer_id nvarchar(50),
    @varSale decimal OUTPUT  
    as

    begin

    begin try

    if exists (
            Select * from tempdb..sysobjects Where id = 
       object_id('tempdb.dbo.#temp')
          )
        DROP TABLE #temp

    else

        select * into #temp from Orders where customer_id=@customer_id

        select @varSale=SUM(total_price) from #temp

    end try

    begin catch
           select error_message(),
           error_severity(),
           error_number(),
           error_state()    
    end catch

    end

As you can see, the SP basically returns just a calculated value. It is not mapped to any entity object/class in my application. The C# code on my backend web api side that I wrote --

[Route("TotalSalesByCustomerID")]
    [HttpPost]
    public JsonResult TotalSalesByCustomerID([FromBody]string customer_id)
    {
        try
        {
            //var salesT = (_appDbContext.Totals.FromSql("call sp_TotalSalesByCustomerID(@customer_id)", customer_id));
            var salesT= _appDbContext.Database.ExecuteSqlCommand("sp_TotalSalesByCustomerID @p0", parameters: new[] { customer_id });
            return Json(salesT);
        }
        catch(Exception exp)
        {
            return Json(exp.GetBaseException());
        }
    }

I don't know what codswallop rubbish it is returning. Probably because it is not mapped to an entity class object. I understand that. Probably some of the C# code is wrong too. I don't need a separate class too. It is just a one value! If I add a class I'll be required to probably make migrations too and that is unnecessary in my scenario. Just for reference, this is my migration builder class too, where I added my sql code in the 'Up and 'Down' --

protected override void Up(MigrationBuilder migrationBuilder)
    {
        var sp = @"CREATE procedure [dbo].[sp_TotalSalesByCustomerID]
                    @customer_id nvarchar(50),
                    @varSale decimal OUTPUT  
                    as

                    begin

                    begin try

                    if exists (
                                Select * from tempdb..sysobjects Where id = object_id('tempdb.dbo.#temp')
                              )
                            DROP TABLE #temp

                    else

                            select * into #temp from Orders where customer_id=@customer_id

                            select @varSale=SUM(total_price) from #temp

                    end try

                    begin catch
                        select error_message(),
                               error_severity(),
                               error_number(),
                               error_state()    
                    end catch

                    end";

        migrationBuilder.Sql(sp);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        var sp = @"CREATE procedure [dbo].[sp_TotalSalesByCustomerID]
                    @customer_id nvarchar(50),
                    @varSale decimal OUTPUT  
                    as

                    begin

                    begin try

                    if exists (
                                Select * from tempdb..sysobjects Where id = object_id('tempdb.dbo.#temp')
                              )
                            DROP TABLE #temp

                    else

                            select * into #temp from Orders where customer_id=@customer_id

                            select @varSale=SUM(total_price) from #temp

                    end try

                    begin catch
                        select error_message(),
                               error_severity(),
                               error_number(),
                               error_state()    
                    end catch

                    end";

        migrationBuilder.Sql(sp);
    }

I don't know what to do. Surely I am missing some nice trick or some hack here that will do it. What is it? Can I make some kind of virtual/abstract entity that I can just use in EF Core to get my data instead of creating tables, adding migration etc to my DB? What other options? I look forward to some good help on this. Thanks All,

1
-1
8/30/2018 5:10:31 PM

Accepted Answer

You have to use the output parameter instdead of return value like the below code.

var outputParam = new SqlParameter("outputParam", SqlDbType.Int) { Direction = ParameterDirection.Output };
await _databaseContext.Database.ExecuteSqlCommand($"EXEC {storedProcedureName} @param1, @outputParam output", new SqlParameter("param1"), outputParam);

To get the value you need to call outputParam.Valueafter above two lines.

The return value of ExecuteSqlCommand() is an integer that is the number of records affected by the query.

0
8/30/2018 5:55:45 PM


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