I am beginner to asp.net core. I have a working function in my posgres called "GetDriverStatusReport" will return some datas. I would like to retrieve those values in my Asp.Net core application. I tried researching on this but I couldn't get a proper solution. Can someone please tell me the correct of calling the stored procedure in Asp.net Core. Thanks in advance
Here is my Function
CREATE OR REPLACE FUNCTION GetDriverStatusReport(ref refcursor) RETURNS refcursor AS $$
BEGIN
OPEN ref FOR (WITH union_tbl AS(
SELECT
DR."DriverId" AS "Driver Id",
DR."DriverName" As "Driver Name",
0 as "RiderIncome",
0 as "Payable To Rider",
0 as "Recievable Balance",
DR."Amount" as "Amount Paid"
FROM "DriverPaymentReferences" as DR
UNION ALL
SELECT
DF."DriverId" AS "Driver Id",
DF."DriverName" As "Driver Name",
DF."RiderIncome" as "RiderIncome",
0 as "Payable To Rider",
0 as "Recievable Balance",
0 as "Amount Paid"
FROM "DeliveryFinances" as DF
UNION ALL
SELECT
TF."DriverId" AS "Driver Id",
TF."DriverName" As "Driver Name",
0 as "RiderIncome",
TF."PayableToRider" as "Payable To Rider",
TF."RecievableBalance" as "Recievable Balance",
0 as "Amount Paid"
FROM "TaxiFinances" as TF)
Select
"Driver Id",
"Driver Name",
case when
((Sum("Recievable Balance") - SUM("Payable To Rider")) -Sum("RiderIncome")-
SUM("Amount Paid")) > 0
then 'CR'
else 'DR' end as "Cash/Debit",
((Sum("Recievable Balance") - SUM("Payable To Rider")) -Sum("RiderIncome")-
SUM("Amount Paid")) AS "Amount"
FROM union_tbl
GROUP BY "Driver Id","Driver Name" );
RETURN ref;
END;
$$ LANGUAGE plpgsql;
Well, You can try this way,
Let's way I have a stored procedure called SP_GetAllMSManager
like this
PG SQL
CREATE OR REPLACE FUNCTION GetWSManager()
AS $$
BEGIN
SELECT DISTINCT WSManager FROM WsEmployee ORDER BY WSManager
COMMIT;
END;
$$ LANGUAGE plpgsql;
C# ASP.NET Core:
NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=postgres;Password=pwd;Database=postgres;");
conn.Open();
// Passing PostGre SQL Function Name
NpgsqlCommand command = new NpgsqlCommand("EXE GetEmployeePrintInfo", conn);
// Execute the query and obtain a result set
NpgsqlDataReader reader = command.ExecuteReader();
// Reading from the database rows
List<string> listOfManager = new List<string>();
while (reader.Read())
{
string WSManager = reader["WSManager"].ToString(); // Remember Type Casting is required here it has to be according to database column data type
listOfManager.Add(WSManager);
}
reader.Close();
command.Dispose();
conn.Close();
OUTPUT:
If context is the same:
If you want to use same context then you can try this way:
C# ASP.NET Core:
var printJobList = _context.PrinterJobs
.FromSql("EXECUTE GetEmployeePrintInfo")
.ToList();
Note: This is belongs to _context.PrinterJobs
means store procedure
only fetch data from PrinterJobs
table. So I can do above way. But if your stored procedure contains other entities data from join, then first approach can be convenient. You can have a look official document here
PG SQL:
CREATE OR REPLACE FUNCTION GetEmployeePrintInfo()
AS $$
BEGIN
SELECT DISTINCT PrinterId ,PrinterName,PrintedBy,TotalPrint FROM PrintJob
COMMIT;
END;
$$ LANGUAGE plpgsql;
Hope it would help you accordingly.