Search code examples
c#postgresqlstored-proceduresasp.net-core-webapi

How to get data from PostgreSQL function in ASP.Net core


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;

This is the data i get after calling the above function in PgAdmin4


Solution

  • 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:

    enter image description here

    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.