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
