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,
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.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow