Call Stored Procedure and return Json in ASP.Net Core MVC 3.0

aminsoraya
 
on Nov 26, 2021 08:52 PM
Sample_138785.zip
5990 Views

hey

today I config the entity framework core 6 and fetch he data list succefuly

but i call the stored procedure and it returned -1 why?

app.MapGet("/PlantsInfo", ([FromServices] ModelContext db) =>
{
    var tr = db.plants.ToList();//succefully
    return db.Database.ExecuteSqlRaw("exec GetPlants");//returend -1
}

i can't see ExecuteSqlCommand option

alter proc GetPlants
as
begin
    set nocount on
    select
        Plants.Id [id],
        Plants.Img [img],
        Plants.Title [title],
        Plants.Description [description],
        Plants.Benefical [benefical],
        PlantGroups.PlantName  [categories],
        Plants.Weight [weight],
        Plants.Price [price],
        Plants.[Date] 
     from Plants  inner join PlantGroups
    on plants.PlantGroupsId=PlantGroups.Id
    for json path
end

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Nov 29, 2021 12:22 AM

Hi aminsoraya,

ExecuteSqlCommand is not available in Core 3.0. So you need to use ExecuteSqlRaw with OutPut Parameter.

So you need to return the Json as OutPut Parameter from the stored procedure.

Refer below example.

Database

I have made use of the following table Customers with the schema as follows.

I have already inserted few records in the table.

You can download the database table SQL by clicking the download link below.

Download SQL file

Stored Procedure

CREATE PROCEDURE Customers_GetJson
    @Json NVARCHAR(MAX) OUTPUT
AS
BEGIN
    SET @Json = (SELECT * FROM Customers
    FOR JSON PATH)
END

Namespaces

using System.Data;
using Microsoft.EntityFrameworkCore;
using Microsoft.Data.SqlClient;

Controller

public class HomeController : Controller
{
    private DBCtx Context { get; }
    public HomeController(DBCtx _context)
    {
        this.Context = _context;
    }

    public IActionResult Index()
    {
        string query = "EXEC dbo.Customers_GetJson @Json OUTPUT";
        SqlParameter param = new SqlParameter("Json", SqlDbType.NVarChar, -1)
        {
            Direction = ParameterDirection.Output
        };
        this.Context.Database.ExecuteSqlRaw(query, param);
        string json = Convert.ToString(param.Value);

        ViewData["Message"] = json;
        return View();
    }
}

View

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    @if (ViewData["Message"] != null)
    {
        @ViewData["Message"];
    }
</body>
</html>

Screenshot