How to gets text array data from postgres to c#

Question

I have called function from postgres & output is like

I am using windows form to display data, i have used npgsql for connecting c# and postgres d.b. my BL class function is given below

    public List<User> view_data()
    {

        NpgsqlDataReader rdr;
        List<User> lstUser = new List<User>();

        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        NpgsqlCommand command = new NpgsqlCommand("select * from public.get_user_details", con);

        rdr = command.ExecuteReader();

        while (rdr.Read())
        {
           
            lstUser.Add(new User()
            {
                id = rdr.GetInt16(Convert.ToString(rdr["id"])),
                roleName = rdr.GetString(Convert.ToString(rdr["rolename"])),
                firstName = rdr.GetString(rdr.GetOrdinal("firstname")),
                lastName = rdr.GetString(rdr.GetOrdinal("lastname"))
       
        });

        }
        rdr.Close();

        return lstUser;

    }

Getting error on rolename. error is cannot convert string to string[]

my User class is given below

public class User
{
    public int id
    { get; set; }
    public string firstName
    { get; set; }
    public string lastName
    { get; set; }
    public string email
    { get; set; }
    public string userName
    { get; set; }
    public string[] roleName
    { get; set; }

}

My question is how to assign text array data in c# using npqsql data reader?

Getting error on this area => roleName = rdr.GetString(Convert.ToString(rdr["rolename"]))

postgres gives role name output is text array. ie; in one column contains multiple values. ie; {admin,user}. So need to set postgres text array into c# format using data reader.

You need to change public string[] roleName { get; set; } to public string roleName { get; set; } — MindSwipe
– MindSwipe, Commented Dec 17, 2020 at 15:31
I have changed roleName[] to roleName as you said, but getting error => IndexOutOfRangeException: Field not found in row: System.String[] — Noufal
– Noufal, Commented Dec 17, 2020 at 15:36
postgres gives role name output is text array. ie; in one column contains multiple values. ie; {admin,user}. So need to set text array into c# format using data reader. Can you help me for this. — Noufal
– Noufal, Commented Dec 17, 2020 at 15:42
I'm note well versed with 1. Direct SQL access in C# (I suggest to use an ORM like Entity Framework) and 2. Postgres. But in my SQL experience, multiple values inside one cell breaks the first normal form and instead you should replace it with a relation to a "roles" table — MindSwipe
– MindSwipe, Commented Dec 17, 2020 at 15:59

Stefanov.sm · Accepted Answer · 2020-12-17 19:24:46Z

3

Keep your code as it is but replace the SQL query with this one below. It joins (aggregates) the contents of the roleName array into a single comma-separated list string. So instead of

select * from public.get_user_details

use this one:

select 
  id,
  firstname,
  lastname,
  array_to_string(rolename, ',') as rolename
from public.get_user_details

I have added new lines for readability, you can remove them. And btw select * in production code is more often than not a recipe for trouble.

edited Dec 17, 2020 at 19:24

answered Dec 17, 2020 at 19:05

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to gets text array data from postgres to c#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related