A
A
alexeysikora2021-12-04 03:58:42
PostgreSQL
alexeysikora, 2021-12-04 03:58:42

How to translate array and jsonb object returned by postgres function to string list and c# dictionary?

I have a function that returns from a database table columns represented by text[] and jsonb types:

CREATE OR REPLACE FUNCTION public.getValues(
    medicamentID uuid)
    RETURNS TABLE("Compound" text[], "DoseInstruction" jsonb)
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
    RETURN QUERY SELECT "Medicaments"."Compound", "Medicaments"."DoseInstruction"
    FROM "Medicaments"
    WHERE "ID" = medicamentID;
END
$BODY$;


I want to store the result of this function in a list of strings (List) and in a dictionary (Dictionary via Json.DeserializeObject). Wrote this code:
Npgsql.NpgsqlCommand pgcom1 = new Npgsql.NpgsqlCommand(@"getvalues", pgcon);
pgcom1.CommandType = CommandType.StoredProcedure;
pgcom1.Parameters.AddWithValue("medicamentid", Guid.Parse(MedicamentsID.Text));
Npgsql.NpgsqlDataReader pgreader1 = pgcom1.ExecuteReader();
List<string> compound = new List<string>();
Dictionary<string, string> doseInstruction = new Dictionary<string, string>(); 
if (pgreader1.Read())
{
    compound = (from IDataRecord r in pgreader1
                            select (string)r["Compound"]
                            ).ToList();
    doseInstruction = JsonConvert.DeserializeObject<Dictionary<string, string>>(pgreader1.GetString(1));
}
pgreader1.Close();
pgcon.Close();


And on the line
doseInstruction = JsonConvert.DeserializeObject<Dictionary<string, string>>(pgreader1.GetString(1));

The compiler throws a "No rows available" error.
How to correctly translate array and jsonb into c# types (received exactly as a result of a database function)? I searched the Internet for 6 hours, I didn’t even get close to the answer, maybe I posed questions incorrectly when searching, but still ...

Addendum: I tried to call the function in the pgAdmin query, the result was given correctly:
SELECT getValues('280ca185-3701-432f-86d0-1209db3f4123');
RESULT:
("{Препарат1,Препарат2}","{""Дети"": ""1"", ""Взрослые"": ""2""}")

Answer the question

In order to leave comments, you need to log in

1 answer(s)
B
Bobsans, 2021-12-09
@alexeysikora

There is an error because you have already read everything here in this place:

(from IDataRecord r in pgreader1
    select (string)r["Compound"]
    ).ToList();

and took from there only "Compound" and "DoseInstruction" flew away to nowhere

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question